Update Numeric Order within a script based on a value update

[Ahmed_Elagami]

Hello beautiful people.

I have a table with 100 kids channels maybe more in future. as we have different territories we push our channels to, we needed to shuffle those channels according to the territory. so manual adjust is needed. 

The main request here is : to be able to change a channel order Manually like I wanted Tigilli channel to be No 1 instead of 5. Now I have Smurfs TV as 1 , it should update to no 2 and Disney Plus channel is there is No 2 , it should update to No 3 and goes on till the end of list.

As Logic : when I change the order value in a #Order field what should happens is all other higher values including the same older value that i have changed should update (+1) the numbers order sequentially according to that to still give me 100 channels with the new order.

I have tried this script, but I'm not going anywhere. Can you please help me rewrite the script in a way that works

const tableName = "Table 1"; // my table name
const fieldName = "Order"; // My order field name

// Fetch all records in the table
let table = base.getTable(tableName);
let records = await table.selectRecordsAsync();

// Get the modified record
let modifiedRecord = input.record;

if (modifiedRecord) {
    let modifiedValue = modifiedRecord.getCellValue(fieldName);

    // Create an array to hold the updated order values
    let updatedOrderValues = [];

    // Iterate through all records
    for (let record of records.records) {
        let currentValue = record.getCellValue(fieldName);
        if (currentValue <= modifiedValue) {
            // If the current value is less than or equal to the modified value, keep it as is
            updatedOrderValues.push(currentValue);
        } else {
            // If the current value is greater than the modified value, increment it by 1
            updatedOrderValues.push(currentValue + 1);
        }
    }

    // Update the order values for all records
    for (let i = 0; i < records.records.length; i++) {
        let record = records.records[i];
        await table.updateRecordAsync(record, {
            [fieldName]: updatedOrderValues[i]
        });
    }
}

Can you please help modifying the script or send me a base that is working with the same logic ?
My Best
Ahmed

[Alexey_Gusev]

Hi,
your logic is not quite clear. In your example, numbers from 6 to 10 should remain unchanged. You need to change values between old and new. And what happens if you raise the number? For example, when you set Disney Plus from 2 to 5? Other problem is that you cannot directly get 'old' value. Of course, you can iterate from 1 to 100 and which number is absent, that is old. Or you can get the sum of all available numbers except changed value and then extract this sum from 1+2+3+...98+99+100  Which is (n+1)*n/2 = 5050 in the case of 100 numbers.

[Ahmed_Elagami]

Hi Alexey,
Thanks for your reply. You are absolutely right. I didn't think it that way. I will try to adapt your logic and come back with some script ideas, or maybe someone would like to help me get the right function script to go through this. Appreciate it.

[ScottWorld]

I think the best way to do this would just be to to re-sort ALL the records by 2 values (i.e. a hierarchical sort):

Sort the records by their new numbers in ascending order (0 to 9), but then within each number, sort by modification date in descending order (newest modification date to oldest modification date).

Then, you can simply start at the top, and reassign incremental numbers for all the records from top to bottom.

[kuovonne]

Can you tell more about where this script came from? Did you write it yourself from scratch? Did you use help from AI? Did you get help from another human? Did you find it on the internet? What is your skill level with writing JavaScript?

Off the top of my head, I see two major issues.

1. Your script is not getting the triggering record correctly. I expect that this line has wavy red underlines in the scripting editor because it is not a valid way to get a record in Airtable scripting. 

let modifiedRecord = input.record;

 The proper way to get the record depends on whether the script needs to run as an automation script or in scripting extension. 

2. As Alexey pointed out, your logic does not match what you actually want. 

Not that if you want to use Scott’s method of having two sorts, you may want to query to view to get the records in order. If you only query the table, the record may not be in the order you expect.

Here are some other possible techniques. 

- Have the user drag and drop the records in the desired order in the view. Then run scripting extension to renumber records in view order. 

- Have the user renumber with a decimal that would provide the correct numeric sort. For example, to move a record to the beginning, the user would type 0.5 instead of 1. Then sort the records according the sort order and everything will be in the correct order. Finally have the script renumber the records sequentially according to the sort order.