Airtable Returning Arbitrary Records/Skipping Records

Hi E, and welcome to the community!

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?

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?

// 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));



}

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

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

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();



}

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

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

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.

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:

$(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();



}

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

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.

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.

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?

The API documentation provides the endpoint URL examples.

Good to hear.

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

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