Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Not all fields updated...
Microsoft Dynamics RMS (Archived)

Not all fields updated when stores sync with HQ

(0) ShareShare
ReportReport
Posted on by Trudy 245

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

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Not all fields updated when stores sync with HQ

    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.

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Not all fields updated when stores sync with HQ

    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.

  • Trudy Profile Picture
    Trudy 245 on at
    RE: Not all fields updated when stores sync with HQ

    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?

  • Spencer McCandless Profile Picture
    Spencer McCandless 2,085 on at
    RE: Not all fields updated when stores sync with HQ

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans