Total calculation based on unit price and UDF field values

[Former Member]

Hi All,

This is for a construction related company. Please find attached screen shot for details.

My scenario is when we are creating sales order my client wants to calculate the total amount based on unit price towards the UDF fields defined. For example user will give the unit price  per sqft in Unit price ie 2800 as per screen shot column and constructed area in Sqft in he will enter the area like cons area in sqft =1500. So this 1500 need to calculate with unit price and will display in total. So in Total field it should show like 2800*1500= result. Like wise other amount values in udf needs to be calculated and displayed in total field  .

Is this possible in SAP B1. Please help me with your valuable suggestions.

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT (T1.[U_Price]* T0.[U_CArea])+ $[ORDR.U_EBexp.number]+ $[ORDR.U_RO.Number]+ $[ORDR.U_Maintenance.Number]+  $[ORDR.U_CorpFund.Number]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

I tried below query but i am getting  below error

SELECT (T1.[U_Price]* T0.[U_CArea])+$[ORDR.U_EBexp.number]+$[ORDR.U_RO.Number]+

$[ORDR.U_Maintenance.Number]+$[ORDR.U_CorpFund.Number]

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '+'.

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT ($[$38.U_Price.number]* $[ORDR.U_CArea.number)+ $[ORDR.U_EBexp.number]+ $[ORDR.U_RO.number]+ $[ORDR.U_Maintenance.number]+ $[ORDR.U_CorpFund.number]

Thanks & Regards,

Nagarajan

[Former Member]

Hi All,

Thank you Augusto

In my scenario only the constructed area (s shown in above screen shot) should multiply with unit price per Sqft and other amounts should be added with that value.How can i make it possible in sap b1.Can any one help me with the query.

Thanks

[Former Member]

Hi Sri,

You can use Freight for additional income. create freight codes for other additional income & map respective account in freight setup.

go to

Administration-->Setup-->General-->Freight

& create freight codes for additional Amounts.

regards,

Raviraj

[former_member186712]

Hi Sri,

In B1, on the line level and if you don't have discounts, Qty * Price = Line Value.

You can't have something like qty * price per Sqft = Value multiplied UDF.

This is not possible.

My sugestion is that you create a UDF on the line level, where the user types the Price Sqft and in the B1 price you can create a FS with the query Like I told you before.

SELECT $[$38.U_LineUDF1] * $[ORDR.U_df2] * $[ORDR.U_df3] ...

In this case you will have qty in the B1 price the multiplied value and in the line total the total of the line.

Then you could print your documents has you wish.

Regards,

Augusto

[Former Member]

Hi Augusto,

As suggested i created a UDF in row level "price per Sqft" just before the unit price

created an FS in the "Unitprice"

Below Query is my requirement

SELECT T1.[U_Price]* T0.[U_CArea]+T0.[U_EBexp]+T0.[U_RO]+T0.[U_Maintenance]+ T0.[U_CorpFund] FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

I saved this query in FMS ,but i dint get any result in the unit price column.Please guide me with the right query

[former_member186712]

Hi Sri,

Try this

SELECT ($[$38.U_Price.number]* $[ORDR.[U_CArea].number)+ $[ORDR.U_EBexp.number]+ $[ORDR.[U_RO.number]+ $[ORDR.[U_Maintenance.number]+  $[ORDR.[U_CorpFund.number]

I think this works

Regards,

[former_member186712]

Hi Sri,

Did you try this query? There was an error in my 1st post.

[Former Member]

Hi Augusto,

I am receiving below error after running this query in sql.Could u please correct  it

SELECT ($[$38.U_Price.number]* $[ORDR.[U_CArea].number)+ $[ORDR.U_EBexp.number]+ $[ORDR.[U_RO.number]+ $[ORDR.[U_Maintenance.number]+  $[ORDR.[U_CorpFund.number]

Incorrect syntax near '$38.U_Price.number'.

[former_member186712]

Hi Sri,

Do you have a field in the lines called U_Price?

The names of the UDF's fields are correct?

In the Sales Order form and filled with 1 line and with all the UDF's filled

Go to Tools - Queries - User queries and select your query

After this

Post the query here

[Former Member]

Hi Augusto ,

Fieldname U_price is correct.I saved the query you given under user queries and assigned FMS.I am using header level udfs also.Now i am receiving the below error internal error

[Former Member]

Hi Augusto,

Please find the below query taken from my user queries

SELECT ($[$38.U_Price.number]* $[ORDR.[U_CArea].number)+ $[ORDR.U_EBexp.number]+ $[ORDR.[U_RO.number]+ $[ORDR.[U_Maintenance.number]+  $[ORDR.[U_CorpFund.number]

[former_member186712]

Hi Sri,

Did you do what I asked?

In your form like it is in the picture

Go to Tools - Queries - User queries and select your query.

B1 has to bring the values that you have on the form.

That's what I would like to see.

Regards,

[former_member186712]

Maybe I found the problem

Try this

SELECT ($[$38.U_Price.number]* $[ORDR.U_CArea].number)+ $[ORDR.U_EBexp.number]+ $[ORDR.U_RO.number]+ $[ORDR.U_Maintenance.number]+ $[ORDR.U_CorpFund.number]

[Former Member]

We are selling , flats , plots commericial buildings ec, for that purpose we have created udf called land area in sqft, con area in sqft etc

[former_member186712]

Hi Sri,

You need to create a UDF in the document lines and then the price will have the total price of the line.

The best way to achieve what you need is doing a small program with SDK.

Other way but not the perfect way is using a Formated search in the Price field.

This is how the FS looks like:

SELECT $[$38.U_LineUDF1] * $[ORDR.U_df2] * $[ORDR.U_df3] ...

Hope it helps,

Augusto

[Former Member]

[kothandaraman_nagarajan]

Hi,

Please reattach above sales order screen with readable size.

Thanks & Regards,

Nagarajan