Skip to Content
0

Help in FMS Query to automate ITEM Based Delivery Note

Aug 16, 2017 at 08:42 AM

40

avatar image
Former Member

Dear Experts

I need a FMS query which calculate 10 % along with the Item cost , and get auto posted the amount in Unit price in Delivery note, and this is only for 1 or 2 customer which i wish to assign.

i had try with a coding, but it gives me an error,

'1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand data type nvarchar is invalid for multiply operator. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.'

pls help me to edit my coding

Regards

select ($[DLN1.StockPrice]*$[DLN1.Quantity])+(10/100)
FROM DLN1
INNER JOIN ODLN ON DLN1.DocEntry = ODLN.DocEntry
WHERE ODLN.CARDCODE = 'RB00000689'
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Balaji Selvaraj Aug 16, 2017 at 09:13 AM
0

Hi Shahan,

This will calculate the 10% from item cost as per warehouse and add with the item cost

SELECT (($[$38.37.number]) * 0.10) + ($[$38.37.number]) where ($[ORDR.CardCode] ='C20000' or $[ORDR.CardCode] ='C30000')

Show 8 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Dear Bala

Thanks for the Support

i just modified you query like this, but when i select other customer other then to whom i had assign in the query, its not showing the unit price in the field

what will be the issue,

Regards

SELECT (($[$38.37.number]) * 0.10) + ($[$38.37.number]) where ($[ODLN.CardCode] ='RB00000689')
0

You can use "IN" operator

where $[ODLN.CardCode] IN ('RB00000689' , 'RB00000690' )

else OR as i had mentioned

where ($[ODLN.CardCode] ='RB00000689' or $[ODLN.CardCode] ='RB00000690' )
0
Former Member

Hi bala,

i try with one single customer, by assigning his card code in fms query using IN

fine the calculation is working, for that customer, but if i add any other customer which i dint need this Criteria to be effected. then the unit price field will be blank, when i click on the Magnifying glass icon , it return me 0.00

Regards

0

Use Case statement

0

try this

After else i am selecting the item cost

If $[ORDR.CardCode] IN('C20000','C30000') Begin SELECT (($[$38.37.number]) * 0.10) + ($[$38.37.number]) End Else Begin SELECT $[$38.37.number] End
0
Former Member
Balaji Selvaraj

Dear Bala

thankfull to you Support

Can u give me the full code, i try to update using you script, as am not an expert in SQL,

it give me an error 'Internal Error'

Regards

0
If 

$[ODLN.CardCode] IN('C20000','C30000') 

Begin 

SELECT (($[$38.37.number]) * 0.10) + ($[$38.37.number]) 

End 

Else 

Begin 

SELECT $[$38.37.number]

 End
0
Former Member
Balaji Selvaraj

Dear Bala

Thanks for all the support and Help

It finally works Perfect.

Regards

0
avatar image
Former Member Aug 16, 2017 at 09:43 AM
0

Dear Shahan/Vindo,

Please try the below FMS .Check the item cost field where it is because as per my FMS configuration is itemcost as per warehouse.

SELECT (T0.[Avgprice]*10/100) FROM OITW T0 WHERE T0.[WhsCode]='Saudi' and T0.[ItemCode]=$[DLN1.Itemcode] and $[ODLN.Cardcode] IN ('C00013','C00014')

Thanks

Ajith

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi ajith

Thanks for the support

I try with the same. when i select the assign customer in FMS query, its returning the % cost

for eg:- if an item cost is 70, its 10% should be add and get auto post in Unit price, here what happen is

its calculating 10% of 70 i.e 7 and in unit price filed 7 is appearing insted of 77

Regards

0
Former Member
Former Member

Try this

SELECT (((T0.[Avgprice]*10/100))+(T0.[Avgprice]) FROM OITW T0 WHERE T0.[WhsCode]='Saudi' and T0.[ItemCode]=$[DLN1.Itemcode] and $[ODLN.Cardcode] IN ('C00013','C00014')

0
Former Member

Dear ajith

Its fixed

0