Forward filter on invoice_date for Teamleader Invoice lines

Ideas (en)
, , ,
#1

We are using the V2Flat.InvoiceLinesAll of Teamleader intensively. There are 2000 invoices in the largest Teamleader environment with in total 18000 invoice lines. Retrieval takes approximately 80 minutes with a lot of problems with Power BI Service trying over and over.

However, we only need the invoices not older between 13 and 24 months. This set is solely 1500 invoice lines.

A filter on invoice_date is not always added correctly by Power BI, but is present already often due to Power BI query folding.

The V2 Teamleader Invoices associated API invoices.list used by the view InvoiceLinesAll has - somewhat cumbersome - parameters for start and end date of the invoice, see:

https://developer.teamleader.eu/#/reference/invoicing/invoices/invoices.list

It would significantly improve performance when a larger than filter on invoice_date would be forwarded to Teamleader APIs also.

#2

The addition of a custom filter has been studied and is feasible for Invoices on the Teamleader V2 API. The addition of invoice_date_before and invoice_date_after stems from December 2021.

However, inclusion of these filters on the pre-defined view InvoiceLinesAll is quite complex. The infrastructure to efficiently implement such filters on views has been forwarded to the next version of the SQL engine. This next version is not scheduled for 2022.

As an alternative, the two new filters invoice_date_before and invoice_date_after have been added as table function parameters to Invoices, which then can be used in a user-specific view to limit the data volume significantly (10x).

#3

These filters are available on several Teamleader V2 tables, including Invoices, starting release 22.0.16. This release will go into production on Invantive Cloud within two weeks.

1 Like
#4

A practical sample on centralizing the filter logic in Invantive Cloud itself is given in the post “Views gebruiken om centraal te filteren voor Teamleader en Power BI” (Dutch).