0

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

null
    • Dave_Irving
    • 11 mths ago
    • Reported - view

    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.

    • Fred
    • 11 mths ago
    • Reported - view

    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.

      • David_Irving
      • 11 mths ago
      • Reported - view

      Fred it seems you got me a lot closer, but the values are just a tad off.  When I just filter the columns for the date and each type of contact value, I get the following.  Phone Calls -20, Att Phone Calls - 130, Texts 130, and Emails - 95.  Using the above formula, I get the following values: 22, 139, 139, and 107.  Not sure if I am getting some double strings from the formula.  I did have to tweak your formula a little..

      let xStart := 'Start Date';
let xEnd := 'End Date';
count((select Chronos)['Date + Time' >= xStart and 'Date + Time' <= xEnd and
        contains(numbers('Type of Contact'), 1)])

      or 

      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))
      • Fred
      • 11 mths ago
      • Reported - view

      David Irving If you want to investigate then you can remove the count and put concat in front and it will show you the record Ids and you can then find out what is going on.

      The big reason to use where in a select is that only those records that match the where will be sent over to your computer. If you use the [ ] then all records of the table are sent over then filtered. Depending on the size of your table, it could cause slow downs. Of course this only applies to cloud DB.

    • Dave_Irving
    • 11 mths ago
    • Reported - view

    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?

      • Fred
      • 11 mths ago
      • Reported - view

      Dave Irving I don't think Ninox can do magic and make up records. :)

      Are you sure you are showing all records in your table?

      Can you post a sample of your DB? It would be easier to troubleshoot.

    • Dave_Irving
    • 11 mths ago
    • Reported - view

    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!

      • Fred
      • 11 mths ago
      • Reported - view

      Dave Irving I've done that myself. Many times. How do you think I knew to ask you about it. 😂

       

      Sleep well.

Login to reply

Content aside

  • 11 mths agoLast active
  • 8Replies
  • 84Views
  • 3 Following