Adapting script to paste into linked field

[capt]

Hello there

I would like to avoid user copy and paste in one of our tables. For this is have created a button and modified the excellent ‘copy values from one field to another’ script by @JonathanBowen to my needs.

This works really well to copy a value from my source field to a target field, replacing the existing value in the target and leaving a create date in the source field.

Now what I want to achieve is that that target field is a linked primary field in another table.

At the moment users do this manually with copy and paste, but we do not want to introduce the idea of copy and paste as a function, a button would make this more elegant.

The idea behind it is that we use one table to build a product by formula from other field values, so that we end up with a full parts code in one field (the source). This is then pasted into the linked field to create a new record in a parts table.

However if I change the field name in the code from a standard text field to the linked field I get:
’string’ is not assignable to type '{ id: string; }

How do I adapt the script so it copies to the linked field?

Many thanks
Tom
Here is the said script (thanks again Jonathan for the inspiration):

// set the table
let partField = base.getTable("Source");
// get the table records
let parts= await partField.selectRecordsAsync();
// Select the current or desired record
let record = await input.recordAsync('Choose a record', partField); {

// set variables to the record values
let parts = record.getCellValue("Data");
let partCreate = record.getCellValue("Data 2");
    // set variable for today's date
let now = new Date().toLocaleDateString("en-GB");
  
// only run on records which have part codes
if (parts) {
    output.text(`Copying Part Codes for record ${record.name}`)
    // build the partCreate value from the part
    partCreate= parts;
    // update the old parts field with create date and reset the parts value
    partField.updateRecordAsync(record, {
        "Data 2":  "",
        "Data 2":  partCreate,
        "Data": "Created on " + now
    })        
}

}

[JonathanBowen]

Hi @capt - I can see a couple of things in the script that could be changed/improved, but could you post some screenshots of your table structure (maybe with data that you have added manually) so that it is easier to see the target you’re trying to hit here?

[capt]

Hi @JonathanBowen thanks for the reply, I was not online yesterday :slightly_smiling_face:

Here are two screen grabs:

This shows the table right now, I copied it and called it ‘Source 2’. This shows a Single Text field ‘Data’ that copies into field ‘Data 2’, also a Single Text field, shown by the green arrow.

The top two records have been actioned with the ‘Copy’ Button and you can see the 'Created date in the ‘Data’ field after the Run. The data should copy and paste into linked ‘Repository’ field (red arrow), which is the primary field (Single Text) in the Repository Table. So the error ‘string’ is not assignable to type '{ id: string; } happens when I use the linked field as target.

It occured to me that I need to create the linked field entry from a Formula field not Single Text. So I modified the table ‘Source’ to how I want the final version to work:

The Formula is similar to the one shown here, two SIngle Text fields ‘Part 1’ and ‘Part 2’ are combined into a part code in field ‘Data’. That part code should then be copied to the linked field ‘Repository’ to create that part entry in the ‘Repository’ table, which has other lookup fields to create a parts register. Right now the code does not function on pasting to a linked field but also does not work on the formula field.

I can understand the code by trial and error, but my knowledge does not extend to fixing this.

Hope this makes sense, thanks for your help
Tom

[JonathanBowen · Accepted solution]

Hi Tom - a few points that might help:

• you might not need the data field if this is just a way to join “part1” with “part2”. You could do this in the script directly:

let record = await input.recordAsync('Pick a record', sourceT);
let name = record.getCellValue('Part 1') + '-' + record.getCellValue('Part 2');
console.log(name);

• As you are running the script against a single selected record you don’t need this part of the script (which selects all records from the table):

let parts= await partField.selectRecordsAsync();

• You can’t copy a string value into a linked field. When you populate a linked record field in table A, referring to table B, there’s an assumption that you know the ID of the record in table B already and if you do know this, then the format would be:

let updatedRecord = await table.updateRecordAsync(record, {
    'Repo': [{id: idFromTableB}]
})

“idFromTableB” will take to form “recXXXXXXXXXXXXXX” - the internal Airtable record ID. To update a linked field you need an array of objects (as there could be more than one) and each object is of the form:

{id: someRecordId}

