13

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

 

Copy

 

result of formula :

45 replies

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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.

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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.

      • Ninox developper
      • Jacques_TUR
      • 2 yrs ago
      • Reported - view

      Alain Fontaine Yes, it's right 👍

      var myTable := ["Jacques", "Marie", "Leon", "Nadine", "Marie-Claire", "Sylvain"];
      var s := this.Name;
      myTable[this like s]
      

      In the code above, the "this" on line 2 represents the current record (where Name is a text field). The one on line 3, between the square brackets, represents the current element of myArray

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    And again: it works with the other comparison operators too. So, a very nice trick with lots of potential applications. 👍

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    And yet another one: it seems that, inside the square brackets, the keyword "this" represents, in turn, the value of each element of the array. So one can do things like:

    myTable[substr(this, 0, 1) = t]
    

    where "t" is a variable. Really amazing.

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    Thanks,

      • Ninox developper
      • Jacques_TUR
      • 2 yrs ago
      • Reported - view

      Fred 

      I think there is a better way to get this result by using the select function, but to stay in the theme of this thread, I suggest you try this 

      let curRec := this;
      let xRes := unique(select Results[RiderID = curRec.RiderID].Year)
      let xSeason := select Seasons;
      xSeason[(var y = this.Year;
               count(xRes[ = y])>0)]
      
      • Fred
      • 2 yrs ago
      • Reported - view

      Jacques TUR Always willing to learn a better way, how would you write it? Would you drop the let xSeason and just do it in select Seasons?

      • Ninox developper
      • Jacques_TUR
      • 2 yrs ago
      • Reported - view

      Fred 

      Another solution is to use JSON arrays. They have the advantage of being used by Ninox as a table. You can use the square brackets to make a filter where and add a dot at the end to iterate through all the elements in the table.

      let curRec := this;
      // creation of a JSON array
      let xRes := (select Results[RiderID = curRec.RiderID]).{y:Year}
      // removal of duplicates
      let uniqueRes := unique(xRes.y)
      //Each year's course and returns the corresponding seasons.
      uniqueRes.(var y := this;
                      first(select Seasons where year = y))
      //results can be used as a data source in views or dynamic simpe/multi choice fields
      

      more condensed

      let curRec := this;
      unique( ((select Results[RiderID = curRec.RiderID]).{y:Year}).y ).(var y := this;
                      first(select Seasons where year = y))
      
      • Ninox developper
      • Jacques_TUR
      • 2 yrs ago
      • Reported - view

      Fred something like this probably

      let curRec := this;
      select Seasons where ( var y := this.Year;
              count(select Results where RiderID = curRec.RiderID and Year = y) >0 ) order by Year;
      
      • Fred
      • 2 yrs ago
      • Reported - view

      Jacques TUR I've never seen the use of the curly brackets. What are you telling Ninox to do with:

      {y:Year}
      • Ninox developper
      • Jacques_TUR
      • 2 yrs ago
      • Reported - view

      Fred 

      In Ninox Script, you can use JSON variable (JSON by Mozzila)

      var config := {lastName : "tur", firsName : "jacques"}
      var myName := config.firstName + " " + config.lastName;
      //myName = jacques tur

      if you use this formation as in a table iteration...

      var myArray := (select Customer where 'First Name' like "s").{
          firstName: 'First Name',
          lastName: 'Last Name'
      }
      myArray;

      you will obtain an array of JSON :

      [
          {
              "firstName": "Sofia",
              "lastName": "Young"
          },
          {
              "firstName": "Lisa",
              "lastName": "Wilson"
          },
          {
              "firstName": "Sebastian",
              "lastName": "Wood"
          },
          {
              "firstName": "Samantha",
              "lastName": "Grey"
          }
      ]

       

      Now you can filter the array by using a JSON key

      myArray[this.lastname like "y"];

      result :

      [
          {
              "firstName": "Sofia",
              "lastName": "Young"
          },
          {
              "firstName": "Samantha",
              "lastName": "Grey"
          }
      ]

       

      Sea also :

      formatJSON

      parseJSON

      • UKenGB
      • 1 yr ago
      • Reported - view

      Jacques TUR You are often using // to mean a comment and I was quite excited thinking we finally could include comments. However that certainly doesn't work on Mac 3.6.8 (the latest AFAIK).

      Is this something just not yet implemented in the Mac version or is Ninox still annoyingly bereft of any commenting facility and you added those just while posting some code to the forum?

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      UKenGB it's just when I post on the forum. Unfortunately, Ninox doesn't allow comments yet.

      • UKenGB
      • 1 yr ago
      • Reported - view

      Jacques TUR 😫

      • Matthias_S88
      • 1 yr ago
      • Reported - view

      Jacques TUR  Hi, i have a view of Records and I want to filter the view by an dynamic multiple choice field. How can I do this. Can you help me please?

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      Matthias S. Could you send me one exemple or tell me more ?

      • Matthias_S88
      • 1 yr ago
      • Reported - view

      Jacques TUR I have a Table Contacts. In the table Contacts I have a dynamic multiple choice field with characteristics.

      Then I have another Table (Dashboard) with a view of the contacts. Here I want to filter the view with the same dynamic multiple choice field.

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      Matthias S. Something like that ?

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      I was inspired by the code published by John Halls below. The idea is to do a boolean AND operation between the DMC of the filter (named Select characteristics) and the DMC of each record (named caracteristics), then to check that the result contains the same number of elements. In other words, we check, for each record, that it contains at least the same characteristics as the one of Select charateristics. 

      var a := numbers('Select characteristics');
      select Contact
          where (var b := numbers('list of characteristics');
          var c := a[var v := this;
                  count(b[= v]) > 0];
          count(c) = count(a))
      
      • Fred
      • 1 yr ago
      • Reported - view

      still trying to wrap my head around how count(c) = count(a) returns a value for the where command to work. it works but my wee brain is just not getting why it does.

      To me the Select Contact where (count(c) = count(a)) says, count the number of items in variable c and check if it is equal to the number of items in variable a.

      It does not read to me, check each item in variable c against each item in variable a and if they are equal return the record in Contacts where the numbers from 'list of characteristics' is equal to that number.

      maybe one day it will sink in.

      • Matthias_S88
      • 1 yr ago
      • Reported - view

      Jacques TUR 

      Wow. That was fast.

      However, when I select two filters, it only shows me the contacts that have both properties. But I want it to show me all contacts that have one of the two selected. How can I fix it. I'm still trying to understand the formula. 

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      Matthias S. Simply replace the code with this one:

      var a := numbers('Select characteristics');
      select Contact
          where (var b := numbers('list of characteristics');
          count(a[var v := this;
                      count(b[= v]) > 0]) > 0)

      In this case, we test if A and B have at least one element in common. To do this, in line 5 and 4 we filter the array A by searching for each element if it also exists in B.

      The same result could be obtained by using For loops :

      var a := numbers('Select characteristics');
      select Contact
          where (var b := numbers('list of characteristics');
          var nbCommun := 0;
          for i in a do
              for j in b do
                  if i = j then
                      nbCommun := nbCommun + 1
                  end
              end
          end;
          nbCommun > 0)
      
      • Matthias_S88
      • 1 yr ago
      • Reported - view

      Jacques TUR It works great. I also added that you can choose whether the properties should be ticked or not. Thanks a lot

    • Fred
    • 1 yr ago
    • Reported - view

    Ok, so I'm trying to remove one array from another.

    let array1 := [1, 2, 3, 4, 5];
    let x := [3, 1];
    array1[this != x]
    

    Doesn't return 2,4,5

    If x = 3  (a single digit) then it works).

    I'm obviously missing something here.

    Thanks in advance for any help.

Content aside

  • 13 Likes
  • 1 yr agoLast active
  • 45Replies
  • 1948Views
  • 9 Following