Query for unique count?

Question

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 total count of sites calculated in this post:
Formula Query - Size ?
I know I will need the Size function and can get the total sites.  I'm just not sure how to only return the unique customers.------------------------------Paul Peterson------------------------------

MarkShnierYou · Answer

Here is something to build onThis 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 ; 5678So 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------------------------------

Sign up

Login to the community