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