Tabel voor PowerQuery in Excel vanuit Exact Online nodig met: aantal herinneringen, laatste herinnering en notities

[jsk]

Voor Invantive cloud maken wij gebruik van de OData verbinding met Exact Online o.a. voor Power BI maar ook voor een debiteuren rapport.

In dit debiteurenrapport gebruiken wij momenteel de tabellen:

• ExactOnlineREST.Financial.AgingReceivablesList@eol en
• ExactOnlineREST.Financial.ReceivablesList@eol

Naast de informatie van deze tabellen is er de behoefte om nog meer informatie vanuit Exact te betrekken bij het rapport. De volgende informatie is nodig:

• Aantal herinneringen verstuurd van desbetreffende openstaande verkoopfactuur,
• datum laatste herinnering verstuurd en
• de eventuele notities van de openstaande verkoopfactuur.

Vorige post werd gevraagd om een screenshot van het scherm in Exact met de notities zie bijlage.

Ik hoor graag of dit mogelijk is!

[forums]

Aantal Herinneringen

Advies is om AROutstandingItems en APOutstandingItems te gebruiken i.p.v. de AgingReceivablesList en ReceivablesList; ze zijn vele malen sneller en flexibeler.

Het aantal herinneringen is af te leiden door de “Extended”-versie van de tabel AROutstandingItems te gebruiken. Dit kan door een user-defined view te maken zoals beschreven in Eigen database-views gebruiken voor SQL en BI-tools met de volgende SQL:

select rge.value reminder_cnt
,      aom.*
from   RANGE(5, 0)@DataDictionary rge
join   aroutstandingitemsEx(Params_Reminders => rge.value)@eol aom

Vervang 5 door het maximale aantal herinneringen.

Datum laatste herinnering

De datum laatste herinnering leiden we zelf dagelijks af, maar kan mogelijkerwijs ook bepaald worden o.b.v. de tabel DocumentsIncremental:

select salesinvoicenumber
,      count(*)
,      max(DocumentDate) DocumentDateMax
from   documentsincremental@eol
where  type = 15 /* Reminder, see DocumentTypes table. */
and    salesinvoicenumber is not null
group
by     salesinvoicenumber

Bij oude herinneringen lijkt de relatie met SalesInvoiceNumber er niet te zijn (of niet meer te zijn).

Notities verkoopfactuur

Elke openstaande post kan één of meerdere notities hebben. Dit lijken taaknotities te zijn. Er is geen tabel bekend waar die mee opgehaald kunnen worden en een snelle zoektocht levert geen resultaat. Advies is om na te vragen bij Exact Online Support of deze überhaupt opgevraagd kunnen worden via een API.

[jsk]

Dank voor de oplossing. Hiermee krijg ik inderdaad de gewenste output echter begrijp ik uit het artikel Eigen database-views gebruiken voor SQL en BI-tools dat het niet mogelijk is de bewerking op te slaan en door middel van een verbinding met Power Query dagelijks te vernieuwen, dit moet dan gebeuren in de SQL editor en de output exporteren?

Dit zouden namelijk evenveel stappen zijn als de export uit Exact te halen.

[forums]

Om welke bewerking gaat het? Cq. welke zin in genoemd artikel geeft de indruk dat het niet mogelijk is; dan corrigeren we dat?

Een view kan opgeslagen worden bij bijvoorbeeld On Startup SQL van de database. Het vernieuwen kan in Power Query met de hand en - als gelijkend op Power BI - automatisch via PowerBI.com.

[jsk]

Dit vind ik terug in het aangeleverde artikel de volgende regel:

Er is op dit moment geen mogelijkheid om de viewdefinities blijvend vast te leggen in de database gedefinieerd via de DataDictionaryConnectionString eigenschap van een Invantive database.

Wellicht begrijp ik het verkeerd.

Daarnaast kan ik uit het artikel niet halen of ik een aparte database moet aanmaken, of dat ik de SQL editor op de bestaande database kan gebruiken. Momenteel heb ik voor de zekerheid een tweede aangemaakt en daar via de SQL-editor de output verkregen. Ik hoor graag hoe ik de output via een OData link in Power Query krijg.

[forums]