From what I understand though, the value created from part 1 and part 2 does not yet exist in the repo table, right? In which case you need to create it first (in the repo table), then link it (in the source table). This isn’t as hard as it might first appear!

let repoT = base.getTable('Repo');
let sourceT = base.getTable('Source');

let record = await input.recordAsync('Pick a record', sourceT);
let name = record.getCellValue('Part 1') + '-' + record.getCellValue('Part 2');

// create the record in the repo table
let newRepoRecord = await repoT.createRecordAsync({
        'Name': name
})
//note that newRepoRecord is the record ID of the created record
// see this by logging the result:

console.log(newRepoRecord);

// now take the newly created record ID
// and set the linked field from this
let updatedRecord = await sourceT.updateRecordAsync(record, {
    'Repo': [{id: newRepoRecord}]
})

• What if your new value already exists in the repo table? The above code will just create a duplicate record in repo table which probably isn’t what you want. If there’s a possibility of the same “data” value occurring more than once then instead creating the newRepoRecord you will need to check if this value exists in repo already - if it does, then get its id; if it doesn’t then create it and get its id (this is what we did above).

• Final point - I wouldn’t overwrite the “data” field with the created date value. In doing this, you’re using one field for two different things, which I tend to think is a bad idea. What I would do is create a new date field in the source table “Created in repo date” and just set this to be the date when it was created. You can do this in the update:

let updatedRecord = await sourceT.updateRecordAsync(record, {
    'Repo': [{id: newRepoRecord}],
    'Created in repo date': now
})

For info, this is my (simplified) table:

Hope that helps!

=======================

Want to learn Airtable Scripting? 1 day bootcamp coming soon!

=======================

[capt]

JonathanBowen:

you might not need the data field if this is just a way to join “part1” with “part2”. You could do this in the script directly:

Thanks @JonathanBowen, I will play with your suggestions. Just in response to the above, here is the user journey:

• User to determine client idea and type of part from a selection
• Selection is concatenated in the Data field and User can see result.
• If needed User will enter custom entry in a 3rd field that provides clarity if the other info is not clear.
• This is part of the concatenate formula so the Data field is the one constant

So I think it is important to retain the field to allow the user only to submit the built part when the syntax is correct.

Also just wanted to say how immensely impressed and grateful I am by the detail and dedication in your reply. Thanks so much!!

Cheers, more feedback later
Tom

[capt]

Wow, so after only a few tweaks I managed to get it all working like I want to:

let repoT = base.getTable('Repo');
let sourceT = base.getTable('Source');

let record = await input.recordAsync('Pick a record', sourceT);
// let name = record.getCellValue('Part 1') + '-' + record.getCellValue('Part 2');
let name = record.getCellValue('Data');
// create the record in the repo table
let newRepoRecord = await repoT.createRecordAsync({
        'Name': name
})
    // set variable for today's date
let now = new Date().toLocaleDateString("en-GB");
//note that newRepoRecord is the record ID of the created record
// see this by logging the result:

console.log(newRepoRecord);

// now take the newly created record ID
// and set the linked field from this
let updatedRecord = await sourceT.updateRecordAsync(record, {
    'Repo': [{id: newRepoRecord}],
    'Part Created': "Part Entered " + now
})

You are absolutely correct, the data does not yet exist in the Repo table so the user would use a copy and paste to create the record and this should not be the obvious MO, much better to use a button :slightly_smiling_face:

The code has been adapted to look at the formula field and that works like a charm! You can tell how my code has the combining the fields code in the comment just so I can switch…

And I agree with the overwrite, I have as you can see used a ‘parts created’ field which logs the instance.

Again a million thanks. I am pretty sure this is just the beginning of my journey. We have a plethora of fields where button action is needed lol.

Cheers
Tom

[capt]

Hi @JonathanBowen

It just occured to me that there could be a simple trick to avoid duplicates at least from the same record, eg the person clicking the button twice.

Is there a way to do an IF statement whicht checks if the ‘Part Created’ field is empty and only rruns the script if it is. If there is a date in that field it could display a message “Part already exists” and not run the script…

thanks again
Tom