Another Update Linked records with automation scripting question

Forum|Forum|2 years ago
February 13, 2023
8 replies
73 views

rcrema
Participating Frequently

Hello everyone,

I hope someone can help me with a scripting problem I'm having. I am a beginner with scripting and have been taking from examples of what others have posted to get this far. I'd like to have the script find all records in the Enrollment table that match the input variable Students, then update the "SEB Goals 2" field (linked record) with the Goal input variable without replacing the Goal(s) that are in the field already. This script could update more than one record but would probably never go beyond 3 or 4 records. I think the problems are in line 23 but am not sure how to fix it. The code as is did run, but it didn't add a record to the field. It seems to have deleted everything in the field. Any help would be appreciated!

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
February 13, 2023

Hey @rcrema!

I'm a bit tired, so apologies if I'm a bit scattered.
First, I would advise that you change the names of your input variables to be lowercase instead of capitalized.
You'll find that the use of capitalization is reserved for use by other JavaScript conventions. This is why the editor colored your input variable names differently.

This code snippet assumes that you've changed the input variable names to be lowercase.

const config = input.config();
const { students, goal } = config;
const table = base.getTable("Enrollment");

const allStudents = await table.selectRecordsAsync({
    fields: ["Record ID (from Students)"],
  })
  .then((records) => records.records);

const filteredStudents = allStudents.filter((studentRecord) =>
    studentRecord.getCellValueAsString("Record ID (from Students)" === students[0]))
  .map((filteredRecord) => ({
    id: filteredRecord.id,
    "SEB Goals 2": [
      ...filteredRecord.getCellValue("SEB Goals 2"),
      { id: goal },
    ],
  }));

if (filteredStudents) {
  await table.updateRecordsAsync(filteredStudents);
};

In this script, we query the "Enrollment" table for every student record. All of the record objects are then returned to the allStudents variable.

Next, we filter all the student records to only return the records that match the first entry in the record id lookup.
Once we've filtered our applicable records, we create an array of objects containing the record id and the new linked record value that we will update the record with.

Finally, we conduct a bulk update to all the found records.
Since there is a limit on the number of records we can update at a time (50 per operation), I would normally write in a quick layer to handle the bulk updates when we have more than 50 records.
I've forgone this because you indicated that you're only ever really working with an average of 4-5 records at a time.

If you'd like to continue to use the general script structure that you already have, then you can use something like this:

const settings = input.config(); 
const table = base.getTable("Enrollment");

let goal = settings["Goal"];
let students = settings["Students"];

const allStudents = await table.selectRecordsAsync({fields: ["Record ID (from Students)"]}).then(records => records.records);

const filteredStudents = allStudents.filter(student => student.getCellValueAsString("Record ID (from Students)") === students[0]);

for (let i = 0; i < filteredStudents.length; i++) {
    let recordToUpdate = filteredStudents[i];
    await recordToUpdate.updateRecordAsync(recordToUpdate.id, {
        "SEB Goals 2": [
            ...recordToUpdate.getCellValue("SEB Goals 2"),
            {id: goal}
        ]
    });
};

I'm currently unable to test either of these scripts, so it's possible that I'm missing something or have messed up some of the formatting, but the idea is the same.

If you want to use either of these, I recommend you read it over and make sure that all the field names, variables, and input variables in your script are compatible.

rcrema
Author
Participating Frequently
Forum|Forum|2 years ago
February 14, 2023

Hey @rcrema!

This code snippet assumes that you've changed the input variable names to be lowercase.

const config = input.config();
const { students, goal } = config;
const table = base.getTable("Enrollment");

const allStudents = await table.selectRecordsAsync({
    fields: ["Record ID (from Students)"],
  })
  .then((records) => records.records);

const filteredStudents = allStudents.filter((studentRecord) =>
    studentRecord.getCellValueAsString("Record ID (from Students)" === students[0]))
  .map((filteredRecord) => ({
    id: filteredRecord.id,
    "SEB Goals 2": [
      ...filteredRecord.getCellValue("SEB Goals 2"),
      { id: goal },
    ],
  }));

