Skip to Content
Jun 03, 2014 at 02:58 PM

Query with FMS field not updating


I have the following query;

SELECT T0.[ItemCode], T0.[ItemName], T0.[OnHand], T1.[AvgPrice], T0.[OnHand]*T1.[AvgPrice] AS 'Stock Value', T0.[LastPurDat], T0.[U_OBItem], T0.[U_LastSale], T0.[ItmsGrpCod] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[OnHand] > 0 AND (T0.[U_OBItem] IS NULL OR T0.[U_OBItem] = 'N') ORDER BY T0.[U_LastSale]

But the UDF U_LastSale is not displaying the date unless the record is updated before the query is run. Is there any way the UDF can be updated? I have tried updating a field via DTW then run the query but the UDF still isnt updated unless entering into the item record and updating from there. I have over 5000 item records to update the UDF. Is this due to the functionality of FMS or can the items be forced to update in some way if the ItemCode is known?

Thanks, Richard