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:
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:
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.
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.
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.
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
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!
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.
Jacques TUR said:
let dMCArray := for i in numbers(dMC) do
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.
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.
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.