Automation script to update records triggered by entering a view, then empty update record to remove from the view

[Josh_Cooper]

Community experts,

Sorry for the long title. I am very new to coding of any kind and without the community I would have never gotten this far.

Basically, I have a synced table in my base and I have a automation script to link the records based on a field, “Part Number” in one table and “Part#” in the other. The trigger for the automation is “When a record enters a view”. I am linking the record in the synced table, Orders, to the Inventory table. I am able to make this happen with the following code.

var mainTable = base.getTable("Inventory");
var mainView = mainTable.getView("Vendor Source Inventory");
var mainTableRecords = await mainView.selectRecordsAsync({fields: ["Part Number","Vendor_Source_Usage"]});
var lookUpTable = base.getTable("Orders");
var lookUpView = lookUpTable.getView("Completed Vendor Sourced Orders")
var lookUpRangeRecords = await lookUpView.selectRecordsAsync({fields: ["Part#","Order Qty","Updated"]});

for(let record of mainTableRecords.records) {
    let lookUpValue = record.getCellValue("Part Number");
    let linkArray = [];

for(let rangeRecord of lookUpRangeRecords.records) {
        if(rangeRecord.getCellValue("Part#") === lookUpValue) {            
            linkArray.push(
                rangeRecord.id
            );
        };
    };

    let d = []
    console.log(linkArray.length)
        linkArray.forEach(r => d.push({id: r}));
        await mainTable.updateRecordAsync(record, {
            'Vendor_Source_Usage': d
        });
};

I realize I could do this in a more elegant fashion, but it works. Because of the nature of the workflow and the relatively fast sync rate, I have no worries of hitting the 50 record limit, as long as I can clear the view that is triggering the automation, “Completed Vendor Sourced Orders”, after I link the records.

What I would like to do now is add code to remove the records from “Completed Vendor Sourced Orders”. I have set up a checkbox field called “Updated” that I would like to check, but I can’t seem to make that work.

I tried the following replacement of the second for loop and the check boxes are getting marked but now the records aren’t being linked.

    for(let rangeRecord of lookUpRangeRecords.records) {
        if(rangeRecord.getCellValue("Part#") === lookUpValue && rangeRecord.getCellValue("Updated") === null) {            
                linkArray.push(
                    rangeRecord.id
                );
                await lookUpTable.updateRecordAsync(rangeRecord, {
                    'Updated': true
                });
            };
        }

Any help would be greatly appreciated!

Thanks in advance!

[Josh_Cooper]

Thanks to all the great post on this forum, I was able to make significant improvements.

This works, but the trigger (when record is added to view - view is set to filter out records that are linked), if no matches exist for the inventory table, removes current links causing an infinite loop.

const ordersQuery = await base.getTable("Orders").getView("Completed Vendor Sourced Orders").selectRecordsAsync();
const invTable = base.getTable("Inventory");
const invQuery = await invTable.getView("Vendor Source Inventory").selectRecordsAsync();

let newUpdate = {};
let updates = []

//Loop through the inventory table and get the part numbers
for (let inv of invQuery.records) {
    let invPN = inv.getCellValue('Part Number');
    let updateOrder = [];

//Loop through the new orders and match them with the part numbers
            for (let order of ordersQuery.records) {
                let orderPN = order.getCellValue('Part#');
                if(!orderPN) {
                    continue;
                }

                if (invPN === orderPN) {
                    let newRec = order.id
                    updateOrder.push({id: newRec})
                }
            };
    //Assign the Inventory id
    let id = inv.id
    
    //Package the update
    newUpdate = [{id: id, fields: {["Vendor_Source_Usage"]: updateOrder}}]

    console.log(newUpdate);

    //Make sure less than 50 records are being updated and update
    while (newUpdate.length > 0){
        await invTable.updateRecordsAsync(newUpdate.slice(0,50));
        newUpdate = newUpdate.slice(50);
    }
}

I solved that problem by removing the filter that filters the orders that are linked.

The only part I am concerned about, is when the “Completed Vendor Sourced Orders” view gets bigger, the script will have to link more and more records. As it is written above, every record on the view is linked to the appropriate part number when a new record enter the view. It would be great if I could append each new order to the appropriate part number on the Inventory table.

Any ideas on how to do that? I am assuming you could loop through each part number and see if the Order is already linked, but I am not sure how to do that.

I will keep working on it, but any help would be greatly appreciated.

Thanks again for the help!

[kuovonne]

This is not a direct answer to your question, but some general thoughts:

• It sounds like the automation is triggered by a single record entering the view, but you are processing all of the records in the view, not just the triggering record. Is there a reason you are not processing only the single triggering record?

• Instead of using a nested loop to find the matching part numbers, consider building a hash table based on the part number outside the loop, then get the part number from the hash instead of using nested loop. (This may not be necessary if you process only the triggering record.)

[Josh_Cooper]

How do I go about only processing the triggering record? Wouldn’t updating the link with one record remove all the existing links? Is there a way to append records?

Thanks for the help!

[Josh_Cooper · Accepted solution]

Wow! I just realized what you meant @kuovonne !

I was stuck on updating the linked field on the Inventory table with all the records on the Orders table when all I needed to do is update each of the records on the Orders table with one record from the Inventory table.

I feel horrible for the lost time anyone spent reading this!

If you are interested the working code is below. The trigger is “when a record enters a view” and the filter is set to remove orders that are linked on the “Inventory” field.

//set variables and constants
let inputConfig = input.config();
const ordersTable = base.getTable("Orders ")
const ordersQuery = await base.getTable("Orders ").getView("Completed Vendor Sourced Orders").selectRecordsAsync();
const invTable = base.getTable("Inventory");
const invQuery = await invTable.getView("Vendor Source Inventory").selectRecordsAsync();
let newOrdId = inputConfig.trigRec;
let newOrdPn = ordersQuery.getRecord(newOrdId).getCellValue('Part#');
let newOrdNumber = ordersQuery.getRecord(newOrdId).getCellValue('Order Info');
let match;

//Loop through Inventory records to find the part number that matches the new Order
for (let invRec of invQuery.records) {
    if (invRec.getCellValue('Part Number') === newOrdPn){
        match = invRec.id
        };
    };

//Update the new Order Linked field with the matching Inventory record id
await ordersTable.updateRecordAsync(newOrdId,
    {'Inventory': [{id: match}]
});

Works like a charm and is so simple!

Thanks again @kuovonne !