Airtable Returning Arbitrary Records/Skipping Records

[E_Robert_Wald]

This is my first post to the community, so please be patient if I haven’t gotten this formatted right on the first go. Also please be patient if I don’t provide enough info right off. I can update as necessary :slightly_smiling_face:

Also please not that due to project limitations I am unable to use Node.JS, so I am prepping all my queries “manually” and sending them out via XMLHttpRequest(); once I have them built. I am also not using any other frameworks or services like Adalo or anything.

I am working on a small web app using Airtable, and I’ve come across a couple oddities when getting records. I’ve tried searching for info on this but haven’t had much luck.

1: When i send a request like this:
https://api.airtable.com/v0/[BASE ID]/Studies?maxRecords=5
I get back five records, but my expectation would be to get either the first five records or the last five records of the table. Instead I get what seem like five arbitrary records.
The table I’m testing with has records with a UniqueID field that goes ID1, ID2, ID3, ID4, ID5, (up to ID229) but the records returned are ID11, ID88, ID96, ID196, ID227
Not sure why this is happening, or how to try to track it down.

2: When I send a request like this:
https://api.airtable.com/v0/[BASE ID]/Studies
I get back 100 records, but some records appear to be left out; such as ID1, ID6, ID7, etc.

In both of the above cases, it seems as if it picks/omits the same records each time I refresh the page.

Stumped at the moment on what this is all about. Hoping someone can shed some light on these issues?

Thanks!

[Bill_French]

Hi E, and welcome to the community!

E_Robert_Wald:

I get back 100 records, but some records appear to be left out; such as ID1, ID6, ID7, etc.

Recommendation - share your code so that we can examine the details.

Another recommendation - create a request that returns 100% of the the records and then examine the data set to see if they are all there. If they are, examine the order. Is it possible you are seeing a filtered view of the data via the API?

[E_Robert_Wald]

Thank you, Bill. Hopefully this information will help. I use the following function to build the URL to send to the API:

// create the URL for a records search call for the provided table using the provided args
recordsSearch.buildURL = function(table, args){

    // variable to differentiate the values in the query string ('?' for the first, '&' for all remaining)
    firstArg = true;

    // variable to concatenate the necessary values for the returned url
    var url = AT_url; // the service url
        url += AT_base; // the name of the base
        url += '/' + table; // the name of the table

        // add any additional arguments to the query string
        if(args != undefined){
            // loop through the arguments
            for(arg in args){
                // declare query string variable (see firstArg info above)
                if(firstArg === true){
                    url += '?';
                    firstArg = false;
                }else{
                    url += '&';
                }
                // give the query string variable a name and value
                url += arg + '=' + args[arg];
            }
        }

        logToConsole(encodeURI(url));

       // return the full url
       return(encodeURI(url));

}

When I call this function as:

recordsSearch.buildURL(AT_tableID, args); // AT_TableID is a global var, args is undefined in this case

It returns:

https://api.airtable.com/v0/[Base ID]/Studies

I then send that URL using the following function…

function AT_runQuery(queryURL, callback){ // the queryURL is the return from recordsSearch.buildURL

    // create the request object
    var xmlhttp = new XMLHttpRequest();

        xmlhttp.open("GET", queryURL, true);
    
        xmlhttp.setRequestHeader('Authorization', 'Bearer ' + AT_key);
    
        xmlhttp.onreadystatechange = function() {

            if (this.readyState !== 4 || this.status !== 200) {
                return null; //error
            }
            
            callback(this.responseText);
            
        }

        // send the request object to the service
        xmlhttp.send();

}

This returns 100 records, but skips some (rows 1, 6, 7, 9, and additional rows throughout the result.)

I checked on whether they were coming back out of order or not and that doesn’t seem to be the case.

I do the same for the call for five records:

recordsSearch.buildURL(AT_tableID, {maxRecords:5});

It returns:

https://api.airtable.com/v0/[Base ID]/Studies?maxRecords=5

This returns five records, but returns what seems like an oddly arbitrary set of records (rows 11, 87, 95, 167, and 198.)

