Solving two conradictory requirements

In the past, there have been two outside customers that were granted permission to connect to our database, but only to see their data. In order to satisfy this requirement, I turned on the 'Authorisation for Sales Reps' option and gave them the authorisation to see anything with their agent number. In order to compensate for this, I marked for all our regular users the 'All Sales Reps Auth?' flag in the Details of Current Company form. So far so good.

I was given a thorny problem to solve a few weeks ago: someone who works with our company had full access to the database, but he has been 'demoted' and now works only as an agent dealing with after sales orders (e.g. a customer wants an extra table or similar). For this capability, he should have the Authorisation for Sales Reps' option marked for his agent number and the 'All Sales Reps Auth?' flag unmarked. But he also has to see orders of other agents so that he can deal with their installation when required.

On the face of it, then, this person has to be able to both see only his orders and to see all orders: two contradictory requirements. For some time (a week), I deliberated how I could achieve this; I wasn't sure that it was a problem that could even be solved. Then I had a brainwave (in the shower, of course, where all good ideas come): I could write a procedure that marked the 'All Sales Reps Auth?' flag via an interface, then all the required orders could be saved in a temporary table, then the permission would be revoked. This sort of worked, but when I tried it under the person's username, I got no data. I realised that this was that by the time the user came to look at the report containing all the required orders, he was no longer be able to see them as the permission to do so had been removed.

The solution then was to divide this procedure into three stages: the first would give the permission, the second would show the report (i.e. the orders) and the third would retract the permission. Despite trying very hard, I couldn't get the third part to work which was very frustrating. I had also missed the fact that this person could do whatever he wanted to do with these orders when displayed, which was not part of the mission requirement.

A day later, the perfect solution popped into my mind. Instead of being fixated on a procedure and a linked report, I should display the data in a form that would be based on the ORDERS table, but allowing write access only to the fields that this person was allowed to change. Also, many fields that appear in the ORDERS form wouldn't have to be displayed thus greatly simplifying the form's logic. The magic in this form comes from something that is documented but that most people would never need. On page 80 of the SDK for version 23.0 appears the following

PRE-FORM triggers perform operations before the form is opened. This applies
to all root forms, as well as sub-level forms for which the Automatic Display
column of the Sub-Level Forms form (a sub-level of the Form Generator) is
blank. This type of trigger may be used, for example:
 to reset the value of a user-defined variable
 to generate a warning or error message concerning retrieved data
 to retrieve and display all records when the user opens the form —
:KEYSTROKES = ‘*{Exit}’;
 to refresh all retrieved records in a form following a Direct Activation —
:ACTIVATEQUERY = 1;
 to deactivate data privileges in a form — :$.NOCLMNPRIV.T = 1;
 to deactivate data privileges for a specific table in a form: in a new PRE-FORM 
trigger for the form in question, define the :$.NOTBLPRIV.T variable
with the name of the desired table; if the table you want to exclude has a
join ID, this should also be specified

In other words, if I include in the pre-form trigger the command $.NOTBLPRIV.T = 'AGENTS' then the user would not be limited to seeing only his orders but could see everyone's (where a different field contained his order order). This didn't work, but swapping this command with :$.NOCLMNPRIV.T = 1 did.

So I managed to solve two conradictory requirements.

Multi-company forms

Yesterday I was asked to create a multi-company form. I know how to create a multi-company report but I had yet to create a form like this. Here are the conditions for a multi-company report:

• A displayed column, with a Column Name of TITLE and a Table Name of
ENVIRONMENT.
• A hidden column, with a Column Name of DNAME and a Table Name of
ENVIRONMENT. Its Expression/Condition should be: = SQL.ENV

Naively, I added these fields to the form. As it happens, my customer wanted the company name to appear anyway, so ENVIRONMENT.TITLE was necessary. When I opened the form, there was massive duplication of data (a cartesian join); basically there were no conditions on the Environment table which is why everything appeared several times.

I then turned to the SDK; as it happens, I have a new copy of the SDK for version 25.1 that documents many things that previously were not documented, but unfortunately there is very little about multi-company forms. There is a section heading that reads simply To prevent users from defining a given form as a multi-company form, specify x in the Oneto-many column, but it doesn't mention exactly how to create one as opposed to preventing one. There are a few more mentions about multi-company forms but these are about variables and not relevant.

