Re: Script to transfer and update collaborator field

Cyril_Schwartz · ‎Feb 07, 2022

Hello,

I am trying to transfer a collaborator field from one table to another. It works perfectly for the other text fields but for the collaborator field it doesn’t work.

 "User (other)": { "id": user },

Doesn’t work

Here is my code :

let mainTable = base.getTable("Table 1");
let mainTableRecords = await mainTable.selectRecordsAsync();

let lookupTable = base.getTable("Table 2");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();

for(let record of mainTableRecords.records) {

    let lookupValue = record.getCellValue("ID Customer");

    for(let rangeRecord of lookupRangeRecords.records) {
        if(rangeRecord.getCellValue("ID Customer") === lookupValue) {
           
            let myName = rangeRecord.getCellValue("myName");
            let user;
            if(rangeRecord.getCellValue("User") != null) {
                user = rangeRecord.getCellValue("User").id;
            } else {
                user = rangeRecord.getCellValue("User");
            }

            await mainTable.updateRecordAsync(record, {
               "My name (other)": myName,
                "User (other)": { "id": user },
            });
        }
    }
}

Thanks,
Dav

JonathanBowen · ‎Feb 07, 2022

@Cyril_Schwartz - this part:

"User (other)": { "id": user }

is the correct format for setting a collaborator field. The user variable needs to be an Airtable user id though - something like:

usrvxdcfrb8JgXXXXXX

(a random string prefixed with usr)

Looking at your script I would have a look at this part:

    for(let rangeRecord of lookupRangeRecords.records) {
        if(rangeRecord.getCellValue("ID Customer") === lookupValue) {
           
            let myName = rangeRecord.getCellValue("myName");
            let user;
            if(rangeRecord.getCellValue("User") != null) {
                user = rangeRecord.getCellValue("User").id;
            } else {
                user = rangeRecord.getCellValue("User");
            }

            await mainTable.updateRecordAsync(record, {
               "My name (other)": myName,
                "User (other)": { "id": user },
            });
        }
    }

I don’t understand the if...else on the User field as you’re trying to get a user id out here. I don’t know your base obviously, but I would rewrite this to something like:

for(let rangeRecord of lookupRangeRecords.records) {
  // check if the ID Customer equals the lookup value 
  // AND if the User field is not null
  if(rangeRecord.getCellValue("ID Customer") === lookupValue && rangeRecord.getCellValue("User")) {
      let myName = rangeRecord.getCellValue("myName");
      
      // Now get the User id
      // User returns an object like this:
      // {
      //   id: "usrvxdcfrb8JgXXXXXX"
      //   email: "hello@example.com"
      //   permissionLevel: "create"
      //   name: "Jonathan Bowen"
      //   profilePicUrl: "https://static.airtable.com/images/userIcons/user_icon_5.png"        
      // }

      let userId = rangeRecord.getCellValue("User")['id']
      await mainTable.updateRecordAsync(record, {
         "My name (other)": myName,
          "User (other)": { "id": userId },
      });
  }
}

See Solution in Thread

Alexey_Gusev · ‎Feb 07, 2022

Hi,
please clarify (in short) the purpose of your script
looks like a piece of lookup script, but then why copy something?

or you are learning scripting and want to test some functions?

try to analyze that part:
 let user;
            if(rangeRecord.getCellValue("User") != null) {
                user = rangeRecord.getCellValue("User").id;
            } else {
                user = rangeRecord.getCellValue("User");
            }

actually, it’s wise to ‘debug’ via

output.inspect(user);

if something wrong with value ‘user’, let’s check the value

updateRecordAsync(record, - should be record.id

but in general, when you are need to update several records, avoid ‘bad habit’ - using updateRecordAsync in loop. If your PC fast enough, you will exceed a limit of writes/sec and script drops at some point…
use updateRecordsAsync instead

Cyril_Schwartz · ‎Feb 07, 2022

Thanks for your answer.

The purpose of my script is to copy the collaborator from “Table 1” to “Table 2”. The script works with the “My name”(type : text) field but not with the “User” (type : collaborator).

I get the ID of the collaborator (if the field is not empty) in the “user” variable. I think that the problem is in this part : “User (other)”: { “id”: user },

Error message :

Type ‘string | Collaborator’ is not assignable to type ‘string’.
Type ‘Collaborator’ is not assignable to type ‘string’.

Alexey_Gusev · ‎Feb 07, 2022

Okay, it’s clear. I will also comment other aspects of your goal, just for understanding.

at first, you are trying to update maintable, which is ‘Table 1’.
while your goal is to ‘read from Table 1, write to Table 2’
(let’s skip the part I said about loops and so on)
you don’t need to repeat variable names from other script, it’s better to choose your own, so script will be self-commenting

here, you write
if(rangeRecord.getCellValue("User") != null)
if field ‘User’ value in Table 2 is not empty

user = …value.id - you swapped tables, because rangerecord is related to loop records from Table 2.
but in general, operation is correct - you put value (collaborator) and save it’s id in variable ‘user’

else (if value is null, means empty)

user = rangeRecord.getCellValue("User"); - let user be empty

it’s ok, but you are ‘overloading’ the code in such way.
you can achieve the same with

 let user=null;
 if(rangeRecord.getCellValue("User") != null)  user =rangeRecord.getCellValue("User").id;

let’s go deeper. instead of using if (x != null ) , you can use if (x)
null or numeric 0 or empty string will result in false, others - in true

i would prefer:

let user = rangeRecord.getCellValue("User");
if (user) user=user.id;

(if user is not null, then let user will be user.id … and if null, so be it …)
you may even use
let x = somevalue? somevalue.id : null
format,
but in that case you don’t want to write all those getCellvalue twice, so lets skip it for now…

but as i said, you are trying to read from Table 2 (because rangeRecord is from loop related to Table 2) and write into table 1 , that’s the problem.
try to correct that .

JonathanBowen · ‎Feb 07, 2022