Creating a custom incrementing Primary Key without using an autonumber

[Hamish_Ahern]

I have a table called “Settings” and a field called “Next Number” and a field called “Format”

Next Number is 101000
Format is ‘TRnnnnnn’

How do I write a script that when I press run, it updates all blank Primary Keys on my view, with the new numbers in sequence, and updates the Next Number setting, ready for the next time I run the auto sequence script.

I have tried using a formula, but that doesn’t work because I need to use the next number from the other table, not from the same table.

I have tried using a script but I get an error all the time trying to update my barcode primary key, so thats very weird.

Type 'string' is not assignable to type '{ text: string; type?: string; }'.(2322)

and

Can't set cell values: No record with id rec711laJqEuPh65f exists

I have looked at all of the script examples. but there are not nearly enough simple examples to do even basic things like updating a simple barcode field.

Thanks in advance.

[Hamish_Ahern · Accepted solution]

oh i figured it out:

//Get the next Number from settings
let tableSetting = base.getTable(“Sequence”);
let queryResultSetting = await tableSetting.selectRecordsAsync();
let recordSetting = queryResultSetting.getRecord(queryResultSetting.recordIds[0]);
let nextnumber = recordSetting.getCellValue(“Next Sequence Number”);
let prefix = recordSetting.getCellValue(“Prefix”);

//output.inspect(nextnumber);

let table = base.getTable(“Main Table”);
let view = table.getView(‘view name with filter for empty primary key’);
let field = table.getField(“barcode id”);

// Load all of the records in the view
let result = await view.selectRecordsAsync();

    await table.updateRecordAsync(record, {
        'Barcode field Name': { text: prefix + nextnumber, },
    });

    countr = countr + 1;
    nextnumber = '' + (parseInt(nextnumber) + 1);


}

await tableSetting.updateRecordAsync(recordSetting, {
    'Next Sequence Number': nextnumber,
});

output.text(‘Done. ’ + countr + ’ records updated.’);