Help with script extension crash when inserting a link record ID in record in another table

[stenkate]

Hi there,

I'm not a developer but I'm exploring and using various scripting options, but can't seem to mitigate the crash I encounter when to use the script below. Can someone help with this? Thanks in advance!

The feature I want to build is a bigger one: it's a vlookup feature with auto categorization based on the intermediate table [personal categorization] - so a script to conditionally link a record.

But this is a simplified version without that auto vlookup to focus first on the crash when trying to link a record:

Here I choose one record from table [personal expenses] and one from the table [personal categorization].

The field [Type] in table [personal categorization] is a linked field with a link to a record in a 3rd table [personal budget].

What I want to do in the end, is that the chosen record in [personal expenses] is linked to the record in [personal budget] via the field [tmpLookUpLinks], and that value should be retrieved from the field [Type] in the [personal categorization] table.

Note: each link has only 1 link, no multiple record link fields are configured

The script crashes when running. FYI: maybe there's a connection with this forum post, but not quite sure.

See below for the simplified code; hopefully someone can help!

// Get the record
let mainTable = base.getTable("personal expenses")
let recordMainTable = await input.recordAsync('Select a record', mainTable)

let lookupTable = base.getTable("personal categorization")
let recordLookupTable = await input.recordAsync('Select a record', lookupTable)

let linkTypeID = recordLookupTable.getCellValue("Type")[0].id;
let tmpLookUpLinks = "tmpLookUpLinks";

await mainTable.updateRecordAsync(recordMainTable, {
    "tmpLookUpLinks": [{id: linkTypeID.id}],
})

[Ben_Young1]

Hey @stenkate! 

Give this a shot:

// Get the record
let mainTable = base.getTable("personal expenses")
let recordMainTable = await input.recordAsync('Select a record', mainTable)

let lookupTable = base.getTable("personal categorization")
let recordLookupTable = await input.recordAsync('Select a record', lookupTable)

let linkTypeID = recordLookupTable.getCellValue("Type")[0].id;
let tmpLookUpLinks = "tmpLookUpLinks";

await mainTable.updateRecordAsync(recordMainTable, {
    "tmpLookUpLinks": [{ id: linkTypeID }],
})

The issue with the snippet you posted is that you were passing the following to the linked record field:

[{ id: linkTypeID.id }]

The problem is that linkTypeID is already a string of record ID belonging to the first linked record in your Type field.
Another way to fix this would be to assign the record object to the variable instead of the record.id property.
From there, you can call the record.id property value to the table.updateRecordAsync operation.

// Get the record
let mainTable = base.getTable("personal expenses")
let recordMainTable = await input.recordAsync('Select a record', mainTable)

let lookupTable = base.getTable("personal categorization")
let recordLookupTable = await input.recordAsync('Select a record', lookupTable)

let linkTypeID = recordLookupTable.getCellValue("Type")[0];
let tmpLookUpLinks = "tmpLookUpLinks";

await mainTable.updateRecordAsync(recordMainTable, {
    "tmpLookUpLinks": [{ id: linkTypeID.id }],
})

[stenkate]

Thanks @Ben_Young1  I appreciate you thinking with me. Unfortunately both options both lead to a crash of the scripts. I did a double check on the configuration of my bases and fields, but don't see anything odd. Maybe there's a link with this earlier mentioned issue?

[stenkate · Accepted solution]

In the end I could not manage to create a solution within one script. However I did create a working workaround that I'd like to share for future users:

1. First I created a field in the [personal budget] table with the recordID() of each row
2. I created a 'vlookup' script to find matches between records in the [personal categorization] and the [personal expenses] table. That script returned a found match ID of the record in the [personal categorization] table and returned it as a link value to that the matching record in the [personal expenses] table.
3. Since there are now links between the expense records in the [personal expenses] table and the categorization records in the [personal categorization] table, I created a lookup field in the [personal expenses] table which would lookup the link values which are created in the [personal categorization] table with records from the [personal budget] table.
4. In that same [personal expenses] table I created another lookup field to - based on the previous lookup value in #3 - retrieve the recordID() in [personal budget] table as mentioned in #1.
5. I then created a script to use this recordID and use it as a link value that was returned to matching record in the [personal expenses] table.

It looks like step #3 en step #5 would create the same result, and in a way it is. The difference is that in the field of #3 I can't manually adjust a link since it's a lookup field. Because the value in #5 is returned via a script and it's a link field, I can also manually adjust the link value in the table.

Maybe this sounds a bit conceptual, but once I designed this structure and running it's working pretty smooth 🙂