Multiple linked objects in lookup field causing script hangup

[Jessica_Pena]

Non-developer here~ I’ve been making our organization a project management base and just ran myself into an issue that I would love an assist on (I feel like the answer is going to be an obvious one)…

We are generating tasks into a Tasks table using Project & Task Templates (P-Temp & T-Temp). To reduce data entry, I have some lookup fields that populate the Assignee for tasks in the T-Temp table, based on the program area that a P-Temp falls under. (e.g. Design Exhibition Card task has P-Temp of Solo Exhibition, which is Program Visual Arts, which is assigned to Jess).

Now, I thought I was being efficient when I linked certain T-Temp tasks to multiple P-Temps because we might complete the same task for several different projects, rather than listing the same task multiple times, once for each P-Temp. As a result, in the T-Temp table, the “Design Postcard” task, when linked to multiple different exhibition types, will end up with an Assignee lookup field that contains the assignee’s name multiple times…

The problem: When I run my scripting app, there is an error because the script tries to pull the Assignee from the T-Temp Assignee field, but understandably gets caught up on there being multiple linked objects in that field.

Can anyone advise on this? I am not sure if there is a way I can modify the script to contend with this, or preferably, I feel like there is a more elegant solution that I am missing for reducing the T-Temp Assignee field to just one instance of the assignee’s name. There is a little more context below to understand how data is flowing around and creates this hang-up.

In a Teams table, we have names of all assignees, with a linked field “Program Areas of Oversight,” which links to the Program Areas table (e.g. Jess is in charge of “Visual Arts”)

In the P-Temps field, the primary field lists our P-Temps (e.g. solo exhibition, curated exhibition, etc.). For each P-Temp, the user enters the Program Area, which is linked to the Program Areas table (e.g. solo exhibition has Program Area of visual arts). There is then a lookup field that auto-fills the Assignee in the P-Temps table, based on the Program Area oversight team member.

In the T-Temps table, individual tasks are entered and the user must assign each task to a P-Temp. Currently, as mentioned above, we are assigning multiple P-Temps to some tasks, so we don’t end up with a million task records. There is a lookup field for Program Area (which may then result in several links to Visual Arts if a task has been assigned to multiple P-Temps) and the lookup for Assignee, which is based on Project Type/based on Program Area. So then CRASH - when the script tries to pull the assignee from here for a certain project type, it can’t when there are multiple linked assignees…

Thank you in advance for any assistance!

[Jessica_Pena]

@Bill.French, @Justin_Barrett, @JonathanBowen - can any of you help with this? Thank you for your consideration :pray:

[Bill_French]

Jessica_Pena:

there is an error because the script tries to pull the Assignee from the T-Temp Assignee field, but understandably gets caught up on there being multiple linked objects in that field.

I think it will be difficult to assist without seeing the script or at least the part that’s hanging up.

[Jessica_Pena]

That makes sense! Here it is:

let projectsT = base.getTable(‘Projects’);
let tasksT = base.getTable(‘Tasks’);
let projectTemplateT = base.getTable(‘P Templates’);
let tasksTemplateT = base.getTable(‘T Templates’);
let teamT = base.getTable(‘Team’);
let tasksTemplateQ = await tasksTemplateT.selectRecordsAsync();

let project = await input.recordAsync(‘Pick a project’, projectsT);
output.text(You picked ${project.getCellValueAsString('Name')});
output.text(This project is a ${project.getCellValueAsString('Project Type')}! Select the appropriate project template for this project.);
let projectTemplate = await input.recordAsync(‘Pick a project template’, projectTemplateT);
let projectTasks = ;
if (projectTemplate) {
output.text(Creating tasks for ${project.getCellValueAsString('Name')} using template ${projectTemplate.getCellValueAsString('Name')} ... please wait);
for (let projectTask of projectTemplate.getCellValue(‘Tasks’)) {
let task = tasksTemplateQ.getRecord(projectTask.id);

projectTasks.push(
    {
        fields: {
            'Task': task.name,
            'Days before/after event date': task.getCellValue('Days before/after event date'),
            'Project': [ {id: project.id} ],
            'Assignee': task.getCellValue('Assignee')
        }
    }
)

}

while (projectTasks.length > 0) {
await tasksT.createRecordsAsync(projectTasks.slice(0, 50));
projectTasks = projectTasks.slice(50);

}

output.text(‘Tasks created!’)
}

