Dynamic Choice Field Assistance

I am having some trouble with a dynamic field:


let ths := this;

let thisUserDepartment := ths.Department.Id;

let thisUserTitle := ths.'Job Title'.Id;

(select 'WFM Permissions' where numbers('Eligible Departments')[thisUserDepartment] and numbers('Eligible Titles')[thisUserTitle]) order by Name


The problem: its finding all the data that contains user department, and all of the data that contains the user's title. I want it to only find data that has the same department and title (hence the AND statement) allike.

Please help! I'm sure it's something simple. I've been staring at this code for too long!

5 replies

    • Fred
    • 1 yr ago
    • Reported - view

    Are Eligible Departments and Eligible Titles multi choice fields (thus the use of numbers)?

    Can thisuserdepartment and thisusertitle have more than 1 record?

    • SMoore
    • 1 yr ago
    • Reported - view

    Hi   Fred ,

    'Eligible Departments' and 'Eligible Titles' are dynamic multi-choice fields, as well.


    thisUserDepartment and thisUserTitle are single records linked directly to Department and Title.

    • Fred
    • 1 yr ago
    • Reported - view

    Once you start working with multiple choice fields (regular or dynamic) then you can't use the equal sign so easily.

    Once a record in WFM Permissions has more than one option selected in the multi choice field, the field now has multiple values. Which then makes a simple equal sign meaningless when using only one value to compare it too.

    The great thing is that Ninox keeps the values selected in an array, so using the new array comparing abilities that Jacques TUR discovered and shared in this post, we can now easily find subsets of arrays.

    Here is one possible solution:

    let t := this;
    let t1 := first(t.Department);
    let t2 := first(t.'Job Titles');
    (select 'WFM Permissions')[(var xED := numbers('Eligible Departments');
                count(xED[= t1]) > 0) and (var xET := numbers('Eligible Titles');
                count(xET[= t2]) > 0)];

    Lines 2 and 3, you need to add the first command, even though there my only ever be 1 record associated with the record, Ninox creates an array and with how this formula is created Ninox can't compare an array to an array with the simple equal sign. If you need this ability then there is another formula for that.

    Lines 4 -6 is where the magic happens. First we create a variable and put the selection of Eligible Departments into it. Then we count the number of times each selection of Eligible Departments equals the value of the current record Department. Then we do the same for Eligible Titles and only keep records where both counts is greater than 0.

    • SMoore
    • 1 yr ago
    • Reported - view

    Fred ,

    This is absolutely fantastic, and so is Jacques TUR 's post. 

    Dynamic choice and multiple choice are a wild animal, and I look forward to learning much more about it!

    Thanks, I greatly appreciate your help!

      • Fred
      • 1 yr ago
      • Reported - view

      Glad to hear it works for you. Please remember to mark this post answered.

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 5Replies
  • 94Views
  • 2 Following