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.

Further work on the fault analyser (PrioXRef) - beware of linked files

I mentioned the other day a 270 line Priority procedure that I used as a test for the cross referencer. This procedure was at the proof of concept stage: it did what it was supposed to do but made several assumptions. I improved it to be more general, a conceptual change that required many changes throughout the procedure, that amongst other things builds a new purchase part with specific characteristics. At the end of the procedure, a purchase order is created that includes this new part. As such, the part needs to have a price in the supplier's price list based on the base part of the new part - whilst creation of the purchase order was part of the POC procedure, addition of the part to the price list wasn't.

After adding the appropriate code for the price list, I saw that it wasn't working, leading to a painful debugging session before the penny dropped. In the query that retrieved the price of the base part, I was using the table PART and this was what was causing the problem - PART was linked and of cause the actual part that I was using wasn't in the linked table. What made this error particularly galling was that I had already documented this problem.

As happens frequently these days, I woke up in the middle of the night. After dealing with my bladder, the first thought that popped into my head was that PrioXRef doesn't deal with 'UPDATE' and 'DELETE' statements; the second thought was how I could get the program to deal with the linked table problem. Unfortunately, this problem occupied my mind for quite some time and I had to take cognitive steps to banish it so that I could resume sleeping.

At the moment, I don't think that there is a way of showing this as a fault. How is PrioXRef supposed to know that the part that I am using as a parameter is not in the linked table? Of course, the problem would not exist if I had the internal part number of :SEARCHNAME and then I wouldn't have to include the PART table in this query. 

/* Get price from supplier's pricelist */                          
SELECT SPARTPRICE.PRICE, SUPPRICELIST.SUPPLDATE, 
SUPPRICELIST.SUPPLIST 
INTO :PRICE, :SDATE, :SLIST                                                                                       
FROM SUPPRICELIST, SPARTPRICE, PART, SUPPLIERS                      
AND SUPPRICELIST.SUP = SUPPLIERS.SUP 
WHERE SUPPRICELIST.SUPPLIST = SPARTPRICE.SUPPLIST                   
AND SPARTPRICE.PART = PART.PART                                     
AND SUPPLIERS.SUPNAME = :SUPNAME                                    
AND PART.PARTNAME = :SEARCHNAME 
ORDER BY 2 DESC;  

The only idea that I have at the moment is to display a warning like "[line number] warning: PART is linked". In fact, whilst PrioXRef knows that on line X that table PART is linked and is then unlinked on line Y, it can't use that information as it ignores SELECT statements. That's not exactly true: SELECT is not a keyword that triggers some handling, but INTO and FROM are such keywords. I should add some code to the FROM handling that checks whether any of the following tables are stored in the binary tree of identifiers. The FROM code should continue to be in effect until the next keyword is found (there may not be a WHERE clause, ORDER BY or GROUP BY in the query). PrioXRef has to check that the token following each part is not an alias (the above problem was solved by aliasing PART which causes the real table to be joined).

This might require a rewrite of the tokeniser as at the moment it does not save the token terminator (this would be a comma in the above example) and so getting the alias may cause the next table name to be obscured. On the other hand, there is code that pushes the next token back into the current text line to be parsed if it's not the expected token. Rewriting the alias detection code should be rewritten and changed to be a function so that it can be called at several different places.

A few people have received PrioXRef, but I have yet to receive any comment bar 'Thanks' and 'Nice idea'.