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. It is quite conceivable that an invoice might have more than one task connected to it (e.g. on 01/03/17, we spoke to the customer; on 02/03/17, we sent a copy of the invoice, etc). 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 this 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.
No comments:
Post a Comment