Suggestion: Filtering data by timestamp using the API

1

Hello Manager Team,
Thank you very much for taking manager a significant step further by adding API support. I have been using the API for a while and am very impressed with what you can do with it. However, I have not been able to accomplish one important thing using the API.

I would like the possibility to filter lists like customers, inventory suppliers etc and transactions by the datetime created or modified (timestamp) using the API. Currently I have only been able to filter by transaction date and not by when the transaction was created or modified. This singular filter ability will open up possibilities for real-time dashboards and analytics by pulling data incrementally based on when it was created or modified. Currently, I have only 97 suppliers in my system at it takes almost 12 minutes to pull the complete list using the API. if I could filter by when they were created, this would save me a lot of API calls.

Thank you

2

The delay you are experiencing is due to direct querying all individual objects – I assume because you need to see some custom fields.

There are solutions to most issues you raised here, however, it’s essential to first know what api version you are using? i.e. api or api2

3

Hi @Ealfardan
I use api2

4

That’s good.

What you need to do is this, suppose you are querying transactional data such as Sales Invoices. It’s not a good practice to run a query for all transactions at once, instead, you should:

  1. Query /sales-invoices path starting from a set timestamp. You should sortByDesc based on Timestamp and set your pageSize to something between 50 to 500 rows at a time, check the earliest timestamp if it crosses your set timestamp, if not, recurse. This should go something like this in pseudo code:
Get_Tab_Data = (table, startingTimestamp, skip, pageSize) => let
   result = Call_Api(skip, pageSize)
   newTable = Append(table, result)
   cleanTable = selectRows(newtable, each [timestamp] >= startingtimestamp)
   newSkip = skip + pageSize
in
   if Min(newTable[timestamp]) < startingTimestamp 
      then cleanTable 
      else Get_Tab_Data(newTable, startingTimestamp, newSkip, pageSize)

  1. After that, you can use the [key] column from your cleanTable result to query /sales-invoice-form/{key} path, only for these entries after the set point we established earlier.

  2. You should then save these results to an offline .csv file in a set folder

  3. For your next query, use the last .csv file timestamp to get your new query and append to your new results.

A better option is to get fill out /report-forms using api2 and then retrieve /report-view/{key} data.

That’s what I personally do for getting detailed reports into Excel.

1 Like
5

Thank you @Ealfardan
I have the impression my code is not correct, but I will verify and revert back to you.
When filtered by the timestamp, it just returns everything, completely ignoring the times specified.
Meanwhile filters by transaction date works perfectly.
Let me check my implementation and get back to you
Thank you @Ealfardan

6

Thank you for your support @Ealfardan
The issue was with my code, I can now filter by timestamp which makes the API more usable now

1 Like