An exercise in how NOT to build private forms

I was recently asked to look at a form that someone (with whom contact has been lost) had defined that displayed purchase order lines belonging to a single vendor; the users wanted to update a private field in the form but were not succeeding in doing so.

I had been asked maybe a month earlier to program for the same customer the same kind of form (purchase order lines of a given vendor) so I wasn't prepared for the mess that I saw. I can understand why the person who programmed this form has disappeared, for it is an excellent example of how not to build a private form and shows many misunderstandings.

Probably the biggest error was not basing the form on PORDERITEMS but instead on a private table. Had the form been based on the standard table then adding a field would have been simple: add the field to the table and then to the screen. But no: this form was based on a private table that of course had a primary key based on PORDERITEMS.ORDI. But not only that: the current user was also part of the primary key! In other words, I could open the form and you could open the form and the possibility exists that we might be seeing different data!

The private field that the customer wanted to be able to update was held in this private table (the person who added the field [not the original programmer] presumably saw on what the table the form was based and so added the field to this table). I didn't see at first that the form was based on this table; I assumed that it was based on PORDERITEMS and so I added a form post-update trigger that would update the private table if the private field were changed. This didn't work. Changes in the field were maintained as long as the form was displayed on the screen, but would vanish when the form was reopened. Eventually I found the reason for this: the form has a PRE-FORM trigger that first empties the private table for the current user then enters the appropriate values for PORDERITEMS.ORDI. 

Excuse me????? What is this rubbish? Such a convoluted structure for something that should be far simpler. In the end, in order to satisfy the customer, I added the required field to PORDERITEMS and updated this (by placing the table/column name combination in the field continuation) instead of using the same field in the private table.

A further request for modifying the existing form was to add the connected customer order if one exists. I saw that already there was some connection to ORDERITEMS so I didn't need to add this; I added the appropriate fields and ran the form. The form would not open. I removed the fields that I had added and reopened the form - it worked properly. I then took a much closer look at the form definitions and saw that ORDERITEMS had been defined with the identifier (alias) 5! (the exclamation mark was not part of the identifier). This is correct if one is adding fields to a table in a standard form, but is totally unnecessary if one is doing this in a private form. This is a clear example of someone not understanding the SDK. Naturally the field that I had added did not have this identifier; adding it allowed the form to be displayed.

Another problem with this form became clear when the customer explained that users can change the status of the connected purchase order from within this form (that's why they thought that the private field that they added was capable of being updated from this form). Maybe the code for this was correct - I didn't waste much time in looking at it - but it was clear that the most basic check had been ignored. First check that the field holding the status had been modified and only then update the order status! But no, this trigger blindly modified the status over and over again (if I remember correctly, this abomination involved touching the table several times, a complete waste of time). Any kind of trigger like this should start as follows

GOTO 99 WHERE :$1.<FIELD> <> :$.<FIELD> ;
/* update field */
LABEL 99;

If at the beginning of my examination I wanted to put a gun to the head of the person that programmed this atrocity, by the end I wanted to put a machine gun there. This sort of programming gives independent programmers a bad reputation.

 

So what are the lessons to be learnt?

1. If one wants to display data from an existing table, it's best to base the private form on that table and define the form either as Q (read only) or N (no deletions). One can add private forms either to the standard table or to a private continuation form; doing the former 'contaminates' the standard table (although this is condoned) whereas the latter requires programming a trigger to update the private table.
2. There is no need to use the 5 identifier when one is working on a private form; this is required only when adding new tables to a standard form (or report).
3. Check whether a field has been modified first before writing code to update its value in the database.