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

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

      Fred You could try it :  

      array1[(var v := this; not x[= v])]
      • John_Halls
      • 1 yr 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

      • Fred
      • 1 yr 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]]
      • Fred
      • 1 yr 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.

      • John_Halls
      • 1 yr 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

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

      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
      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      Fred I copied and pasted this code and it works fine for me. Are there other people who have a problem with this code?

      • John_Halls
      • 1 yr ago
      • Reported - view

      Jacques TUR Worked for me Jacques.

      • Fred
      • 1 yr 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.

      • John_Halls
      • 1 yr 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

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

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

      • Fred
      • 1 yr ago
      • Reported - view

      Just sent an email to Ninox.

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

      Fred It works on the mac application if you put this code : 

      let array1 := [1, 2, 3, 4, 5];
let x := [1, 3];
array1[var v := this;
    count(x[= v]) = 0]
      • Fred
      • 1 yr ago
      • Reported - view

      Thanks for figuring it out.

      It is not good that the two products don't function the same.

    • Fred
    • 1 yr ago
    • Reported - view

    I'm obviously not getting the logic behind all this so I'm putting this out there.

    Here is my code: 

    let xH1 := allhito1;
let xH2 := allhito2;
xH1[var ah1 := this;
    count(xH2[= ah1]) = 0]

    The two fields (allhito1 & 2) are the results of select statements.

    Now taking the new formula from Jacques from below I thought it would do the subtraction of xH2 records from xH1, but I get the same number of records in xH1. If change xH2 to hard coded numbers that are in xH1, then they get subtracted properly.

    I think I need to add a var section to count(xH2...) but my little brain can't figure it out.

    Thanks all for your help.

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

      Fred I copied your code and it works fine. 

      If allhito1 contains the records : A1,A3,A4,A5,A6,A8,A10,A12,A13, 
      and allhito2 contains : A4,A10,A11,A13,
      the result is: A1,A3,A5,A6,A8,A12.

      To do this test I modified your code as follows: 

      let xH1 := (select Customer where 'First Name' like "a");
let xH2 := (select Customer where 'First Name' like "e");
xH1[var ah1 := this;
    count(xH2[= ah1]) = 0].string(ID)

      I tested it on the web and on the Mac application.

      • Fred
      • 1 yr ago
      • Reported - view

      Thanks Jacques. It was stupidity on my end. I was looking at the wrong field to subtract.

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view
    Fred said:
    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.

    The ‘magic’ comes from the fact that the variable C contains all the records that are in A and in B. so if the number of records in C is equal to the number of records in A, it means that the content of C is equal  to the content of A.

      • Fred
      • 1 yr ago
      • Reported - view

      Well then how does Ninox know which records to show if you are only doing a count? 

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

      Line 1 creates a variable "a" and stores the number results of the selections from the 'Select characteristics' dMC.

      Line 2 starts a select of the Contact table.

      Line 3 starts to filter the select from Line 2. The first thing it does is to create a variable called "b" that stores the number results of the selections from the 'list of characteristic' dMC.

      Lines 4 and 5 creates a variable "c" that consists of numbers that are in both variable "a" and "b".

      Line 6 then counts the records in c and compares the results to the count of records in a.

      So then how does Ninox know which records to filter on if you are just counting the records?

      I don't see where you tell Ninox to filter based on 'list of characteristics'.

      Thanks for putting up with my simple questions.

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

      Fred Fred 

      Lines 4 and 5 creates a variable "c" that consists of numbers that are in both variable "a" and "b".

      On line 4 and 5, the variable C contains the numbers common to A and B. 
      For example : 

      if A = [1, 2, 3] and B = [1, 3], then C = [1, 3];

      if A = [1, 2, 3] and B = [1, 2, 4], then C = [1, 2];

      if A = [1, 2, 3] and B = [1, 3, 4, 5, 6 ], then C = [1];

      if A = [1, 2, 3] and B = [1, 2, 3, 4 ], then C = [1, 2, 3];

      So, if the number of elements in C is equal to those in A, then the content of C is equal to that of A.

      Does this help you understand better?

Login to reply

Content aside

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