Simple way of adding multiple records to a Linked Record Field in table from a view - please!

[Claire_Conza]

Hi

I simply need to add some records to a table into a "Linked Record’ field. I struggle to understand why it’s so difficult.

I have this VIEW:

and I want to add single and concatenated ‘Linked Orders’ via a script.

This my array of records I’d like to add…

Here is my code and results…

full code

let table = base.getTable("Daily Orders");

let ws = base.getTable("Production Required");

let view = table.getView("Unfulfilled Daily Orders");

let sku = table.getField("Line Items: Sku"); 

let ordernum = table.getField("Order: Name"); 

let currsku = "";

let ordstr = "";

let result = await view.selectRecordsAsync({

    sorts: [{field: sku, direction: "asc"}]

});

output.inspect(result);

let ordersArray = [];

for (let record of result.records) {

    ordernum = record.getCellValue("Order: Name");

    currsku = record.getCellValue(sku)[0]; 

    ordersArray.push({

          fields: {

              "Order_Num": ordernum,

              "SKU": currsku

                }

            })

}

var res = [];

ordersArray.forEach(obj => {

  var found = res.find(ob => ob.fields.SKU === obj.fields.SKU);

  if(found){

    var order_nums = found.fields.Order_Num;

    var new_order_num = order_nums+","+obj.fields.Order_Num;

    found.fields.Order_Num = new_order_num;

  } else {

      res.push({fields: {SKU: obj.fields.SKU,Order_Num:obj.fields.Order_Num}});

  }  

});

let ordersRecords = [];

var orn;

res.forEach(obj => {

    orn = obj.fields.Order_Num;

    //output.text(orn);

    ordersRecords.push({

        fields: {

        "Linked Orders": orn

               }

       })

});

output.inspect(ordersRecords);

let recordsCreated = await batchAnd('Create', ws, ordersRecords);

/*

    Use this function to perform 'Update', 'Create', or 'Delete'

    async actions on batches of records that could potentially 

    more than 50 records.

    ::PARAMETERS::

    action = string; one of 3 values:

           - 'Update' to call table.updateRecordsAsync()

           - 'Create' to call table.createRecordsAsync()

           - 'Delete' to call table.deleteRecordsAsync()

    table = Table; the table the action will be performed in

    records = Array; the records to perform the action on

            - Ensure the record objects inside the array are

            formatted properly for the action you wish to

            perform

    ::RETURNS::

    recordsActedOn = integer, array of recordId's, or null; 

                   - Update Success: integer; the number of records processed by the function

                   - Delete Success: integer; the number of records processed by the function

                   - Create Success: array; the id strings of records created by the function

                   - Failure: null;

*/

async function batchAnd(action, table, records) {

    let recordsActedOn;

    switch (action) {

        case 'Update':

            recordsActedOn = records.length;

            while (records.length > 0) {

                await table.updateRecordsAsync(records.slice(0, 50));

                records = records.slice(50);

            };

            break;

        

        case 'Create':

            recordsActedOn = [];

            while (records.length > 0) {

                let recordIds = await table.createRecordsAsync(records.slice(0, 50));

                recordsActedOn.push(...recordIds)

                records = records.slice(50);

            };

            break;

        case 'Delete':

            recordsActedOn = records.length;

            while (records.length > 0) {

                await table.deleteRecordsAsync(records.slice(0, 50));

                records = records.slice(50);

            }

            break;

        default:

            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);

            recordsActedOn = null;

    }

    return recordsActedOn;

}

I’m assuming it’s something I must do with the recordids but just not sure what :-).

Can someone help and share some example code with my field and table names please.

Thanks so much

Claire

[Justin_Barrett]

Claire_Conza:

I’m assuming it’s something I must do with the recordids but just not sure what

Yeah, it’s kinda tricky. In short, linked record fields need to be passed an array of objects. Each object must have an id property assigned to the ID of the record you wish to link to. For example, if you want to make two links, it would look something like this:

[{id: "recXXXXXXXXXXXXXX"}, {id: "recYYYYYYYYYYYYYY"}]

That array is what needs to be passed to your {Linked Orders} field toward the end of the script.

