Filter values based on multiple values

Use the JavaScript array method includes().

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

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]))

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.

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

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

@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);
 

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);
 

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

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.

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

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

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

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.

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.

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);
 

Any thoughts on having this return the recordID from the linked table, not the recordIDs that match from the table with the terms?

@Bill.French - Here is a sample table. Essentially I am trying to build the equivalent of finding all the menu items // recipes that have peanuts in them.

Ah, okay. You are building a full-text search engine of sorts, right?

• You want the results (hits) to be field-agnostic
• You want the hits to be ranked (maybe) based on relevance
• Some terms and some fields carry more weight than others

Hi - They don’t need to be ranked, and weights aren’t important. I am trying to avoid a loop (with the type of peanut foods for example) within a loop (all the recipes).

I’d like to have it spit out all the recipes with all the types of peanuts all at once.

The code below would work if I had an efficient way of mapping all the values from the table into keys.

var data = [{"id":"123","color":"Red","model":"Tesla"},{"id":"124","color":"Black","model":"Honda"},{"id":"125","color":"Red","model":"Audi"},{"id":"126","color":"Blue","model":"Tesla"}]

var keys = ["color", 'model'];
var values = ["Tesla", "Audi", "Red"];

var result = data.filter(function(e) {
  return keys.every(function(a) {
    return values.includes(e[a])
  })
})

console.log(result);

var data = [{"id":"123","color":"Red","model":"Tesla"},{"id":"124","color":"Black","model":"Honda"},{"id":"125","color":"Red","model":"Audi"},{"id":"126","color":"Blue","model":"Tesla"}]

var keys = ["color", 'model'];
var values = ["Tesla", "Audi", "Red"];

var result = data.filter(function(e) {
  return keys.every(function(a) {
    return values.includes(e[a])
  })
})

console.log(result);

Yep - the hash index would work.

Hi Bill - Thanks for the suggestion but I do not now how to do make the hash index for 8000 values. Do you have a way?

Yes, it’s outlined in the article. The premise is simple - loop through all records of the table to generate JSON objects based on keys before performing the logic that uses the index. This typically takes a second or two and has the added advantage of removing duplicate keys. You are left with a clean index to perform single-digit millisecond lookups.

Is there a way to save and store a hash and/or import it?

Also, responded in line on your page. It isn’t clear what is the format of oRecord

Sure, as long as it’s smaller than 100k, you could save and re all it from a long text field. But it would have to be stringified first and parded when reading back into your script. I would focus first on making it dynamic and then if there are reasons to persist the index, work on that part separately.

The format is a JSON object; the structure is whatever you need the index to be. For example, a list of customer name keys might look like this in a variable named oCustomers:

{ "ABC Transportation" : {
   "customer_id" : "1001",
   "customer_contact" : "Jimmy Johns",
   other attributes needed for the index...
   }
}
... objects representing rest of the customers ...

With such a structure, one could instantly know the customer ID with one line of code in about 2 milliseconds (i.e., all without iterating across the data table):

let customerID = oCustomers['ABC Transportation'].customer_id;

{ "ABC Transportation" : {
   "customer_id" : "1001",
   "customer_contact" : "Jimmy Johns",
   other attributes needed for the index...
   }
}
... objects representing rest of the customers ...

Is the o in the variable name an instance of Hungarian Notation?

Um, no - it’s just an indicator (for my team) of a parsed JSON object. sRecords (for example) would be a serialized JSON object. Declaring (in code) the nature of JSON values is helpful where lots of integrations exist.

Isn’t that a variation of Hungarian Notation?

It’s a prefix that states metadata about the variable–in this case the variable’s data type.

I am back to this problem again trying to work in automation.

I am wanting to filter multiple records by an array. For instance – I am trying to match recipes that have wheat products in to find the ones with gluten.

Gluten will have multiple different names in the table, and I need to filter by these multiple record IDs. Is it possible to do this without hard coding the &&?

Yes, it is possible.

One way is to use the array function includes() inside a loop on the ingredients.

Another way is to make an array that includes all the ingredients and all the gluten ingredients. Then make a new array that contains only unique ingredients. Assuming that each ingredient is listed only once in a recipe, if the two arrays are the same length, there was no gluten in the original list of ingredients.