Skip to main content
Solved

Dynamic Values for parameter in same endpoint REST data source

  • February 27, 2025
  • 6 replies
  • 54 views

anders.e.jonsson
Contributor
Forum|alt.badge.img+1

Hi,

I am trying to set up a REST connection. Here are the prerequisites:
 

The API delivers data for a maximum of one month, and no more than 10,000 rows at a time.

I'm a bit of a beginner when it comes to the new Rest data source 🙄
I wonder what the easiest way to do is to;
1. Read all rows for the selected period using the fetchFromActivityId field. That field is in one of the API's tables.
2. Loop through month by month until I arrive at today's date.
The API works fine. It is no problem to fetch 10,000 rows from a selected period.
All ideas are gratefully received 🙂

BR
Anders

Best answer by nmo

If you are sure that no date returns more than the 10.000 values you could use the “dynamic values from SQL query” in the TimeXtender REST connector with something like this

Then reference the DateFrom and DateTo values in your API calls and thereby creating an API call per date

 

View original
Did this topic help you find an answer to your question?

6 replies

Thomas Lind
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • 1017 replies
  • February 28, 2025

Hi ​@anders.e.jonsson 

You need to know what to apply in the fetchFromActivityId so you can get the next 10.000 rows.

If each activityId is of a higher value with no gaps, you can use the socrata method.

Essentially you add pagination that sets the initial fetchFromActivityId to {currentactivityid] where you generate this dynamic query to specify this number.

SELECT {TX_CurrentPage} * 2000 as currentactivityid

Then you just need it to stop once it hits the page where no rows are returned and it should work.

If it is not a number, or there are gaps, then you will need to know the latest activityId and use that in the field instead. This will be a different method, so I hope the above is possible.


anders.e.jonsson
Contributor
Forum|alt.badge.img+1

Hi ​@Thomas Lind ,

Thanks!

Unfortunately, there are spaces, and sometimes several lines come with the same number. ActivityId is much like order number on order lines. Each order/ActivityId can have several rows.
But the value in the ActivityId field that comes in a table called ActivityOccurrence_Tx_Root is the highest. In the batch I read in, the value is 4,108.

BR
Anders


Forum|alt.badge.img+1
  • Contributor
  • 17 replies
  • Answer
  • March 3, 2025

If you are sure that no date returns more than the 10.000 values you could use the “dynamic values from SQL query” in the TimeXtender REST connector with something like this

Then reference the DateFrom and DateTo values in your API calls and thereby creating an API call per date

 


anders.e.jonsson
Contributor
Forum|alt.badge.img+1

Hi ​@nmo,

Thanks, yes that could have worked. But unfortunately there seems to be more than 10 000 rows every day.

BR
Anders


anders.e.jonsson
Contributor
Forum|alt.badge.img+1

Hi ​@nmo,

I misunderstood your suggestion. I works. 😀 Only about 1500 rows per day.
But there seems to be some problems with the API.
But you solution should fix my original problem.

Thanks!
Anders


anders.e.jonsson
Contributor
Forum|alt.badge.img+1

Hi ​@Thomas Lind ,

It works fine, but the account I specify is not used to run the query. It appears to be the ODX/Ingest service account being used.

BR
Anders


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings