7

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 :

30replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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.

    Like 1
  • 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.

    Like
    • 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

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

    Like 1
  • 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.

    Like 1
    • Fred
    • Fred
    • 3 mths 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,

    Like
    • 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)]
      
      Like
      • Fred
      • Fred
      • 3 mths 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?

      Like
    • 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))
      
      Like
    • 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;
      
      Like
      • Fred
      • Fred
      • 3 mths 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}
      Like
    • 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

      Like 1
    • Fred
    • Fred
    • 2 mths 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.

    Like
    • Fred You could try it : 

      array1[(var v := this; not x[= v])]
      
      Like
      • John Halls
      • John_Halls
      • 2 mths ago
      • Reported - view

      Jacques TUR Hi Jacques. Plenty of new concepts for me here.

      • So 'this' is not confined to records
      • x[=v], v doesn't have to be a single element, and can in fact be larger than x

      All good stuff, thank you

      Like
      • Fred
      • Fred
      • 2 mths ago
      • Reported - view

      Sadly that gives me a blank result:

      let array1 := [1, 2, 3, 4, 5];
      let x := [1, 3];
      array1[var v := this;
          not x[= v]]
      
      Like
      • Fred
      • Fred
      • 2 mths ago
      • Reported - view
      John Halls said:
      x[=v], v doesn't have to be a single element, and can in fact be larger than x

      If I'm learning the concepts correctly, I'm sure people smarter than me will correct me, variable v is a single element. Ninox is taking each individual element of array1, putting it in variable v and checking it against x.

      Like
      • John Halls
      • John_Halls
      • 2 mths ago
      • Reported - view

      Fred You are right Fred, thank you.

      You need to put the var v... inside brackets

      let array1 := [1, 2, 3, 4, 5];
      let x := [1, 3];
      array1[(var v := this;
          not x[= v])]
      
      

      Regards John

      Like
    • Fred It's exactly the same code as this one, but with a For loop it's perhaps easier to understand.

      let array1 := [1, 2, 3, 4, 5];
      let x := [1, 3];
      for t in array1 do
          var v := t;
          if not x[= v] then t end
      end
      
      Like
    • Fred I copied and pasted this code and it works fine for me. Are there other people who have a problem with this code?

      Like
      • John Halls
      • John_Halls
      • 2 mths ago
      • Reported - view

      Jacques TUR Worked for me Jacques.

      Like
      • Fred
      • Fred
      • 2 mths ago
      • Reported - view

      I put the  ( ) in but everytime I save it and go back in Ninox strips them out.

      That is so weird that it is not working for me.

      I've even try the for loop and that also gives me a blank field.

      I've tried restarting the app. I've tried it in another DB. Still nothing.

      I guess I'm going to have to email Ninox.

      Like
      • John Halls
      • John_Halls
      • 2 mths ago
      • Reported - view

      Jacques TUR Fred I became a bit curious about all of this so started looking up about sets. What Fred is asking for is the Complement. There is also the Union and Intersection.

      The union of two sets contains all the elements contained in either set (or both sets).

      let A := [1, 3, 5, 8, 10, 15, 22, 41];
      let B := [1, 8, 10, 11, 16, 22, 48];
      sort(unique(array(A, B)))

      The intersection of two sets contains only the elements that are in both sets.

      let A := [1, 3, 5, 8, 10, 15, 22, 41];
      let B := [1, 8, 10, 11, 16, 22, 48];
      let C := A[(var v := this; not B[= v])];
      A[(var v := this; not C[= v])]
      

      The complement of a set A contains everything that is not in the set A

      let A := [1, 3, 5, 8, 10, 15, 22, 41];
      let B := [1, 8, 10, 11, 16, 22, 48];
      B[(var v := this; not A[= v])];
      

      Regards John

      Like 2
    • Fred It work on web, but not on mac application !!! Of course, you can send one email to Ninox.

      Like
      • Fred
      • Fred
      • 2 mths ago
      • Reported - view

      Just sent an email to Ninox.

      Like
Like7 Follow
  • 7 Likes
  • 1 mth agoLast active
  • 30Replies
  • 294Views
  • 5 Following