That one sentence did give me an idea, though. I went to the one-to-many column in the form header and saw that there was an option 'm' that defines a multi-company form. Choosing that option gave me the message 'There must be a field called ZOOMDNAME, a character string of length 8 characters. I guessed that this should replace the field with the column name DNAME (even though the field name is still DNAME as this is the name of the field within the ENVIRONMENT table), and that the TITLE column should be replaced by ZOOMDTITLE. 

Lo and behold, the cartesian join disappeared and the correct data appeared. So this functionality is still undocumented. There are at least 20 standard forms with the 'm' flag (the actual number depends on the version) so one can learn from these.

Saving a part's picture by means of an interface

The scenario: a part is copied from company A to company B, along with various fields, including the path to the part's picture. The picture has a special status in the table PARTEXTFILE: the value of the field EXTFILENUM will be -1.

In the procedure that has been running for the past few years, the path to the picture has been obtained correctly, but was being stored as a regular entry in company B. The person responsible for company B had never brought this subject to my attention until now. When I saw that the picture was being saved as a regular entry, I tried to overcome this by passing the value -1 in the appropriate tuple in GENERALLOAD. This did not work.

My next attempt was a typical hack: find the value of EXTFILENUM for the part in company B and update it to -1. This worked, but obviously was not the correct way of solving the problem. Fortunately sanity was restored when I asked myself how regular saving of the picture works in the form LOGPART. This is when I discovered that the path is stored in the form variable EXTFILENAME, and that the trigger BUF11 causes this value to be stored as a picture in PARTEXTFILE (below is the standard code).

/* Insert, Update and Delete EXTFILENAME */
GOTO 111 WHERE :$.EXTFILENAME = :$1.EXTFILENAME
;
/* clean previous record */
DELETE FROM PARTEXTFILE
WHERE PART = :$.PART
AND EXTFILENUM = -1
;
GOTO 112 WHERE :$1.EXTFILENAME = '';
EXECUTE DELATTACH '-I', :$1.EXTFILENAME;
LABEL 112
;
/* insert new attachment */
GOTO 111 WHERE :$.EXTFILENAME = '';
INSERT INTO PARTEXTFILE (PART, EXTFILENUM, EXTFILENAME)
VALUES (:$.PART, -1, :$.EXTFILENAME)
;
LABEL 111;

Note the use of the procedure DELATTACH: this can cause the actual file to be deleted if it is not referenced anywhere. Saving the file parth in PARTEXTFILE is done by an insertion, thus enabling the value -1 to be set; doing this via an interface doesn't work.

Now I save the file path as the equivalent to :$.EXTFILENAME in the same tuple as the part name before passing it to the interface. And this works.

Unprepared forms

There are two rules that one has to know when preparing forms: one is obvious and one is less obvious. The obvious rule is that a form has to be prepared after adding a field or a trigger to it (a form does not have to be prepared if a procedure or report is added for direct activation).

The less obvious rule is that if one adds a table to a form, a table that was not previously defined on that form, then every form that is built on that table has to be prepared.

An example: I was asked to add the status of a purchase order line to the form DOCPORDI ('choose order items', a sub-form of DOCUMENTS_P, 'Goods Receiving Vouchers'). This form is built on the PORDERITEMS table; the order line status description can be found in PORDISTATUSES, and one has to use the PORDERITEMSA table to connect between these two. Thus one would add the following lines to the form

PORDERITEMS.ORDI = PORDERITEMSA.ORDI, identifier 5?
PORDERITEMSA.PORDISTATUS = PORDISTATUSES.PORDISTATUS (identifier 5 on both sides)
PORDISTATUSES.PORDISTATUSDES to be displayed, identifier 5

First of all, the identifier 5? is used to introduce the PORDERITEMSA table - 5 because it is not an original table of this form, and ? because not every line in the PORDERITEMS table has a corresponding line in PORDERITEMSA (e.g. not every line has a status). The identifier 5 has to be used with PORDISTATUSES because this table too is not original.

Obviously, DOCPORDI has to be prepared, but also any form built on the tables PORDERITEMSA and PORDISTATUSES has to be prepared (i.e. the forms PORDISTATUSES, PORDIORDI and ORDPORD), even though no changes have been made to these forms. If there are many forms that have to be prepared then of course it's best to run the 'prepare all unprepared forms' program.

How does one know specifically which forms have to be prepared? This is a question that I have often wanted to ask, and a few weeks ago I found the answer. A simple SQL query would be

SELECT EXEC.ENAME
FROM EXEC, EXECPREPLOCK
WHERE EXEC.EXEC = EXECPREPLOCK.EXEC
AND EXEC.TYPE = 'F'
AND EXECPREPLOCK.UPD <> 'N'
FORMAT;

