Need help with an easy script

[Tom_Quigley]

Hi! First post here. Very beginning JS user but excited to learn!

I’m working on a pretty basic script in a base I’m working on. I’ve been learning JS but I’m not quite at the point where the examples are making sense to me.

I have 2 Tables in my Base, “Projects” and “Judges”. In Projects, I will assign 3 judges from the Judges table in one Linked Record column. Then, in a third Table, “Scores”, I want to use the Script block to have three unique rows generated - one for each judge with the same project.

So for example: Project “Coral Reef Building” gets assigned “Mark”, “Sam”, and “Amy” as judges. In “Scoring”, three rows are generated:
“Coral Reef Building, evaluated by Mark”
“Coral Reef Building, evaluated by Sam”
“Coral Reef Building, evaluated by Amy”

I was thinking that .forEach method might be useful here, but I’m getting confused with the exact terminology around tables and queries and records.

Thanks all for your help & advice! Even pointing me in the right direction of some specific functions or frameworks that might help would be useful.

[Tom_Quigley]

This is where I’m at:

let projectsTable = base.getTable(“Projects”);
let judgesTable = base.getTable(“Judges”);
let projectQuery = await projectsTable.selectRecordsAsync();
let judgesQuery = await judgesTable.selectRecordsAsync();

That at least has successfully pulled the records from both the tables, and I can express either table in the console, but I’m having trouble with the output I want.

Perhaps defining a function that would run .forEach on the project table? But I need to specific that it’s for each of the Judges, not any of the other column.

[JonathanBowen]

Hi @Tom_Quigley - I think you won’t need to query the judges table (if a project already has the judges assigned in a linked field). What you should be able to do is iterate through the projects (maybe only the ones not yet dealt with) and, for each project, loop through the array of judges presented by the linked judges field. Maybe something like this:

projects.forEach((project) => {
  project.getCellValue('Judges').forEach((judge) => {
    // do some stuff here
  })
})

Not sure how you have structured the Scores table, but potentially, this could be two linked fields (one for project and one for judge) and a score field (with a primary field that is something else, an autonumber or combo on project/judge).

From your nested loop you can can form a set of records that can be inserted into the scores table. If they are both linked fields, you’ll need the project id and the judge id and for linked fields you insert an array of ids to a linked field. So perhaps you end up with something like this:

let recordsToInsert = [
{"Project": [{ id: project1id }], "Judge": [{ id: judge1id }]},
{"Project": [{ id: project1id }], "Judge": [{ id: judge2id }]},
{"Project": [{ id: project1id }], "Judge": [{ id: judge3id }]},
{"Project": [{ id: project2id }], "Judge": [{ id: judge1id }]},
{"Project": [{ id: project2id }], "Judge": [{ id: judge2id }]},
{"Project": [{ id: project2id }], "Judge": [{ id: judge3id }]},
]

(the project and judge ids would all be of the form “recSOMETHING”)

The you could either a) loop through the records to insert array and insert them one by one or b) do it in a batch of 50 with createRecords

I’m just typing this out blind as I can’t try this out right now, so hopefully I’m not missing something obvious, but I think this will work. Post back if not!

JB

[Tom_Quigley]

Thank you, this is useful. Here’s my new approach (sorry for the formatting challenges)

// Call out the tables we will use

let projects = base.getTable(“Projects”);
let judge = base.getTable(“Judges”);

// loop through Projects table to find assigned Judges

projects.forEach((projects) => {
projects.getCellValue(“Assigned Judge”).forEach((judge) => {
console.log(“success”);
})
})

But I’m getting the error
TypeError: projects.forEach is not a function
at main on line 8

Right now I’m just trying to post “success” as an output to see if it’s working. Once that’s good, I’ll do something to give it a unique value.

[JonathanBowen]

OK, I can see the problem - you’re doing forEach on the table, but you need to query the table first (which you did in the first version). This works for me:

let table = base.getTable('Projects')
let query = await table.selectRecordsAsync();

query.records.forEach(project => {
    project.getCellValue('Judges').forEach(judge => {
        console.log(project.name, project.id, judge.name, judge.id)
    })
})

(Aside - you can wrap you code in code markup, like this:

to get it to format as code. Or can use backticks - 3 at the start and the end for a multiline statement. [code] gives you the colour formatting too, which makes it a bit nicer).

JB

[Tom_Quigley]