As previously mentioned, the records skipped are the same each time I refresh the page. If it isn’t an issue with the code, then maybe the table? Any idea what I need to look for?

[Bill_French]

Okay, here are some things to consider…

The request URL itself…

Whenever I’m trying to debug an API problem, I always simplify, simplify, simplify. Using a big function to generate what amounts to a 50 character string is the first to go. I suggest removing the complexities and just create a variable to hold the API endpoint and arguments. Once you know it works well, then you can complexity the approach.

let atURL = "https://api.airtable.com/v0/[Base ID]/Studies";

One word of caution - the table name in the URL must be URL-encoded if there are spaces in the name.

XMLHTTP()…

I would have a read of this article (Why I still use XHR instead of the Fetch API). It’s not exactly what the title suggests. :winking_face:

As the author makes clear, you will invariably need to make many subsequent calls to get more than 100 records, yet another challenge that Chinara James has addressed with this excellent guide using the Airtable SDK in NodeJS.

As the author of the fetch article makes clear, xmlhttp() hits a brick wall when it comes to recursive processing.

When working with APIs, you might need to make an API call, then get that response and make another call, and then another one, or do something with that data. This can result in nested XHR calls several layers deep. The Fetch API uses promises, so you can chain .then() methods together as many times as needed to keep working with your data.

API Climate…

I feel uncomfortable about making any further recommendations without knowing the climate where you are developing this API call to Airtable. If you are able to use XMLHTTP() in that climate it likely also supports fetch().

I typically use a variant of fetch() in the Google Cloud Platform (urlFetch). This is a kinder, gentler development environment with vast advantages, a subject for another time. But, in just a few simple steps, the Google scripting ecosystem (aka Google Apps Script) provides an excellent learning environment as evidenced by this post.

Another recommended exercise is to look at Autocode, an API development environment that is able to generate the code for you, host it, and allow you to manage it. It also supports direct plugins and examples for Airtable and has a pretty useful JSON query language.

Conclusions…

• The API results that are missing records should not be happening. Something is really off about your script that would cause this.
• The use of xmlhttp() will work; it’s just a bit more tedious to build, maintain, and debug.
• Without knowing the overarching objectives of your specific API endeavour, it’s difficult to steer you into calmer waters.

[E_Robert_Wald]

For “The API Climate…”

If I’m understanding “climate” correctly, I am developing on my local machine. Due to project restrictions the app has to be virtually plain vanilla to minimize external dependancies in the interest of portability (having a copy of jQuery in a scripts folder is fine, but Node.js is out) so unless I can package the entire dev project in a zip to send to the client to use by unzipping, it’s a no-go (not ideal, I know, but it is what it is).

For " XMLHTTP()…"

I looked over the article but at first glance I’m kind of confused. Right at the top is an update line about being “all-in on fetch()” so I’m not sure if I’m reading this to assist my XMLHTTP or to ditch it altogether? I’m willing to see if fetch() will work, but please see the results below as I am still not understanding the results I’m getting from the URL

For “The Request URL itself…”

I’ve tried to strip things down without the other functions and am just running this as a test:

