Query for Unit Commision

[Former Member]

I am trying to write a query show the per unit commission for an item on a Sales Order and put that value into a UDF.

I have got as far as getting a result on the document for a specific row, but I cannot get it to select for all rows on the document.

What I have is as follows:

SELECT (T0.Price* T0.Commission/100)

FROM RDR1 T0 INNER JOIN ORDR T1 ON T0.DocEntry = T1.DocEntry

WHERE T1.DocNum = $[$8.0] AND T0.ItemCode = $[$38.0]

It is the last part where I am trying to specify by itemcode that is proving troublesome. I have used a specific line number instead of ItemCode and that works.

Can someone help me on this?

Rick Winn

[former_member583013 · Accepted solution]

The syntax for ItemCode on the marketing document is $\[$38.1.0]

You simply need

SELECT ($\[$38.17.Number]*$\[$38.28.Number]/100)

38.17 refers to Price After Discount column and 38.28 refers to the commission column

[Former Member]

How do I get 2 decomal places for the second query?

[former_member583013]

Please try this.

SELECT (CAST($\[$38.17.Number] AS DECIMAL(8,2))*CAST($\[$38.28.Number] AS DECIMAL(8,2))/100)

[Former Member]

Thank You very much,

Rick

[Former Member]

Thank you worked perfectly.

Rick