How to setup an entry report

Question:

I am in the process of interfacing our extract from yourselves into our in-house accounting package. In order to do this efficiently we shall require 4 new extract files to be created which contain selected data from your system (column details taken from the current extract we have set up). Details of these files are as follows:

2 files for GBP (1 for invoices and 1 for credits)
2 files for USD (1 for invoices and 1 for credits)

An example of the structure I require which includes the column names we shall require to be present within the file:

Answer

We can setup custom reports with required report structure. The reports can be executed for a particular entry, by making them appear as a menu item in the Entry Screen. Once this is all done, we can create 3 similar reports, and to each of the four reports, apply a data filter so each report shows only the applicable currency and credit/debit invoices.

Setup report with required structure

Go to Administration | Reports, and add a new report.

 

To remove screen clutter, we filtered the list of reports by searching on the 'Entry' text, in order to limit the number of reports visible. This shows that below the report table editor, there are also the query and columns table editors.

After clicking Add New, we enter the name of the new report. This first report will eventually be used to show the GBP/Debit invoices. 

After creating the report, we add a query to the report.

The query we need is the Services query. It will populate the excel sheet with a row for each order line. At this stage, we will skip filtering the lines based on currency and credit/debit state, we will configure that after testing the general layout of the report.

Then, we add each of the column from the services query

The report columns have been fully defined, similar tot the required format:

Next step is to enable access to the report. Got to Administration Report Access, limit the list by searching for 'Entry', and then click Add New to allow a role acess to the new report. In the example, we allow the ACC_ENTRIES role access to our new report:

After access has been granted, uses that hold this role can now see and run the report from the reports menu.

By clicking the report, we can test the report as follows:

It will now show all order lines for all order of all invoices booked in the selected period.

Next steps are to make the report appear in the entry screen, so we can run it for the current entry without having to go through the reports menu. Then, we also need to set it up so that it only includes order lines from the debit invoices that are in GBP.

First, filter the report for lines that are on an invoice in GBP and that are positive (Debit). We need to filter the rows in the report using the following filter expression

row["CurrencyCode"]=="GBP" && row["IsOnCreditOrder"] == false

The second step is to make the report appear in the Entry screen:

Now, the Back Office, look up an entry, click the entry number to enter the Entry screen

In the Entry screen, test the report:

This gives us the result we are looking for:

To setup the other 3 reports, GBP/Credit, USD/Debit and USDCredit, we can follow the same steps. We will then have each of the reports available in the entry screen for download.