FMS on Sales Order UDF-Title level
We have a FMS on a UDF Title Level of a Sales Order which computes for the sum of the Line total, Freight 2, Freight 3 and Tax amount and subtracts with any amount on the FreeTxt field. Here's the SQL query:
select (select sum(($[$38.21.number] + $[$38.112.number] + $[$38.116.number]+$[$38.120.number])/NullIF($[$38.11.number],0))
- sum(case when isnumeric($[$38.163.0]) = 1 THEN convert(decimal(19,2),$[$38.163.0]) Else 0 end)
where DocEntry = (select DocEntry from ORDR where DocEntry = $[ORDR.DocEntry]))
What i notice is that when we use the field name (like linetotal) instead of the dynamic syntax ($[$38.21.number]) it gives us the correct total but we need to use the dynamic syntax instead since we want to have the result to update/popup even if the sales order has not yet been saved/added in the system. Will this be possible? Is there anything wrong in the code above?