4

Track selection of Dynamic Multiple Choice field

Hi all -

Here is one possible solution if you want to track the selection of choices in a dynamic multiple choice (dMC) field.

In my test DB I have Table2 as my working table. I have a dMC field that points to Table4. I then created a table called child that is a subtable of Table2. In the child table I have the following fields:

Number

Table2 - reference field to Table2 with composition on

Table4 - reference field to Table4

 

Here is the code that I put in the Trigger after update of my dMC field:

let t := this;
let childCnt := count(child);
let dMCcnt := count(numbers(dMC3));
let childArray := child;
let dMCArray := numbers(dMC3);
let diff := if childCnt > dMCcnt then
        let xRec := childArray[(var dt := Table4;
                    count(dMCArray[= dt])) = 0];
        delete child[Table4 = xRec.Table4]
    else
        let xRec := dMCArray[(var dt := this;
                    count(childArray[Table4 = dt])) = 0];
        let newRec := (create child);
        newRec.(
            Number := first(xRec);
            Table4 := first(xRec);
            Table2 := t
        )
    end;

Line 1 gathers the data of the current record.

Line 2 counts the number of records in the child table

Line 3 counts the number of selections in the dMC

Line 4 gathers all of the records from the child table

Line 5 gathers all of the values selected from the dMC field

Line 6 checks to if the count of child is greater than the dMC count. This assumes that there will be a deletion of a record.

Lines 7-8 figures out which record is no longer in the dMC and line 9 deletes that record

Line 10 is the else if dMC count is greater than child so we are assuming a new record needs to be added.

Lines 11 -12 figures out the new record to add.

Lines 13-18 adds the new record.

To see this work, create a new formula field in the main table and add:

child.Table4

You will see the record Id, of the selections made in the dMC field be added and deleted as you select and de-select. As long as you don't manually add records to the child table this will always show you the selection order.

I started off with just a number field that tracks the record Id of the selection from the dMC (that is why I left the number field in the table and in the new record creation), but then I changed it to link a reference field back to Table4, so now you can use that link to gather data from Table4.

I hope this helps someone or spurs some great idea in others.

