Determining what records changed

[Craig Tooker]

On 10/23/14 01:45, Mike Fedkiw wrote:
> Okay, I've been thinking about this for a while now and what I come up with is pretty much nothing.
>
> I have a website with my inventory displayed on it and I want to update the on hand, add new items and remove any deleted items every hour throughout the day. The web developers say that's not an issue to automate that process but they also said that it's really not feasible to update the entire inventory file containing roughly 20k records in it on an hourly basis.
>
> I don't know if it's possible for the website to save the last uploaded file and compare it to the new file being uploaded and compare them to see what changed...but if it was that easy, hell, just compare the qty on hand to see what changed.
>
> What I've been thinking about is somehow using the last updated date to determine which items may need updating, but still, it's pretty easy to find a flaw there. What if after the last update of the day, something is sold before the date changes to the next day? Well now it's a new day and that item's qty changed the night before and has the previous days date so it's possible it could think it's not necessary to update it. Hell Idk, I think I confuse myself more every time I think about this.
>
> I also thought about adding some additional fields in the inventory file and copying the on hand, price, description, etc to those fields so I could compare the next time the file is generated and I can catch any changes and tag records needing updated. Yea, well what if for some reason that particular file doesn't make it to the website for whatever reason and another one gets generated an hour later. Now there's a ton of records that probably need to be updated but won't because the fields I added were updated with the file that ran before this one that never made it to the website.
>
> Now that I don't even know what I'm writing about anymore, someone must have had to do something similar this before, I hope.
>
Mike,

Simply identify each process you have that touches the quantity fields 
in the inventory file.  Update those processes to additionally maintain 
a 'delta' qualifier in the inventory file.  After all updates are done 
to the record in the main file (and before you release the lock on that 
record), copy that record to the 'delta' qualifier.  By 'copy' I mean 
create the record if the item does not exist in 'delta' or update that 
record if it does exist.  The 'copy to' processing command can be used 
for this function.

When it is time to update the website, just export all the records in 
the 'delta' qualifier and then delete all the records in that qualifier.

If this 'batch' creation process needs to run whilst the 'main' file is 
being actively updated you will need to maintain two levels of 
qualifiers.  The first, 'delta' is updated when a lock flag is off and 
is just as I described above.  When the batch process is run that 
exports data from that qualifier, first set the 'lock' flag.  The 
processes above that update 'delta' first check the 'lock' flag and if 
set, update instead the 'lock' qualifier.  At the end of the batch 
exporter, it unlocks the 'lock' flag and then also exports and deletes 
any records in the 'lock' qualifier.

If you also have an 'in' batch for inventory from the website, this 
should be processed *before* the exporter is run to make sure the 
website always has the most up-to-date quantities.

Craig
> Adding fields to inventory so I can catch the actual time of any updates is not really an option. There's so many processes that have access to and change the on hand, I'm not even sure I could find all of them at this point.
>
> Mike's head hurts now, Lol
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list
>