Formula Query - How to 'Count' return values
Hi,
Two questions, which are probably a very new-user questions, but I'm stumped. I'm trying to build a summary table that uses formula queries to refer to data in other tables.
Question 1:
I want to count the number replies in a given field - the value returned is irrelevant, I just want to count the number of non-null values in this field:
var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
Count(GetRecords($QUERY,"bru6bq226"),72)
There should be around be a range month-to-month from 50 to 150.. with any text in the field, but the system just gives me '2' for each month. Why?
Which leads to my second question:
I thought perhaps maybe it was counting the number options available in that field, in this case, the response 2 would be the correct; so I tried to use it in another field where I need to actually count the number of communities represented at our events (between 20 and 40 each month), and it also only returned the value '2' - so that's clearly not what it is counting.
How do I count the # of unique responses in a field?
Thank you so much for any guidance you all can offer! I'm spinning my wheels and not quite understanding how to do this.
Take care,
Jen
------------------------------
Jennifer Juhasz
------------------------------
Two questions, which are probably a very new-user questions, but I'm stumped. I'm trying to build a summary table that uses formula queries to refer to data in other tables.
Question 1:
I want to count the number replies in a given field - the value returned is irrelevant, I just want to count the number of non-null values in this field:
var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
Count(GetRecords($QUERY,"bru6bq226"),72)
There should be around be a range month-to-month from 50 to 150.. with any text in the field, but the system just gives me '2' for each month. Why?
Which leads to my second question:
I thought perhaps maybe it was counting the number options available in that field, in this case, the response 2 would be the correct; so I tried to use it in another field where I need to actually count the number of communities represented at our events (between 20 and 40 each month), and it also only returned the value '2' - so that's clearly not what it is counting.
How do I count the # of unique responses in a field?
Thank you so much for any guidance you all can offer! I'm spinning my wheels and not quite understanding how to do this.
Take care,
Jen
------------------------------
Jennifer Juhasz
------------------------------
