Beware of single character variables in form triggers that log changes

I'm going to describe something which isn't a bug in the strict sense of the word, but will cause Priority to act in a way that we don't want and is unexpected.

There is code in almost every screen to record changes; frequently, there are fields in the screen for which changes are not logged and so we have to add them to our own screen triggers, using the same mechanism for storing the changes. There is a table called GEN_CHANGE_LOG which holds data about changes for most screens and there is a standard procedure for entering data into this table (#INCLUDE TRANSORDER_p/BUF2-CH).

Now down to specifics: I added code a few years ago to the PARTPARAM screen to record changes in the default warehouse of the part using the above procedure. The original value of the field being checked is stored in a variable called OLDVALUE and the new value is stored in - you guessed it - NEWVALUE. As the name of the trigger came after the standard POST-UPDATE-CH trigger, my code executed after the standard code. This shouldn't be a problem as the order of checking should make no difference.

But unfortunately it does. I noticed in the changes log for a few parts that the default warehouse had been changed from 'M' to 'M'. How could this be, when the warehouse names are something like M271 or M504? Why was the trigger storing only one character when the field itself has four characters? I looked at the standard trigger and noted that it was saving the value of a field that could be either Y or empty. Priority handles single character strings in a different manner from longer strings; this gave me the clue that somehow the standard trigger was defining the length of OLDVALUE and NEWVALUE to be single character strings, so that when my trigger executed, only the first character of the value would be stored.

There's no way of redefining the length of a string (AFAIK) in Priority, so I had to look at the problem with some lateral thinking. The solution was to change the name of my trigger so that it executed before the standard trigger. I implemented this and checked: lo and behold! Now warehouse names are being stored in their entirety and are not truncated.

---

I wrote the above a few years ago. Today I ran into a similar case: changing the process of a part to Issue in the PART form caused the error log to show that the new process is 'I'. The previous change in the log was changing the type from O to I - that's where the 'single character variable' comes from.

Edit: this bug appears to have been fixed in later versions of Priority; it's certainly fixed in version 21.

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).

More on POST-FORM triggers

Following my previous post on the topic, I've come across a problem with this trigger. Price quotations with a total greater than 50,000 NIS that were created before the trigger was added are obviously not connected to a sales opportunity. Simply entering the price quotation in order to look at it will trigger the error message when exiting the quotation.

The practical solution to this is to press F7 which causes the current record to be removed from the screen. Possibly inelegant but it works.

POST-FORM triggers

I was recently tasked to create an error message when a price quotation's total price is over 50,000 NIS and the quotation is not connected to an opportunity. This is more difficult than it sounds, as the price comes from the price quotation's lines. The standard scenario would be that a user opens the price quotation, adds lines then exits the price quotation, possibly changing the status of the quotation. If the status is changed, then a pre-update trigger would seem to be the place to put the check about the quotation's price. But if the status is not changed, no event occurs at the quotation level and so no code will be triggered. After all, the post-form trigger performs operations when a form is exited, provided there were insertions, updates or deletes in the form [emphasis mine].

One event that is guaranteed to occur is the item form's post-form trigger (this saves having to write both post-insert and post-update triggers) that will be triggered when the amount in the line changes. Here one would have to write code that sums the prices in the lines connected to the price quotation and then checks whether this total is greater than the designated price. I don't like this too much as the same check will be executed several times (once for each line) and also variables that belong to the price quotation form have to be named with a double dollar sign (because they're in the parent form), like :$$.BRANCHNAME. Whilst this isn't a problem, it's one more thing that has to be remembered. 

Whilst idly looking through the SDK for the description of the post-form trigger, the search found a system variable whose acquaintance I had yet to meet: ACTIVATE_POST_FORM. Its description is assign it [the variable] a value of Y in a PRE-FORM trigger to activate the form's POST-FORM trigger upon exiting the form, even if no changes have been made [emphasis again mine]. This is exactly what I need! In the private post-form trigger I included the private pre-update trigger, so one way or another, the error message will be activated as requested.

This technique makes certain checks in forms much easier.

By the way, I couldn't use a business rule as I had to check more than three conditions.