Payment as well as invoice totals on Nominal Balance report

[decomplexity]

A Sage Instant (V15) query, but I hope someone can help.
How, please, can I add to a Nominal Balances report a column of total received payments (as allocated to invoiced line items and hence to Nominal codes)?
The obvious problem is that neither side of a payment transaction nor the transaction itself appears to refer to the nominal code of the invoice to which the payment was applied.

One possibility seems to be something like:

- join AUDIT_SPLIT to AUDIT_USAGE on SPLIT_NUMBER
- filter on Sales nominal codes only
- sort the report on NOMINAL_CODE
- specify detail lines to contain:
AUDIT_SPLIT.NOMINAL_CODE
AUDIT_SPLIT.GROSS_AMOUNT
IF AUDIT_SPLIT.PAID_FLAG = 1 then AUDIT_USAGE.AMOUNT else null

There is a lot of guesswork in this! Is AUDIT_USAGE.AMOUNT the payment value or do I need to use (e.g.) the AUDIT_USAGE.SPLIT_CROSS-REF to link to the actual payment transaction? And - gotcha - what if there were several payments against one invoice, i.e. how does Sage manage several usages for one split (invoice line item)?

Can anyone clarify if this is feasible pls?

[brucedenney]

Well... it is a bit more complex..

The structure of transactions in Sage is a Header, with multiple splits which in turn are paid by multiple usages.

The thing here is that you are looking at a Sales Receipt transaction, this has a headers that links to splits that links to usages that links to other splits and other headers.

The information you want is in the split of the "other" matching transaction so you have to link the split to the usage the usage to the (other) split to get the nominal code out of the other split.

In the usage you will find a field called SPLIT_CROSSREF I think this is field you need to link to the other split. I would need to spend more time than I have spare to check that out.

A good trick : You will have 2 Split tables in your report and this IS confusing, so rename the OTHER_SPLIT so you don't get confused.

Hope this is enough of a pointer to enable you to do what you are attempting.

[decomplexity]

Very many thanks Bruce. Light is (very) slowly dawning...
Am I then right in thinking that because not only is the invoice-to-payment relationship n-to-n but also the relationship between invoice line [=invoice split] and payment [='part payment'] n-to-n, a usage record's SPLIT-NUMBER and SPLIT-CROSSREF link one invoice split to one payment split. In other words, invoice line and 'part payment' form a bill-of-materials structure with USAGE as the linking table? Or in relational database terms, SPLIT-NUMBER and SPLIT-CROSSREF form a concatenated primary key to the usage table and uniquely identify a particular occurrence of a part-payment of an an invoice line?
Max

[brucedenney]

Well nearly.

The BOM is nothing to do with it.

You are wanting bits of information out of 2 different (sets of) records in the SPLIT table.

The way you do this is by putting the split table in twice.

The first one of the split tables links to a (or several) usage records which then crosslink back to their parent split records which contain the other part of the information you want.

So a Sales receipt is linked to multiple usage records, each of those usage records crosslinks to another transaction that it "pays" typically an invoice (or refund), that invoice split contains the nominal code you want.

[decomplexity]

Many thanks Bruce - I now (much to my surprise) have a working report !
I only encountered two oddities on which someone might be able to shed some light:

a. when I defined the relationships between invoice_split => usage <= payments_split,
the Join mechanism refused to accept my specifying both relationships as having the primary keys in the respective split tables (or rather it let me do so and then, when I quit the Join, it inverted the direction of the usage <= payments_split to usage => payments_split with primary key at the usage end)

b. the Amount field in the usage records contains some but not all of the entries in the linked payment_split record's Gross Amount field. I am intrigued as to what the Amount field is supposed to contain

[brucedenney]

Yep,

Here is my attempt to draw it...

headersplitsusages.jpg

The primary key is not normally at the usage end, but in your scenario you are not going header to split to usage you are going split to usage to split. The invoice_split can link to several usages.

The usage contains only the part of the payment that relates to the invoice it is matching.
The split record's Gross Amount field is frequently paid by several usages so you would not expect the whole amount to always be the same in the usage as it is in the split it pays. Part payments, payments using credit notes and all that.

[decomplexity]

Thanks Bruce. A brilliant and most useful diagram - perhaps it should be put (with some explanatory text) as a 'sticky' post at the front of the forum. It is information which is otherwise very difficult to come by.