Query for unique count?
As you can tell from my recent posts, I'm going down the formula query rabbit hole. I would like a suggestion for getting a unique count for customers who subscribe to a specific service. I have the...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendHere is something to build on
This formula as a multi select text will return the all the Record IDs for the record where fid 73 in the while tale matches the value in a field called NPI. In this case that was the identifier of the Customer.
GetFieldValues(
GetRecords("{73.EX." & [NPI] & "}"),3)
From the inside out that formula says query for fid 73 (which is the field for NPI) and go off and get the records from the table I'm sitting on where the [NPI] for the record I'm sitting on matches with the same value in any other record. Then bring back the Field values from that Query for Field ID number 3. Of course field ID number three is the Record ID.
So this will return as string like 123 ; 234 ; 5678
So those three Record ID's are for the same Customer.
But then how to find the one with the Minimum Record ID# of all the duplicates?
Most conveniently in my use case, the records are returned in Record ID sequence!
This formula will check if the record I am on is the first one on the list.
ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]
So if you limit your Size to the ones that are the first of the duplicates your would get a count of the unique Customers.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
This formula as a multi select text will return the all the Record IDs for the record where fid 73 in the while tale matches the value in a field called NPI. In this case that was the identifier of the Customer.
GetFieldValues(
GetRecords("{73.EX." & [NPI] & "}"),3)
From the inside out that formula says query for fid 73 (which is the field for NPI) and go off and get the records from the table I'm sitting on where the [NPI] for the record I'm sitting on matches with the same value in any other record. Then bring back the Field values from that Query for Field ID number 3. Of course field ID number three is the Record ID.
So this will return as string like 123 ; 234 ; 5678
So those three Record ID's are for the same Customer.
But then how to find the one with the Minimum Record ID# of all the duplicates?
Most conveniently in my use case, the records are returned in Record ID sequence!
This formula will check if the record I am on is the first one on the list.
ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]
So if you limit your Size to the ones that are the first of the duplicates your would get a count of the unique Customers.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
- Hi Mark,
I'm trying this code in my own instance here, but I'm struggling to get the query correct. Not sure what the syntax error is.
My Formula Query is:
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#]
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)
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 get that to pull the information from the outside table?
Thank you in advance for your help and time!
Jen
------------------------------
Jennifer Juhasz
------------------------------
