Skip to main content

Automatic script to combine two fields into one

  • October 29, 2021
  • 3 replies
  • 45 views
E
Forum|alt.badge.img+6

Hi,

I’ve got a view which filters all records with an empty ‘Name’.

As I capture ‘FirstName’ and ‘LastName’ separately, I want to routinely combine these into a single text field (a formula won’t work for this case)

I added an automatic script, running every 15m which seems to work with one exception.

I do not know how to reference the combined ‘FirstName’ and ‘LastName’ field, anything in quotes is treated a string.

Sure there must be a way, hope someone can help;

let records = input.config().step2Records 
let table = base.getTable("People"); 
let view = table.getView("emptyname");
let selectFieldName = **`???????`**

for (let record of records) {
    await table.updateRecordAsync(record, {
    [selectFieldName]: **`???????`**, 
    })
}

3 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • 6009 replies
  • October 29, 2021

Can you please explain why a formula will not work?

It looks like you are getting data from a previous automation step. What is the value of that input variable?

E
Forum|alt.badge.img+6
  • Ewan_Farry
  • Author
  • New Participant
  • 4 replies
  • October 29, 2021

Can you please explain why a formula will not work?

It looks like you are getting data from a previous automation step. What is the value of that input variable?


Hello,

Thanks for checking this;

  • A formula will not be read by an Airtable sync with our CRM and I want the “Name” to remain the table’s primary field (formulas can’t be used as my primary field)

  • The previous automation step has a value of record ID returned from ‘Find Records’

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • 6009 replies
  • October 29, 2021

Hello,

Thanks for checking this;

  • A formula will not be read by an Airtable sync with our CRM and I want the “Name” to remain the table’s primary field (formulas can’t be used as my primary field)

  • The previous automation step has a value of record ID returned from ‘Find Records’


The code in your screenshot is now different from the code in your initial post. I’m not sure if your initial question still remains or not, but here are my thoughts.

Airtable plays a little fast and loose with what a “list” is. Sometimes Airtable says “list” when it means an array, and sometimes Airtable says “list” when it means a comma-separated text string. In this particular case, the value of your input variable is a comma-separated text string, but your for loop looks like it wants an array. If you put a console.log(record) inside your for loop, you will notice that you do not get the value you expect.

Your code does not have access to any of the cell values for the records. Thus, the script cannot set the “Name” field based on existing values for the record. To get access to the cell values, you will need to do a selectRecordsAsync() call. You can do this with the record IDs from your “find records” action, or you can get the records directly from the view if you don’t need the “find records” action for something else.

let queryResult = await view.selectRecordsAsync({fields: ["FirstName", "LastName"]});
let records = queryResult.records;

Then you can get the cell values for each record.

let firstName = record.getCellValue("FirstName");

There are a few other issues that I see, but I do not want to overwhelm you right now.


Terms & ConditionsAccessibility statement