Skip to main content

Hey everyone,

I am new to this forum! I thought id just throw this out there to see who I can stump and if I can get some help. I tried writing this with chatgpt but I dont think it has the most recent coding and API’s in its database. 

 

I am on the free version of Airtable.

 

The script I want to write is as follows. 

 

First, the script gives a new entry or existing entry under the ‘ORGANIZATIONS table a random 6 digit number that no other entry will have and places it under the "Identifier Number" field which is a single line text. Once this entry has a number, it will never change. 

As a note, it would be really awesome if the first two numbers could be the year the record was created (for example its 2023 and the first two numbers would be a 23). 

 

Next,  if any of the following fields match to new or existing entries: Street Address 1, Street Address 2, Zip, Organization Name. It will make sure all those above fields are correct and add the same “Identifier Number” to all the entries. 

 

BUT, if the Street Address 1, Street Address 2, or Zip differ, then it will give an added .1 to the Identifier Number (xxxxxx.1, xxxxxx.2 etc) to be used to identify different franchised locations. 

 

I also want it to keep specific parent and child records updated if a condition is selected. I am unsure if I can do all of this in one script! 

 

Thank you for your time!

Caleb

Here is the code that I have now right now..

 

// This script generates and assigns a unique identifier number to each record in the "ORGANIZATIONS" table based on matching criteria.

// Constants for table names and field names
const TABLE_NAME = "ORGANIZATIONS";
const ID_FIELD_NAME = "Identifier Number";
const ORG_NAME_FIELD_NAME = "ORGANIZATION NAME";
const ADDR1_FIELD_NAME = "Street Address 1";
const ADDR2_FIELD_NAME = "Street Address 2";
const ZIP_FIELD_NAME = "Zip";

// Generate a random 6 digit number with the first two digits representing the current year.
function generateIdentifierNumber() {
const year = new Date().getFullYear().toString().slice(-2);
const randNum = Math.floor(Math.random() * 900000) + 100000;
return year + randNum.toString();
}

// Find matching records in the table based on the given field values.
async function findMatchingRecords(table, orgName, addr1, addr2, zip) {
const queryFormula = `AND(
{${ORG_NAME_FIELD_NAME}}="${orgName}",
{${ADDR1_FIELD_NAME}}="${addr1}",
{${ADDR2_FIELD_NAME}}="${addr2}",
{${ZIP_FIELD_NAME}}="${zip}"
)`;
const queryResult = await table.select({
filterByFormula: queryFormula,
maxRecords: 100,
}).all();
return queryResult.records;
}

// Get the maximum suffix number in the list of identifier numbers.
function getMaxSuffixNumber(identifierNumbers) {
const suffixNumbers = identifierNumbers.map((id) => {
const suffix = id.slice(-2);
return Number(suffix);
});
const maxSuffix = Math.max(...suffixNumbers);
return maxSuffix;
}

// Assign an identifier number to a new or updated record based on matching criteria.
async function assignIdentifierNumber(record, table) {
const orgName = record.getCellValueAsString(ORG_NAME_FIELD_NAME);
const addr1 = record.getCellValueAsString(ADDR1_FIELD_NAME);
const addr2 = record.getCellValueAsString(ADDR2_FIELD_NAME);
const zip = record.getCellValueAsString(ZIP_FIELD_NAME);
 
// Find matching records in the table
const matchingRecords = await findMatchingRecords(table, orgName, addr1, addr2, zip);
 
// If no matching records found, generate a new identifier number
if (matchingRecords.length === 0) {
const newId = generateIdentifierNumber();
await table.updateRecordAsync(record, { [ID_FIELD_NAME]: newId });
} else {
// If all matching records have the same values, assign the same identifier number
const isAllSame = matchingRecords.every((rec) => {
const recOrgName = rec.getCellValueAsString(ORG_NAME_FIELD_NAME);
const recAddr1 = rec.getCellValueAsString(ADDR1_FIELD_NAME);
const recAddr2 = rec.getCellValueAsString(ADDR2_FIELD_NAME);
const recZip = rec.getCellValueAsString(ZIP_FIELD_NAME);
return orgName === recOrgName && addr1 === recAddr1 && addr2 === recAddr2 && zip === recZip;
});
if (isAllSame) {
const existingId = matchingRecords[0].getCellValueAsString(ID_FIELD_NAME);
await table.updateRecordAsync(record, { [ID_FIELD_NAME]: existingId });
} else {
// If matching records have different values, assign a new identifier number with a suffix
const existingIds

 

Terms & ConditionsAccessibility statement