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.

Importing problematic data from an external source into Priority

I was recently faced with the challenge of writing a procedure that would import data from an external source and then build an "over the counter invoice" from that data. The procedure has several stages, but basically it involves the following:

1. Copying a source file from wherever it is to a given filename in ../../system/load where the 'given filename' is the name of an interface
2. Executing a table interface whose name is according to stage 1. This interface places the data into the table LOADORDERS for the sake of convenience.
3. Optional: visually checking the data in the LOADORDERS form to ensure that they are correct (for example, Hebrew is written right to left, quantities). This is necessary only in the development stage.
4. Reading the data from LOADORDERS into GENERALLOAD then executing a form interface on this data in order to create the invoice.

I've done this several times before so it doesn't faze me. But when I started running tests on the procedure, all kinds of error messages starting appearing, mainly concerned with the data that was entered into the invoice's lines. Some of these errors are standard (e.g. insufficient inventory) and some appear to be local to the client. After asking for guidance, I was told to try and ignore all of these errors and so a invoice would be created with lines that may be problematic.

Part of the documentation for flags concerning interface reads: Ignore Warnings — Priority forms generate two types of messages: errors and warnings. Leave this column blank if you want the INTERFACE program to treat warning messages as errors. To ignore warning messages, flag this column. Note: The same purpose is served by the –w parameter, which can be included during the form load [emphases mine].

So one can ignore warning messages, but there is no option to ignore error messages. But of course, the client was asking me to make the procedure/interface ignore those messages. It turns out that there is a badly documented (if at all) method for doing this: enter the :FORM_INTERFACE and :FORM_INTERFACE_NAME standard variables. One can add or update a trigger in the target form (in this case, EINVOICEITEMS) that will skip a check if the trigger is called by any interface or by a specific interface only.

GOTO 1 WHERE :FORM_INTERFACE = 1;
ERRMSG 1 WHERE [some check is true]
LABEL 1;
GOTO 2 WHERE :FORM_INTERFACE_NAME = 'TEST_TEST';
ERRMSG 2 WHERE [some other check is true]
LABEL 2;

In other words, assuming that the above is part of a PRE-INSERT trigger, the first check will be skipped if the data is being entered by any interface, whereas the second check will be skipped only if the interface name is TEST_TEST. This is fine where the form is privately developed, but problematic if the form is standard; whilst it is possible to alter the PRE-INSERT trigger by adding code to skip over check, such a change is liable to overwritten when the Priority installation is upgraded.

So what can one do to skip over the tests in a standard PRE-INSERT trigger? There is a very simple solution: first, one creates a trigger that will be executed before the PRE-INSERT trigger, e.g. AAAA_PRE-INSERT, then this trigger is defined with the following line:

END WHERE :FORM_INTERFACE_NAME = 'TEST_TEST';

My simple test showed that the standard PRE-INSERT trigger was skipped, although the internal PRE-INSERT trigger was executed - this enters the data into the database.

Obviously I have to check this solution on the client's site but all the signs are positive.
-Update: well, it didn't work as I expected on the client's site, but fortunately the primary error that I was trying to avoid was in a private trigger, so I used the first technique of bracketing the check (actually a call to a private buffer on a different form) with the 'goto 310325 where :form_interface_name = 'test_test' statement and the label 310325.

Adding line items to an existing document

There is a section in the SDK with the above title (in chapter 6, 'Documents) that reads as follows: When an interface that adds line items to a document is executed, by default the new items are inserted first in the document (that is, they receive a smaller line number than existing records). For instance, if a given interface adds lines to an existing order that contains two lines, the new record will appear on line 1, the first existing order item will move to line 2 and the second existing order item will move to line 3. Unfortunately this is true. Following this is an explanation of how to insure that new lines will be added at the end of the document, but as usual, this isn't particularly clear (or at least, not clear to me).

As it happens, I have a procedure that I run most days that may add lines to a warehouse transfer document (form DOCUMENTS_T). I won't go into the whys and wheres of this, but I do know that whenever I run this, lines will be added to the beginning of the warehouse transfer and not at the end. For my purposes, it doesn't matter where the lines are added, but I want to use this example in order to deepen my understanding as I have a procedure for an external client that does something similar, except that there I am adding a line to an invoice, and for reasons that aren't relevant, it's very important that the added line be at the end.

So what does one do? In the case of the warehouse transfer document, I added to the interface for form TRANSORDER_T the field TRANS (saved in INT10), where TRANS is the automatic key of the table and is also hidden. At first I thought that the following would  suffice

SELECT MAX (LINE) INTO :LINE
FROM TRANSORDER
WHERE DOC = :DOC;
SELECT TRANS INTO :TRANS
FROM TRANSORDER
WHERE DOC = :DOC
AND LINE = :LINE;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10)
VALUES (2, '2', :TRANS);

Although TRANS is the A key, the form is sorted by LINE, so I thought it best to get the TRANS of the last line then insert this into GENERALLOAD. Lines that the procedure will add will come after this line.

The interface manager complained that line 2 didn't have a part. This led me to realise that I need to enter all of the necessary fields that already exist in TRANSORDER, as well as TRANS into GENERALLOAD before the added lines. Thus my procedure now contains the lines

INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1)
VALUES (1, '1', :DOC);
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10, TEXT1, INT1,
TEXT13, TEXT17, TEXT18)
SELECT 1 + SQL.LINE, '2', TRANSORDER.TRANS, PART.PARTNAME,
TRANSORDER.QUANT, CUSTOMERS.CUSTNAME, CUSTOMERS.CUSTNAME,
WAREHOUSES.LOCNAME
FROM TRANSORDER, PART, CUSTOMERS, WAREHOUSES
WHERE TRANSORDER.PART = PART.PART
AND TRANSORDER.CUST = CUSTOMERS.CUST
AND TRANSORDER.WARHS = WAREHOUSES.WARHS
AND TRANSORDER.DOC = :DOC;
SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;

I tested this by deleting the last few lines of the existing warehouse transfer, then running the procedure. If the above code works correctly, then the lines that I deleted should be restored to the document and they should be the final lines. Indeed they were added at the end.

What about invoiceitems, especially considering that this table doesn't have an automatic key but rather a composite key of IV and KLINE? The following works nicely to copy the lines

INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1)
VALUES (1, '1', :IV);
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10, TEXT1, INT1,
REAL1)
SELECT 1 + SQL.LINE, '2', INVOICEITEMS.KLINE, PART.PARTNAME,
INVOICEITEMS.TQUANT, INVOICEITEMS.PRICE
FROM INVOICEITEMS, PART
WHERE INVOICEITEMS.PART = PART.PART
AND INVOICEITEMS.IV = :IV;
SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;
....
/* then new lines get added without INT10 */
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT1, INT1, REAL1)
VALUES (:LINE, '2', '11055332YA307S', 6000, 250.0);

This new line does get added at the end of the invoice.

Loading data from external files

This is a topic that has received almost complete documentation in the SDK. The usual scenario is that a file arrives from an external source; this may be an Excel file (xlsx or csv) or some other format. The file has to be converted into a tab delimited text file and placed in the system\load directory; Priority comes with a program that converts xlsx to tab delimited (EXL2TXT) so theoretically this should not be a problem. Both the file name and its source directory name have to be in English: Priority gets confused with Hebrew in the full file name. After the file has been converted, it has to be copied to the system\load directory where its name must be the same as the interface program that loads it. The interface program is usually invoked as a step in a procedure. So far, so good.

I have been working with a file that sometimes causes errors when loaded; as far as I can establish, this is due to there being quotation marks in one of the fields that causes fields in the tab delimited file not to align with the definitions in the interface. This causes an error message to be displayed, explaining the error (normally 'Conversion failed when converting character string to smalldatetime data type'); unfortunately this error message does not state on which line the error occurred. This obviously is not a problem when only a few lines are in the file, but is problematic when there are a few hundred lines.

Reading the SDK chapter again on load files, I came across the section that explains that the interface can be invoked with flags, one of which (-E) causes non-loaded lines to be exported to an error file. Exactly what I need. I couldn't figure out at first how to use this flag when the interface is invoked as a separate step, but I could use the alternative syntax of invoking the interface directly (EXECUTE DBLOAD '-L', <interface name>, '-E', <output file>. Whilst the interface was invoked, the error file was empty.

Today I had the time and lack of pressure to find examples of invoking an interface as a procedure step with flags; I found gold with the procedure BANKPAGE_AMEXCARD. The way one defines the flags is to define a parameter (let's call it E although the name itself is irrelevant) in position 10 (very important) whose value is '-E' and is of type CHAR. Following this, a second parameter is defined (named DBF, again not important) in position 11 (very important - it must follow the previous parameter) with value 'name of error file' (this will normally be stored in system\load), again of type CHAR.

I quickly wrote an interface for a very simple file consisting of one integer field and one date field. I then created such a file where the second line had the integer 16 in the date field - an obvious error. I wrote a procedure to empty the work space then invoke the interface. As expected, I received the error message but more importantly the error file contained the line that was not loaded by the interface. Exactly what I needed.

I will try and figure out why the bad lines in the real file did not get copied to the error file. But more importantly, this is a very useful technique that I will introduce to most, if not all, of my interface programs, especially those that receive external files (as opposed to those whose layout I defined).

Order of fields can matter in a form interface

I wrote several months ago  about how the order of tuples can matter; what I didn't know at the time was that the order of the fields in an interface can also be critical. The picture below is an example of a form interface, not the one that is discussed underneath the picture.

It happened that I was asked to create an interface for invoices where the invoice had to have a specific project number; as I had already developed the interface, I simply added the project number at the end of the list of fields. Although theoretically this should be sufficient, it turned out that each customer is connected to some project that is not the project that should appear in the invoices. I explain this to myself that entering the customer number causes the customer's project to be entered into the 'project' field of the screen, and that the latter project number insertion fails because there is already a value there. 

The interface worked as my customer desired when the specific project number was the first field to be entered; again, the insertion of the customer's project failed because there was already a value in the 'projects' field.

So beware: sometimes the order of fields in an interface can be critical!

An important definition in 'HTML documents'

I had occasion today to program an 'HTML document' that enables me to document (no pun intended) an interface that reads an external file. A standard report exists that lists the fields defined in the interface but there is no means of extracting the SQL part of the interface, which is the more interesting part that requires documenting. Hence the HTML document.

As usual, I was faced with the problem of how to remove the frame around the document's header. I can never remember how to do this so I am documenting it here. In the report that contains the header, one goes to the 'HTML definitions' son form and looks for the field '# Page Columns'; this will probably be the first field in the third tab. This should be set to 1.

Another field that is useful in this son form is 'Show Title? ' - this should be set to D when one wants the report's title to be displayed and left empty when one doesn't want the title. Normally reports that display a table should have their title displayed, whereas reports that show comments or similar should not have a title.

Don't mess with standard interfaces

A week ago, I wrote a procedure which iterates over a set of work orders and sets their status to 'cancelled'. In order to strengthen the cancellation, I decided to disconnect the work order from the connected customer order line by writing zero in the appropriate field. As the interface for updating the status of a work order only contains the work order number and status, I decided to add the customer order line, and because I need to write zero into this field, I marked the flag 'accepts null values'. I checked my procedure and it worked fine.

A few days later, someone drew my attention to the fact that after printing a work order (which changes its status from 'approved' to 'printed'), the connection to its customer order line was lost. For you, the reader, the reason should be obvious, but for me it wasn't. I checked the work orders form which - unusually - was free of any customisation which I might have done. There are rules defined for the screen, but they are only for me; one says that the status should be changed to 'cancelled' if the order line is zero - this is not what is happening and anyway, the problem was happening to other users.

Very problematic. I tried rebuilding the screen but this had no effect. As it happens, I had added to a daily report data about work orders which was based on the connected customer order line. There was data for the first two weeks in July but a mysterious disappearance this week. From this, I was able to conclude that the problem occurred only during the past few days. I didn't remember programming anything to do with the work orders form, and anyway I had already checked that there were no customisations.

Then inspiration struck: there is a module which I rarely use that prepares updates: one can choose a selection of changes that have been made in order to package them and transfer them to another installation. I opened this module and requested all the changes that had been made in the past week - all kinds of changes appeared. But one stood out: the change to the standard interface for updating the status of work orders.

Then the penny dropped: the procedure that prints a work order also calls a procedure called DocStat which updates the status of the work order to 'printed', presumably by means of the standard interface which I had modified. This procedure doesn't know about the field that I added and so the value passed would be 0 (null). And because I defined that this field can accept null values, this value was indeed passed ... and disconnected the work order from the customer order line. Ooooops! Red face time (again).

Immediately I removed my field from the interface whereupon printing ceased to disconnect. I then altered my original procedure to update the work orders table directly (nu nu nu) in order to disconnect.

Moral of this story: don't mess with standard interfaces!

Order of tuples can matter in a form interface

One can suggest that there are several kinds of form interface: those which update one form, those that update two forms and so on. Of those that update two forms (father and son forms) there can be multiple lines to be updated in the son form. Apart from the obvious limitation that the information for a father form has to come before its sons, the order of lines within the son form is normally  irrelevant.

Yesterday I came across a case where the order of lines within the son form is vitally important, which is what I want to write about today. Specifically I am writing about reporting completed work orders: the father form is "Reporting Production" and the son form is "Production Details". We have begun working with several actions within a work order: to prepare the upholstery for a chair, the cloth has to be cut, the seat has to be made by glueing together wood and plastic, and then the cloth has to be stretched over the seat and sewn together. We are trying to introduce a system whereby each worker reports each action by means of a personal wireless barcode scanner.

Unfortunately, what management wants is not what is happening, so it frequently happens that someone will report only the final stage in the process. Doing so closes the work order but leaves the inventory in an unstable state, so it is desired that reporting the final stage will also report any previous unreported stages. I wrote code which is activated after a barcode is read: first it enters into a load table the number of the current production report (I open one report per day for the department), then a line is inserted for the action which is encoded in the barcode. Following this, a check is made to see whether this is the last stage in the work order, then if so, any previous unreported stages are added to the load table, which can look like this:

Line	Record type	Payload
1	1	Report number
2	2	Action as encoded in the bar code
3	2	Prior unreported stage in work order
4	2	Prior unreported stage in work order

The first two lines were doing their job but the final, additional, lines did not cause their stages to be reported. I looked at this for some time before I had an inkling of what might be the problem: reporting the final stage of a work order causes itself to close automatically. Then when the extra lines come along, the work order is closed and so will reject any extra lines. My solution was to number the barcode line as 99, so the work table looks like this prior to its being inserted:

Line	Record type	Payload
1	1	Report number
99	2	Action as encoded in the bar code
2	2	Prior unreported stage in work order
3	2	Prior unreported stage in work order

The lines are handled according to their line number, and so line 2 is handled before line 99 even though it was physically inserted after line 99. This indeed solves the problem.

Lesson of the story: sometimes the line number within a load table for a form interface is very important.