0

Product of related fields before sum in NL(Table)

Reinder Wolting

June 13, 2018 14:05

None

Follow

On sales order lines in NAV there is a cost price and quantity field, but not a total cost field. You need to multiply cost and quantity to get the cost PER LINE.

If you want to get the total cost of PER SALES ORDER, you need to do that multiplication on each line before you can total anythnig on the order.

I want to use NL(Table) to get me some sales orders with the total cost per order on the same line.

NL(Table) has the LinkSum option in the fields, but you can only use 1 field in it. Is it possible to multiply 2 fields in NL(LinkSum) before summing them?

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis June 13, 2018 14:18

  Perhaps, something like this?

  

  

• 0
  
  Reinder Wolting June 13, 2018 20:23

  Thx for your reaction Harry, but that is not the issue.

  I would like to use Sales HEADER as the main source for my NL(Table).

  To have a total of an order from the Sales LINES you can use a field like NL("LinkSum";"Sales line";"Sales LCY")

  Because the is no field containing the total cost in the Sales line table, you need to multiply Quantity and Cost amount, before you summarise them.

  To be clear, you can't use 2 NL(LinkSum) functions and use your formula, because the result would not be corrcect.

  Say you have 2 lines with on the first Quantity 1 and Cost amount 1000 and the second with Quantity 1000 and Cost amount 1. The correct sum would be (1*1000)+(1000*1) = 2000. If you use 2 separate NL(LinkSum) fields and multiply those you will get (1+1000)*(1000+1) = 1.002.001

  I can't find a way to have 2 fields multiplied in a NL(LinkSum).

  Anyone?

   

• 0
  
  Harry Lewis June 26, 2018 15:26

  In that case, my suggestion would be to create a report using a structure other than NL(Table).  While NL(Table) is extremely powerful, you would have more flexibility to accomplish your specific task using NL(Rows).