Filter values based on multiple values

Rose_Haft1 · ‎May 16, 2022

Hi there - We are trying to filter records based on multiple IDs but it is doing so in a loop so we can’t use the || or && as we don’t always know if there are multiple and often times there are 5 or more variables.

We want something like this:

var these= tabale_await.records.filter(f_record => f_record.getCellValueAsString(“Term_at_id”) == [any of these])

Is it possible?

kuovonne · ‎May 16, 2022

Use the JavaScript array method includes().

[any of these].includes(f_record.getCellValueAsString("Term_at_id"))

Rose_Haft1 · ‎May 16, 2022

Yes, I know how to use that.

Here is a re-phrasing to hopefully explain a bit differently:

Table 1 is a list of terms
Table 2 is a list with two columns relating terms from table 1

We want to filter a list of terms from Table 1 and find all the matching pairs in table 2. With what you posted we would have to loop it which we want to avoid.

What is preferred is to have something like:

[any of these from table 2].includes(f_record.getCellValueAsString([any of these from table 1]))

kuovonne · ‎May 16, 2022

It sounds like either you want to find the intersection of two arrays, or maybe you would benefit from creating an object that hashes a term to all of its related values. Bill French has written extensively about using hashes/maps to avoid loops inside loops.

Rose_Haft1 · ‎May 16, 2022

Interesting. Do you have any links I can check out?

Or @Bill.French, can you provide a recommendation?

Rose_Haft1 · ‎May 16, 2022

@kuovonne - I have it partially working. I need it to recall which record IDs from the table with the links, not the individual terms.

Ideas?

var t_links = base.getTable("Table 1")
var t_links_aw = await t_links.selectRecordsAsync

var t_table = base.getTable("Test linking")
var t_table_aw = await t_table.selectRecordsAsync()


var id_peanuts = [] // ids from the linked terms 
id_peanuts.push("recfMSt4Dd25XNi1V")
id_peanuts.push("recfMSt4Dd25XNi1V")
id_peanuts.push("rec5hD3qqvEKDKIfh")

var f_foods = t_table_aw.records.filter(f_record => f_record.getCellValue("Table 1")!=null)

var arr_t1_ids = f_foods.map(recordID => recordID.getCellValueAsString("recordID (from Table 1)"))

console.log(arr_t1_ids)

function intersection(first, second)
{
    first = new Set(first);
    second = new Set(second);
    return [...first].filter(item => second.has(item));
}
 
var first = id_peanuts
var second =arr_t1_ids
 
var common = intersection(first, second);
console.log("Common elements are: " + common);

Rose_Haft1 · ‎May 16, 2022

The problem is that I already know there are matches, but I need to find the individual records/ lines where they match.

Bill_French · ‎May 16, 2022

This is a classical case for hash indexes; given (x), is it in y(x)?

Imagine Table 1 is a collection of JSON objects whose items are index pointers to the labels themselves.

let hashIndex = {
  "term01" : { 
    "recordID" : "rec01234567",
    "topic" : "science"
  }, {
  "term02" : { 
    "recordID" : "rec01234567",
    "topic" : "technology"
  }
}

When you want to know if “term01” exists in Table 1, you simply test for it - it is instant and no looping:

if (hashIndex["term01"]) {
  ...
}

And if you need to access the record for any given term, you could use the record ID to do so. Alternatively, simply include the data from the record in the hash index and you don’t need to go foraging for the record to know the other attributes of the term.

if (hashIndex["term01"]) {
  let topic = hashIndex["term01"].topic;
}

One of the big benefits of hash indices is performance which for these tests would be measured in single-digit milliseconds.

Rose_Haft1 · ‎May 16, 2022

Hi Bill - We have more than 8000 terms in our table_links. Do you have a recommendation for doing this without hard coding it?

I am not understanding if you’ve told me.

Bill_French · ‎May 16, 2022

Probably not without seeing the grand scheme of the requirement. But, there is nothing about hash indexes that tilt toward hard coding. Setting a model that is capable of determining if and where something exists is an indexing challenge; the most elegant implementation that avoids brittle hard coding techniques is really a different challenge.

I always recommend getting it to work in a performant manner first; then make it less brittle and more reusable.