Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Filter values based on multiple values

Topic Labels: Scripting extentions
815 25
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

25 Replies 25

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

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.

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.

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

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