Major restructuring in INSIGHT v16.158 Clark Wilkins, Simplexable 2017.12.16

When INSIGHT began in 1997, it was a simple Filemaker-Pro based inventory tracker. In v3, we switched to open-source Unix with PostgreSQL and a new structure called "attachments" was created. The first attachment designed was type 1: "this inventory item is attached to this job".

Since a discrete item cannot be attached to two jobs at once, the design always made sure that only one such attachment existed for each item, and hence, we had a problem. In many cases, we would link an item, bill the job, and on the invoice, show a link from a billed item (invoice line) back to the item. The problem was that an item could be attached, billed, and then removed. The linkage would be gone, and subsequent updates would not "know" about the item because it was no longer part of the attached items set.

I gave a lot of thought to this and even started designing a second attachment that would not be torn down if the item was moved off the current job, but this didn't get far. The item:job attachment logic is used in lots of places, and things got messy.

I finally had an idea that became v16.158. What if we made a permanent ("this item was/is on this job") as well as a possibly semi-permanent link ("this item is currently on this job"). That's what will happen going forward. When we attach to a job, a second (transient) link is made that indicates current attachment. If the item is moved off the job, this gets cleared, but the original type 1 remains. This update was made on all INSIGHT installations today. All routines that need to know about current attachment status (around 85%) now use the new structure, but all that need to know if/when the item was ever on the job, rely on the original type 1 attachment. This took a lot of editing and checking glue logic — approximately 35 patches system-wide — but it's now in place and running.