Colums out of order

[Russell_Born]

When the data from the table is retrieved through the API, the field order (columns) don’t always come in in the same order that is in the view. For instance, if I have Column1, Column2, Coulmn3 in that order in the Airtable, it might come in with Column3 first, then Column1 second, and Column 2 last.

I am using the standard code from the API page and this to display the results:

const allRecords = records.map((record) => record.fields);
console.log({ allRecords });

It appears that if I make a new table and make the fields it will work. But when the fields (columns) are modified, like change the order or delete columns or add some in, the order gets messed up.Specifying what fields to get doesn’t seem to help.

I made a new table with three columns, put data in, it read it correctly. I added a 4th column, then moved it over between the 2nd and 4th columns and it still shows the original order I created the table in with the newly created column that I moved last.

Can’t say I can find something consistent. I made another new table with 3 columns. Put data in all the boxes but one. It read it as the 1st columns, then 3rd, then 2nd.

Thanks,

Russell

using:
console.log(‘Retrieved’, record.fields);

The order of the fields in the in the view is (left to right): Name, IN, Location, Out Time, In Time.

[Bill_French]

Russell_Born:

But when the fields (columns) are modified, like change the order or delete columns or add some in, the order gets messed up.Specifying what fields to get doesn’t seem to help.

A JSON response document is accessible in any order; it should not matter. Asking for … results.fields[“Column 3”] or results.fields[“Column 1”] doesn’t matter; these are logical references, not physical.

Am I missing something?

[Russell_Born]

You have me at a loss since I am new to this but I am using the standard JavaScript that Airtable gives you on the API page similar to this:

    ar Airtable = require('airtable');
    var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('appmGatIa3ezEl8f9');

    base('foofoo').select({
        // Selecting the first 3 records in Main View:
        maxRecords: 3,
        view: "Main View"
    }).eachPage(function page(records, fetchNextPage) {
        // This function (`page`) will get called for each page of records.

        records.forEach(function(record) {
            console.log('Retrieved', record.get('Name'));
        });

        // To fetch the next page of records, call `fetchNextPage`.
        // If there are more records, `page` will get called again.
        // If there are no more records, `done` will get called.
        fetchNextPage();

    }, function done(err) {
        if (err) { console.error(err); return; }
    });

I then map the records to allRecords and print it out. The output is random in it’s column order, at least it is not like it is in the view. Again, I am new to this and JavaScript but I would rather not limit the columns I read or output, meaning the table could change, I could move columns around, although this seem to have no affect on the order. I don’t want to implicitly read Column1, Column2…etc. or display Column1, Column2…etc. I would like to have it setup in the table view, read it in as it is, then output it back out as it is ordered. I’m not taking about row order, that actually seems to work fine.

Maybe this is not how it works. What I am doing now work, I mean I can read anything in and get anything back out, just not in the order I expect. I can also map the records implicitly by saying what key/value I want in what order, but then this is not flexible since if I add a column or use another table, I would need to modify the code.

[Bill_French]

Russell_Born:

I don’t want to implicitly read Column1, Column2…etc. or display Column1, Column2…etc. I would like to have it setup in the table view, read it in as it is, then output it back out as it is ordered.

Yep - I totally understand your objectives, but unfortunately, you are expecting the Airtable API to precisely reflect the view from which the API accessed the data. Ideally, this should be the case, but sadly, it isn’t.

The remedy is to read in the data and transform the columns into the order you desire. This is possible with JSON objects. This is not that different when we endeavour to sort (or group) rows to be in the order best suited for the reporting requirements.

[Russell_Born]

I’m not familiar with JSON objects, or what they refer to. Is this different than how I am reading them using the example from the Airtable API (shown above)? I am using the following code to arrange them as I want them. The ‘A’, ‘B’, ‘C’, etc. in the field name is because I renamed them to check to see if it was sorting them by name but it doesn’t mean anything beyond that. Not sure this is the most efficient but it works and I don’t know any better.

record.fields = {
                Name: [record.fields.A_Name], 
                Status: [record.fields.B_IN],
                Location: [record.fields.C_Location],
                'Out Time': [record.fields.D_OutTime],
                'Return Time': [record.fields.E_BackTime]
  };

[Bill_French]

Russell_Born:

Not sure this is the most efficient but it works

Yeah, this is the way you would transform the data into a new [bankable] order - it does what I suggested…

The remedy is to read in the data and transform the columns into the order you desire.

What you’re doing here is taking the JSON data that you received from Airtable and all its chaos and transforming into another JSON object. I would recommend that you get into the habit of consistently building these objects - here’s an example…

record.fields = {
   "Name"        : [record.fields.A_Name], 
   "Status"      : [record.fields.B_IN],
   "Location"    : [record.fields.C_Location],
   "Out Time"    : [record.fields.D_OutTime],
   "Return Time" : [record.fields.E_BackTime]
};

You can also create this object using your own variable thus exchanging record.fields with something ling thisRecord. Ergo…

thisRecord = {
   "Name"        : [record.fields.A_Name], 
   "Status"      : [record.fields.B_IN],
   "Location"    : [record.fields.C_Location],
   "Out Time"    : [record.fields.D_OutTime],
   "Return Time" : [record.fields.E_BackTime]
};

[Russell_Born]

Ok, thanks for your help, this makes sense.

[Dragoljub_Marko]

Hey Bill,
Is this ever going to be fixed? I made my whole app with the idea of being able to add new columns and rely on their order to select which ones are used. I would prefer to stay with Airtable on this project, but this really is a dealbreaker for me.
Thanks :slightly_smiling_face:

[Bill_French]

Dragoljub_Markovic:

Is this ever going to be fixed?

I don’t work for Airtable nor do I have a crystal ball.

Dragoljub_Markovic:

I made my whole app with the idea of being able to add new columns and rely on their order to select which ones are used.

That’s unlikely to happen. APIs are not end-user tools. As such, there are no incentives (or obligation per-se) to mimic any given UI behavior.

Dragoljub_Markovic:

but this really is a dealbreaker for me.

Show me an alternate tool like Airtable where the API exports information in the exact way it is shown in the UI.

[aareeph1]

I tried couplier.io add-on to export data from Airtable to Google Sheets and the order of columns are fine. I wonder how they are able to identify the column order.