The Universal Payment Report has been built to produce transactional payment data in a format that can be easily read by accounting software. The report pulls payment information from your Agreements, including IFRS Component and Non IFRS Component Payments.
You can run the report using the default set up that loads when you open the report, or you can customise it to fit your existing accounting needs. In the Column Builder section, you are able to apply functions and formulas to the report before running it. This will produce a customised output which you can use as part of your regular accounts payable and/or receivable processes. This article will walk you through the column set up process, including entering functions, using column keys, using the Mapping feature, and removing columns from the export.
For information on the more niche, customer-specific features of the Column Builder of the report, check out this article.
Please note: Currently, this report is only visible for select customers. If your company has not requested to use the report, you will not see it available in your view of the system. If you believe the report would be useful for your company's accounting practices, please reach out to your Account Manager or our Support Team (support@nomosone.com) to discuss further.
Column Builder
Firstly, a reminder of how the Column Builder works. The section allows for data to be pulled from Agreement, Entity, Property, and Payment-related fields, including Custom Data Fields added to the former three. You can add new columns, remove columns, and edit existing columns.
To add a new column:
- In the Column Name field, enter the title of the column.
- In the Value field, enter the information required to produce your desired output. This information could include data fields, functions, numbers, operators, and plain text.
- Once you are happy with the information in the Value field, click Add.
To remove a column, click the bin icon for the relevant column:
To edit an existing column, simply make the required changes in the Column Name and/or Value fields. The changes will be saved, and you can run the report.
To change the order of columns:
- Click on the dotted square.
- Drag and drop the column to the desired location.
If you are making any of the mentioned changes while using an existing Preset, you will need to click “Save preset changes” in the top right corner of the report in order to save your changes to that Preset.
Data Fields
When entering information into the Value field of a column, typing a forward slash (/) brings up a list of text options. These are what we call “data fields”. They pull information from other parts of the Nomos One system, in order to produce the report output. To add a data field:
- In the Value field, type a forward slash (/). A list of data fields will appear, separated by whether the field relates to Agreements, Properties, Entities, or Payments.
You can narrow down the fields displayed in the list by searching. For example, typing “/known” will bring up the data fields agreement.agreementKnownAs and property.propertyKnownAs.
- Click the data field you want to include. The added data field(s) will appear in the Value field.
.gif)
- Adding the data field as-is will produce that data from your Agreement. Combining the data field with other inputs allows you to manipulate the data as you see fit.
For a full list of fields that can be added to the report, along with a description of what information each field will produce, check out this article.
Custom Data Fields
If your Organisation has Custom Data Fields set up, these fields can be used in the Universal Payment Report. The data field name for use in the report will depend on how the custom field is named in your Organisation, and what kind of custom field it is.
For Agreement custom fields, these appear using the format of agreement.custom.ExampleField. For example, an Agreement custom field called “New Zealand Region” will appear as a data field in the report as agreement.custom.NewZealandRegion.
Entity and Property custom fields work in the same way as Agreement fields, but do not include the word “custom” in the formatting. Instead, these appear using the format entity.ExampleField and property.ExampleField respectively.
Functions
A unique feature of the Universal Payment Report is the ability to add Excel-like functions to the Column Builder in order to customise the output. Using functions can help produce a report output that more closely matches your external accounting practices.
As an introduction to using functions in the Universal Payment Report, check out these column inputs from the default set up of the report:
In the Value fields of both columns, the functions “ROUND” and “IF” have been used, alongside some operator symbols and some numbers. The combination of functions, operators, numbers, and data fields results in the following output in the Debit and Credit fields:
Here is what exactly the functions are doing:
- The Debit column is reading the
payment.amountfield, and producing that amount in the output, as long as that number is above zero. If that number is below zero, the output will be blank. - The Credit column is essentially doing the opposite, and producing the amount in the output, if that number is below zero. If that number is above zero, the output will be blank.
- If the amount in either column is not a whole number, the ROUND function will round it to 2 decimal places. If the amount is exactly zero, both columns will be blank.
Taking the above as an example, you can see that being able to add functions to the report input presents you with various ways to customise the report. Given the numerous possibilities, and that we do not know what exact outputs customers would like to see from the Universal Payment Report, we cannot tell you what exact functions, operators, numbers, and data fields to use. Instead, we have collated the functions, operators, and data fields that you are able/likely to use into lists, which you can find in this article.
How to enter functions
To utilise functions in the Column Builder, type the function you want into the Value field. The capitalisation of the function does not impact the output, however, we do recommend typing the functions in all capital letters so that they can be easily distinguished within the Value field.
Most functions need at least one argument in order to work. An argument is a specific input value or reference to another column that a function requires to perform its calculation or action. These arguments are encapsulated in parentheses, also called round brackets. Here are some examples, working with data fields from the report:
| Function | Output |
| ROUND(payment.amount, 2) | The payment amount, rounded to 2 decimal places. |
| IF(payment.amount=0, “none”, payment.amount) | If the payment amount is equal to 0, the output will be the word “none”. If not, the output will be the payment amount. |
| TEXT(TODAY(),"DD/MM/YY") | Today's date, formatted as day/month/year. This example uses two functions, TEXT, which changes the format of a number using a specified format code, and TODAY, which produces today's date as a serial number. |
| MID(payment.paymentDate,6,2) | The month of the payment date. The payment date field is formatted as YYYY-MM-DD, so this function is producing the 2 values from that date, starting at the 6th value. |
| CONCAT("owed=", payment.amount) | The text “owed=” followed by the payment amount relevant to that row. |
You'll note the use of numbers, operators, and plain text in the function examples above. These are centrally important to your use of functions in the report - without these, the functions would not produce an output/the desired output.
We recommend that you review your external accounting needs to determine which functions would be appropriate for use in your Universal Payment Report processes.
If you are new to using the Universal Payment Report, it can be helpful to first run the report using the default setup that appears when you load the report without a Preset. You can review this output to determine whether it is sufficient for your needs, or whether you need to add additional functions/inputs to the report.
Columns as Inputs
Any column in the Column Builder can be used as an input in subsequent columns, allowing you to reference the output of a previous column.
As shown in the image below, the name entered for the column becomes the second half of a “column key”, which can then be used as an input:
For example, if you created a column with the name “Debit”, it would create the column key column.debit. If you created a column with the name “Operating Expense”, it would create the column key column.operatingExpense.
To use a column key as an input in a subsequent column:
- In the Value field of the subsequent column, enter the relevant column key.
- If adding to a new column, click Add. If adding to an existing column, it will save automatically.
For this process to work, the column you are using as a column key needs to be before any columns where it is being used as a column key. You can rearrange the order of columns using the dotted square to drag and drop the column.
Using columns keys as inputs can be helpful when wanting to process complex calculations using the report.
As an example, we can create a column that outputs the payment amount for payments entered using the Repeating OPEX/Outgoings Payment field. We'll name this column “Opex Payment Amount”, which will create the column key column.opexPaymentAmount.
As we know that our OPEX payments are paid monthly, we know this column will display the monthly amount, however, we want to see an additional column that displays the annual amount. We will create a column with the name "Opex Annual Amount", and the following input in the Value field: IF(column.opexPaymentAmount<>0, (PRODUCT(payment.amount, 12)), "none").
Our output is the payment amount, multiplied by 12, if the payment is an OPEX/Outgoings Payment. If not, our output is the word “none”.
Mapping
The Mapping section allows you to create specific “keys” that can then be used in columns. These keys can only pull information from Entities and Assets (properties). They are similar to filters, in that they allow you to narrow down information about entities and assets so that you just get the information you need.
To create a key:
- Input a Key Name. This is what will create the second half of the key.
- In the Value section, type a forward slash (/) to choose from a list of Entity and Asset related data fields. Once you choose one, the forward slash will disappear.
.gif)
- Still in the Value section, type an equals sign (=), then a value inside quotation marks (" "), in order to filter the key.
- Click Add.
As an example:
- Entering the Key Name ‘Landlord’ creates the key: ‘key.landlord’.
- Entering in the Value section 'entity.role="landlord"', tells key.landlord to only pull information from Entities that are assigned the role of landlord in an Agreement.
Using Mappings in Columns
The keys you create can then be used in the Column Builder. Any of the available data fields for either Entity or Asset that appear in the Column Builder can be used in conjunction with keys. Simply remove the start of the data field that says either “entity” or “asset”, and replace it with your created key. Using keys in the Column builder allows you to view more specific information about certain data fields.
To add a key to a column:
- Create a key using the instructions above.
- In the Value field, type a forward slash (/), then the key you want to include. You just need to type the key name to find the key you want.
- After the key, type the data field you want to view information for.
- Add any other information you want to see in the column output, and click Add.
As an example, we have created a key called key.tenant, that just pulls information from Entities that are assigned the role of tenant in an Agreement. To include it in a column, and see the city recorded for the tenant:
- In the Value field, type a forward slash (/), then “tenant”. The drop down menu will display the word “tenant”, as that is what we named our key.
- Having selected the “tenant” key, the Value field displays “key.tenant”.
- Type “.city” after the other text, and click Add.
Having run the report, we see that the column “Tenant City” only displays the city recorded for the tenant attached to each Agreement. In comparison, the column “Entity City”, using the data field entity.city, displays the city for all entities attached to each Agreement.
For a list of all the data fields you can use in conjunction with Mapping keys, check out this article.
Exporting
Once you have generated the report, you can download the output using the Download button in the top right corner of the page.
If you have columns that are included in the report that you do not want to be included in the exported file, open the Column Builder section, and uncheck the “In Export” box for the relevant columns.
The columns will still be included in the browser output of the report, but not in the exported output. This means that you can use columns as inputs for other columns, but don't have to have those input columns included in the export.
Nomos One does not provide or purport to provide any accounting, financial, tax, legal or any professional advice, nor does Nomos One purport to offer a financial product or service. Nomos One is not responsible or liable for any claim, loss, damage, costs or expenses resulting from your use of or reliance on these resource materials. It is your responsibility to obtain accounting, financial, legal and taxation advice to ensure your use of the Nomos One system meets your individual requirements.