Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Accruals to Cash Accounting Via Oncourse

So Hornsby Ku-Ring-Gai Community College has been attempting to get Cash accounting information out of the onCourse system.

We were printing 'Cash movement detail reports' from the Banking Deposits list and then manually checking individual invoices against the payment entries for GST/FRE. However today, while staring down the prospect of writing a custom export/report to automate this process (with next to no scripting experience), I realised that there must be a way to get the information we need without figuring out code.

That way is as follows:
Open the Banking Deposits interface and highlight all the desired entries.
Click 'Find Related' and select Payments In.
Highlight all the resulting Payment In entries in the list.
Click 'Find Related' again, this time selecting Invoices.
Highlight all the Invoices in the list and click the Export button (top right of window, next to Report and Cogwheel).
In the Export window, choose the 'InvoiceLine CSV Export' and choose a location to save the resulting file.

Contained in the CSV created in this process is all the invoice line data such as tax codes and discount amounts etc, which can be easily modified in Excel to suit your needs.

Hope this is helpful to someone out there in similar circumstances, or perhaps anyone else wanting a way to collate their invoice data for review.


  • Hi HKCC! Thanks for sharing that! This is a great tip for other users, and I'm going to add a "Tips" category, and put this post in it, so it can be found by others.
  • @HKCC_IT_Admin It is important to note that there actually just isn't a way to get 100% accurate cash accounting data out of onCourse. Or really from almost any accrual accounting solution.

    The problem is that payments can be made across multiple invoices and those invoices can each have multiple invoice lines against different GST or income accounts. You will be limited to making estimates based on the portion of partial payments allocated across invoice lines based on a percentage basis. Which is mostly OK until you hit some rounding issues.

    At any rate, the problem is mostly irrelevant. Just stop using cash accounting. Really. You aren't legally allowed to do it anyway if you turn over more than $2 million.

    Cash accounting also means you just can't make proper business budgets since your income falls into different periods compared to your expenses (like wages).
  • @ari I am aware that trying to get accurate data out automatically is a fool's errand, some things just aren't meant to work.

    What I set out to achieve was finding a faster and scale-able way to get the data we have been using for this process, because the alternative so far has been manually opening each invoice to view the lines. This report is no more or less accurate than the manual process as far as I can tell, but it is much more time-efficient.

    When your Accounts Department says jump, there is only one correct answer...
  • > When your Accounts Department says jump, there is only one correct answer...

    I disagree. I've been working in IT long enough to have seen many situations where people request the impossible from technology and it rarely works out well. What you are trying to do:

    1. Breaks ATO rules
    2. Guaranteed not to work in all circumstances because the database is not constructed to do cash accounting

    Sometimes the right answer is no.
Sign In or Register to comment.