Issue linking table in script

Anonymous · ‎May 10, 2023

Hey everyone, i'm having an issue and i can't figure out why. I can't update a linked record field (The last line of the snippet below); i've performed this action before in the past with identical syntax i.e.

await myTable.createRecordAsync({
    "linked record field": [{id: idToLink}]
})

Here is the code with an example of the data:

let inputConfig = input.config();
let inputRecordId = inputConfig['input_record_id']

let CRFM_table = base.getTable('Camper Reports Form Manager');
let CRFM_fields = [];

// build array of all fields in CRFM for query
for (let record in CRFM_table.fields) {
    //fields by name
    CRFM_fields.push(CRFM_table.fields[record].name)
}

let CRFM_query = await CRFM_table.selectRecordsAsync({
    fields: CRFM_fields
})

let dataObj = {};

//build object containing every field:value from record
for (let record of CRFM_query.records) {
    if (record.id === inputRecordId) {
        for (let each of CRFM_fields) {
            dataObj[each] = record.getCellValueAsString(each)
        }
    }
}
console.log(dataObj)

//build final data structure
let resObj = {};
let maxNum = 0;
//CHANGE STOP CONDITION TO REFLECT MAX # OF STUDENTS
for (let i = 1; i < 12; i++) {
    for (let each in dataObj) {
        if (i < 10) {
            if (each.includes(`0${i}`) && dataObj[each].length > 0 && !each.includes('record')) {
                let key = each.includes('Report') ? 'Report' : 'Name'

                if (!resObj[i]) {
                    resObj[i] = {
                        [key]: dataObj[each]
                    }
                    if (i > maxNum) maxNum = i;
                } else if (resObj[i]) {
                    resObj[i][key] = dataObj[each];
                    if (i > maxNum) maxNum = i;
                }
            }
            if (each.includes(`0${i}`) && dataObj[each].length > 0 && each.includes('record')) resObj[i]["Summer Campers id"] = dataObj[each]
        }
        if (i >= 10) {
            if (each.includes(i) && dataObj[each].length > 0 && !each.includes('record')) {
                let key = each.includes('Report') ? 'Report' : 'Name'

                if (!resObj[i]) {
                    resObj[i] = {
                        [key]: dataObj[each]
                    }
                    if (i > maxNum) maxNum = i;
                } else if (resObj[i]) {
                    resObj[i][key] = dataObj[each];
                    if (i > maxNum) maxNum = i;
                }
            }
            if (each.includes(i) && dataObj[each].length > 0 && each.includes('record')) resObj[i]["Summer Campers id"] = dataObj[each]       
        }

    }
}
console.log(resObj)

let CR_table = base.getTable('Camper Reports');

for (let i = 1; i <= maxNum; i++) {
    let idToLink = resObj[i]['Summer Campers id'] ;
    // let nameToLink = resObj[i]['Name'];
    console.log(idToLink);

    await CR_table.createRecordAsync({
        "Camper Name": resObj[i]['Name'],
        "Camper Report": resObj[i]['Report'],
        "Summer Campers": [{id: idToLink}], /*PROBLEM HERE*/
    })
}

kuovonne · ‎May 10, 2023

I started looking at your post and stopped when I got to these weird looking lines. Can you tell a little more about how you came up with this script and what you level of scripting experience is? You state that you’ve done this many times in the past, but what part of “this” are you referring to?

// build array of all fields in CRFM for query
for (let record in CRFM_table.fields) {
//fields by name
CRFM_fields.push(CRFM_table.fields[record].name)
}

Anonymous · ‎May 10, 2023

Sure! Thanks for the help.

All that that snippet is doing is dynamically generating a list of field names to provide to the selectRecordAsync function.

//instantiate list
let CRFM_fields = [];

//populate list
for (let record in CRFM_table.fields) {
    //fields by name
    CRFM_fields.push(CRFM_table.fields[record].name)
}

//provide list to Record Query
let CRFM_query = await CRFM_table.selectRecordsAsync({
    fields: CRFM_fields
})

instead of :

