We have a user story that mandated that we combine all Open and Closed Order data. Due to partial orders/refturns/refunds we did an exhaustive review of upsert routines and identifiers and ultimately needed DAILY purges of Open Orders with daily updates in order for our reports to be accurate. So hmm..well.. what to do?





First...we opened a support ticket requesting Admin capability to schedule record deletion... and that is frowned upon... then we asked for Ops to delete data daily based off a custom identifier... (fingers crossed) ... and then we started to try to get creative since a due date for this is of course... yesterday..





Here are some facts about the scenario and then the solution, maybe someone can think of a way to improve/consolidate or perhaps leverage part of this approach?





Two files on S3: OpenOrders, Closed Orders





One data object: AllOrders





Three Rules to rule them all (sequentially and in rule chain, please review rule 2 prior to 1)





1) Null/Clear Records identified by boolean "Remove" flag: create an action to update the AllOpen object with ONLY GSID as identifier, then add CUSTOM FIELDS for EVERY field in your table and click to "set them to null" and filter on the "TRUE" value





2) Insert Open Records: When inserting records daily, we also set a "Remove" boolean field on the "All Open" object to TRUE





3) Insert Closed Records: Staright up insert, no fuss, no muss





Ultimately we satisfy our current user stories to combine our Order data and prep our environment to potentially receive less frequent data purges and have all set an identifier for all records going forward and keeping the most relevent data in front of our CSMs.