Announcements
Hi All
I am using RMS version 2. I have a problem when data is transferred/synced using worksheet style: 401 Request data upload from store to HQ; not all fields are being updated -lastsold and lastreceived fields are not being updated for some items. These fields are being updated on some items but not for all items.
I have not been able to figure out why it would be updated in one item and not in another.
Has anyone ever experience anything like this? or know what might be the cause?
Would really appreciate any help.
Trudy
*This post is locked for comments
Oh, I realized today when doing some similar update statements that the update statements I posted are incomplete, because the lastsold comparison wont find items with null values in their lastsold blanks. The where clause at the end of each query should be changed to
WHERE (item.lastsold < correct.lastsold OR item.lastsold IS NULL)
and
WHERE (item.lastreceived < correct.received OR item.lastreceived IS NULL)
Sorry for the omission. It shouldn't have caused any problems, but it also wouldn't have updated the values of some items.
We're running 2.0.0155 as well at the moment, but I believe the last sold/last received issue has been present for longer. I think as far back as 2010 (before I was really comfortable using SQL) MS support gave me a statement to update the values in the item table using data from the itemdynamic table before running something to make a bunch of items that hadn't been sold in several years inactive.
I'm not sure about the Valuation report. We've never really utilized it, and looking at the raw text I see that it calls a procedure to build the view that it queries, and I'm not sure of how to get at the contents of the procedure to see what tables it fills from. If you want to check if it is related to the lastsold/lastreceived issue, I think you could (after backing up, no liability, yada yada) try running something like
UPDATE item SET item.lastsold = correct.lastsold FROM item JOIN (SELECT MAX(itemid) AS itemid, MAX(lastsold) AS lastsold FROM itemdynamic GROUP BY itemid) AS correct ON item.id = correct.itemid WHERE item.lastsold < correct.lastsold
and
UPDATE item SET item.lastreceived = correct.lastreceived FROM item JOIN (SELECT MAX(itemid) AS itemid, MAX(lastreceived) AS lastreceived FROM itemdynamic GROUP BY itemid) AS correct ON item.id = correct.itemid WHERE item.lastreceived < correct.lastreceived
on the Headquarters database to bring the values up to date, then try running the report again and see if it fixes the problem.
Thank you so much. I've been busting my brains and writing a lot of queries trying to find some connection.
The problem actually stemmed from my client indicating that the values in the Inventory Valuation report in HQ does not match the values when you run the same report in Store ; the opening and closing quantities are different for each department. I saw some posts that mentioned this report getting screwed up with feature pack 2 but I'm using version 2.0.0155 Is this a bug this version as well?
For some reason those values stopped being properly updated in the items table with an update released a few years back, if I recall. The itemdynamic table, which contains data for each store, is updated with the correct data for the respective locations, but the most recent one isn't entered into item.lastsold and item.lastreceived as you would expect. It's particularly annoying because several of the built in headquarters reports still reference the values in the item table. You can update the data with a sql statement, but it will only stay correct until the next time an item is sold. The only solution I can think of would be to schedule a sql statement to automatically run periodically to bring these values up to date, which is a really dirty way of doing it and shouldn't be necessary for a commercial product.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,807 Super User 2024 Season 2
Martin Dráb 229,135 Most Valuable Professional
nmaenpaa 101,156