Journaltransactions: reporting

by DataVacuum

Hey Everyone,


Currently using journaltransactions in order to retrieve the journaltransactionlines to do calculations on. Problem is that the journal entries do not add up to zero.


Everytime I do another GET the data seems to be different. I used Period ID to retrieve the data per page, but somehow some rows seem to receive different data. I figured I wasn’t receiving the fully compisite PK and tried multiple things. My final attempt was to combine both period ID and lasttimemodified to retrieve unique rows which is killing performance.


Is it possible to sort my GET request, guaranteeing unique rows? Am I doing something wrong? Is it possible to use an equals operator instead of “bigger or equal to” for datetimemodified?


my getrequest in a nutshell



Pagesize 1000

page (loop)

periodID (YYYYMM)

lastdatemodified >= 

Accepted solution

by DataVacuum

Hey Yildirim, 


Thank you so much for your explanation. Somehow I missed "General Ledger Transaction" endpoint!!! My code below (power-query) solved this!


 (Page as number)=>
        Source = Web.Contents(GetRequestBase, 
        [Headers=[#"ipp-company-id"=CompanyID, Authorization=Token], 
        RelativePath = "/controller/api/v1/GeneralLedgerTransactions?" 
        & "branch=1&ledger=WERKELIJK"
        & "&includeUnposted=false"
        & Number.ToText(HuidigBoekjaar-1) & "01" 
        & "&toPeriod="
        &  Number.ToText(HuidigBoekjaar)&"12"
        //Maximaal = 1000
        & "&PageSize=1000"
        & "&pagenumber=" 
        & Number.ToText(Page)]),

        json_output = Json.Document(Source),
        result_json = json_output,
        invoice_table = Table.FromList(result_json, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

    in invoice_table,
    List.Generate( ()=>
        [Result= try FnDataPaged(1) otherwise FnDataPaged(1), Page = 1],
        each not(Table.IsEmpty([Result])),
        each [Result = try FnDataPaged([Page]+1) otherwise null, 
        Page = [Page]+1],  each [Result]),
    #"Converted to Table" = Table.FromList(TotalData, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    #"Converted to Table"


Accepted solution

by Yıldırım

Thanks for letting us know Wouter ! 😊


by Yıldırım



Unfortunately we're unable to set a specific sort order, it is internal record ID ASC by default and currently lastModifiedDateTime Condition is automatically applied as ">="

Journal transactions Endpoint GET returns the data based on how the entities are listed in the screen (batch-by-batch ScreenId=GL301000) with details such as including Header+Lines(Totals) per batch.

Journal Transaction has a specific condition. It returns number of transactions (lines)and not number of batches(batch number).
2021-06-18 13_25_49-Window.png


If clients wants to know all transactions in batches, one way to do is to read all transactions until the batch number is changed and when the batch number is changed in the response, then you got all transactions for the batch:


while (pagelefts)
if (previous.batchNumber != current.batchNumber)
-- I have all transactions for prevoius batch, do what i need to do with the batch
-- start the new batch
previous.batchNumber = current.batchNumber;
-- check if several pages to fetch


Also, while using pagination in combination with filter on "LastModifiedDateTime" (where
is available), you will only get documents which have changed since the last GET operation was executed.


Alternatively "General Ledger Transaction" endpoint can also be used. 
In General ledger transaction Endpoint, it is more general and only the transactions with the range of the filters specified are returned. This might be more helpful depending on what data you need while reading.