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
-
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
-
Ask and you shall receive!
Open Table2 and you will see the form to try it out.
-
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
-
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!
-
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 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.
-
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.
-
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!
-
your last loaded file
-
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
- 2 mths agoLast active
- 33Replies
- 1230Views
-
8
Following