$(document).ready(function(){

    var xmlhttp = new XMLHttpRequest();

        xmlhttp.open('GET', 'https://api.airtable.com/v0/[BASE ID]/Studies', true);
    
        xmlhttp.setRequestHeader('Authorization', 'Bearer ' + AT_key);
    
        xmlhttp.onreadystatechange = function() {

            if (this.readyState !== 4 || this.status !== 200) {
                return null; //error
            }
            
            var stuff = JSON.parse(this.responseText)
            console.log(stuff.records);
            for(record in stuff.records){
                console.log(stuff.records[record].fields.UniqueID)
            }
            
        }

        // send the request object to the service
        xmlhttp.send();

}

The log for stuff.records[record].fields.UniqueID shows:

ID88
ID227
ID196
ID11
ID96
ID8
ID25
ID162
ID3
ID204
ID211
ID152
ID57
ID147
ID2
ID185
ID225
ID153
ID5
ID177
ID63
ID13
ID52
ID131
ID194
ID140
ID166
ID164
ID240
ID170
ID26
ID219
ID42
ID21
ID64
ID255
ID210
ID23
ID188
ID71
ID78
ID93
ID35
ID179
ID145
ID15
ID237
ID46
ID241
ID135
ID252
ID161
ID249
ID235
ID134
ID75
ID221
ID79
ID85
ID39
ID48
ID226
ID230
ID133
ID217
ID70
ID182
ID17
ID165
ID238
ID215
ID224
ID95
ID16
ID137
ID187
ID92
ID4
ID205
ID231
ID233
ID198
ID89
ID30
ID28
ID55
ID260
ID83
ID41
ID155
ID229
ID201
ID197
ID175
ID254
ID10
ID81
ID69
ID62
ID98

This result skips the same records and is out of order vs my expectation that I would get the first 100 records in the table. Here is a screenshot of the first 35 records to show their corresponding UniqueIDs:

[Bill_French]

Robert,

I’m intrigued by this mystery, so I’ll continue the quest to learn the cause.

I don’t believe the API is skipping records. Rather, I do believe the records you perceive as “skipped” are actually outside the first 100 rows in the data set. If I’m right, your unique IDs are ordered physically, but the API is returning them in logical order.

Tell me - we’re the unique IDs created sequentially as the records were created?

Also, have the records been dragged in the table to fit a specific and desired order?

Lastly, please show a screenshot of the table with ID88 in view. Then perform this test - drag ID88 to the bottom of the table and RE-run the API process to see if it is skipped.

[E_Robert_Wald]

Thanks for your patience in trying to help with this.

I didn’t create the table, so I sent your questions to the person who did and he responded with:

Q: Tell me - we’re the unique IDs created sequentially as the records were created?
A: Yes. The ID is sequential and created automatically through the Autonumber field type.

Q: Also, have the records been dragged in the table to fit a specific and desired order?
A:. No

I also tried reordering the field with ID88 in the table…

Before:

After:

Either way, the ID88 record is still the first record in the result.

[Bill_French · Accepted solution]

E_Robert_Wald:

Either way, the ID88 record is still the first record in the result.

So, this tells us that the API is pulling the data logically (as expected), and the unique auto IDs are not in sequential order with the logical order of the data despite answering the first question as “Yes”. This further tells me that perhaps some records have been deleted and ID numbers possibly reassigned?

I would try one more test -

• Create a view that is sorted by ID.
• Change the API call to use the view instead of the table.

My assessment is leaning toward a test strategy that involves expanding your API process to get 100% of the data. Then sort the data (in your script), then log the data to the console to see if (a) all records are accounted for, and (b) all IDs are accounted for and ordered by the ID values.

[E_Robert_Wald]

Wow! What a process, but illuminating along the way!

As you suggested, I created a view sorted by ID and changed the call to use that view (I had to make a blind guess on how to do that via URL since I didn’t find an example via google search.)

Success! The result is now what I had expected from the get go.

I also backtracked through the changes I made while working with you and testing alternatives for the URL building functions, using those functions again, and those worked great again. Also, thanks to this process, I was able to identify an unrelated bug in my code that wasn’t reading the offset properly for calls over 100.

Thank you so much for taking the time to help me get this sorted out!

One last little bit to potentially save posting another topic:

Am I correct in my findings that it’s not currently possible to get the info for a field through the API? Like getting the field type for that field, or to be able to get a list of the values for a field like Collaborators?

[Bill_French]

E_Robert_Wald:

I had to make a blind guess on how to do that via URL since I didn’t find an example via google search.

The API documentation provides the endpoint URL examples.

E_Robert_Wald:

Success! The result is now what I had expected from the get go.

Good to hear.

E_Robert_Wald:

it’s not currently possible to get the info for a field through the API?

This is correct, although, there are some approaches to overcome this limitation.

E_Robert_Wald:

Like getting the field type for that field, or to be able to get a list of the values for a field like Collaborators?

Correct. We’re living like animals until they expose the underlying schema.