Solved

Scripting: Link Two Records into Record Field at Once

Forum|Forum|1 year ago
November 28, 2023
7 replies
105 views

byrnes
Participating Frequently

Title.
I am still getting the hang of scripting, Airtable, and scripting for Airtable. Goal of script:

User chooses up to two options in multi-select field.
Those multi-select options are 1:1 with the names of the records that are being linked. (IDs are included regardless).
Link those up to two records from the Signature Hardware Vendor multi-select field into the Vendor(s) linked records field.

Ill gladly take hints on updating or fixing my bad code leading up to the portion in question, but I can confirm everything shown below is working BESIDES the linking of two different records.

const { recordId , QASubtype, SHVendor} = input.config();
const table = base.getTable('tblFvUDsFR14aJ8Nu');

if (QASubtype==="Signature Hardware Quick Add"){
  await table.updateRecordAsync(recordId,{
    "Manufacturer(s)": [{ id: 'recBUCS8K6yzHS1Nd' }]
  });
  if (SHVendor==="Signature_Hardware - 1481"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }]
    });
  };
  if (SHVendor==="All Ferguson (Group)"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recSIDlhGWnWdlpdv' }]
    });
  };
  if (SHVendor==="Signature_Hardware - 1481, All Ferguson (Group)" || SHVendor==="All Ferguson (Group), Signature_Hardware - 1481"){
    table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }]
    });
    await table.updateRecordAsync(recordId,{
      "Vendor(s)":[
        ...recordId.GetCellValue('Vendor(s)'),
        { id: 'recSIDlhGWnWdlpdv' }
      ]
    });
  };
};

Best answer by Sho

That is because it does not follow the format when there are multiple records.
For multiple records, vendorId should be like this.

const { recordId, QASubtype, SHVendor, Mfr, VCVendor, BCUrgent, OPriority } = input.config();
const table = base.getTable('tblFvUDsFR14aJ8Nu');
let vendorId, seriesName, manufacturerId, priorityName;
 
if (QASubtype === "Signature Hardware Quick Add") {
  manufacturerId = 'recBUCS8K6yzHS1Nd'
  if (String(SHVendor) === "Signature_Hardware - 1481") {
    vendorId = [{id: 'recyW5pRLYxqFIMkt'}];
  } else if (String(SHVendor) === "All Ferguson (Group)") {
    vendorId = [{id: 'recSIDlhGWnWdlpdv'}];
  } else if (String(SHVendor) === "Signature_Hardware - 1481,All Ferguson (Group)" || String(SHVendor) === "All Ferguson (Group),Signature_Hardware - 1481") {
    vendorId = [{id: 'recyW5pRLYxqFIMkt'}, {id: 'recSIDlhGWnWdlpdv'}];
  }

  if (manufacturerId) {
    await table.updateRecordAsync(recordId, { "Manufacturer(s)": [{ id: manufacturerId }] });
  } 
  if (vendorId) {
    await table.updateRecordAsync(recordId, { "Vendor(s)": vendorId });
  }
}

+21

Sho
Inspiring
Forum|Forum|1 year ago
November 28, 2023

Do you want to do it like this?

const { recordId , QASubtype, SHVendor} = input.config();
const table = base.getTable('tblFvUDsFR14aJ8Nu');

if (QASubtype==="Signature Hardware Quick Add"){
  await table.updateRecordAsync(recordId,{
    "Manufacturer(s)": [{ id: 'recBUCS8K6yzHS1Nd' }]
  });
  if (SHVendor==="Signature_Hardware - 1481"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }]
    });
  };
  if (SHVendor==="All Ferguson (Group)"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recSIDlhGWnWdlpdv' }]
    });
  };
  if (SHVendor==="Signature_Hardware - 1481, All Ferguson (Group)" || SHVendor==="All Ferguson (Group), Signature_Hardware - 1481"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }, { id: 'recSIDlhGWnWdlpdv' }]
    });
  };
};

byrnes
Author
Participating Frequently
Forum|Forum|1 year ago
November 29, 2023

Do you want to do it like this?

const { recordId , QASubtype, SHVendor} = input.config();
const table = base.getTable('tblFvUDsFR14aJ8Nu');

if (QASubtype==="Signature Hardware Quick Add"){
  await table.updateRecordAsync(recordId,{
    "Manufacturer(s)": [{ id: 'recBUCS8K6yzHS1Nd' }]
  });
  if (SHVendor==="Signature_Hardware - 1481"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }]
    });
  };
  if (SHVendor==="All Ferguson (Group)"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recSIDlhGWnWdlpdv' }]
    });
  };
  if (SHVendor==="Signature_Hardware - 1481, All Ferguson (Group)" || SHVendor==="All Ferguson (Group), Signature_Hardware - 1481"){
    await table.updateRecordAsync(recordId,{
      "Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }, { id: 'recSIDlhGWnWdlpdv' }]
    });
  };
};

Sho,

I actually tried to run that originally and got nothing. I am discovering I made an error both in my post and in my solution, Vendor(s) is not linking no matter which value it is. Manufacturer(s) fills in, then nothing else.

When ran onto this record, the second one listed here, we see it adds "Signature Hardware" as the Manufacturer(s), but not "Signature_Hardware - 1481" as the Vendor. So as of right now neither my version or yours is working on "Vendor(s)".

Also confirmed the ID:

We determined one of the errors was lack of string (), however this and a small overhaul has still left us with the double case still not working.

const { recordId, QASubtype, SHVendor, Mfr, VCVendor } = input.config();
const table = base.getTable('tblFvUDsFR14aJ8Nu');
let vendorId;
 
