Records not sorting properly in automation script

[Tonal_Chaos_Producti]

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,});
}```

[Adam_TheTimeSavingCo]

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

[Tonal_Chaos_Producti]

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”

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!")
    }

}

[kuovonne]

@Tonal_Chaos_Producti

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

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”.

[Tonal_Chaos_Producti]

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)

Incorrect (when I run the code in the Automation script)

[Tonal_Chaos_Producti]

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.

[kuovonne]

Thank you for the screen shots.

@Tonal_Chaos_Producti

Correct (when I run the same code in a script that isn’t in Automations)
…
Incorrect (when I run the code in the Automation script)

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?