Query to Count the # of Unique Instances
Hi -
I posted this 13 days ago, but onto an older thread that seemed relevant - but never received a reply, so I'm thinking it just got lost in the shuffle...
I'm trying this code below to count the Number of unique communities that attend an event, but I'm struggling to get the query correct. So - as an example, say we hold an event, and have 50 attendees, representing 10 different communities - how can I get that unique count of 10? My code keeps returning a syntax error and I'm completely puzzled as to why.
My Formula Query is:
But, the field value [What community do you live in?] is generating a syntax error.
Oddly, it goes away when I have a repeat line below like this:
But then the syntax error moves to the '.' following EX.
I think I just have it mixed up where I'm putting in the "'s ...but not quite sure.
Can you offer any guidance how to do this?
Thank you in advance for your help and time!
Jen
------------------------------
Jennifer Juhasz
------------------------------
I posted this 13 days ago, but onto an older thread that seemed relevant - but never received a reply, so I'm thinking it just got lost in the shuffle...
I'm trying this code below to count the Number of unique communities that attend an event, but I'm struggling to get the query correct. So - as an example, say we hold an event, and have 50 attendees, representing 10 different communities - how can I get that unique count of 10? My code keeps returning a syntax error and I'm completely puzzled as to why.
My Formula Query is:
var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
GetFieldValues(GetRecords($QUERY,"dbID") & ("14.EX." & [What community do you live in?] & "),12) &
ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]
But, the field value [What community do you live in?] is generating a syntax error.
Oddly, it goes away when I have a repeat line below like this:
var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
GetFieldValues(GetRecords($QUERY,"bru6bq226") & ("14.EX." & [What community do you live in?] & "),12) &
ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]
GetRecords("{14.EX." & [What community do you live in?] & "}"),12)
GetRecords("{14.EX." & [What community do you live in?] & "}"),12)
But then the syntax error moves to the '.' following EX.
I think I just have it mixed up where I'm putting in the "'s ...but not quite sure.
Can you offer any guidance how to do this?
Thank you in advance for your help and time!
Jen
------------------------------
Jennifer Juhasz
------------------------------
