Tulip Tables Architecture and Tagging/Flagging

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.

Thoughts on this approach?

Hi James,

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:

  1. Can you explain what the goal of this cycle count is and what cycle it is counting?
  2. Are you looking to get the count of kits have not been returned or checked in the last 12 months?
  3. How often is the count supposed to be executed?