I have built a very simple report based on this query and added it to the scheduler so that I would receive a report every 15 minutes if there is an unprepared form anywhere in the system. I have found that it's better to reprepare any forms that appear in this report as opposed to simply preparing  them. I don't know what the difference between preparing and repreparing is, except that the latter is 'stronger' and takes more time. This always reminds me of a scene in the film 'A few good men', when Demi Moore objects to something in the trial, then strenuously objects. This results in a sarcastic remark from Kevin Pollak (I forget the exact wording); if preparing a form doesn't work then reprepare it.

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.

Improving links in messages generated by a BPM

Priority has several BPM form that manage the various statuses of a document (customer order, purchase order, etc). One can define that messages can be sent according to rules defined on the BPM form, e.g. send a message to user X when an order reaches a given status. One can also include pre-defined fields in these messages, e.g. order number, so that clicking on the order will open the appropriate form (e.g. customer orders) with the appropriate order.

What happens when one wants a different form to be opened automatically? As opposed to the form manager or report manager, there is no possibility of defining a target form for the predefined field. How does one overcome this?

I've never thought about this before, but presumably every field that appears as a predefined field in the rules manager is displayed on the given form. I checked a somewhat obscure field that appears on the purchase orders screen and is defined as read-only; this field appears in the list of predefined fields for addition to a message.

So, it seems quite probable that adding a private field to a form, e.g. adding a copy of the field PORDERS.ORDNAME to the PORDERS form, where this field is defined as read-only and has a defined target form would solve my problem. I could add this new field to a message sent by a rule, click on the message received and have the new target form open.

Unfortunately not. Whilst adding the private field to the form allows it to be attached to the message that is sent by a rule, the order number comes without a link and so is valueless. This requires more thought and experimentation.

Logging changes in bypass forms

I am often asked to create 'bypass' forms: this is my term for a form that is based on a standard table but only exposes a few fields. The most common example is a bypass form for customer orders; assume that the order is at a status that does not allow changes, but the user would like to change a few non-essential or private fields. The bypass form exposes those fields but is not bound by the order's status.

There is a relatively simple means of logging changes in this form so that they can be seen in the 'real' orders form.

:DETAILS = '';
:SONEXEC = 0;
SELECT EXEC INTO :EXEC
FROM EXEC
WHERE ENAME = 'ORDERS'
AND TYPE = 'F';
:CHARKEY1 = ITOA (:$.ORD);
:CHARKEY3 = :CHARKEY2 = :CHARKEY4 = :CHARKEY5 = ''
;
GOTO 71 WHERE :$1.DETAILS = :$.DETAILS;
:OLDVALUE = :$1.DETAILS;
:NEWVALUE = :$.DETAILS;
:COLNAME = 'DETAILS';
#include func/UpdateChangesLog
LABEL 71;

The first part of this code (upto the line containing a single semicolon) sets up necessary data so that the changes will be written to the standard orders form changes log. If one wants to use these lines for a different form, then ENAME will have to be the name of the target form. ORD is of course the number of the order being edited.

The second part checks whether a change has been made in the field 'details'; if so, the original value and the new value are saved along with the column name. The column name will appear in the change log the same way in which it is defined in the form.

There is a very large 'gotcha' in that last sentence. One of the fields displayed in the bypass screen is a private field added to the ORDERS form that is based on another table (i.e. like displaying CUSTOMERS.CUSTNAME). The column name for this field in the ORDERS form will be something like TEST_CUSTNAME. But in the bypass form, there's no need to use four letter prefixes as the form is private, so I had defined this column as CUSTNAME.

Changes in this field were not being logged - or rather, they were being added to the CHANGES_LOG table but were not appearing in the change log form. After a bit of head-scratching, I realised that the column name (:COLNAME) should be the name of the column as it appears on the 'real' form and not how it appears on the bypass form. Once this seemingly minor change had been made in the trigger code, changes to the CUSTNAME field now appeared in the change log.

Incidentally, if one wants to log changes in a bypass form for orderitems, the prologue code becomes

:DETAILS = '';
SELECT EXEC INTO :EXEC
FROM EXEC
WHERE ENAME = 'ORDERS'
AND TYPE = 'F';
SELECT EXEC INTO :SONEXEC
FROM EXEC
WHERE ENAME = 'ORDERITEMS'
AND TYPE = 'F';
:CHARKEY1 = ITOA (:$.ORD);
:CHARKEY3 = ITOA (:$.ORDI);  /* This is the order line number */
:CHARKEY2 = :CHARKEY4 = :CHARKEY5 = ''
;

