cancel
Showing results for 
Search instead for 
Did you mean: 

Query with FMS field not updating

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

This query gives me the data I want but I'm getting duplicates?

SELECT T0.[ItemCode], max(T1.[DocDate]) AS 'Last Sale Date', T2.[LastPurDat], T2.[OnHand], T2.[U_OBItem], T3.[AvgPrice] FROM INV1 T0  INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode WHERE T2.[OnHand] >0 AND (T2.[U_OBItem]  IS NULL OR T2.[U_OBItem] ='N') GROUP BY T0.[ItemCode], T0.[U_ObsoleteItem], T2.[OnHand], T2.[U_OBItem], T3.[AvgPrice], T2.[LastPurDat] ORDER BY T0.[ItemCode]

Any ideas as to why I'm getting duplicates?

Richard

ganeshram_v
Explorer
0 Kudos

Hi Richard,

Need to link the warehouse try this.

SELECT T0.[ItemCode], max(T1.[DocDate]) AS 'Last Sale Date', T2.[LastPurDat], T2.[OnHand], T2.[U_OBItem],

T3.[AvgPrice] FROM INV1 T0  INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2

ON T0.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode and T0.WHSCODE = T3.WHSCODE

WHERE T2.[OnHand] >0 AND (T2.[U_OBItem]  IS NULL OR T2.[U_OBItem] ='N') GROUP BY T0.[ItemCode], T0.[U_ObsoleteItem], T2.[OnHand], T2.[U_OBItem], T3.[AvgPrice], T2.[LastPurDat] ORDER BY T0.[ItemCode]

Thanks,

Ganesh

Former Member
0 Kudos

Hi Richard

Try using max or avg for T3.AvgPrice. You are not grouping by warehouse, but the OITW will have the same item more than once, for each warehouse. Or maybe base all prices on 1 specific warehouse. Also try using SELECT DISTINCT ...

Kind regards

Peter Juby

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Where did you get last sales date for that UDF? Do you have any FMS for that UDF?

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Nagarajan,

The FMS for U_lastSale is a query:

SELECT max(T0.[DocDate]) FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] = N'1'

When item master data is opened and an item record found, the field U_LastSale is updated.

I am trying to run the query where the U_LastSale field displays the Last Sale date of each item in the query but the results for the query below shows U_lastSale field data as when the item record was last accessed and not the most up to date data from OINV.

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]


Can both queries be combined in some way?


Thanks, Richard

ganeshram_v
Explorer
0 Kudos

Hi Richard,

AR Invoice is not created for the item then also you need the output from this combined query?

Thanks,

Ganesh

Former Member
0 Kudos

Hi Ganesh,

I don't understand your advice really? Basically there are fields in OITM I want to display in a query along with the last date each item was sold. The Item record updates a UDF field by FMS from OINV & INV1 when entering into the item master data record. So Can I run a query using OITM fields combined with the max DocDate from OINV for each item in the query?

Thanks, Richard

Former Member
0 Kudos

Hi Richard,

Your query is a normal query, not a FMS query. Could you post your screen to show what you need?

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Yes it is a normal query with a FMS based UDF in the results, U_LastSale. Look at my reply to Nagarajan to see what I am trying to achieve. Any ideas please advise me.

Thanks, Richard