Sunday, March 12, 2017

How to exclude rows from summation

In Priority, it is easy to mark a report column containing numerical data so that its total appears at the bottom of the report (it's also possible to mark a column so that sub-totals appear). This is normally the required behaviour, but every now and then there are problems with this mechanism. I have been working on a technique which allows users to assign tasks to documents, although this name is not always apt. Two similar examples are 'samples' - goods have been sent to a customer for evaluation and we want to record communications with the customer about the return of the goods, and 'outstanding debts' - we want to record communications with the customer about the payment of the debt. In an unaware implementation, having two tasks per document (open invoice) will cause two lines to appear in the report, and the debt will be summed twice. Not good.

The technique which I have developed is to have a separate procedural stage before the report in which the tasks are stored along with the invoice. This is necessary as I have not found a way of displaying in the same report debts with tasks and those without (there is a problem with what is known as 'left joins' which I haven't been able to resolve). The technique to prevent repeated summing is to store not only the primary key to each task, but also store the ordinal number of the task (i.e. this is the first task, this is the second task, etc). In the report, the value of the unpaid invoice is displayed only if the ordinal number is less than 2. This way, each invoice is summed only once, regardless of how many tasks are connected to it.

In the procedural stage, the pre-defined variable SQL.LINE will give the ordinal number of each task.

Monday, March 06, 2017

Priority technique for 'choose screens'

It sometimes happens that I need to create a screen from which users can choose values intended for a procedure. For example, I developed a program which allows a manager to choose lines from an existing customer order, have those lines inserted into a new order which the program creates, and have the original lines zeroed out.

Why would I need to develop such a screen when from the "input parameters" dialog of a procedure, one can press F6 twice and open the base form of the parameter (e.g. the customers screen, the parts screen)? The reason is that pressing F6 twice can only open what the documentation calls "a root form [screen] (having no upper-level form of its own)". In the above example, data is required from the 'orderitems' table, whose screen is not a root screen (its upper level screen is, of course, the 'orders' screen).

Normally, the way I have solved this in the past is to define a specific table for this task and then develop a screen based on that table. Today I discovered an easier way of solving this problem.

One still has to write a procedure which at the least will consist of two stages, a screen and procedural code, but there is no need to define a specific table upon which the screen will be based. Let's say that I want to re-implement the example which I gave at the beginning. I can define a screen which is based on the 'orderitems' table, showing the fields which I want, along with the parent order number (which will appear on every line). The trick is that the procedure passes to the screen a file parameter, into which will be stored the lines which are displayed on the screen. This file parameter is then passed to the procedural code which acts on the lines in the file (table) - which are the lines which were stored in the screen!

For my own reference, the original example where I found this technique is a procedure called TEC_CALCINVLEVEL, which unfortunately for my readers is not part of standard Priority, but rather was developed in the early days of my company using Priority - before I arrived on the scene. Whoever it was didn't bother in signing their work, let alone documenting it.

I doubt that I'm going to rewrite the various programs of this type which I've already written, but I'll try and bear this technique in mind in the future.