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.
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.
I have taken part of your idea, In the Table Deliverables there are Docs with information, text, Date, Week etc, some Docs are issue to cliente in the same week in the Select_Week there are a Multiple Choice (Dynamics) My Questions is there are any way to consolidate the weeks on the Multiple Choice (Dynamics) ans in the view table all Deliverabkes of the Week.
If the select weeks dMC is from the deliverable table and there are two records with the same data then I do not know of a way to filter out just one but still keep the ability to have an array of record Ids.
What I mean is you can do a unique of the field week, but then you would only have an array of the data from the Week field and thus can't be used in a dMC anymore.
I still haven't figured out how to filter out all but one of a group of records that have the same data in a field, but still keep all other records in a found set.
My current system is to create new tables of the selections that I need and then find a way to filter it so I can get the results I want.
So I would have a new table called weeks and have all the weeks as a single record with no duplicates and then link them to the deliverable records so then you can view the weeks anyway you want.