In this lesson we will cover:
– How to use a custom SQL query to calculate the Licence/ Subscription Count on Recurring Invoice Lines
Custom SQL queries can be used to calculate the quantity of invoice lines based on information from your database. This provides many more options and flexibility to how your invoice line quantities are calculated as they can be calculated using data in your database rather than just based on the standard entities (licence count, user count, asset count etc). A SQL query will need to be written to calculate the line quantity using data in the database, the query must return a column labelled 'QUANTITY' that contains the desired quantity.
Custom queries can be configured by pressing the button below found within Configuration > Billing > Recurring Invoices (this requires the setting to allow calculated quantities to be enabled):
Fig 1. 'Allow recurring invoice line quantities to be calculated using Licence, User and Asset counts' setting
The query must return a single cell with a column named [QUANTITY]. A test button is provided so you can input an example recurring invoice to ensure the calculation works as intended before generating a recurring invoice.
Fig 2. Custom SQL quantity example
These custom queries can then be selected on recurring invoice lines using the 'Quantity' dropdown.
Fig 3. Set invoice line to be calculated based on custom SQL quantity
Adding for example a MS 365 Business Licence product line to a recurring invoice, and then editing the line and then selecting the quantity option "Custom SQL Quantity"
Note: These will not be calculated in recurring invoice list views. Pro-rata will not pull through for the quantities
Calculating quantities from custom table data (v2.182+)
If you have a custom table in your instance that already contains/calculates the quantities you would like to use for your invoice lines you can use $-INVOICELINEID to match each invoice line to a row in the the custom table. This allows you to use the same SQL query to calculate multiple line quantities, rather than having to create multiple queries that only return a single cell of data. You can have a column containing all the line quantities, then determine which row the invoice line uses by matching on the invoice line ID. This requires the invoice line ID to be stored in the custom table.
