Yes unfortunatley we are manually adding to this CSV,
date filtering
adding CNxxxxx’s (Credit notes)
add a running balance column,
and subtracting Balance from GBP_Total column which creates a Red -ve number if it has a Credit note assigned to it to help us see that it has had a credit note assigned.
I’m in the process of looking at marcos or importing to a excel template, but want to get as much POST formating options sorted with your good selves first.
After reformating the CSV, we check against our supplier paperwork and statements. We would not pay a supplier 1000’s of £’s without tallying PO’s and credits they have issued.
I would be happy with the balances on the reports if, either I could see how the total has been derived (what QF’s and CN’s are included) or guarantee our staff enter Credit notes against Unpaid PO’s correctly, but at the moment we are having to manually check if the credit or credits are included especially if more than one credit is issued to a PO.
In the CSV process what other fields can you export?, i.e. at the moment you are using,
SUPLLIER_NAME,
ID,
PURCHASE_NUMBER
etc,
So are there other Fields you can add in a column?, such as PURCHASE_CREDIT_NUMBER, and if so what fields are they?
What other Functions can you do before generating the CSV?
Example can you create a running Balance column as per my origianl post picture above?
Can you do maths or logic?
Finally can you interrogate the accounts “Database”, (I assume the accounts are a database?) directly from Excel, simular to the way there used to be for SAGE using there Excel Integrated Reporting for
Sage Line 50 ODBC (Open Database Connectivity) driver?
Sorry that was the point of my question above, we can’t without messing about with other reports in excel because of the way quickfile handles credit notes !
You can’t integogate the database that QuickFile uses as unlike Sage it is a multi-user database. Our database holds data on every user’s account rather than a local database containing only your data, as would be the the case with desktop accounting software. The closest you can get to querying your own data is via our API. I suppose you could also write some Excel function against the CSV backup files we issue.
In regards to the batch payment report it has been designed to output the balance on the account of all outstanding invoices. It uses the same methodology as the creditor ageing report, so if you wanted to understand what invoices were backing those totals, you could pull up the ageing report and drill down on the link for each aggreate total. From there you can pull up the invoices and isolate any credit notes that have been attached.
Combinding all those relationships into a single Excel file amounts to a significant repurposing of the batch payment report, I’d argue that it’s actually something more akin to a supplier statement than a payment report. We never have enough developer resources to work on as many projects as we’d like so we have to prioritise those areas that get the most interest from our users. I’m fine to leave this open for discussion but unfortunately it’s not something we are able to focus on in the short to mid term.