Proper Syntax for counting any combination in a multiple choice field that includes at least one selection.
I have been trying all sorts of syntax along with using +, and, or to no avail. Here is my formula.
let xStart := 'Start Date';
let xEnd := 'End Date';
count((select Chronos)['Date + Time' >= xStart and 'Date + Time' <= xEnd and ('Type of Contact', &1)=1])
I need to count all the times Phone Call (id# 1) is selected. There are 4 selections in the multiple choice field 'Type of Contact' and Phone Call can be included in any combination of those selections. The beginning of the formula including Date Range is just fine. It's the ending I need help with.
Any help is greatly appreciated as I'm very stumped.
'Type of Contact' = Phone Call (id#1)
'Type of Contact' = Attempted Phone Call (id#2)
'Type of Contact' = Text (id#3)
'Type of Contact' = Email (id#4)
8 replies
-
Some more clarification. The result value should equal 20. I want it to count each record where Phone Call was selected. The problem is that Phone Call can be selected with other items in Type of Contact also, as it's a multiple choice.
-
Since you have a multiple choice field, Ninox will return an array of numbers. One way to look for a particular selection amongst an array is to use the contains() function. It would look something like:
let xStart := 'Start Date'; let xEnd := 'End Date'; count((select Chronos) where 'Date + Time' >= xStart and 'Date + Time' <= xEnd and contains(numbers('Type of Contact'), 1)
As you can see I'm using the numbers() function as well. This tells Ninox to return an array of the choice numbers. Which then allows me to use 1 to compare.
-
Okay, I identified the problem but don't know why it is occurring or how to fix it. It is bringing up some records that don't even exist ??? For example, the first field identifying phone calls in my Chronos subtable, brings up 2 extra records that when I do a search for, don't return anything: ID 615 and 972.
My only thought is that when I merged my stats table over to be a subtable underneath my parent table, so I had the correct relationship...that something screwy happened with ID #'s that may have existed prior?
So, the question now, is how do I fix this? Is there a way it checks the current cache of the table?
-
Oh my, wait. I am such an idiot !!! My date filter for my column is time sensitive !!! I think it is time for bed! The formula works perfectly!
Content aside
- 1 yr agoLast active
- 8Replies
- 112Views
-
3
Following