acces to array items like with Where fonction
Hello to all,
This new forum made me want to share something with you .
I chose this code which allows to find elements in a table by filtering as we would do with the Where function of Select.
var myTable := ["Jacques", "Marie", "Leon", "Nadine", "Marie-Claire", "Sylvain"]; myTable[like "Marie"]
result of formula :
Very nice trick. It also works with [= "Marie"], to look for a perfect match, and with other element types. Which leads to a way to check if an array contains (at least one instance of) an element with a certain value:
var myTable := ["Jacques", "Marie", "Leon", "Nadine", "Marie-Claire", "Sylvain"]; cnt(myTable[= "Marie"]) > 0
It is a much better way than flattening the array into a string and using the "contains" function, a procedure that can lead to false positives.
Another note… This trick is even more useful if one can look for a value that is not a literal. So I tried with the name of a field (after "like" or "="), and got an error message. But with name of a variable, it works. It is probably because the context inside the square brackets is not the current record. So, if one needs to look for a value that is contained in a field, one must first copy the value in a variable, just like when filtering a "select" or a reference.
I'm trying to wrap my head around this. How would you do something like this comparing the value of one array with another?
let curRec := this; let xRes := unique(select Results[RiderID = curRec.RiderID].Year) let xSeason := select Seasons; xSeason[this.Year like xRes]
The above doesn't work. I'm doing this is a view element. It resolves to the Seasons table correctly but doesn't show any records.
I'm in the Rider table.
Line 2, I go to the Results table to find all results related to the rider and then returns only unique years that the rider has results for (i.e. 2019,2021).
Line 3 finds all the records of the Seasons that I have (i.e. 2016,2017,2018,2019,2020,2021,2022).
Line 4 I want to only find the records in Seasons where the Year matches the array found in line 2.