Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 15, 2020 02:14 PM
Hello. I’m learning the scripting block and not a programmer but hope to get my feet wet.
I have taken the basic script to “select a record” and added that to a button called COPY.
// Change this name to use a different table
let table = base.getTable("Table 3");
// Prompt the user to pick a record
// If this script is run from a button field, this will use the button's record instead.
let record = await input.recordAsync('Select a record to use', table);
if (record) {
// Customize this section to handle the selected record
// You can use record.getCellValue("Field name") to access
// cell values from the record
output.text(`You selected this record: ${record.name}`);
} else {
output.text('No record was selected');
}
But now I want to use the record selected to copy Field 1 (which is a lookup field) into Field 2 which is just a basic number field.
Can anyone point me how to “copy” data from one field to the other?
Jul 15, 2020 04:03 PM
I recently wrote a script that does this.
https://community.airtable.com/t/scripting-contest-using-newly-launched-button-field/31386/8
Jul 16, 2020 06:06 AM
thank you! Checking it out!
Jul 16, 2020 06:39 AM
@kuvonne is it possible to copy TO a linked field if the link exists? Thereby avoiding having to search for the linked field, pressing the add button and finding the matching record?
Jul 16, 2020 07:08 AM
Your tag for @kuovonne didn’t work. To tag someone in the current thread, type the @ preface, then wait a second and the forum will show you a list of everyone in the current thread. Use the up/down cursor keys to select the person you want to tag, then hit Enter and the tag will be inserted. No need to worry about possibly misspelling a username that way. :slightly_smiling_face:
To your question, you can copy the data anywhere else in the table with the right code. When you say you want to copy to “a linked field,” are you talking about a field in another table that’s connected via a link in the current table? I’m not sure I get the idea based on how you worded the question.
Jul 16, 2020 07:50 AM
Thank you @Justin_Barrett!
Yes, i think you have the right idea.
Essentially i want to add a button to “check” to see if a linked record exists. So if PQ1033 exists in the linked field, I’d like to select that linked field. If not, then just output a “No Match” text or something.
This is the code I have so far:
// edit the table and field names to match your base
const tableName = “Plates”;
const sourceFieldName = “Plate”;
const targetFieldName = “Match”;
const table = base.getTable(tableName);
const record = await input.recordAsync(“Pick a record”, table);
// change the function name below for your desired field types
const writeValue = await copyToSingleSelect(table, record, sourceFieldName, targetFieldName);
output.markdown(Copied value of **${sourceFieldName}** to **${targetFieldName}**
);
output.inspect(writeValue);
async function copyToSingleSelect(table, record, sourceFieldName, targetFieldName) {
let writeValue = record.getCellValueAsString(sourceFieldName);
if (writeValue) {
writeValue = {“name”: writeValue};
} else {
writeValue = null;
}
await table.updateRecordAsync(record, {[targetFieldName]: writeValue});
return writeValue;
}
But when I execute, I get:
ERROR
R: Can't set cell values: invalid cell value for field 'Match'.
Cell value has invalid format: <root> must be an array.
Linked records field value must be an array of objects with property 'id' corresponding to linked record id.
at copyToSingleSelect on line 19
at main on line 8
I notice in @kuovonne’s solution there was no function to copy data to a Linked Field.
Jul 16, 2020 07:53 AM
I guess I figured that I could “copy” in a value there since I already use a Zap to add data to that field and if there is a match, it does execute. But the rest of the zap fails if there is no match which is annoying.
Jul 16, 2020 08:01 AM
I guess ultimately I want a script to check for a match with data from another table.
I have table A being fed with a Zap with license plate numbers
I have table B having a list of License plate numbers and accounts linked
So when a new License plate is inputted into Table A, i want to display the linked Accounts with that license plate.
My understanding was that a script could not be triggered on a new record creation, so that I why i went with the button method and copying the data over. I sort of expected that if Table B had that license plate in it, then the copy would succeed, but if it did not, it would simply fail.
Jul 16, 2020 08:31 AM
In order to write to a linked record field, you need to get the record Id of the linked record and then write in the proper format. I did not include this situation in my initial script because it is a more complex situation than the rest of the script. (I also squeezed in writing that script while creating 3 custom blocks and various other projects, so I didn’t have much time for it.)
For your situation, you need to
Select the records from the target table, then search them for the a matching record using JavaScript.
Find the record id of the record that you want to link to.
Use the correct write format for a linked record when updating the current record.
I recommend reading the api documentation and working through the examples.
As for having the script triggered on new record creation, there was a post about beta for running scripts automatically on the creation of a new record. Unfortunately, I cannot find a link to that beta anymore.
Since you are already using Zapier, is there a particular reason you want to use scripting block instead of Zapier for this? You should be able to setup Zapier with a multi-step zaps where it looks for an existing record and creates one if it doesn’t exist yet.
Jul 16, 2020 08:38 AM
Here’s the link to the thread about the scripting action beta:
I’ve been using it for a little bit, and it’s incredibly useful. I think it would be a good solution in this case.