Just putting this out here as an unfinished thought, to drive some discussion.
I ended up creating two types of flags/tags to attribute to records in different tables to keep track of what table records are associated with a given event, and which records have already been handled in the current app session. I’m calling these tags “Event ID” and “Session ID”.
Basically I have a site list table, and when I start the event (an inventory cycle count for that site), I write the Event ID to a corresponding field for the site record. Then, as my app loops through all the inventory master data records (surgical loaner kits with discrete IDs) associated with that site, it tags those with the event ID, and also with the app session ID. The counting app creates one child record for each master, for the current cycle count event, and assesses an initial count status of counted (set master data indicates set checked in date within last 12 months) or not counted (blank or date >12 months past).
On subsequent app sessions, any newly created set master data records associated with the site are also captured (based on their not having been tagged with the Event ID). Also, child records that had an initial status of not counted can be reassessed to see if they have been located and checked in since (causing a more recent date to be populated into the master data table, which is referenced in the trigger logic). All child records assessed in the session are tagged with a Session ID, which is used to exclude them from further looping in the current session, otherwise the loop array would never get emptied.
I meant to answer earlier but needed to spend some time reviewing your question to make full sense of what you are trying to achieve. Despite having discussed your app with you a few times already, I am not clear on what you are looking to achieve during the inventory cycle count.
When it comes to your specific question, I assume the reason you use those 2 fields is because of trigger limitations you need to retrigger the logic, and therefore use a Session ID to filter out items that have already been processed. Is that correct? If so, this seems to make sense however that logic is quite complicated, understanding more of your use case might help propose a simpler solution.
I think it would help to get back to your base requirements as well as the current design of your app, then explain what are you trying to achieve. My understanding is that you are building a Surgery Loaner Kit Inventory Tracking to track the check in and check of the kits:
For each kit, track when kit was picked up and returned
You have built application(s) that let user check kits in and out.
Other applications lets visualize the status of the kits.
Information tracked in Surgery Loaner Kit Inventory table (Physical Artifact) similar to this one:
- ID
- Type
- Status
- Last Check Out Date
- Last Check Out By
- Last Check In Date
- Last Checked In By
- Site
- Notes
I am assuming I am grossly under-representing what you are trying to achieve, so please correct me when I am wrong. Then regarding the inventory cycle count:
Can you explain what the goal of this cycle count is and what cycle it is counting?
Are you looking to get the count of kits have not been returned or checked in the last 12 months?
Yes, this is correct - the Session ID is used as a flag to exclude records that have already been processed (included in the query aggregation result that drives the looping trigger logic) in the current app session.
You are pretty close - we actually don’t capture the “check out” details you mentioned in Tulip, this activity is handled by an existing system. The use case was very narrow to close a specific audit observation around the lack of durable documentation of who completes the check-in process for which sets and when - the other system only retained that information for one cycle and would overwrite it the next time the set was checked in.
<3> The cycle count currently needs to be completed once per year. In practice we are proposing that each site initiate their cycle count by running the app for the first time sometime in Q1, and seek to have all uncounted sets resolved (either counted or documented as not countable, with the set master data tagged as inactive an the reason for inactivation documented) before Thanksgiving (as after that would be a challenge due to vacations, etc.)
<2> The app displays analyses on various steps showing the progress toward completion, which is defined as no sets remaining without a final status assigned, and that the sum of the “sets counted tally” and “sets confirmed as not countable” tally equals the total number of sets associated with the cycle count event.
<1> Surgical loaner kits vary in their “popularity” as well as the number of replicates, and some sets may be so infrequently used that they might not be requested at all in a given year. Sets can also be transferred to hospitals as consignment inventory, transferred to other territories/sites, returned to the Operating Company, or decomissioned (i.e. if fully depreciated and no longer needed). Our enterprise standards require either a wall-to-wall inventory count, or a cycle count approach to be executed annually to ensure any inventory variance is documented.
