Airtable Returning Arbitrary Records/Skipping Records
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
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.
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.
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!
Page 1 / 1
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?
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();
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?
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();
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?
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/aBase ID]/Studies";
One word of caution - the table name in the URL must be URL-encoded if there are spaces in the name.
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.
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/oBase ID]/Studies";
One word of caution - the table name in the URL must be URL-encoded if there are spaces in the name.
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);
var stuff = JSON.parse(this.responseText)
console.log(stuff.records);
for(record in stuff.records){
console.log(stuff.recordserecord].fields.UniqueID)
}
}
// send the request object to the service
xmlhttp.send();
}
The log for stuff.records}record].fields.UniqueID shows:
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:
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:
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:
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.
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.
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.
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.
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.
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?
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.
