Add record to another table, help!

[East_Lima_Realt]

hi, i’m exploring how to use scripts.
i have properties table and transactions table.
properties have property ID, status, owner, address, photos fields.

i wish to have a script wherein when status is changed from ‘active’ to ‘closed’, a record will be automatically created in transactions table, and copying the information like property ID, owner.

i’m new to airtable and hope to consult with experts here. thanks in advance.

[Julie_Burke]

I have the same issue!

[Sam_Cederwall]

Hello,

I do not believe that scripts in the scripting block can be run automatically in Airtable currently (hopefully someday though) so I might not advise going that route, I’m also just learning the Scripting block and am pretty inexperienced so I could be wrong.

Instead, I would encourage you to go look at programs like Zapier and Integromat. These would probably be able to accomplish what you need.

For example, if you have a view that filters records based on a ‘closed’ status, you could create a process in Zapier (called a zap) to create records in your transactions table with the appropriate information as soon as a record enters that view.

The overall process would look like this:
Status is changed to ‘Closed’ in the ‘properties table’ -> record goes to ‘Closed Status’ view -> Zapier finds that new record in the view -> Zapier creates a record in the ‘transactions table’

Hope that this was helpful!

[Rebecca_Meritz]

As @Sam_Cederwall pointed out scripting blocks cannot be automatically be run in Airtable.

However depending on your exact use case you might still find one useful though you would have to run in manually after you made every set of updates to the Propertes table.

Here is draft of a script which would add a Transaction for every record in the Properties table with the status closed that has not yet been accounted for in the Transactions table.

/*
The MIT-Zero License

Copyright (c) 2020 Bocoup

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.

*/

let propertiesTable = base.getTable("Properties");
let properitesQuery = await propertiesTable.selectRecordsAsync();
let propertyRecords = properitesQuery.records;

let transactionsTable = base.getTable("Transactions");
let transactionsQuery = await transactionsTable.selectRecordsAsync();
let transactionRecords = transactionsQuery.records;

let newTransactions = propertyRecords.filter(propertyRecord => {
    let isClosed = propertyRecord.getCellValueAsString("Status") === "Closed";
    let transactionRecordExists = transactionRecords.some(transactionRecord => (
        transactionRecord.getCellValue("Property")[0].id === propertyRecord.id
    ));
    // Only copy "Closed" Properties to the Transactions Table if the haven't already been recorder there
    return isClosed && !transactionRecordExists;
}).map(propertyRecord => ({
    fields: {
        "Property": [propertyRecord],
        // Add any other fields that you want copied from the Properites table to the Transaction Table to this fields object
        "Owner": propertyRecord.getCellValue("Owner")
    }
}));

output.text('Creating ' + newTransactions.length + ' Transaction Records for Properties that have the status Closed but have not yet been recorded in the transactions table.')

let batchSize = 50;
for (let i = 0; i < newTransactions.length; i += batchSize) {
    await transactionsTable.createRecordsAsync(newTransactions.slice(i, i + batchSize));
}