The key to ‘thinking’ about this subject is to remember that the original transaction in G/L is Linked TO a Cash Account. The logic here, is that the original transaction is part of General Ledger whether it not is has been paid by a bill payment check, or deposited into a bank account. It is has first ‘dibs’.
So we are operating in reverse.
The transaction is entered into General Ledger e.g. as an expense via a bill, or income via an invoice. At some point (you would hope anyway), the bill gets paid from a particular bank account, and the invoice gets a payment received against it, and the payment in turn gets deposited to a bank account. There are exceptions to those rules depending upon how people setup and use their QB, but the premise is the same. Base transaction – eventually gets linked to a bank account.
There are three pieces to this analysis:
This is the base G/L item whether linked or not. e.g. expense from bill, income from invoice:
This is the next step from the base transaction e.g. bill payment from bill, receive payment from invoice
The Cash Source Transaction is the final ‘resting place’ for any series of linked transactions, and can have the following values:
There is ONE singular problem with databases and linked transactions: you can have MULTIPLE links for one transaction, e.g. one invoice may have several payments applied to it.
The result is that the original GL item is REPEATED AS MANY TIMES AS THERE ARE LINKED TRANSACTIONS.
You could average the figures in your amount column to have the correct individual amount, but you won’t be able to summarize that data amount using the natural pivot table natural functions.
In this example, we see three payments (Ref# 5096,5127,5139) for Invoice 1008. This results in the same information being repeated. The actual amount of the invoice is $5,516.05 but in this Pivot Table Example it would look like this:
What we have done in QQube, is create a ‘divisor’, so that your amount totals will ALWAYS act to give you the correct summary result.
It is actually the combination of two fields: Txn Detail Count, and Txn Linked Count.
Here is what you do: Create a Calculate Field.
And fill it in with the following formula:
='Line Amount' * ('Txn Detail Count' /'Txn Linked Count' )
That will give you the following Field, which now sums up correctly, as the payment amounts are split evenly.
NOTE: THIS FIELD IS ONLY USED TO CALCULATE THE GRAND TOTAL FOR THE SOURCE CHECKING ACCOUNT. IT DOES NOT ACCURATELY REFLECT THE AMOUNTS FOR ACTUAL ITEMS, ETC. THIS IS STRICTLY A CALCULATED FIELD FOR A SPECIFIC PURPOSE.
is requesting access to a wiki that you have locked: https://clearify.com/wiki/view/199/cash-source