2

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.

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

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

    Ask and you shall receive!

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

      • Sam
      • Sam.1
      • 4 mths ago
      • Reported - view
      Like
  • 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

    Like 1
      • Sam
      • Sam.1
      • 4 mths ago
      • Reported - view

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

      Like
      • Sam
      • Sam.1
      • 4 mths ago
      • Reported - view

      Sam experience similar to yours

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

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

    Like
  • 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
    
    Like 1
    • Fred
    • Fred
    • 4 mths 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!

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

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

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

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

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

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

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

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

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

       Which changes worked?

      1) the removal of the variable?

      2) Jacques?

      Like
  • your last loaded file

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

    Like
    • Fred I feel like you used the first code I posted. Indeed it deleted all the records systematically and therefore had to recreate all those who were selected. This is what changed the order of the recordings.

      If you use the second code I posted (https://forum.ninox.com/t/m1hwj3v?r=g9hwj0v) that fixes that, it works fine.

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

      Jacques TUR As always, you are correct. I thought I updated the DB with your changes, but I obviously didn't.

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

    What if your dynamic multiple choice field is from a third table?

    Here is your answer.

    Open TableA and you can see two dynamic multiple choice fields (dMC and dMCJ). Why are there two? One is my solution (dMC) and the other is Jacques TUR 's (dMCJ) solution. To show that there is always more than one way to solve a problem.

    The dMC fields are created from TableC and write to TableB.

    So this DB now has three examples of tracking choices in a dynamic multiple choice field.

Like2 Follow
  • 2 Likes
  • 2 mths agoLast active
  • 29Replies
  • 279Views
  • 6 Following