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

      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 mths ago
      • Reported - view

      Fred Hi again

      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. 

      Like
    • Fred
    • Fred
    • 2 mths ago
    • Reported - view
    Rafael said:
    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.

    Like 1
    • Fred In had to solve a similar problem some time ago. Here is a small database I made to test two possible solutions.

      • Rafael
      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 mths ago
      • Reported - view

      Alain Fontaine Fred

      Hi guys Here is the same database but modify data with Text, Date, Week, ManHours, the MultipleDynamicChoice saw the Week with the Date, and really work. 

      Thanks Alain 

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