Automatic UPC and/or find missing number in sequence?

[matt_stewart1]

Trying to automate our UPC code generation (not the barcode image). We have our company prefix, and I know how to do the check sum. What I can’t figure out in AT is to process the current numbers already used/assigned in our block, and see what is the first missing number in the sequence.

So we should have 10K numbers in our block, 00000 - 99999.
We have used random numbers in past, all done manually. I would like to do this automatically in future by looking at that above sequence, removing anything that has been used, and then using the lowest number possible to start auto assigning SKU’s to projects moving forward.

If this needs to be a script, can it be run by a button and/or an automation triggered by conditions?

[Rupert_Hoffsch1]

Hi @matt_stewart1, this can definitely be done. Airtable also has an Autonumber field, but I usually go for a script since the autonumber is saved “in the background”, aka if you delete a record, the next one will still get a new number (thereby skipping one number, the number of the deleted record).

A script could run every time a record is created and is usually very straightforward. You code will in the end be a string by stitching together the autonumber with your prefix. I don’t know how familiar you are with Javascript, but here is an example of a script I wrote & use:

// calculate highest number
let highestNumber = 0;

records.map((rec, i, arr) => {
    // this ensures that the script works for the first record as well (numbers starting at 10000 in this case)
    if(arr.length === 1) {
        highestNumber = 9999;
        return; 
    }     
    const currentNumber = rec.getCellValue("NumberField");
    if(currentNumber > highestNumber) highestNumber = currentNumber;
})

// update record
if(recId) {
    await table.updateRecordAsync(recId, {
        "NumberField": highestNumber + 1
    })
}

Let me know if you need further help with this!

[matt_stewart1]

Thanks @Rupert_Hoffschmidt … however your script sounds like its taking highest number used so far and building off of that? I am trying to find the missing numbers since unfortunately teams have used a variety of numbers from all over our available sequence.

[matt_stewart1]

Rupert_Hoffschmidt:

// calculate highest number
let highestNumber = 0;

records.map((rec, i, arr) => {
    // this ensures that the script works for the first record as well (numbers starting at 10000 in this case)
    if(arr.length === 1) {
        highestNumber = 9999;
        return; 
    }     
    const currentNumber = rec.getCellValue("NumberField");
    if(currentNumber > highestNumber) highestNumber = currentNumber;
})

// update record
if(recId) {
    await table.updateRecordAsync(recId, {
        "NumberField": highestNumber + 1
    })
}

Thinking of trying this with a new sequence/block… so we would start at 00000 with max of 99999. I tried updating your script but getting errors. Are you setting any input variables on your automation to make this work?

[Rupert_Hoffsch1 · Accepted solution]

Yes, this is just the part that is calculating the highest number. You would also need to query all records first (variable is called records here in the script). A classic query in Airtable looks like this:

const table = base.getTable("YOUR TABLE NAME");
const records = await table.selectRecordsAsync({fields: ["FIELD 1", "FIELD 2", ...]}).then(result => result.records);

Also I’m passing recId to the table.updateRecordAsync(). Since this is an automation script, this record id is passed as an input (set on the left side) and then called via input.config().

const {recId} = input.config();

So all in all:

const table = base.getTable("YOUR TABLE NAME");
const records = await table.selectRecordsAsync({fields: ["FIELD 1", "FIELD 2", ...]}).then(result => result.records);
const {recId} = input.config();

// calculate highest number
let highestNumber = 0;

records.map((rec, i, arr) => {
    // this ensures that the script works for the first record as well (numbers starting at 10000 in this case)
    if(arr.length === 1) {
        highestNumber = 9999;
        return; 
    }     
    const currentNumber = rec.getCellValue("NumberField");
    if(currentNumber > highestNumber) highestNumber = currentNumber;
})

// update record
if(recId) {
    await table.updateRecordAsync(recId, {
        "NumberField": highestNumber + 1
    })
}

Re. finding missing numbers: Maybe it would be better to find the first highest number then.

[matt_stewart1]

Rupert_Hoffschmidt:

Thank you SOO much!!!

[Nadege]

@Rupert_Hoffsch1 You're the man 🙂 Not only in Docs Automator but also in stuff like this. I'm impressed 🙂