Formatted Search - Invoice & Credit Memo

[Former Member]

I am trying to create a formatted search that calculates commission when an invoice or credit memo is added. We have a UDF where commission is entered when creating the Sales Order (U_CommPct).

Essentially, I need the following calculation to occur when the invoice or credit memo is added    

(Total Before Discount * U_CommPct)/100

[former_member211473 · Accepted solution]

Hiiii  

i wrote this SQL query which satisfies your requirement ,  u will have to do little manual work like first add the invoice and then update udf to get the desired result . i would like some SQL EXPERTS to convert this SQL query into fms .

SELECT ( (((T0.DocTotal - T0.VatSum) - T0.TotalExpns )  * $[oinv.u_comm] ) / 100 )   FROM oinv t0  WHERE T0.Docnum = $[oinv.docnum.number]

Regards,

Ranu

[Former Member · Accepted solution]

Thank you!!!! I used the following....

SELECT ((T0.DocTotal - T0.VatSum - T0.TotalExpns) * $[OINV.U_CommPct]) / 100 From OINV T0 WHERE T0.Docnum = $[OINV.DocNum.Number]

I don't know why the other one was giving me such a hard time; it was similar to what I had come up with on my own. Would the UDF type being set as percents (%) cause the error???

[kothandaraman_nagarajan]

Hi,

Try this:-

($[$22.0.Number] * $[OINV.U_CommPct])/100

Thanks.

[Former Member]

I'm still getting an error message.

Incorrect syntax near '500' (price before discount) FMS execution failed on U_CommPct

[kothandaraman_nagarajan]

Try this,

SELECT ($[$22.0.number] * $[OINV.U_CommPct])/100