Scripting

I have a v2017 Premium client that wants a snapshot of the Physical Count Variance Register written to a new table. I thought about doing a shadow table, but they want it to ONLY populate this new table when they run the PC Variance Register. So first, has anyone done anything similar to this that may have suggestions on best route? And if not, any guidance to how/when the work table is populated that would allow me to grab data from it?

Tami and I were talking about this for another client as well. We discussed a Mirror Table, and Knowledgesync. Knowledgesync can monitor the Work table and create a csv file (if they have workflow) or create an email for the snapshot.

Samantha, when the worktable is being populated there is a function that can be used to do whatever you want prior to when it writes a record to the table. Unfortunately, it would entail a modification to to PC Variance Register to do this. I can be done though.

On Premium, the Work table is actually a temp table in TEMPDB instead of MAS_XXX db. Also the table name itself will start with a # sign which means presumably the data inside it is visible only to the session that created it. Not sure about that but regardless you can test K-Sync and see if it's visible for you. My Part 2 answer was to do what @MarcosDeluna suggested.

Well shoot @AlnoorCassim You are spot on! The Work Table didn't populate, that being said, the IM_Physical Table is monitored and has all the info needed (Qty Counted, Qty On Hand, Change) So you can monitor that table and append a csv file (every minute if needed) with the data. See attached, I change the qty of the top item, and got the results as shown. *Costs are available as well, but I didn't include them in this, only because I didn't think about it :) )

Well done!