Complex conditional lookups within same table

[D_McGhie]

Hi All!
Been thinking about this for a while, but cannot wrap my head around it! Looking for a bit of help.

I have multiple tables with lots of lookups all connected together, but am struggling to get a same table lookup to work. Thinking maybe scripts or automations?

My table is as below:

I am trying to accomplish the following:
When the current row is updated to ‘Existing Supply Rating’ it (ie the automagical widget or script) searches for similar rows and returns the file associated with the most recent similar matching row. It then copies the URL of the file into this record.

Kind of a fuzzy search which then returns and inserts a file for my current row.

if ({Existing Supply Rating}=True)
return Attachment into this ‘cell’
where {Site Type} = this {site type}
and {in/Out} = this {In/Out}
and {Proposed Config} = this {Proposed Config}
and {Existing Supply Location} = this {Existing Supply Location}
and {Existing Mains Supply} = this {Existing Mains Supply}
and {Existing Supply Rating} = this {Existing Supply Rating}
and {creation date} = most recent

The above logic is kind of what I am thinking. There is no fuzzy-ness, as it has to be exact, but functions like a fuzzy search does.

Any pointers would help.

[Prosammer]

Hey there!

I’ve created a really rough outline of a script that would do this for you, although it will most likely take a bit of tweaking to get it setup specifically for your base! You could connect this script to a button, and it would return the most recently created record meeting all your other criteria.

Let me know if I can help tailor this script to your needs!

Sam

// Change this name to use a different table
let table = base.getTable("Table 3");
let allrecords = await table.selectRecordsAsync({sorts: [{field: "creation date"}]});


let originalrecord = await input.recordAsync('Select a record to use', table);
let originalrecordID = originalrecord.id;
let existingSupplyRating = originalrecord.getCellValue("ExistingSupplyRating");

if (existingSupplyRating) {
    let original_site_Type = originalrecord.getCellValue("Site Type");
    let original_in_Out = originalrecord.getCellValue("in/Out");
    let original_proposed_Config = originalrecord.getCellValue("Proposed Config");
    let original_existing_Supply_Location = originalrecord.getCellValue("Existing Supply Location");
    let original_existing_Mains_Supply = originalrecord.getCellValue("Existing Mains Supply");
    let original_existing_Supply_Rating = originalrecord.getCellValue("Existing Supply Rating");
        
    let returnedRecordAttachment = function returnRecord() {
        for (let record of allrecords.records) {
            let site_Type = record.getCellValue("Site Type");
            let in_Out = record.getCellValue("in/Out");
            let proposed_Config = record.getCellValue("Proposed Config");
            let existing_Supply_Location = record.getCellValue("Existing Supply Location");
            let existing_Mains_Supply = record.getCellValue("Existing Mains Supply");
            let existing_Supply_Rating = record.getCellValue("Existing Supply Rating");

            if (site_Type == original_site_Type ||
                in_Out == original_in_Out ||
                proposed_Config == original_proposed_Config ||
                existing_Supply_Location == original_existing_Supply_Location ||
                existing_Mains_Supply == original_existing_Mains_Supply ||
                existing_Supply_Rating == original_existing_Supply_Rating) 
                
                return record.getCellValue("Attachments");
        }
    }
    table.updateRecordAsync(originalrecordID,{"Attachments":returnedRecordAttachment})
}

[D_McGhie]

Wow! That is helpful! I shall attempt using this!

[Prosammer]

Cool, let me know if I can help tweak it or anything, i’m stuck in quarantine and bored! :slightly_smiling_face: