May 16, 2022 03:59 AM
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?
May 16, 2022 06:18 AM
Use the JavaScript array method includes().
[any of these].includes(f_record.getCellValueAsString("Term_at_id"))
May 16, 2022 10:55 AM
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]))
May 16, 2022 12:59 PM
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.
May 16, 2022 01:26 PM
Interesting. Do you have any links I can check out?
Or @Bill.French, can you provide a recommendation?
May 16, 2022 01:47 PM
@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);
May 16, 2022 01:48 PM
The problem is that I already know there are matches, but I need to find the individual records/ lines where they match.
May 16, 2022 01:56 PM
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.
May 16, 2022 01:57 PM
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.
May 16, 2022 02:01 PM
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.
May 16, 2022 02:02 PM
Any thoughts on having this return the recordID from the linked table, not the recordIDs that match from the table with the terms?
May 16, 2022 02:03 PM
@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.
May 16, 2022 02:06 PM
Ah, okay. You are building a full-text search engine of sorts, right?
May 16, 2022 02:14 PM
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.
May 16, 2022 02:41 PM
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);
May 16, 2022 04:28 PM
Yep - the hash index would work.
May 16, 2022 11:31 PM
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?
May 17, 2022 06:04 AM
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.
May 17, 2022 10:37 AM
Is there a way to save and store a hash and/or import it?
May 17, 2022 10:55 AM
Also, responded in line on your page. It isn’t clear what is the format of oRecord