The third way

I have written before about requirements that seem to be mutually incompatible. I faced another problem like that yesterday and I want to share its solution. I call this kind of solution "looking for the third way" that requires us looking past the blinkers on our eyes.

The factory floor has a report that prints out a list of work orders, along with a barcode that encodes the work order, activity and quantity. The report works well as such but has problems in defining which work orders should be printed: the upholstery department receives a list of customer orders and has to obtain the work orders connected to these. The department was informed verbally of the required orders; seeing as the order numbers were derived from a report, I suggested that the department be sent this report - as Excel (ugh) - and then paste the order numbers into the orders form in order to get the appropriate work orders (the procedure takes care of this).

So far so good. Unfortunately, the report containing the order numbers is produced on a per-line basis and so the same order number can (and does) appear on several consecutive lines. The orders form cannot accept such a list of numbers, rejecting the duplicates on the basis that "key already exists". Here is the mutual incompatibility: on one hand, we have a list of customer orders that may include duplicates, whereas on the other hand we have a form that requires discrete order numbers.

It didn't help that the Excel file was created from the HTML report, making it useless, as opposed to creating an Excel file directly.

The solution came to me whilst walking the dog at 5:30 am this morning. The real problem is that the orders form cannot accept duplicates; the insight is to do away with the orders form, replacing it with a form that can accept duplicates. Such a form would be based on a table that is not linked to the orders table - I defined for it three fields (user, line and 'ordname') and built a form on this basis where the primary key is composed of the user and line number. The 'ordname' field has a post-field trigger that stores the user number and current line into the appropriate fields, thus the same order number can be entered several times - each line will have a different internal number.

I wrote a procedure that displays an initial parameters form, including a check box as to whether the user wants to input order numbers from Excel. Assuming that this check box is marked, my special form will then be displayed into which the user can paste the numbers from Excel. Upon closing the form, a piece of code inserts the distinct order numbers into a linked orders tables. This is then used as the basis for querying the work orders.

Conclusion: try to see past the constraints of the system! Sometimes I tear my hair (what's left of it) when I hear some of the requests from the CEO, but he has an advantage on me in that he doesn't know what is seemingly impossible to do with the system. He is often right, and requires me to think out of the box, or see past the blinkers.