if (QASubtype === "Signature Hardware Quick Add") {
  if (String(SHVendor) === "Signature_Hardware - 1481") {
    vendorId = 'recyW5pRLYxqFIMkt';
  } else if (String(SHVendor) === "All Ferguson (Group)") {
    vendorId = 'recSIDlhGWnWdlpdv';
  } else if (String(SHVendor) === "Signature_Hardware - 1481, All Ferguson (Group)" || String(SHVendor) === "All Ferguson (Group), Signature_Hardware - 1481") {
    vendorId = ['recyW5pRLYxqFIMkt', 'recSIDlhGWnWdlpdv'];
  }
 
  if (vendorId) {
    await table.updateRecordAsync(recordId, { "Vendor(s)": [{ id: vendorId }] });
  }
}

+21

Sho
Inspiring
Forum|Forum|1 year ago
November 29, 2023

Are errors displayed?　If there is an error indicated, tell me about it.

Updates to linked record fields must follow the field's write format.

Array<{ id: string }>

It needs to be an array of objects

"Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }, { id: 'recSIDlhGWnWdlpdv' }]

byrnes
Author
Participating Frequently
Forum|Forum|1 year ago
November 30, 2023

Are errors displayed?　If there is an error indicated, tell me about it.

Updates to linked record fields must follow the field's write format.

Array<{ id: string }>

It needs to be an array of objects

"Vendor(s)": [{ id: 'recyW5pRLYxqFIMkt' }, { id: 'recSIDlhGWnWdlpdv' }]

No errors - just for some reason it refuse to accept two values.

The field is set to accept multiple values, I can do it by hand, and as far as we can tell the way the script is written should satisfy all requirements.

+21

Sho
Inspiring
Forum|Forum|1 year ago
November 30, 2023

It appears to be an IF statement issue.

Try running

console.log(String(SHVendor));

does it match?

If SHVendor is a Lookup field, the separator should be "," and not contain spaces.

  } else if (String(SHVendor) === "Signature_Hardware - 1481,All Ferguson (Group)" || String(SHVendor) === "All Ferguson (Group),Signature_Hardware - 1481") {

byrnes
Author
Participating Frequently
Forum|Forum|1 year ago
December 1, 2023

It appears to be an IF statement issue.

Try running

console.log(String(SHVendor));

does it match?

If SHVendor is a Lookup field, the separator should be "," and not contain spaces.

  } else if (String(SHVendor) === "Signature_Hardware - 1481,All Ferguson (Group)" || String(SHVendor) === "All Ferguson (Group),Signature_Hardware - 1481") {

Good call on this. Strangely the output was showing a space after the comma so I emulated the same. Turns out, no space. New error though!

Error: Field "fldl2gccVFlUGyvn1" cannot accept the provided value.
    at main on line 19

Line 19 is of course the line where the record field is being set to the array. The current code:

const { recordId, QASubtype, SHVendor, Mfr, VCVendor, BCUrgent, OPriority } = input.config();
const table = base.getTable('tblFvUDsFR14aJ8Nu');
let vendorId, seriesName, manufacturerId, priorityName;
 
if (QASubtype === "Signature Hardware Quick Add") {
  manufacturerId = 'recBUCS8K6yzHS1Nd'
  if (String(SHVendor) === "Signature_Hardware - 1481") {
    vendorId = 'recyW5pRLYxqFIMkt';
  } else if (String(SHVendor) === "All Ferguson (Group)") {
    vendorId = 'recSIDlhGWnWdlpdv';
  } else if (String(SHVendor) === "Signature_Hardware - 1481,All Ferguson (Group)" || String(SHVendor) === "All Ferguson (Group),Signature_Hardware - 1481") {
    vendorId = ['recyW5pRLYxqFIMkt', 'recSIDlhGWnWdlpdv'];
  }

  if (manufacturerId) {
    await table.updateRecordAsync(recordId, { "Manufacturer(s)": [{ id: manufacturerId }] });
  } 
  if (vendorId) {
    await table.updateRecordAsync(recordId, { "Vendor(s)": [{ id: vendorId }] });
  }
}

+21

Sho
Inspiring
Answer
Forum|Forum|1 year ago
December 2, 2023

That is because it does not follow the format when there are multiple records.
For multiple records, vendorId should be like this.

const { recordId, QASubtype, SHVendor, Mfr, VCVendor, BCUrgent, OPriority } = input.config();
const table = base.getTable('tblFvUDsFR14aJ8Nu');
let vendorId, seriesName, manufacturerId, priorityName;
 
if (QASubtype === "Signature Hardware Quick Add") {
  manufacturerId = 'recBUCS8K6yzHS1Nd'
  if (String(SHVendor) === "Signature_Hardware - 1481") {
    vendorId = [{id: 'recyW5pRLYxqFIMkt'}];
  } else if (String(SHVendor) === "All Ferguson (Group)") {
    vendorId = [{id: 'recSIDlhGWnWdlpdv'}];
  } else if (String(SHVendor) === "Signature_Hardware - 1481,All Ferguson (Group)" || String(SHVendor) === "All Ferguson (Group),Signature_Hardware - 1481") {
    vendorId = [{id: 'recyW5pRLYxqFIMkt'}, {id: 'recSIDlhGWnWdlpdv'}];
  }

  if (manufacturerId) {
    await table.updateRecordAsync(recordId, { "Manufacturer(s)": [{ id: manufacturerId }] });
  } 
  if (vendorId) {
    await table.updateRecordAsync(recordId, { "Vendor(s)": vendorId });
  }
}

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded