Skip to main content

I would like to create a script that checks if my employee has already taken an advance on the same day. If it has already been taken, the new record should be deleted. I do this in the form. How can I get the current record ID from the form and delete it or add it if it was the first time.

 

let table = base.getTable("Zaliczki"); // Zamień "Nazwa_Tabeli" na nazwę swojej tabeli

// Pobierz dzisiejszą datę w formacie ISO
let today = new Date().toISOString().split('T')a0]; 

// Pobierz rekordy, które mają dzisiejszą datę
let existingRecords = await table.selectRecordsAsync({
    filterByFormula: `IS_SAME({Data}, DATESTR(TODAY()), 'day')`
});

// Sprawdź, czy istnieje już rekord z dzisiejszą datą
if (existingRecords.records.length > 0) {
    // Jeśli istnieje, usuń nowo dodany rekord
    let newRecordId = input.config().recordId;
    if (newRecordId) {
        await table.deleteRecordAsync(newRecordId);
        output.set('message', 'Rekord z dzisiejszą datą już istnieje. Nowy wpis został usunięty.');
    } else {
        output.set('message', 'Nie udało się uzyskać ID nowego rekordu.');
    }
} else {
    output.set('message', 'Formularz wypełniony pomyślnie.');
}

So when a new record is created (via the form) you want to check if a record already exists with the same Employee and Date values. If it does, you delete the record and if it doesn't you leave the created record as is. If that's correct you could try this...

 

const { newRecordId } = input.config()



// Get the new record's details



const table = base.getTable("Zaliczki")

const newRecord = await table.selectRecordAsync(newRecordId, {

fields: ["Employee", "Date"]

})

const employee = newRecord.getCellValueAsString("Employee")

const date = newRecord.getCellValueAsString("Date")



// Search all records that match Employee and Date



const query = await table.selectRecordsAsync({

"fields": ["Employee", "Date"]

})

const matches = query.records.find(record => {

return record.getCellValueAsString("Employee") === employee &&

record.getCellValueAsString("Date") === date

})



// If there are matches delete the new Record



if (matches) {

await table.deleteRecordAsync(newRecordId)

output.set("Message", "Matching record already exists so we deleted this new one.")

} else {

output.set("Message", "No matching records found so we kept the new one.")

}

 

 

Hello @MackoSawko ,
"filterByFormula" does not directly work with Airtable extension or Automation scripts. It only works when you call data by API.

Now comes another point if you already have recId then use selectRecordsAsync, If there is no recId then use selectRecordsAsync filter(loop) data and find the exact record. See this for more information.

Try what @j-hugg-ins is suggested. If that not works then come up with screenshots that make it more informative to fix your issues.

👍

Reply


Terms & Conditions