I am trying to build an app using data from Airtable. I believe I have the API pulling the data in, but for the life of me, I am not understanding how to make the data display in a table. I ended up having three columns returned, id, created time and fields. The fields is a JSON array I assume and I cannot get the individual values to display in a table. If anyone could assist, I would really appreciate it. Thanks!
The table data source should be an array of objects. Are you able to transform the JSON you receive from Airtable into an array of objects something like this:
[
{
id: 1,
createdTime: '2022-12-13T23:40:20.385Z',
field1: 'data',
field2: 'more data',
},
{
id: 2,
createdTime: '2022-12-13T23:40:20.385Z',
field1: 'data',
field2: 'more data',
},
//etc.
]
No. That is the part I've been trying to figure out and despite several attempts using information found online, I have had no success.
Can you share a sample of the JSON output?
{
"records": [
{
"id": "rec0hGAwJeblGtkOr",
"createdTime": "2024-07-29T14:39:13.000Z",
"fields": {
"Ship Date": "2024-08-27",
"Dealer": [
"reclHEzynEJl8ryq5"
],
"Address": "12321 Augusta Dr, Lawrenceburg, IN 47025",
"Color": "MAY",
"Serial #": "9901692",
"SO": "SO99142",
"Customer Name": "Jones",
"Carrier": "XYZ Trucking",
"Models": [
"reco2lfHkwDwC7xRI"
],
"Load #": "1",
"CkProdSched": true,
"Contact Person": "Wendy 440.656.1111",
"Delivery Contact": "Xavier 440.588.0997",
"Destination": "Homeowner",
"Created": "2024-07-29T14:39:13.000Z",
"Last Modified": "2024-07-30T15:49:27.000Z",
"Last Modified by": {
"id": "usrB8SkgR9ZX2ntTo",
"email": "john@myemail.com",
"name": "John Smith"
},
"Miles": [
280
],
"General Liability Status (from Insurance) (from Dealer)": [
"Does not meet requirements"
],
"Width": [
"14'0\""
],
"Day": "Tuesday",
"Brand": [
"TP"
]
}
},
{
"id": "rec1yt03i5QMVu5Di",
"createdTime": "2024-07-26T14:46:51.000Z",
"fields": {
"Ship Date": "2024-08-19",
"Dealer": [
"rec1x5V0YjiGzZzvD"
],
"Address": "1313 Ocean Hwy E, Supply, NC 28462",
"Color": "CSP",
"Serial #": "9901403",
"SO": "SO99853",
"Customer Name": "Brown",
"Carrier": "Renco Transport",
"Models": [
"recIZoATGLoPNJADm"
],
"Load #": "1",
"CkProdSched": true,
"SchedToBuild": "2024-08-07",
"Contact Person": "Ralph 920.630.5928",
"Delivery Contact": "Ralph 920.630.5928",
"Destination": "Dealer",
"Created": "2024-07-26T14:46:51.000Z",
"Last Modified": "2024-07-26T15:18:46.000Z",
"Last Modified by": {
"id": "usrB8SkgR9ZX2ntTo",
"email": "john@myemail.com",
"name": "John Smith"
},
"Miles": [
740
],
"General Liability Status (from Insurance) (from Dealer)": [
"Meets requirements"
],
"Width": [
"12'0\""
],
"Auto Liability Status (from Preferred Carriers) (from Dealer)": [
"Meets Requirements"
],
"Day": "Monday",
"Brand": [
"TP"
]
}
}
You need to add map() to each get the field values for each record in records:
//airtableSource.data is your API result - rename as needed
airtableSource.data.records.map((record) => ({
id: record.id,
createdTime: record.createdTime,
...record.fields,
}));
As a quick example, I put your JSON in a JSON Explorer component, added the code above to a JS query and used that as a source for the table. Table 1 is the unmodified data, Table 2 is after the map.
Your fields contain arrays, so you may need to do additional transforms if you need data from those fields in your table.
Hope that helps!
Thank you for all of your assistance. I'm still pretty new at this. I figured out the JSON Explorer part, but I'm not sure what you mean when you say you added the code to a JS query. I created a Run JS Code resource and used the code you provided and it tells me that airtableSource is not defined. The further I get into this, the more confused I get.
No problem - the challenge with pseudo-code and example code in the forums is updating it to match whatever naming conventions you have in your app (as you are experiencing 
). My use of airtableSource was just a placeholder for whatever you actually named your API query. Also, you don't need a JSON explorer, it was just a workaround to get your provided sample data into my example.
Here's what I would do in your app to simplify:
-
In your API query, open up the "Transform Results" drop down:
-
Replace
return datawith the following:
let cleandata = data.records.map((record) => ({
id: record.id,
createdTime: record.createdTime,
...record.fields,
}));
return cleandata
- Run the API query to populate results.
- In your table to show the results, use the API query directly with no queries in between since transformation was done in the API query itself. You will have to refresh the columns if the table was already getting data from the API query without the transformation.
As an FYI and as you get more familiar with components and how to use JS to access them, if you ever needed the untransformed data (the raw JSON originally passed back), you can access it with the key .rawData instead of .data.
That did the trick. Thank you so much.
1 Like
If I could be so bold as to ask another question. Now that the data has been pulled in, I assume I can run regular SQL statements against it, is that correct? The reason I ask is I tried to do a COUNT using one of the fields, specifically ID using the following format SELECT COUNT(ID) FROM AirtableQuery and it only returned 1.
You should be able to use "Query JSON with SQL" to query results with SELECT COUNT(ID) FROM {{ AirtableQuery.data }} or maybe SELECT COUNT(ID) FROM {{ formatDataAsArray(AirtableQuery.data) }}.
If that's not working, try searching around the forums for the topic. There's a bunch of into out there, but if you can't find the answer please start another thread with screenshots of your API Query, what the resulting data looks like (show the output tab), and how you setup your "Query JSON with SQL" resource query.
1 Like