if (filteredStudents) {
  await table.updateRecordsAsync(filteredStudents);
};

In this script, we query the "Enrollment" table for every student record. All of the record objects are then returned to the allStudents variable.

If you'd like to continue to use the general script structure that you already have, then you can use something like this:

const settings = input.config(); 
const table = base.getTable("Enrollment");

let goal = settings["Goal"];
let students = settings["Students"];

const allStudents = await table.selectRecordsAsync({fields: ["Record ID (from Students)"]}).then(records => records.records);

const filteredStudents = allStudents.filter(student => student.getCellValueAsString("Record ID (from Students)") === students[0]);

for (let i = 0; i < filteredStudents.length; i++) {
    let recordToUpdate = filteredStudents[i];
    await recordToUpdate.updateRecordAsync(recordToUpdate.id, {
        "SEB Goals 2": [
            ...recordToUpdate.getCellValue("SEB Goals 2"),
            {id: goal}
        ]
    });
};

I'm currently unable to test either of these scripts, so it's possible that I'm missing something or have messed up some of the formatting, but the idea is the same.

If you want to use either of these, I recommend you read it over and make sure that all the field names, variables, and input variables in your script are compatible.

Hi Ben! Thanks so much for help! I tried each of your solutions. Here are screenshots of both. The problem is that as a beginner, I don't know what I don't know. I see the error messages but don't know how to fix them.

rcrema
Author
Participating Frequently
Forum|Forum|2 years ago
February 14, 2023

Sorry, forgot to change to lower case. Here's what it says now...

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
February 14, 2023

Whoops! I misplaced a parentheses in my original script it seems.

Here it is corrected:

const config = input.config();
const { students, goal } = config;
const table = base.getTable("Enrollment");

const allStudents = await table.selectRecordsAsync({
    fields: ["Record ID (from Students)", "SEB Goals 2"],
  })
  .then((records) => records.records);

const filteredStudents = allStudents.filter((studentRecord) =>
    studentRecord.getCellValueAsString("Record ID (from Students)") === students[0])
  .map((filteredRecord) => ({
    id: filteredRecord.id,
    "SEB Goals 2": [
      ...filteredRecord.getCellValue("SEB Goals 2"),
      { id: goal },
    ],
  }));

if (filteredStudents) {
  await table.updateRecordsAsync(filteredStudents);
};

I'd be more than willing to write out an explanation of what we're actually doing with this script if you'd like!

rcrema
Author
Participating Frequently
Forum|Forum|2 years ago
February 14, 2023

Whoops! I misplaced a parentheses in my original script it seems.

Here it is corrected:

const config = input.config();
const { students, goal } = config;
const table = base.getTable("Enrollment");

const allStudents = await table.selectRecordsAsync({
    fields: ["Record ID (from Students)", "SEB Goals 2"],
  })
  .then((records) => records.records);

const filteredStudents = allStudents.filter((studentRecord) =>
    studentRecord.getCellValueAsString("Record ID (from Students)") === students[0])
  .map((filteredRecord) => ({
    id: filteredRecord.id,
    "SEB Goals 2": [
      ...filteredRecord.getCellValue("SEB Goals 2"),
      { id: goal },
    ],
  }));

if (filteredStudents) {
  await table.updateRecordsAsync(filteredStudents);
};

I'd be more than willing to write out an explanation of what we're actually doing with this script if you'd like!

It still throws

Error: Field "SEB Goals 2" isn't in this record. Make sure it was included in the QueryResult this record comes from.

I'd be happy to learn anything I can about this but want to be respectful of your time.

I think I follow most of what's happening but then I get tripped up in certain places. For instance, the

.then((records) => records.records);

The error message throws me too since I know there is a field called "SEB Goals 2" in the "Enrollment" table.

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
February 14, 2023

It still throws

Error: Field "SEB Goals 2" isn't in this record. Make sure it was included in the QueryResult this record comes from.