let CRFM_query = await CRFM_table.selectRecordsAsync({
    fields: ["Reporter Name", "How Many campers", "Camp Location", "Summer Campers - FiDi - Child 01", "Camper Report - FiDi - Child 01", "Summer Campers - FiDi - Child 02", "Camper Report - FiDi - Child 02", "Summer Campers - FiDi - Child 03", "Summer Campers - FiDi - Child 04", "Summer Campers - FiDi - Child 05", "Summer Campers - FiDi - Child 06", "Summer Campers - FiDi - Child 07", "Summer Campers - FiDi - Child 08", "Summer Campers - FiDi - Child 09", "Summer Campers - FiDi - Child 10", "Summer Campers - FiDi - Child 11", "Camper Report - FiDi - Child 03", "Camper Report - FiDi - Child 04", "Camper Report - FiDi - Child 05", "Camper Report - FiDi - Child 06", "Camper Report - FiDi - Child 07", "Camper Report - FiDi - Child 08", "Camper Report - FiDi - Child 09", "Camper Report - FiDi - Child 10", "Camper Report - FiDi - Child 11", "record_id", "record_id (from Summer Campers - FiDi - Child 01)", "record_id (from Summer Campers - FiDi - Child 02)", "record_id (from Summer Campers - FiDi - Child 03)", "record_id (from Summer Campers - FiDi - Child 04)", "record_id (from Summer Campers - FiDi - Child 05)", "record_id (from Summer Campers - FiDi - Child 06)", "record_id (from Summer Campers - FiDi - Child 07)", "record_id (from Summer Campers - FiDi - Child 08)", "record_id (from Summer Campers - FiDi - Child 09)", "record_id (from Summer Campers - FiDi - Child 10)", "record_id (from Summer Campers - FiDi - Child 11)"]
})

The 'this' is providing a record id to a linked record field to create a relationship with the appropriate record in another table.

await CR_table.createRecordAsync({
    "Camper Name": resObj[i]['Name'],
    "Camper Report": resObj[i]['Report'],
    "Summer Campers": [{id: idToLink}],/*PROBLEM HERE*/
})

To be clear, everything works 100% as desired without the inclusion of that last line; I provided all of the data as its formatted in the parent post. I'm at a total loss, so thanks again for taking a look! As it stands right now i've commented out that line (i.e. "Summer Campers": [{id: idToLink}], ) and am using a second automation to make the link, but i would really like to understand why this is not working!

kuovonne · ‎May 11, 2023

Did you hand write this script yourself from scratch? Did you get the script from someone else? If you got it from someone else, can you ask that person what the issue is?

Although the script runs up until trying to update the record, the issue is probably earlier in the script.

Have you checked the table to verify that particular record ID exists in that table? If it doesn’t exist in that table, could it be an ID of a record in a different table?

The screen capture of the output does not seem to match the code snippet you list. The number of console.log() lines don’t match.

Anonymous · ‎May 11, 2023

I wrote this which is why i'm asking here

I checked the table for existence / correctness of the record ids, they are all good; even if provided an incorrect or non-existent id it would not cause the execution to error out, a link would just not be generated to the correct record. The error message is indicating that i am providing an incorrectly formatted value.

I only included those console logs for the purpose of posting that data here with additional clarity, I removed them from the code i posted.

kuovonne · ‎May 11, 2023

> All that that snippet is doing is dynamically generating a list of field names to provide to the selectRecordAsync function.

I know what that snippet is doing. I also know how it works. What I don't know is why it is written the way it is. It is an unusual way of accomplishing that task. There are also a few other unusual things in the script; this is just the first. If you can provide insight into why this snippet is written the way it, that could provide some clues to the rest of the script.

Anonymous · ‎May 11, 2023

Of course,

We are using a form to conditionally show a number of student name, student report pairs (up to 20) for 5 different locations, with an associated id to link them to their information. I did not want to manually type all of those field names so i did it like this; additionally this future proofs for creation of additional locations without necessitating modification.

The script is intended to take information from that excessively complicated table (necessary to achieve what we're trying to do in the form) and create individual records for each student in another table from which we can mail those reports

Anonymous · ‎May 11, 2023

Of course,

We are using a form to conditionally show a number of student name, student report pairs (up to 20) for 5 different locations, with an associated id to link them to their information. I did not want to manually type all of those field names & writing it like this future-proofs so that i can add additional locations / number of students in the future without hard coding necessitating editing each time.

The script is intended to take information from the excessively complicated table this creates (necessary to achieve what we're trying to do in the form) and create individual records for each student in another table from which we can mail those reports.

You can see in the res obj above this works successfully, in fact the technique was simply modified from an example in the Docs. I don't understand the relevance to not being able to pass in the record ids (which can be seen above exactly as i am passing them) on record creation