I’m posting from Bow and Stern IT professional services - we are a Tulip system integrator currently working on a work instructions project within the life sciences industr.
Having built a table based work instructions app, we have encountered an issue when attempting to delete old records of defunct instructions/procedures.
As this method uses a COUNT table aggregation method to determine which task should be loaded next, we have found that it is not possible to delete table records for tasks/procedures/instructions that are no longer being used, as this will cause the wrong tasks to be loaded due to the table aggregation being affected.
Aside from having an option to set the tasks/procedures as active/inactive, is there a way to get around this issue to allow us to keep the tables containing the actual instructions clean? Or will we need to accept that there may be junk records in that table that we will need to filter out through the app?
I want to take a step back and ask a bit more about the Work Instructions use case here. What led you to choosing a table-based approach?
There can be times where this approach is viable, but it can lead to monolithic apps and table architecture, which hinders citizen development and digital transformation due to their complexity.
That being said, regarding you original question - there are 3 main ways to delete table records. Is there an option to delete old WI from the table directly, that way they do not make it into the app? Thinking from the operator perspective, they should only have the current, active WIs available to them and not have to worry about selecting and deleting old WIs from the app themselves.
The app solution utilises a Kit builder app in addition to the Work Instructions app and this is where we see this issue come up - if a supervisor wants to remove a task, instruction or procedure, the table aggregations used to load/create records will be affected, causing incorrect instructions to load.
At present, the supervisor would need to set an instruction/procedure/task as inactive in order to remove it from view in the runner app (rather than deleting the actual record) - this isn’t a huge issue, but just wanted to throw this out there in case there is a quick solution that will allow us to keep the tables a bit tidier.
If you are set on using a table based approach, setting a field to inactive has the advantage of never loosing the definition of the work instructions, and is probably a good solution to your current design. Since you need to have a management app to configure the work instruction, this app will keep the data tidy to the users (users should not have to see the entire database). It does require to modify your queries to use the new active/inactive field though, adding a bit more complexity to the app.
Why do you see the need in deleting those entries from the table?
Not knowing the design of your app, it is not clear what the best alternative solution might be (except for looking into a more composable design as recommended by Beth), however one solution that is often used it to use a Sequence field: it can be used to order the work instruction definitions, and help find the ID of the next one with a query/aggregation. This way let’s say you have 3 instructions with Sequence 5,6,7; when #6 is deleted, you can still retrieve the ID of #7 by looking the the next work instruction with Sequence higher than 5 (make sure to set Limit field to 1 as shown below)
In regards to our need for deleting the entries in the table - in all honesty, we did not have a specified reason beyond wanting to keep the data tidy, especially in a scenario where instruction kits may only be used for short periods of time before becoming obsolete. However, if there is no clear downside to keeping these records in the tables (and marking them as inactive) then we are happy to stick to that solution.
Many thanks, I think we can move forward with the option to just mark old instructions/tasks as inactive.