Thanks JB! Getting closer. I got it to successfully pull the Judge and Project data, now I am working on getting it to create records in the ‘Scoring’ table. Here’s my most recent version, which doesn’t return errors, but also it doesn’t work.

Something to do with the recordIDs variable, I think. I think I’m declaring a variable, but not doing anything with it. But anytime I tried to use await or a function, it threw an error.

Also, side note, do I have to list ALL the fields on the destination table when I createRecordsAsync or just the few ones I want to write to?

Thank you for all your help! It’s great practice for me.

// Call out the tables we will use
let table = base.getTable('Projects');
let query = await table.selectRecordsAsync();
let destination = base.getTable('Scoring');

// pull combined info for assigned project/judge pairs
query.records.forEach(project => {
            let Judges = project.getCellValue('Judges');
            if (Judges != null) {
                Judges.forEach(judge => {
                   let recordIDs = destination.createRecordsAsync([
                        {
                                fields: {
                                    'Name': `${project.name} evaluated by ${judge.name}`,
                                    'Judge': [{id: judge.id}],
                                    'Project': [{id: project.id}],
                                },
                            },
                        ]);
                    console.log("Success!")
                });
            };
         });

[JonathanBowen]

Hi @Tom_Quigley - I plugged your code in without editing and it worked straight away, so there’s nothing wrong with it as far as I can see.

A few things you might consider though:

• You’re using the createRecords (plural) method, even though, by definition from your code, you are only ever passing data for one record on each iteration. As above, this works fine, but you could use the createRecord method (singular) when you know you’re only passing a single record. It is very similar:

let recordId = destination.createRecordAsync(
    {
        'Name': `${project.name} evaluated by ${judge.name}`,
        'Judge': [{id: judge.id}],
        'Project': [{id: project.id}],
    },
)

• One thing I had sort of understood, but only now understanding better, is that await doesn’t work within a forEach loop. So, you can code it as you have where the createRecords is not proceeded by await. What happens (I think) is that the second and subsequent requests do not wait for the previous request to return before firing off. This makes everything happen really fast, but, depending upon what your code does after this it might not give you the result you expected, as some subsequent code might run before all of the records are created. The alternative method would be to use for-of loops which can make use of await:

// Call out the tables we will use
let table = base.getTable('Projects');
let query = await table.selectRecordsAsync();
let destination = base.getTable('Scoring');

// pull combined info for assigned project/judge pairs
for (let project of query.records) {
    let judges = project.getCellValue('Judges');
    if (judges) {
        for (let judge of judges) {
            let recordId = await destination.createRecordAsync(
                {
                    'Name': `${project.name} evaluated by ${judge.name}`,
                    'Judge': [{id: judge.id}],
                    'Project': [{id: project.id}],
                },
            )
            console.log("Sucess!");
        }
    }
}

• final side point - instead of if(judges != null) you can simplify to if(judges) as in “if judges exists”

[JonathanBowen]

@Tom_Quigley

Also, side note, do I have to list ALL the fields on the destination table when I createRecordsAsync or just the few ones I want to write to?

No, you only need to specify the fields you want to write to.

[Tom_Quigley]

Hey Jonathan - it works!!! This is my first real-world application of the JS I’ve been learning and I’m incredibly excited that it’s working. Not just for the satisfaction of getting code right - it’s going to make my work life SO much easier. I really appreciate the time you took to help me through this. Thank you so much!

// Call out the tables we will use
let table = base.getTable('Projects');
let query = await table.selectRecordsAsync();
let destination = base.getTable('Scoring');

// pull combined info for assigned project/judge pairs
query.records.forEach(project => {
            let Judges = project.getCellValue('Judges');
            if (Judges) {
                Judges.forEach(judge => {
                   let recordId = destination.createRecordAsync(
                                {
                                    'Name': `${project.name} evaluated by ${judge.name}`,
                                    'Judge': [{id: judge.id}],
                                    'Projects': [{id: project.id}],
                                },
                        );
                    console.log("Success!")
                });
            };
         });

[Tom_Quigley]

Hey Jonathan (and future searchers) - quick update, when I ran this against my database, there was indeed a race condition with the .forEach system, and I was only getting ~160 of the ~450 records I needed. I edited it to use the for (let project of query.records) syntax you suggested and, sure enough, it slowed down enough to print a record for each iteration.

Thanks again for all your help!