Page through API response and format data for use in controls

Hey guys, new here. Super excited and also overwhelmed. I have an API query setup and filtered, but the API only returns 25 records at a time with a NEXT url for the next page. I am curious how I page through that to put them all in the same result set. I want to take some data like First and Last name to populate a drop down box but using the query says there is no data in the array. I assume I have to somehow format that response with a transformer to put it into an array? I am unsure quite how to do that. Appreciate any help.

Hi @RadianWear!

First, welcome to the Community + welcome to Retool :hugs:

Second, would either of these approaches work for you? Let me know if there's anything I can help clarify or explain!

yes, the second link looks to be exactly what I need. Thanks @victoria. Any other assistance on how to access that data for use like a dropdown or table?

Absolutely! You'll likely just need to do something like {{}}. Docs on using double curlies in Retool here.

Tables expect an object of arrays or an array of objects, which is what that second approach should result in.

Dropdowns expect a query input (if using the "Mapped" tab) and then you can specific specific label or value properties.

Let me know if you have any other questions :blush:

So I must be missing something, my API call is considered a query, but when I select it as the data source in the mapping tab it says "The selected data source is empty or could not be converted to an array." I do see the results of the query and they are in JSON format. Do I have to parse the results into a text array for use in the drop down?

1 Like

I kinda figured it out but it was weird I had to set my data source to Javascript and use {{ }}. Not sure why I needed the second data.

Hi @RadianWear! Awesome. Glad you were able to figure out the nested data structure. Occasionally, endpoints return data in this way and you can access it exactly as you've done it. If you're ever curious about the data structure being returned from a query so you know exactly how to access it, check it out in the left panel of your app!

Also, if you ever tire of entering everywhere for this query instead of just .data, you can enable a Transformer at the bottom of your query to return and now, everywhere you reference this query, you can just reference since it was already unnested in the Transformer!

well that is good to know :slight_smile: thanks @victoria.

I am trying to figure out how to go to the next page of results. I tried the post about the cursor based data but it just keeps looping and running the request over and over again, probably missing something stupid.

I can try to help you debug what you currently have if you feel comfortable sharing your JS!

@victoria sure, I basically just took the code from the post and swapped out the query name

/ Airtable uses cursor-based pagination. This function recursively calls the API as long as the cursor from the previous request exists. 
const fetchAll = (offset, records) => {

  // Base case: we've reached the end, and there are no more cursors.
  if (offset == null) return records
  // Wrap the query result in a promise
  return new Promise(resolve => {
    return MYQUERY.trigger({
    	additionalScope: {
    	onSuccess: queryResult => {
        // Add the records from this query to all the records we have so far
        const newResults = records.concat(queryResult.records)
        return resolve(fetchAll(queryResult.offset, newResults))
return fetchAll(0, [])

My API returns this as the cursor for the next page

   "links": {
        "self": "[stage]=snoozed",
        "next": "[stage]=snoozed"

I call that JS script as a query from the on success step of my first query as per the article.

Okay awesome! So in the original code, the stop condition is

if (offset == null)

When your query returns all the data, what does offset become? In some cases, it may be something like an empty string so you’d need to change your stop condition to

if (offset == ‘’)

I will double check when I get back to my computer but I think there is no "next" in the links section.

Sorry this fell off my radar, my API returns a SELF and NEXT link and there is an offset embedded in the link. Offset DOES = null in the url, but I wonder if my code needs to account for the SELF vs the NEXT url? How does it know which offset it is reading? Also, when I use offset as a url parameter it tells me it is not defined.

"links": {
"self": "[stage]=ready",
"next": "[stage]=ready"

No worries at all!

As for {{offset}} being defined in that query, that’s actually expected since the value is being passed in at runtime from the JS query. At the time of looking at this API query, no value for offset has been passed in yet.


I'm trying to "use Retool and Fauna" by following the instructions at Use Retool and Fauna to build a Todo app - Fauna Documentation.

When I (think I) do as instructed in "Edit the select1 form element to have the following properties" step, I get a "The selected data source is empty or could not be converted to an array" message.

If the solution is already in this thread, I don't have the knowledge to see it.

Please help.

@hyberson hello!

Would you mind writing this up as a new post? I suspect the issue is somewhere in your component settings (right panel) and how you're formatting the data you're passing into the select1, but we will see!

Feel free to tag me in your new post as well to make sure I see it :slightly_smiling_face:

Sure, Victoria.

Done: The selected data source is empty or could not be converted to an array

1 Like