Getting started with Airtable, importing JSON, data structure

Ambroise_Dhenai · ‎Feb 23, 2019

I’m fairly new to AirTable and I’m trying to get a peak of all the possibility this tool has to offer.

I’m thinking about migrating an app, which is currently configured using a JSON format, to AirTable. The goal is to allow my teammates to see/update the data. Also, I saw there are tons of possibilities with AirTable and I’m also looking for creating forms and user interfaces eventually. Basically, AirTable may replace the backoffice I’m supposed to build.

But I have a few questions about how I should proceed, and I hope I can get some advices here! :slightly_smiling_face:

JSON to AirTable

AirTable doesn’t support JSON format. Therefore, I’ll convert JSON to CSV and then import the CSV into AirTable.

But I wonder how I should convert those data to be efficient in AirTable and allow proper usage using JavaScript when fetching the records later on, through the API.

Here is my data structure:

[
  {
    "state": "active",
    "name": "name",
    "label": "Label",
    "logo": {
      "url": "url-img",
      "link": {
        "url": "url-webpage",
        "target": "_blank"
      },
      "title": "Title",
      "width": "1293px",
      "height": "298px",
      "alt": "Logo X"
    }
]

This gets converted in CSV as follow, using the dot . as depth separator:

"state","name","label","logo.url","logo.link.url","logo.link.target","logo.title","logo.width","logo.height","logo.alt"

Is it the recommended way to handle nested fields?

I could also use another table to store the logo, but I don’t think that’s necessary because it’s a 1-1 relationship.
And I’d still have nested field with logo.link.url so it wouldn’t fix the issue either.

Selecting the primary field when importing

Unfortunately, there is no way to select the primary field when importing a CSV as a new table. Using the following CSV will therefore configure the state as primary field where I’d like the name to be the PK. Is there any workaround besides customising the CSV fields order? (which would require some programming #boring)

"state","name","label"

API Caching

I saw that the API rate limit is 5 request per second , I wonder if there are smart ways to configure a caching mechanism within AirTable. For instance, apply a default caching strategy when using API calls, and configure a cache invalidation/update when a row gets updated, every 30s at most, or something like that.

The idea is to avoid the setup of my own caching mechanism (Redis, most likely) and rely on a predefined caching automated behaviour which would update the cache if there is a change in the data, and only from time to time. I could implement this, but if there is a trick/built-in it’d be awesome!

Since I’m planning on having a web app hitting AirTable API (data source), I’ll have to setup some caching mechanism very quickly.

Primary field

Coming from the DB world, I’m used to having a primary key that is often a UUID or auto-incremented value. AirTable seem not to rely on such behaviour and usually use a comprehensive label field as PK.

While using a comprehensive value as PK has great benefits in the understanding of the data, I wonder if that is possible to use a auto-generated field as PK and another more “user-friendly” field to be displayed instead of the PK.

This would allow to use an auto-increment PK field while displaying the label field in other tables.

Tables and fields naming conventions

On most of the AirTable official examples, Tables and Fields have “humanized” names, usually starting with an Uppercase letter. While this is better for the visual experience, I wonder what are the recommendations regarding this naming convention when using the API. Is it possible to have a “display name” and an “internal name”? For instance, display Groups on AirTable but getting back a groups through the API?

Thanks for your help!

Ambroise_Dhenai · ‎Feb 23, 2019

Alright, I’m gonna answer a few of my own questions after looking in-depth and trying things out:

JSON to AirTable

Using a dot . as a separator works very well in addition of https://github.com/hughsk/flat

I was able to JSON->CSV and then use the API to convert the records back into JavaScript objects.

API Caching

Haven’t found what I was looking for, looks like I’ll need to put a Redis instance in between. Or I could use this as well https://github.com/Airtable/airtable.js/issues/47 at start, but Redis looks like the only viable solution at the moment

Tables and fields naming conventions

The table name doesn’t matter, when doing so:

base('groups').select({
  view: 'Grid view',
}).firstPage(function (err, records) {
  if (err) {
    console.error(err);
    return;
  }
  records.forEach(function (record) {
    groups.push(unflatten(record.fields, { object: true }));
  });

  console.log(JSON.stringify(groups, null, 2));
});

Whether I use base('groups') or base('Groups') or base('GROUPS') the case isn’t an issue, it finds the table correctly in any case.

It works differently for fields though, they are case sensitive. Changing them in the UI will affect the API results accordingly in real time. => I’ll keep naming my columns in a developer format, not human-friendly format.

Joseph_Sondow · ‎May 26, 2019

Regarding your wish for an auto-generated primary key, Airtable does already provide an autogenerated field called “id” where the values look like “rec” followed by a string of alphanumeric characters such as “recxz7mE9Scn70Mwn” or “recm3PjV0FPEnio9O”. However, I only seem to have access to read that field through the API. I don’t see a way to make that field appear in Airtable’s web GUI.

There’s also a field type called Autonumber which assigns each row an incrementing integer number. That’s near the bottom of the field type drop-down menu, so perhaps the developers added that feature after you posted your questions.

JonathanBowen · ‎May 26, 2019

HI @Joseph_Sondow - you can use the formula:

RECORD_ID()

To show the internal key for a record in the base GUI.

JB

John_YS · ‎Dec 06, 2020

JSON Import Block can be used for a one time import of JSON.

We built the application at SVI to solve one of our own problems.

It’s open source on Github and might provide a good starting point if you’re looking to integrate with your existing application.

Hope this helps!

John YS