Alternative to PatternCount for summarising unique values for a chart?

[mrmacmusic]

I've hit a snag using PatternCount to count values within a list for a summary chart and hope someone can point me in the direction of an alternative solution... 

Here's my problem: I have a summaryReferralReason Summary field which is a List of the referralReasons field, which can contain 5 different unique values selected from a drop-down list and are the subject for a simple bar chart in my summary layout. I couldn't understand why adding up the totals for each of these 5 values in my bar chart were different from the summarised total (which is simply a count of all values) and then it twigged... d'oh!!

Here are the first three lines of the calculation I've used to generate the chart data... 

PatternCount ( contacts::summaryReferralReason ; "Other" ) & ¶ &
PatternCount ( contacts::summaryReferralReason ; "Another Retailer" ) & ¶ &
PatternCount ( contacts::summaryReferralReason ; "Trade Press" )... 

...and I've realised that my problem is that PatternCount will count every instance of 'Another Retailer' in the Summary List because 'another' contains the characters 'other'  

Am I missing a straightforward fix that will count exact text strings or will this require some lateral thinking?

Everything else in the summary layout is working a treat so any help or advice with this would be much appreciated! 

[eos]

will this require some lateral thinking?

​No, just studying the functions that are available …  Use

List (
  ValueCount ( FilterValues ( contacts::summaryReferralReason ; "Other" ) ) ;
  ValueCount ( FilterValues ( contacts::summaryReferralReason ; "Another Retailer" ) ) ;
  …
)

But it would be more straightforward to use a related table ReferralReasonsForYourEntity – where each record is one referralReason – and one summary field for referralReason, and use its values for charting. Then you also will automatically summarize the referralReasons that do exist in that table (or in your found set), without having to check for each one that might exist.

[mrmacmusic]

​No, just studying the functions that are available …  ​

Thanks for that eos LOL!

Ah yes, FilterValues... I knew it was in there somewhere! Part of the problem with having to dip in and out of FMPro development rather than doing it full time is that my brain can take a bit of time to get back up to speed...  

Thanks for the heads up on the more straightforward related table option... makes sense.

[Kris M]

Just to add other options you could use Exact() instead of PatternCount () in your original calculation.

Or ExecuteSQL ( "SELECT COUNT (Field) FROM Table WHERE Field = ?" ; "" : "" ; "Other" )

Edited May 6, 2015 by Kris M

[mrmacmusic]

Just to add other options you could use Exact() instead of PatternCount () in your original calculation.

Or ExecuteSQL ( "SELECT COUNT (Field) FROM Table WHERE Field = ?" ; "" : "" ; "Other" )

​Thanks Kris.

[comment]

Just to add other options you could use Exact() instead of PatternCount () in your original calculation.

​Not really.

[Kris M]

​Not really.

​Thats what i get for not paying attention.