Help

Re: Records not sorting properly in automation script

1477 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Tonal_Chaos_Pro
4 - Data Explorer
4 - Data Explorer

Hi,

I have a track table and an album table.
When an album status changes to “ready for release”, I have an automation that finds tracks that belong to that album, then feeds those IDs to a script.
The script should do the following:

  • Based on the track info, populate an “internal number” (works fine)
  • Do another query on those same ids, but sort by this newly added “internal number” and another existing field, then update track numbers in sequence.

It appears that the new search does not return results sorted according to what is specified in my script.

Any ideas why?

I also have this script separately from automations, where I go and pick an album manually, and that one works 100%. I’m not sure why the same thing in automations isn’t behaving correctly.

// Sort by:
// - album (fieldToRank1), 
// - the newly added main track number (fieldToRank2), and 
// - nesting sort position (fieldToRank3) 
// to create and inject Harvest track number in each track.

var harvestTrackNumber = 0;
saRecords = await tracks.selectRecordsAsync({
    recordIds: ids, 
    sorts:[
        {field:fieldToRank1, direction:"asc"},
        {field:fieldToRank2, direction:"asc"},
        {field:fieldToRank3, direction:"asc"},            
        ],
    fields: [
        "Master ID",
        "SourceAudio ID",
        "Track Number",
        "INTERNAL: master track no",
        "Album Code",
        "Nesting Sort Position",
        "INTERNAL: Harvest no"],
});
for (let record of saRecords.records){
    harvestTrackNumber ++;
    // Update selected field with the new track number
    await tracks.updateRecordAsync(record.id, {"INTERNAL: Harvest no":harvestTrackNumber,});
}```
7 Replies 7

Hi Tonal, could I confirm that’s the full script? I’m asking because I don’t see where you’re declaring what fieldToRank1, fieldToRank2 and fieldToRank3 are

I’m also not entirely sure what you’d like the output to be. If you could provide a screenshot of data with the three fields you’re ranking by as well as INTERNAL: Harvest no that’d be really helpful

No, of course not. This is only the snippet of code that has gone wrong. Everything else in the code works fine.

fieldToRank1: album code
fieldToRank2: INTERNAL: master track no
fieldToRank3: Nesting Sort Position

Below is a screenshot showing these fields and the correct “INTERNAL: Harvest no”

Screen Shot 2022-08-06 at 8.50.54 AM

I purposely did not include the full code because it would probably be more confusing than helpful. The idea here is that we’re trying to augment a sheet with the original track numbering system with the track numbering system of another sheet format. The only thing I need help with is PART 3.

Full code:

/// PART ONE - CREATE ID DICTIONARY (WORKS FINE)
console.log(`Updating internal record of master track numbers for non-master tracks in main table of  ${base.name}!`);

let sourceAudio = base.getTable("Source Audio Metadata");
let fieldToRank1 = sourceAudio.getField("Album Code");
let fieldToRank2 = sourceAudio.getField("INTERNAL: master track no");
let fieldToRank3 = sourceAudio.getField("Nesting Sort Position");

console.log("Retrieving master ID mappings");

// get ids from previous step
const ids = input.config().records;

// get the table records
let saRecords = await sourceAudio.selectRecordsAsync({
    recordIds: ids, 
    fields: [
        "Master ID",
        "SourceAudio ID",
        "Track Number",
        "INTERNAL: master track no",
        "Album Code",
        "Nesting Sort Position",
        "INTERNAL: Harvest no"],
});

// dictionary of master IDs to master track number
var masterTrackMapping = {};

// loop through the records to create master -> track no dictionary
/** redacted logic, this works fine, and we get the masterTrackMapping dictionary**/



/// PART TWO - FIRST UPDATE - add dictionary lookup to each track
console.log("Updating records with master track number");
/** redacted logic, this works fine, and we get the "INTERNAL: master track no" populated as seen in the screenshot **/



/// PART THREE - FINAL UPDATE - sort by the newly added field in part 2 + "Nested Sort Position", then enumerate and inject "INTERNAL: Harvest no" for records in that order. 

// Sort by the newly added main track number, nesting sort position to create and inject Harvest track number
console.log("Sorting by master track no, nesting position & updating the harvest track number...")

saRecords = await sourceAudio.selectRecordsAsync({
    recordIds: ids, 
    sorts:[                                      // this sorting does not seem to be applying
        {field:fieldToRank1, direction:"asc"},          
        {field:fieldToRank2, direction:"asc"},         
        {field:fieldToRank3, direction:"asc"},            
        ],
    fields: [
        "Master ID",
        "SourceAudio ID",
        "Track Number",
        "INTERNAL: master track no",
        "Album Code",
        "Nesting Sort Position",
        "INTERNAL: Harvest no"],
});

var harvestTrackNumber = 0;

for (let record of saRecords.records){
    harvestTrackNumber ++;
    console.log("track no: " + harvestTrackNumber);
    // Update selected field with the new track number
    await sourceAudio.updateRecordAsync(record.id, {"INTERNAL: Harvest no":harvestTrackNumber,});

    // Let user know the script is running & notify when done
    if (harvestTrackNumber%5 === 0){
        console.log("Still running...")
    }
    if (harvestTrackNumber === saRecords.records.length){
        console.log("Done!")
    }

}

Can you give examples of the actual sort and the expected sort? If you sort (without grouping) in the same fields in the user interface, does the result match what the query provides or the expected sort?

Also, it looks like you are sorting on text fields that contain numbers. Airtable sometimes does a weird so that is a mix of a text based sort and a numeric sort when sorting such fields. For example, Airtable sometimes sorts “2” before “10”.

Negative, both fields 2 and 3 are text fields, as denoted by the “A” next to the column name.
Field 1 is the linked record “Album Code”. This shouldn’t matter in this case as they all have the same album code.

The screenshots are sorted in the same way as in my code, no grouping.

Below are the correct sort and the incorrect sort:

Correct (when I run the same code in a script that isn’t in Automations)
Screen Shot 2022-08-06 at 11.35.03 AM

Incorrect (when I run the code in the Automation script)
Screen Shot 2022-08-06 at 11.35.58 AM

I should add that the incorrect sort always seems to end up in the exact same order. So I suspect there is something happening where the data type returned in the fields from .selectRecordsAsync is different when I’m passing the IDs from the album through automation vs when I’m manually running the script and choose the album at the prompt. Maybe one is returning the field ID vs the actual value or something like that.

Thank you for the screen shots.

It is very unusual for the exact same code to produce different results like this. I personally have not experienced issues like this–I invariably track down the issue to either a difference in code or a difference in inputs. Sometimes the difference is in a totally different part of the code from where I have been initially looking.

Some more generic troubleshooting ideas.

  • If you include console.log({ids}), what is the value and data type of the ids variable? Is it an array or a text string? How are you setting ids in the button script?
  • Do you see “Still running…” the same number of times in both the button script and the automation script?
  • Have you clicked the button to update the automation after the latest code changes?
  • If you run the automation in “test” mode do you get the same result as when the automation is turned on?
  • If you do a console.log of the query results, do you get the expected order?
  • If you do a conosle.log of other intermediate results, do you get the expect
  • Can you strip down the script to only the third part to isolate and reproduce the issue?

I was searching for another "search" topic when I bumped into this thread.

If what is happening is what I think is happening, sorts doesn't work when paired with recordIds within selectRecordsAsync(). It's a royal pain, and I've logged this very problem with Airtable support months/years ago - but never heard anything from it.

Although I haven't tested to see if the limitation still exists within an Automation script, my current solution is manually sorting my record object, via code such as below.

let myTable = base.getTable("myTable");

let myRecords = await myTable.selectRecordsAsync( {
  fields : [ "myFieldA", "myFieldB"],
  recordIds : controlLinkedRecords.map( record => (record.id))
});

console.log(myRecords)

let detailedMyRecords = myRecords.records.map( record => ({
  id: record.id,
  "myFieldB" : record.getCellValue("myFieldB")
}))

console.log(detailedMyRecords)

let sortedMyRecords = detailedMyRecords.sort((a, b) => Number(a["myFieldB"]) - Number(b["myFieldB"]))

console.log(sortedMyRecords)

I'm often needing to sort Link record fields by secondary fields found within the target records, you can go a bit cross-eyed at first when learning how to do this - but it's super functional and allows you to do some very interesting things in Airtable.

What I'd really like to see however, is out-of-the-box support for a proper sort mechanism/state for Linked fields, along with their Lookup/Rollup derivatives. It's almost like, I'd like my very own "Sorted" field that draws from a Linked Field, and that also supports Lookup/Rollup fields, meaning the original Link field can continue to do what it does in an unsorted fashion (but don't get me started on how from the Grid View you can manually sort Linked records by expanding the cell and manually dragging and dropping... not very useful when dealing with countless linked records!!!)