Misleading error message

I developed a private form several years ago that displays values of private constants. When I open this form, the error message 'Value exceeds permitted quantity' appears; I press on this three times and it disappears whilst the data is being displayed. As this sort-of works and I'm the only person who accesses this form, I hadn't given much thought as to what could be the problem.

The same thing happened at one of my external clients: they want to load an external file then display the contents in a 'load form' for visual checking before converting the data into invoices. The load form displays this 'value exceeds permitted quantity' error message and clicking on it a few times doesn't help. I originally thought that this behaviour was due to some system constant being too low - the example file has 1220 lines. Changing various system constants didn't make any difference.

Last night I decided to solve this problem once and forever. My first act was to delete the form entirely - maybe something had contaminated it - then redefine it. The data displayed in the form all come from the same table, with no joins, so 'programming' the form was a simple matter. Retrieving all the data caused the same error message; I had to close the form via the task manager. I then retrieved the first record - it displayed correctly. The first ten records display correctly. The first hundred records display correctly. The first seven hundred records display correctly. 

It was only when I retrieved record number 1000 that I found what the problem was and finally understood the misleading error message. The familiar error message appeared, I clicked on it; the error window disappeared and the record appeared. The line number field displayed 1,00 instead of 1000 as I had been expecting - this is the smoking gun. The record number is stored in a field called KLINE that I had defined as an integer of length 4, as I wasn't expecting more than 9999 lines in the file. But the form is displaying this number with a comma separating the thousands, so 1000 was displayed as 1,000 - and this is five characters! Hence the value (i.e. required column width) exceeds the permitted quantity (of characters in the column). All I had to do was lengthen the field from 4 to 6 characters and rebuild the form. Now all the data displays correctly. I suspect that the error message appeared once for every problematic line, meaning that had I pressed on the error message 220 times, the data would have appeared.

The English error message is a faithful translation of the Hebrew message that I was seeing. I don't know which came first, but in both languages the message is extremely misleading and doesn't say what the real problem is. A far better message would be '<column name> is trying to display X characters but is defined of length Y only'.

Defining a dynamic target form name for a form column: ZOOM1

This blog will describe a technique that is documented in the SDK but could be written more clearly, as always.

In previous installments, we have seen how to define a dynamic form target for a field in a report. For example, a report might display data from both purchase demands and purchase orders, where one field shows either the purchase demand number or the purchase order number. Clicking on this field will cause the appropriate form to be opened in order to see in detail the purchase demand or the purchase order.

The same mechanism exists in forms, but the implementation is different. I developed a few years ago a form that can display invoices of different types ('A', 'C' or 'F') and allows editing of certain fields in the invoice. Pressing F6 on the invoice number opens an intermediate form, not the specific form for the specific type (AINVOICES, CINVOICES or FINVOICES). This annoyed me sufficiently to seek the solution, which is ...

One defines a form column named ZOOM1, whose value should be the 'exec' number of the required form (not the name of the target form). In the case of the invoices, the exec number is in IVTYPES.EXEC, and so the column name will be EXEC and the table name IVTYPES. This can be seen in the screenshot on the left. This form column is used in the IVNUM field: in the form column extension sub-form, the target form name is set to ZOOM1.

ZOOM1 is predefined so one does not have to define it somewhere. Using 'ZOOM' without a numerical suffix does not work! Apparently ZOOM2-ZOOM9 are also defined, should one need more than one target form for a given form.

I have another private form that displays data about all the programs that I have developed: their name, date of creation, notes and most importantly, for whom it was developed. This form can display reports, procedures, forms and interfaces, so a dynamic target form name is required should one wish to open the chosen program (and frequently I do). In this case, however, I don't want the EXEC number of the program itself, but rather the EXEC number of the form that displays the program: if the program is a report then the form EREP should be opened, and if the program is a procedure then the form EPROG should be opened. As the table EXEC is already being used in this form, I will need to alias the table and use this in what Pascal would call a 'case' statement:

(form column) EXEC1.ENAME
(expression) = (EXEC.TYPE = 'R' ? 'EREP' :
(EXEC.TYPE = 'P' ? 'EPROG' :
(EXEC.TYPE = 'F' ? 'EFORM' :
(EXEC.TYPE = 'I' ? 'EINTER' : 'NULL'))))

