You can apply a filter from Azure Data Factory to solely retrieve the last 3 days as follows:
- Calculate date 3 days ago as a text in the format “YYYY-MM-DD HH24:MI:SS”.
- Note that Simplicate has chosen to project all dates to text values in their API using the Swagger metadata definition.
- Note that this textual representation has as nice feature that the same ordering of values applies: when date increases, the lexicographical ordering of the text increases too.
- Include the filter in your OData4 query with column, greater than or equals and textual representation.
- Invantive Bridge Online receives the OData4 and rewrites it into Invantive SQL, including the filter you have sent being rewritten to a
where-clause. - The Invantive SQL is then translated into calls for the Simplicate API. The
where-clause is rewritten to the Simplicate specific filter implementation. - The Simplicate API receives a request on behalf of your Azure Data Factory to get the data for the last 3 days.
Some more background at this topic on copying Simplicate to SQL Server.
Background: server-side filtering
This concept of forwarding filter criteria (called “server-side filtering”) does not apply always.
For instance, filters on fields added manually by Invantive are most often not forwarded. There are some exceptions like DivisionLabel on Exact Online. Although it is not an actual column in the Exact Online API filters on it are translated into specific divisions to be queried.
Neither are all APIs capable on filtering on any field. For instance, Exact Online has column-specific exceptions. But also Teamleader has considerable limitations.
Whether filters on fields can ultimately be forwarded to the cloud platform can be found in the documentation. Look at the column CAN_FILTER_SERVER_SIDE in SystemTableColumns@DataDictionary.
Finally note that server-side filtering is not possible with many more advanced filters, such as complex combinations of and, or and maybe even calculations. Invantive SQL has an extensive optimizer which simplifies expressions, performs peephole optimizations and rewrites queries, but still there are many thinkable constructs that can not be optimized yet. During regular monitoring we try to identify further optimization possibilities that impact many users and try to optimize these over time too.