Dank voor de uitleg; die zin is inderdaad verwarrend en is aangepast. De zin verwijst specifiek naar een huidige beperking dat het laden van de viewdefinities vanuit een persistent data dictionary nog niet mogelijk. Het is wel mogelijk om op Invantive Cloud de viewdefinities automatisch te laden via de On Startup SQL van een database.

Het is niet nodig om een aparte database te maken; het is handiger en minder complex om de viewdefinities toe te voegen aan de database waar het om gaat door in het veld “On Startup SQL” de viewdefinities toe te voegen zoals bijvoorbeeld dit voorbeeld voor Tix (een leisure ticketing systeem) waarbij drie views gemaakt worden:

use all
;
create or replace view tix_settings
as
select '0fcb23d28a8542f199373a1b1c125b2b' token
,      500 pagesize
,      true diskCache
,      3600 diskCacheMaxAgeSec
;
create or replace view tix_customers
as
select rge.value
,      jte.*
from   tix_settings@datadictionary stg
join   range(400000 / stg.pagesize, 1)@datadictionary rge
join   HTTPDOWNLOAD@DataDictionary
       ( url => 'https://crmapi.tixnl.nl/v3/customers?pageSize=' || to_char(stg.pagesize) || '&page=' || to_char(rge.value)
       , method => 'GET'
       , acceptMimeType => 'application/json'
       , headers => 'Authorization=Bearer ' || stg.token
       , diskCache => stg.diskCache
       , diskCacheMaxAgeSec => stg.diskCacheMaxAgeSec
       ) htp
join   jsontable
       ( 'Data[*]'
         passing htp.CONTENTS_CHAR
         columns id              int      path 'Id'
         ,       gender          int      path 'Gender'
...
) jte
;
create or replace view tix_giftcard_sales
as
select rge.value
,      jte.*
from   tix_settings@datadictionary stg
join   range(30000 / stg.pagesize, 1)@datadictionary rge
join   HTTPDOWNLOAD@DataDictionary
       ( url => 'https://crmapi.tixnl.nl/v3/giftcards/sales?pageSize=500&page=' || to_char(rge.value)
       , method => 'GET'
       , acceptMimeType => 'application/json'
       , headers => 'Authorization=Bearer ' || stg.token
       , diskCache => stg.diskCache
       , diskCacheMaxAgeSec => stg.diskCacheMaxAgeSec
       ) htp
join   jsontable
       ( 'Data[*]'
         passing htp.CONTENTS_CHAR
         columns Number         varchar2 path 'Number'
         ,       Pin            varchar2 path 'Pin'
         ,       Amount         decimal path 'Amount'
...
) jte
;

In het scherm van een database ziet dat er als volgt uit:

Hou er rekening mee dat het langere tijd kan duren momenteel vooraleer nieuwe viewdefinities opgepikt worden. Deze tijd kan verkort worden door op Bridge Online zelf in het dropdownmenu rechtsboven te kiezen voor “Resetten Cache”.

[jsk]

De volgende query heb ik nu toegevoegd bij de SQL Editor en bij de Startup SQL

use all
;
select *
from   me

select *
from   SystemDivisions
limit  5000

create or replace view Invoice_reminders
as
select rge.value reminder_cnt
,      aom.*
from   RANGE(5, 0)@DataDictionary rge
join   aroutstandingitemsEx(Params_Reminders => rge.value)@eol aom

select salesinvoicenumber
,      count(*)
,      max(DocumentDate) DocumentDateMax
from   documentsincremental@eol
where  type = 15 /* Reminder, see DocumentTypes table. */
and    salesinvoicenumber is not null
group
by     salesinvoicenumber

Momenteel zie ik, na het legen van de cache, nog geen Datadictionary.custom tabellen in Power Query. Dit is hoe ik deze tabel kan vinden, toch?

[forums]

Ook in september 2023 zijn er nog geen API’s om de notities bij facturen op te halen. Verdere opvolging vindt plaats via:

Invantive Control for Exact Online, data analyse Openstaande verkoopfacturen toevoegen Notities

Update 8 september 2023 Ook na actualisatie met de 12 nieuwe Exact Online-tabellen blijkt dat er nog geen tabel is om de notities bij openstaande facturen via de API op te halen. Dit blijft de momenteel meestgehoorde wens op de Exact Online API’s.

[forums]

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.