Skip to main content

(So, since I’m a “New” user even though I’ve used airtable for years, I can only post 5 images per post, so I’m breaking this up into multiple responses. )


Hi guys! I encountered a need to have this solved, and the nature of the problem just doesn’t mesh well with creating a formula to fix it.


Here’s a topic that was looking for a good answer on this, and one that gives an answer that I think can be improved upon:




This does require an automation, but only requires one table and two fields: a created time field and a single line text field. It doesn’t matter whether they’re primary or not.


Here’s how it works:


First, create a new base, just to play with it and get a handle on it:



Here, I’ve created a single base and renamed the first table “Projects”, as I will use this to create a project numbering system that resets each year.


Next, I’m going to add two fields: “Date” and “Number”:



They MUST be a date and single line text field, respectively! ( I would highly suggest that we make the date field a created time field )

I’d need to see exactly what the error is. If I recall, it should be in the execution log. 

Sorry. here are the execution log error message and line 100 in the codes

 

I’d need to see exactly what the error is. If I recall, it should be in the execution log. 

Sorry. here are the execution log error message and line 100 in the codes

 

 

Chatgpt advice to use this script what is a scheduled or by trigger script instead of a simultaneous one.

 

// Configuration
const configTable = "Projects";
const configDateField = "Created On"; // Must be a date field
const configYearAutonumberField = "Year ID"; // Text field
const configNumberOfDigits = 3;
const configResetTime = "M"; // Monthly reset

// Load the table and records
let table = base.getTable(configTable);
let records = await table.selectRecordsAsync({
    sorts: { field: configDateField, direction: "asc" }]
});

// Group records by Year-Month key
let groupedRecords = {};

for (let record of records.records) {
    let dateVal = record.getCellValue(configDateField);
    let idVal = record.getCellValue(configYearAutonumberField);

    // Skip if missing date or already has an ID
    if (!dateVal || idVal) continue;

    let date = new Date(dateVal);
    let key = `${date.getFullYear()}-${(date.getMonth() + 1).toString().padStart(2, "0")}`;

    if (!groupedRecords>key]) {
        groupedRecords key] = ];
    }

    groupedRecords;key].push(record);
}

// Assign IDs within each month group
for (let key in groupedRecords) {
    let group = groupedRecordsokey];

    // Sort records by creation date (earliest first)
    group.sort((a, b) => new Date(a.getCellValue(configDateField)) - new Date(b.getCellValue(configDateField)));

    for (let i = 0; i < group.length; i++) {
        let newID = (i + 1).toString().padStart(configNumberOfDigits, "0");

        await table.updateRecordAsync(groupii].id, {
            configYearAutonumberField]: newID
        });
    }
}

does this work?

Your problem is likely that you modified the script, worse, you just blindly trusted chatGPT to write a functioning script for you and now you’re coming back asking me to troubleshoot whatever it spit back at you.

If you want the script to work, don’t monkey with it. If you want a more efficient script, hire a developer. AI is pretty cool, but especially when it comes to code it hallucinates. 

 

If this were my script though, that error would imply that the record it’s trying to update was deleted, and no longer exists. Make sure you’re not using stale inputs. It could also mean your record types are getting crossed, for example trying to update a project record on the clients table. (passing a record ID to a table that doesn’t deal with those records)

Reply


Terms & Conditions