Long story short, it looks like you’ve got most of the code properly structured. What you need to do is replace the references to the order number (in various places) with a record ID object following the structure I outlined above.

[Claire_Conza]

Thanks Justin.

Is there any possibility of you helping out with the syntax of the appropriate code in the context of my script and where to put it please?

Thanks

Claire

[Justin_Barrett]

Yes, I can do that, though I won’t be able to get to it until tomorrow. If anyone else is able to jump in sooner, feel free.

[Justin_Barrett · Accepted solution]

This modified script should do the trick. Here are the changes I made:

• The main optimization was replacing order number references with record IDs. What you see when linking to a record in another table is the contents of its primary field, which in this case is the order number. However, Airtable is internally tracking the record IDs of linked records, which is why it asks for an array of objects containing those IDs.
• I also removed some unnecessary nesting of the helper objects. The only place where you absolutely need an object containing a “fields” property which is, itself, another object is when passing data to Airtable. Using that nested structure for the earlier steps just made them unnecessarily complex.

I didn’t build a test base to test this new code, but I gave it a pretty thorough review, so it should work. If you hit any snags, let me know.

let table = base.getTable("Daily Orders");
let ws = base.getTable("Production Required");
let view = table.getView("Unfulfilled Daily Orders");
let sku = table.getField("Line Items: Sku"); 
let ordernum = table.getField("Order: Name"); 
let currsku = "";
let ordstr = "";
let result = await view.selectRecordsAsync({
    sorts: [{field: sku, direction: "asc"}]
});
output.inspect(result);

let ordersArray = [];
for (let record of result.records) {
    currsku = record.getCellValue(sku)[0]; 
    ordersArray.push({
        Record_ID: record.id,
        SKU: currsku
    });
}

var res = [];
ordersArray.forEach(obj => {
    var found = res.find(ob => ob.SKU === obj.SKU);
    if(found){
        found.Record_IDs.push({id: obj.Record_ID});
    } else {
        res.push({SKU: obj.SKU, Record_IDs:[{id: obj.Record_ID}]});
    }  
});

let ordersRecords = [];
res.forEach(obj => {
    //output.text(orn);
    ordersRecords.push({
        fields: {
            "Linked Orders": obj.Record_IDs
        }
    })
});

output.inspect(ordersRecords);
let recordsCreated = await batchAnd('Create', ws, ordersRecords);

/*
    Use this function to perform 'Update', 'Create', or 'Delete'
    async actions on batches of records that could potentially 
    more than 50 records.
    ::PARAMETERS::
    action = string; one of 3 values:
           - 'Update' to call table.updateRecordsAsync()
           - 'Create' to call table.createRecordsAsync()
           - 'Delete' to call table.deleteRecordsAsync()
    table = Table; the table the action will be performed in
    records = Array; the records to perform the action on
            - Ensure the record objects inside the array are
            formatted properly for the action you wish to
            perform
    ::RETURNS::
    recordsActedOn = integer, array of recordId's, or null; 
                   - Update Success: integer; the number of records processed by the function
                   - Delete Success: integer; the number of records processed by the function
                   - Create Success: array; the id strings of records created by the function
                   - Failure: null;
*/

async function batchAnd(action, table, records) {
    let recordsActedOn;
    switch (action) {
        case 'Update':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.updateRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            };
            break;
        
        case 'Create':
            recordsActedOn = [];
            while (records.length > 0) {
                let recordIds = await table.createRecordsAsync(records.slice(0, 50));
                recordsActedOn.push(...recordIds)
                records = records.slice(50);
            };
            break;
        case 'Delete':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.deleteRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            }
            break;
        default:
            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
            recordsActedOn = null;
    }
    return recordsActedOn;
}

[Claire_Conza]

Thanks Justin, much appreciated. Works perfectly. I condensed the linkedorders array down further with this…

const skus = [...new Set(ordersArray.map(obj => obj.fields.SKU))];

const linkedRecordsArray = skus.map(sku => {
    return {

        fields: {

            'Linked Orders': ordersArray.filter(obj => obj.fields.SKU === sku).map(obj => ({ id: obj.fields.id }))

        }

    };

});