33 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    Thanks Fred ,

    Always fun and appreciated when someone exchanges ideas.

    It would be even nicer if there were a sample database shared with the community.

    Steven

    • Fred
    • 1 yr ago
    • Reported - view

    Ask and you shall receive!

    Open Table2 and you will see the form to try it out.

      • Sam.1
      • 1 yr ago
      • Reported - view
    • Mel_Charles
    • 1 yr ago
    • Reported - view

    Fred - Looks great and clearly very useful

    Might be me but I could not get any of the deletions to work 

    Nor choice 6 after others had been selected - if you select 6 first then all other child records added seem to be blank

      • Sam.1
      • 1 yr ago
      • Reported - view

      Mel Charles it works flawlessly on the Mac Version for me. My experience was similar on the iPad and windows version

      • Sam.1
      • 1 yr ago
      • Reported - view

      Sam experience similar to yours

    • Fred
    • 1 yr ago
    • Reported - view

    Just tried it on my iPad and it works fine. I guess something in it the web version doesn't like.

    That is very interesting.

    Just noticed on line 6 I put the main part of the code in a variable then do nothing with the variable. Can you try removing the variable command?

    I just did it on the Mac version and it still works.

    • Fred
    • 1 yr ago
    • Reported - view

    Looking again at my code the top part can be better written:

    let t := this;
    let childArray := Child;
    let dMCArray := numbers(dMC);
    let childCnt := count(childArray);
    let dMCcnt := count(dMCArray);
    

    The idea behind this is that I should be setting my arrays first then doing counts on them. I originally wrote the code to gather the records and numbers from the child table and the dMC. Then I did the same thing again when I created the arrays. Not very graceful.

    Granted in this instance you probably would never notice. But it is always better to write code that is efficient.

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

    Very interesting code, thanks a lot Fred  !

    I had fun modifying it so that it would take into account all the changes that would be made in the table at once and not only an addition or a removal.

    Here is what it looks like 

    let t := this;
    let dMCArray := numbers(dMC);
    "remove unselected childs";
    for i in Child do
        if count(dMCArray[this = i.Table4.ID]) > 0 then
            delete Child
        end
    end;
    "add selected childs";
    for i in dMCArray do
        let xRec := record(Table4,i);
        if count(Child[Table4 = xRec]) = 0 then
            (create Child).(
                Number := xRec.Number;
                Table4 := xRec;
                Table2 := t
            )
        end
    end;
    void
    
    • Fred
    • 1 yr ago
    • Reported - view

    I wonder if this will fix the non-delete in the web version?

    Looking at your code I wouldn't have thought this up as it seems like Ninox would run both loops. But I guess that is OK since the loops are designed to only function at the appropriate times, so they won't step on each other.

    I tried it out and the code did not delete the last related record when I deselect everything in the dMC. So I made the change from > to >= on line 2:

    for i in Child do
        if count(dMCArray[this = i.Table4.Id]) >= 0 then
            delete Child
        end
    end;

    And that seems to have cleared things up.

    I've uploaded an updated DB with Jacques' code. His is called dMCJ. Just remember to only use one dMC at a time. You have to de-select everything from one dMC before using another.

    Love the community share!

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

      Fred Sorry, I published too fast. After some tests, I modified the code so that it doesn't delete all the children every time.

      let t := this;
      "retrieve all selected records from Table4";
      let dMCArray := for i in numbers(dMC) do
              record(Table4,i)
          end;
      "delete Childs that are not selected only";
      for i in Child do
          if count(dMCArray[this = i.Table4]) = 0 then
              delete i
          end
      end;
      "add only the selected items not present in the Child table";
      for i in dMCArray do
          if count(Child[Table4 = i]) = 0 then
              let c := (create Child).(
                      Number := i.Number;
                      Table4 := i;
                      Table2 := t
                  );
              void
          end
      end;
      void
    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view
    Fred said:
    So I made the change from > to >= on line 2:

    Dans ce cas, la condition sera toujours vrai, que Count retourne 0 ou plus de 0;
    La bonne solution est = 0;

      • Fred
      • 1 yr ago
      • Reported - view

      Jacques TUR 

      Again you are correct. My change to ">=" actually broke the tracking and when I changed it to =0 the tracking of selection worked again.

      I've updated the test DB and have included it here.

      • Struggling Student of Conference Interpreting
      • Rvl
      • 12 days ago
      • Reported - view

       Hey Fred et, I can't get any of the files listed here to download or open on my mac.  This has never happened before.  Any clues why?  Actually the files download but when I try to open them nothing happens..-

      • Fred
      • 12 days ago
      • Reported - view

      I just tried it and it worked fine on my mac.

      • Fred
      • 11 days ago
      • Reported - view

      Can you open any zip files on your mac?

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view
    Fred said:
    Just remember to only use one dMC at a time. You have to de-select everything from one dMC before using another.

    I'm not sure to understand why you would recommend clearing the dMC before using another one. You can have a different dMC per record.

      • Fred
      • 1 yr ago
      • Reported - view

      It is only for this test DB since dMC and dMCJ both write to the Child table, making selection/de-selections in both dMC at the same time will cause the Child table and dMCs to not align.

    • Fred
    • 1 yr ago
    • Reported - view
    Jacques TUR said:
    let dMCArray := for i in numbers(dMC) do
    record(Table4,i)
    end;

     I like this part. I didn't think of putting a loop in a variable to get an array of a dMC.

    This is much better then numbers by itself as you now have an array that links directly to records in a table and not just their Id number.

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

      Fred Yes, it is sometimes important to distinguish between the types of variables returned by the Ninox functions :

      - numbers(dMC); return an array of number record id.
      debugValueInfo(numbers(dMC)) return : number([1,3,5])

      - let dMCArray := for i in numbers(dMC) do
      record(Table4,i)
      end;
        return in dMCArray an array of rid (RecordID) like select function.
      debugValueInfo(dMCArray) return : rid([1,3,5])

      - dMCArray.ID return an array of nid (Number of ID).
      debugValueInfo(dMCArray.ID) : nid(["C1","C3","C5"]),
      where "C" is the ID of Table4 ( cf. tableId("Table4 ") ) and the number that follows is the unique number of the record (Table4.ID).

    • Fred
    • 1 yr ago
    • Reported - view

    What happens is you want to keep the history of the selections? Well I've modified the DB to now have a table (withHistory) where you can see how you can now have a history of the selections. Select the withHistory table to try it out.

    • Mel_Charles
    • 1 yr ago
    • Reported - view

    Fred - Following your changes it is definitely working on the web version now 👍

    This will be very useful and practical and wait to put it to good use!

      • Fred
      • 1 yr ago
      • Reported - view

       Which changes worked?

      1) the removal of the variable?

      2) Jacques?

    • Mel_Charles
    • 1 yr ago
    • Reported - view

    your last loaded file

    • Fred
    • 1 yr ago
    • Reported - view

    Jacques TUR After some playing around I noticed that your code reorders the selection if I select 3 choices (2,6,4) and then remove the first selection (2), the child table gets reorderd to 4,6.

    I've also noticed that it recreates the selections everytime I remove a choice, which then reorders the choices.

Content aside

  • 4 Likes
  • 11 days agoLast active
  • 33Replies
  • 1155Views
  • 8 Following