I'd be happy to learn anything I can about this but want to be respectful of your time.

I think I follow most of what's happening but then I get tripped up in certain places. For instance, the

.then((records) => records.records);

The error message throws me too since I know there is a field called "SEB Goals 2" in the "Enrollment" table.

Could you just quickly confirm that your script contains this specific snippet:

const allStudents = await table.selectRecordsAsync({
    fields: ["Record ID (from Students)", "SEB Goals 2"],
  })
  .then((records) => records.records);

If you confirm that this is in the final script, go ahead and try this and see if it still throws the error:

const config = input.config();
const { students, goal } = config;
const table = base.getTable("Enrollment");

const allStudents = await table.selectRecordsAsync().then((records) => records.records);

const filteredStudents = allStudents.filter((studentRecord) =>
    studentRecord.getCellValueAsString("Record ID (from Students)") === students[0])
  .map((filteredRecord) => ({
    id: filteredRecord.id,
    "SEB Goals 2": [
      ...filteredRecord.getCellValue("SEB Goals 2"),
      { id: goal },
    ],
  }));

if (filteredStudents) {
  await table.updateRecordsAsync(filteredStudents);
};

If you paste in this entire code snippet, the editor is going to strikeout the selectRecordsAsync() portion of the table.selectRecordsAsync() call.
Despite this, the script will still run without an error. No need to worry.

And don't worry about my time!
I live and breathe Airtable. It's literally how I pass time.

rcrema
Author
Participating Frequently
Forum|Forum|2 years ago
February 14, 2023

Could you just quickly confirm that your script contains this specific snippet:

const allStudents = await table.selectRecordsAsync({
    fields: ["Record ID (from Students)", "SEB Goals 2"],
  })
  .then((records) => records.records);

If you confirm that this is in the final script, go ahead and try this and see if it still throws the error:

const config = input.config();
const { students, goal } = config;
const table = base.getTable("Enrollment");

const allStudents = await table.selectRecordsAsync().then((records) => records.records);

const filteredStudents = allStudents.filter((studentRecord) =>
    studentRecord.getCellValueAsString("Record ID (from Students)") === students[0])
  .map((filteredRecord) => ({
    id: filteredRecord.id,
    "SEB Goals 2": [
      ...filteredRecord.getCellValue("SEB Goals 2"),
      { id: goal },
    ],
  }));

if (filteredStudents) {
  await table.updateRecordsAsync(filteredStudents);
};

And don't worry about my time!
I live and breathe Airtable. It's literally how I pass time.

You were right, it struck out that portion of the code. However, this is the error now...TypeError: filteredRecord.getCellValue is not a function or its return value is not iterable

(filteredStudents); on the last line has been underlined red each time as well.

rcrema
Author
Participating Frequently
Forum|Forum|2 years ago
February 14, 2023

The following code actually placed a goal id into the "SEB Goals 2" field, but replaced the goal that was previously there. I'm getting closer.

let settings = input.config();

console.log(settings);

const table = base.getTable("Enrollment");

let goal = settings.goal;

let students = settings.students;

console.log(students);

const queryResult = await table.selectRecordsAsync({fields: ["Record ID (from Students)", "SEB Goals 2"]});

console.log(queryResult);

const filteredRecords = queryResult.records.filter(record => (

record.getCellValueAsString("Record ID (from Students)") === students[0]

))

console.log(filteredRecords[0]);

for(let i = 0; i < filteredRecords.length; i++) {

let record = filteredRecords[i];

if (record.getCellValueAsString("Record ID (from Students)") === students[0]) {

//Update the record

let goals = record.getCellValueAsString("SEB Goals 2")

let goalsArray = []

if (goals) {

goalsArray = goals.split(",");

}

if (!goalsArray.includes(goal)) {

goalsArray.push(goal);

}

await table.updateRecordAsync(filteredRecords[i].id,{"SEB Goals 2": [{"id": goal}]});

}

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded