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.

Followup procedures

A few times I have had the need to perform some action after closing an invoice (sometimes customer invoices, sometimes supplier invoices); this has sometimes been updating an invoice that was opened in another company as a customer invoice and transferred to the current company as a supplier invoice. One quickly comes to the conclusion that this is not possible to do via a POST-UPDATE trigger on the invoice as the closure is performed by a separate procedure and the actual form does not create any event that can be handled.

The gurus at Priority Software were aware of this problem and so added a solution that unfortunately is barely known and certainly not documented. If one goes to the Financials > Maintenance of Financials > Basic Data > Financial Attributes > Financial Documents menu option (form name IVTYPES), two columns can be seen: Initial Procedure and Follow-up Procedure. The help text for the followup procedure appears below.

But of course, there is no documentation that might explain how such a procedure can be defined. Obviously an invoice number has to be passed in a linked file, but what is the parameter name for that file? Is it :$.IV - the CLOSEYIV procedure that closes a supplier invoice has the parameter defined as :$.IV - or is it :$.PAR? 

There's only one way to find out and that's by trial and error. It turns out that the parameter should be called :$.PAR. Further testing showed that it's best to extract the IV field from the linked table after which the linked table should be closed, and any futher access be to the unlinked INVOICES table. This is probably because the linked table contains stale data, primarily the new number of the invoice that it receives after having been closed. Following is a very simple sample procedure that writes the new invoice number to a private table, simply to check that the value is being obtained.

LINK INVOICES TO :$.PAR;
:IV = 0;
SELECT IV INTO :IV
FROM INVOICES
WHERE IV > 0;
UNLINK INVOICES;
SELECT IVNUM INTO :PAR2
FROM INVOICES
WHERE IV = :IV;
UPDATE TEST_CONST SET
VALUE = :IV,
CHARVALUE = :PAR2
WHERE NAME = 'AB';

A problem with this kind of procedure is that debugging by means of inserting WRNMSGs throughout the procedure doesn't work, or more accurately, the warning messages don't get displayed. That's the reason that I had to update fields in a table.

VERY IMPORTANT: the followup procedure has to appear on a menu such that the person who closes the invoice will have permission to run the procedure.

Designing an Excel template for a report that does not have data in every row

One of my fellow workers wanted that certain columns in a report would not be displayed. I told him that the HTML template does not display these columns, which is when I realised that he is probably exporting the data to Excel. To the best of my knowledge, Excel does not honour designed templates and shows all the columns. The answer was to create an Excel template that hid the required columns. 

I have never (knowingly) done this before so I wasn't sure how to start. I right clicked on the appropriate menu option and chose 'design Excel template'; I indicated that I was creating a new template. I then filled in the parameters for the report and after a while Excel displayed the data. I deleted the appropriate columns in the spreadsheet then saved it. When I tried to create the report again using this template, the unwanted columns still appeared.

After receiving some help, I was told to add a new worksheet then copy the appropriate data from the 'datasheet' worksheet. I thought that I would be clever, so I added this formula to cell A1:

=datasheet!A:L

Whilst this effectively copied the appropriate data (I added the formula =datasheet!P:V to cell M1), it also copied over a million empty lines! In other words, I had to find a formula for copying the contents of a column until an empty cell is reached. I reached for my trusty AI and received this formula

=FILTER(DataSheet!A:A, (DataSheet!A:A <> ""))

I then copied this formula to the appropriate column headers ensuring that I was accessing the appropriate column in the datasheet (i.e. skipping over three columns). This seemed to be correct, but unfortunately was not, as some columns have empty cells in rows where other columns do have values, and so the data was 'non-aligned'. I knew that columns A-C would have data in every row, so I asked the AI program how I could use these columns as a reference. Below is the final formula, for column D, that shows how each column can use column A (the dollar sign means "don't change the column when the formula is copied to another column). Again, the formula for column M (the column that shows data taken from column P) simply subtitutes P for D in the formula.

=FILTER(DataSheet!D:D, (DataSheet!$A:$A <> ""))

In my humble opinion, this is sufficiently non-intuitive to warrant documenting it here.

String length bug found

In a procedure that sends email, I had the following expression

:EMAIL = (:$.DBG = 1 ? 'noamn@testing.com' : :EMAIL);

The value of :EMAIL prior to this expression was sigall@somecompany.co.il - the length of this string is 24 characters, whereas the length of noamn@testing.com is 17 characters. Regardless of the value of :$.DBG, the resulting string would have a length of 17 characters, and so if :$.DBG = 0, there would be an attempt to send an email to the non-existing address sigall@somecompan; of course, this would fail.

As always, it took me quite some time to find out why the email address in the letter was being truncated but took only a minute to fix. If my address is assigned to a variable prior to evaluation, then the resulting value will have the correct length.

:EMAIL = 'sigall@somecompany.co.il';
:NOAM = 'noamn@testing.com';
:EMAIL = (:$.DBG = 1 ?  :NOAM : :EMAIL);

If :$.DBG = 0 then :EMAIL = 'sigall@somecompany.co.il'. If :$.DBG = 1 then :EMAIL = 'noamn@testing.com'.