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
      • 2 yrs agoThu, May 5, 2022 at 1:03 AM UTC
      • 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
        • 2 yrs agoThu, May 5, 2022 at 7:30 AM UTC
        • Reported - view

        Fred You could try it :  

        array1[(var v := this; not x[= v])]
        • John_Halls
        • 2 yrs agoThu, May 5, 2022 at 10:49 AM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:17 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:20 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:30 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:33 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:34 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:34 PM UTC
        • Reported - view

        Jacques TUR Worked for me Jacques.

        • Fred
        • 2 yrs agoThu, May 5, 2022 at 3:44 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:53 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 3:55 PM UTC
        • Reported - view

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

        • Fred
        • 2 yrs agoThu, May 5, 2022 at 3:59 PM UTC
        • Reported - view

        Just sent an email to Ninox.

        • Ninox developper
        • Jacques_TUR
        • 2 yrs agoThu, May 5, 2022 at 5:35 PM UTC
        • 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
        • 2 yrs agoThu, May 5, 2022 at 5:38 PM UTC
        • Reported - view

        Thanks for figuring it out.

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

      • Fred
      • 2 yrs agoThu, May 5, 2022 at 5:56 PM UTC
      • 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
        • 2 yrs agoSat, May 7, 2022 at 2:36 PM UTC
        • 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
        • 2 yrs agoSat, May 7, 2022 at 8:00 PM UTC
        • Reported - view

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

      • Ninox developper
      • Jacques_TUR
      • 2 yrs agoThu, August 4, 2022 at 7:45 PM UTC
      • 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
        • 2 yrs agoThu, August 4, 2022 at 11:47 PM UTC
        • 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
        • 2 yrs agoFri, August 5, 2022 at 10:57 AM UTC
        • 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

    Categories

    View all topics
    • 13 Likes
    • 2 yrs agoFri, August 5, 2022 at 1:11 PM UTCLast active
    • 45Replies
    • 2437Views
    • 9 Following