Another form column will be required: its name will be ZOOM1 and its value EXEC1.EXEC. I later discovered that the condition EXEC1.TYPE = 'F' should be added in order to prevent duplicate rows in the form (apparently there are two entries in EXEC with the name EREP: one is a form and the other is a menu).

Conditional opening of a form from a report

In what might be termed a 'normal' report, one can cause a specific form (screen) to be opened with the current field in the report by pressing F6; this is default behaviour, but can be over-ridden by defining a specific form in the Target Form Name column of the Report Column Extension sub-level of the Report Columns form (this is documented). Thus for a part, normally the form LOGPART will be opened, but this can be changed to open a different form such as FLUSHERROR.

In more complicated reports that display different types of document in the same field (such as an order number or a delivery note number), the above mechanism does not work and so one has to define a special field that contains the name of the form to be opened. I wrote about this a few months ago but cleverly managed not to document how the given field is marked; this used to be undocumented, but recent versions of the Priority SDK devote a section to this, although as usual this is somewhat unclear.

In order to achieve this, for the report column in question, record the following settings in the Link/Input tab of the Report Columns-HTML Design sub-level of the Report Columns form:

• Link/Input Type = P
• Return Value Name (:HTMLACTION) = _winform
• Return Value Column# (:HTMLVALUE) = the number of the column containing the ENAME of the target form. Note: The column with the ENAME of the target form must have a Sort value.
• Internal Link Column# = same as :HTMLVALUE above.

I recently had to add a new wrinkle to the above. The report in question sometimes displayed part numbers but could also display text labels (the report in question is based on exploding a BOM but then adding values for variables such as 'shipping' at the end of the report); I wanted that clicking on the part name would bring up the LOGPART form, but that clicking on a text label would do nothing.

One can't use the standard specific form in the Target Form Name column as the given field is an expression. Using the _winform approach means that the 'column containing the ENAME' should be defined as = (STACK4.INTDATA = 0 ? 'LOGPART' : <something>) and a further hidden field be defined as EXEC.TYPE = 'F'. The '<something>' is a place holder intended for the text labels; I wasn't too sure what to place there at first.  

Initially I replaced <something> by ' ' (i.e. the null string), but then these extra lines did not appear as there is no entry in the EXEC table for ENAME = ' ' and TYPE = 'F'. I then replaced the empty string with 'F' (as I noted in a previous blog, there is a dummy form called F); this worked in the sense that the extra lines appeared, but clicking on the text label caused this dummy form to appear, which I did not want. My next attempt was to replace the condition on the EXEC.TYPE field with  = (STACK4.INTDATA = 0 ? 'F' : ''); this worked properly (i.e. no form opened when clicking on the text label) but seemed too fussy.

Glancing through the SDK one more time, I found a more elegant solution: EXEC.TYPE can be equal to 'F' and ENAME becomes = (STACK4.INTDATA = 0 ? 'LOGPART' : 'NULL'). This is documented  both in the 'Forms' chapter and the 'Reports' chapter of the SDK (to disable automatic access from a given column, specify the NULL form as the target form in the Form Column Extension form).

I learn something new every day.

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.

Calling a procedure from a screen trigger

