GL transaction based Custom reports and credit notes

Anyone got any ideas why the following picks up and includes invoices perfectly fine but not credit notes?

I should add that the credit memo’s hit the same sales accounts as the invoices that do show up in the report.

Credit notes are normally posted to Accounts receivable, so would be excluded by your second Where criterion. Without knowing more about your chart of accounts, that’s the only observation I can make. I would suspect the report is picking up the sales invoice information from the income account. I would consider exactly where the credit note is actually being posted—both legs of the transaction. If you’re only seeing sales invoice transactions once, that supports my idea that you’re only seeing the leg of the transaction that isn’t being posted to Accounts receivable. If you’ve somehow assigned credit notes to a non-default account, that could be the origin of your problem.

I also find it useful for troubleshooting custom reports to select debits and credits instead of amounts so I know for sure which leg of a transaction is being listed.

Thanks Tut. How are you. Yes, I am trying to get the Non Accounts receivable side of the transaction as you suspect and that works great for the sales invoices.

As an example consider an Invoice line has a Dr AR and Cr Flight Fee Revenue. That invoice transaction shows up perfectly on the report showing the Non AR side of the entry.

The credit memo uses EXACTLY the same accounting except obviously it is a Dr to Flight Fee Income and a Cr to Accounts receivable (i.e. the opposite of the invoice).

So this is why I am puzzled. The two transaction types have exactly the same accounts involved. I am trying to pick up the credit memo information from the income account side in exactly the same manner as it is already perfectly picking up the invoice information from the income side.

Any thoughts/insights appreciated.

OK Here is what I have found to be the underlying issue:

Here is the issue:
Lets look at a select:
image

This returns:
image

So we can see that the customer name for Sales Invoice transactions is associated with ‘both sides’ of the GL transactions In this in this case both the Receivable Account entry and the revenue entry

Now lets’s do the same thing for a credit memo
image

Returns:
image

So what seems to be the issue is that for the Sales Invoice related records in the GL transactions view/table the Customer | Name column is being populated for both sides of the transaction, whereas for some reason for Credit Note related transactions in the same GL transactions view/table, the Customer column is only being populated on the Receivables side records :frowning:

Would we call this a bug? or could it at lease be considered an enhancement request for the GL Transactions view/table to make the customer field joins work at the same level for all types of customer related transaction?

Hope this helps. I’m pretty sure this is the underlying issue.

Your last post is correct, @alasdair. But I don’t know if I’d really consider it a bug. The income account does not include subsidiary registers like Accounts receivable does. So for the credit note, the debit is posted to an undifferentiated income account, while the credit is posted to a subsidiary ledger under the customer’s name. It helps, as I mentioned earlier, to add Debits and Credits to the report to visualize this. Thus, this report definition:

37%20PM

produces this output:

19%20PM

I guess the question in my mind is why the sales invoice equivalent does include the customer’s name on the credit side of the transaction, since it’s being posted to that undifferentiated income account. But it does, even though it’s only necessary for the debit side:

26%20PM

I’ll invite @lubos into this topic to get his opinion. He likes consistency.

Thank you. Yes, consistency would be great (as long as it’s adding to both not removing from the Invoice :slight_smile: ) and would facilitate reporting on ALL transactions by customer. That is what the original report was all about, listing all the transactions for the year by customer with the income/expense (I…e. non AR side) accounts. Don’t really want to have to include the balance sheet side when I’m simply trying to report on the Income Statement/Revenue activity.

Thanks again,
Alasdair

I agree, this is not ideal. I’m planning to cut down number of columns in General Ledger Transactions and add new tables for each “aspect” e.g. Sales Invoice Transactions, Customer Transactions, Inventory Item Transactions, Special Account Transactions … etc.

This will make it much more intuitive. Right now, General Ledger Transactions behaves like a “god” table so it needs to be broken down into segments.

1 Like

Thanks Lubos. Understood. That said, one really nice thing about having the god transaction table in the GL is that users don’t have to think about joins when creating custom reports. Personally, as long as the schema is understood no big deal either way. My question was more about consistent population of table columns for both invoices and credit memos regardless of the tables or table structure involved. As always, thanks for an amazing and continuously improving product.

This has been implemented now as part of the new custom report implementation.