Filteren op velden uit de Twinfield API

Wiki (nl)
,
1

Dit topic biedt informatie over hoe met Invantive SQL de hoeveelheid gegevens die uit de Twinfield (SOAP) API opgehaald worden gestuurd kan worden.

Twinfield administraties bevatten soms veel financiële gegevens. Via de Twinfield API kunnen enorme hoeveelheden gegevens in 1x opgevraagd worden die ook in 1x retour komen. Uitschieters tot boven een gigabyte aan data (ongecomprimeerd) in 1 antwoord op een verzoek zijn niet ongewoon.

Drie Twinfield API groepen: ProcessXML, Finder en BudgetService.svc

De Twinfield SQL driver van Invantive benadert de Twinfield API server via drie kanalen:

  • De financiële gegevens zijn “transactiedata” en worden aangeboden via de processxml service URL.
  • De tabellen met relatief weinig gegevens worden aangeboden via de finder service URL.
  • Budget-informatie is beschikbaar via de BudgetService.svc service URL.

Het filteren is niet mogelijk voor de tabellen op basis van de finder service URL en de budget-informatie. Dat hoeft geen probleem te zijn aangezien het dan om relatief kleine hoeveelheden gegevens gaat. Alleen voor de API’s achter de ProcessXML service URL biedt Invantive SQL filtermogelijkheden.

De processxml API groep kan herkend worden doordat bij service URL de waarde webservices/processxml.asmx staat, maar intuïtiever is jezelf de vraag te stellen of er veel rijen kunnen terugkomen en of de rijen wijzigen door het verwerken van transacties? Als het antwoord op beiden ja is, dan zal het een tabel uit de processxml API groep zijn.

Filters

Gezien de grote hoeveelheden gegevens is het bij SQL queries handig en verstandig om filters mee te geven zodat:

  • de Twinfield API server zo weinig mogelijk werk hoeft te verrichten om de gegevens te selecteren in de administratie,
  • de financiële gegevens snel retour komen en
  • het netwerk niet overbelast wordt.

In de SQL code kunnen achter where filters opgenomen worden die de hoeveelheid gegevens zoals alle boekingen in periode 4 van 2019:

select *
from   generalledgerdetails
where  FIN_TRS_HEAD_YEARPERIOD = '2019/04'

Filter Doorsturen naar Twinfield API Server

Niet elk filter wordt doorgestuurd naar Twinfield: bovenstaande query op GeneralLedgerDetails gebruikt wel een filter op year/period zoals zichtbaar bij opvragen via SessionIOs@DataDictionary:

Gebruikte filters op Twinfield API
Gebruikte filters op Twinfield API1055×462 38.1 KB

Maar de functioneel identieke query:

select *
from   generalledgerdetails
where  fin_trs_head_year = 2019
and    fin_trs_head_period = 4

gebruikt geen filters, waardoor eerst alle boekstukken opgehaald moeten worden en lokaal gefilterd:

Twinfield query zonder filter

Invantive SQL zoekt zelf uit welke filters - eventueel na een transformatie - doorgestuurd kunnen worden naar de Twinfield API servers. Dit noemen we “server-side filtering”. Meer informatie hierover is te vinden in How to filter data from OData query on Azure Data Factory? - 3 van forums.

Op de meeste platformen werkt de server-side filtering automatisch doordat het hardgecodeerd is in de code of de metadata specifiek op welke velden gefilterd kan worden. Op Twinfield is de huidige Invantive implementatie van server-side filtering onpraktisch omdat Twinfield via de metadata wel vertelt welke server-side filters mogelijk zijn, maar bovendien ook specificeert welke operator (gelijk, tussen waardes) gebruikt moet worden.

De ondersteunde filters zijn te vinden in de inhoud van de Twinfield metadata-tabel TableColumnSpecifications:

Twinfield browser filters
Twinfield browser filters974×577 30.8 KB

Hier in bijvoorbeeld terug te lezen dat voor BankTransactions op het veld fin_trs_head_yearperiod een filter met een between gebruikt kan worden.

Ook in de Twinfield online documentatie is de lijst van velden waarop sneller gefilterd kan worden terug te vinden zoals het veld FIN_TRS_HEAD_NUMBER met een between, >=, >, =, < of <=:

Query Omschrijven

Invantive SQL is slim genoeg om voor Twinfield ook de Invantive SQL gelijkheidsoperator om te schrijven naar een Twinfield between, dus bijvoorbeeld een query zoals:

select *
from   banktransactions
where  fin_trs_head_yearperiod = '2019/01'

gebruikt een Twinfield between filter operator op fin_trs_head_yearperiod. Dit is ook terug te lezen in SessionIOs@DataDictionary waarbij de Invantive notatiewijze getoond wordt:

FIN_TRS_HEAD_YEARPERIOD Equals 2019/01, fin.trs.head.banktype equal bank

Meer details zijn ook te vinden in de trace zoals uitgelegd in Hoe krijg ik weer trace files van een geautomatiseerde download met Data Hub? - 2 van forums. De native call log laat alle details zien zoals beschreven in Collect Native Platform Call Data.

Samenvatting

Ook grote tot heel grote Twinfield administraties kunnen eenvoudig verwerkt worden in Invantive SQL of gerepliceerd naar een database door het formuleren van de juiste filters in een Invantive SQL where-clause.