[Bill_French]

Is your code really this? I’m pretty sure this is not runnable.

[Jessica_Pena]

Yes, that is the code (I’m going to curl up into a ball of doubt now). It did run until I jammied it all up trying to populate the ‘assignee’ into Tasks table. Because there are some T-Temp tasks that have multiple instances of ‘assignee’ (certain tasks are linked to many different P-Temps), the code currently can’t deal with the multiple linked records in T-Temp’s assignee field. I have tested the code on a template where there is only ONE assignee in the T-Temp’s assignee field, and it still runs fine.

[Jessica_Pena]

And I should also say that I understand that I could have avoided the above pictured flock of assignees… the flock came about because I was hoping to avoid multiplying my quantity of records by just linking template tasks to multiple project templates.

[Justin_Barrett]

Unfortunately this is another issue of Javascript formatting clashing with forum formatting syntax. In Javascript the grave symbol (`) can be used to wrap around strings for easier variable insertion. In this forum, graves are used as wrappers around preformatted text. The lines above look so odd because the the graves from the code have disappeared. That’s also why a small portion of the script—beginning with projectTasks.push—appears as a block of preformatted text, because another way of applying that styling is to indent lines with four spaces. The indentation from the code effectively auto-styled that chunk, but in isolation it looks odd.

@Jessica_Pena Would you mind updating your original post to style the entire script as preformatted text? To do that, just select it all, then click the preformatted text button in the editor toolbar: </>

[Jessica_Pena]

let projectsT = base.getTable('Projects');
let tasksT = base.getTable('Tasks');
let projectTemplateT = base.getTable('P Templates');
let tasksTemplateT = base.getTable('T Templates');
let teamT = base.getTable('Team');
let tasksTemplateQ = await tasksTemplateT.selectRecordsAsync();

let project = await input.recordAsync('Pick a project', projectsT);
output.text(`You picked ${project.getCellValueAsString('Name')}`);
output.text(`This project is a ${project.getCellValueAsString('Project Type')}! Select the appropriate project template for this project.`);
let projectTemplate = await input.recordAsync('Pick a project template', projectTemplateT);
let projectTasks = [];
if (projectTemplate) {
  output.text(`Creating tasks for ${project.getCellValueAsString('Name')} using template ${projectTemplate.getCellValueAsString('Name')} ... please wait`);
  for (let projectTask of projectTemplate.getCellValue('Tasks')) {
    let task = tasksTemplateQ.getRecord(projectTask.id);

    projectTasks.push(
        {
            fields: {
                'Task': task.name,
                'Days before/after event date': task.getCellValue('Days before/after event date'),
                'Project': [ {id: project.id} ],
                'Assignee': task.getCellValue('Assignee')
            }
        }
    )
  }

while (projectTasks.length > 0) {
    await tasksT.createRecordsAsync(projectTasks.slice(0, 50));
    projectTasks = projectTasks.slice(50);
    
}

output.text('Tasks created!')
}

[JonathanBowen]

Hi @Jessica_Pena - I think this issue is one of base design rather than a script issue, which mostly looks fine based on a quick scan.

If I understand correctly, the template task assignment comes from the Project Template, right? Based on this:

I have some lookup fields that populate the Assignee for tasks in the T-Temp table, based on the program area that a P-Temp falls under. (e.g. Design Exhibition Card task has P-Temp of Solo Exhibition, which is Program Visual Arts, which is assigned to Jess).

I think this is the wrong way to do it. The task assignment would work better if it was directly on the task. This might mean a bit more set up, but this feels like a better place to have it. This way, each task will only have one assignee and even if used in multiple project templates, it is still going to work.

You could also work the script to reduce the array of multiple linked assignees down to a single unique assignee, but the assignment at project template level feels wrong somehow.

Of course, very possible that I am not understanding your scenario correctly, so please post again if so!