Solved

Script to transfer and update collaborator field

Forum|Forum|3 years ago
February 7, 2022
5 replies
61 views

Cyril_Schwartz
New Participant
2 replies

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

Best answer by JonathanBowen

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’.

@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 },
      });
  }
}

+25

Alexey_Gusev
Brainy
1261 replies
Forum|Forum|3 years ago
February 7, 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
Author
New Participant
2 replies
Forum|Forum|3 years ago
February 7, 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

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’.

+25

Alexey_Gusev
Brainy
1261 replies
Forum|Forum|3 years ago
February 7, 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’.

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 .

+18

JonathanBowen
Inspiring
1110 replies
Answer
Forum|Forum|3 years ago
February 7, 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’.

@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 },
      });
  }
}

Cyril_Schwartz
Author
New Participant
2 replies
Forum|Forum|3 years ago
February 8, 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 },
      });
  }
}

Thanks, it’s perfect :slightly_smiling_face:

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded