JenniferJuhasz

3 years agoQrew Cadet

# 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

------------------------------