Journaltransactions: reporting

[Anonymous]

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

journaltransactions 

Pagesize 1000

page (loop)

periodID (YYYYMM)

lastdatemodified >= 

[Anonymous · Accepted solution]

Hey Yildirim, 

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

let

FnDataPaged=
 (Page as number)=>
    let
        Source = Web.Contents(GetRequestBase, 
        [Headers=[#"ipp-company-id"=CompanyID, Authorization=Token], 
        RelativePath = "/controller/api/v1/GeneralLedgerTransactions?" 
        & "branch=1&ledger=WERKELIJK"
        & "&includeUnposted=false"
        &"&fromPeriod="
        & 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,
                    
    TotalData=
    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)
in
    #"Converted to Table"

[Yıldırım · Accepted solution]

Thanks for letting us know Wouter ! 😊

[Yıldırım]

Hello, 

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).

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.