I am starting work as a sub-contractor (i.e. someone else is the consultant who interfaces with the client and defines their work procedures whereas I'm the programmer who implements those procedures in Priority) for a company that wants to have one master company in which are defined the customers, suppliers, parts, etc. and satellite companies in which the actual transactions take place. My primary task is to write interfaces that will automatically transfer data from the master company to the satellites. 

For some tables (e.g. supplier type), new/updated data will be copied to all the satellites, whereas for other tables (e.g. customers), the user can decide to which satellites will be copied the new/updated customer. Upon hearing this, I realised that I have to define a table with four fields: the id of the new datum, the type of the new datum, satellite name and whether the data should be copied to this satellite. I also developed a form based on this table; this forms will be a 'son' form to all the forms that allow a choice of satellite. This form is easy to define as it takes its inspiration from another general form, EXTFILES. The fun starts in the copying procedure.

Within Priority, there exists a documented method for sending data from one procedure or trigger to another procedure or report. I use this frequently when building procedures which by means of the scheduler will send reports by mail. The procedure uses a local copy of one of the 'stack' tables (normally STACK which has precisely one field - ELEMENT - which is of course the table's primary key). For example,

:GROUP = 'NO_CNC';
EXECUTE WINACTIV '-R', 'TEST_NOPROD_METAL', 'STACK', :$.STK, 
'-g', :GROUP;

My original thought would be to do something similar in the form's post-form trigger: pass the parent form's id number to a procedure that then iterates through the chosen satellites, invoking an interface to do the actual addition to the satellite. As each procedure/interface is specific to a given form (i.e. the code necessary for updating customers is similar but different to the code necessary for updating suppliers), there is no need to pass the data's type (customer, etc) to the procedure. As only one datum is required, I could use the STACK table as shown above.

The communal form's POST-FIELD trigger had code like this:

GOTO 2 WHERE :TEST_CHANGED = 0;
SELECT SQL.TMPFILE INTO :FILE FROM DUMMY;
LINK STACK TO :FILE;
INSERT INTO STACK (ELEMENT) VALUES (:$$.NSCUST);
GOTO 2 WHERE :RETVAL <= 0;
GOTO 1 WHERE :$$.EXTTYPE <> 'p'; /* not a part */
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYPART', 'PART', :FILE;
GOTO 2;
LABEL 1;
GOTO 1 WHERE :$$.EXTTYPE <> 'C'; /* not a customer */
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYCUST', 'PART', :FILE;
GOTO 2;
LABEL 1;
...
LABEL 2;
UNLINK AND REMOVE STACK;

This seemed reasonable but it didn't work! I spent a very frustrating hour discovering that the procedure was not receiving data. It transpires that every procedure has to have its data passed in a linked table of the correct type: the 'copy part' procedure has to receive a linked table of parts and the 'copy customer' procedure has to receive a linked table of customers, etc. So the final code became

GOTO 2 WHERE :TEST_CHANGED = 0;
SELECT SQL.TMPFILE INTO :FILE FROM DUMMY;
GOTO 1 WHERE :$$.EXTTYPE <> 'p'; /* not a part */
LINK PART TO :FILE;
INSERT INTO PART
SELECT * FROM PART ORIG
WHERE PART = :$$.NSCUST;
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYPART', 'PART', :FILE;
UNLINK AND REMOVE PART;
LABEL 1;
GOTO 1 WHERE :$$.EXTTYPE <> 'C'; /* not a customer */
LINK CUSTOMERS TO :FILE;
INSERT INTO CUSTOMERS
SELECT * FROM CUSTOMERS ORIG
WHERE CUSTOMER = :$$.NSCUST;
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYCUSTOMER', 'CUSTOMERS', :FILE;
UNLINK AND REMOVE CUSTOMERS;
LABEL 1;
...
LABEL 2;

Using the BACKGROUND parameter after EXECUTE means that the actual copying occurs in the background and so allows the user to continue working without interruption.

One final problem to look out for: users must be able to execute the copying procedures, which means that they have to appear in a menu for which regular users have access. 

Removing a private field from a form

Disclaimer: all of the below is written to the best of my knowledge. As this topic is barely documented, I have to base my comments on my experience which may well be limited. In other words, there may be a simple way of getting around the pitfall described below of which I am simply unaware.

Extra disclaimer: the pitfall described below seems to a problem specific to one company. I cannot reproduce the error on my system.

The scenario: a client wants to add a new personalised field to an existing form. My normal way of doing this is to add the required field to the base table of the form and then add the field to the form which will display it; a more complicated method involves adding the field to a continuation table (good examples of this are the service calls and projects forms). Built-in triggers on every form are responsible for loading and saving all data which is displayed on the form which is derived from the base table, whereas the developer is responsible for loading and saving data which comes from a continuation table. In other words, if I add a field to a base table and then to a form, I don't have to worry about it being loaded or saved. 

The problem: after a few days' testing, the client decides that she doesn't need the added field. OK: the automatic, unthinking, simple solution is simply to remove the field from the form by deleting the definition. WRONG!!!! Form preparation, after adding the field, will modify the built-in triggers to include the new field (if it was defined as belonging to the base table) and so deleting the field from the form will cause these triggers to scream that they are missing a field when a user accesses this form. What is worse is that 'form preparation' does not detect this problem. The developer has no way of accessing the built-in triggers so it is not possible to remove from them the references to the new field. 

The correct way to 'remove' the added field would seem to be to hide it, not delete it.

In my opinion, the bug is that after deleting the field, form preparation does not update the built-in triggers and instruct them to remove the added field.   

Ironically, displaying the field via a continuation table does not cause this problem as the field is loaded and saved 'manually' and so the developer can simply remove the references to the added field.

[Edit from 09/22: the same problem happened at another company running Priority 21. Fortunately we were working on a test server so the problem will not be propagated to their real server.]