Brian Dunning's FileMaker Custom Functions

Distribution ( uniqueList ; dataList )

For each item in list 1, counts occurrences in list 2

  Average rating: 4.7 (33 votes) Log in to vote

Bruce Robertson   Bruce Robertson

Share on Facebook Share on Twitter

  Sample input:
Distribution( "A¶B¶C"; "A¶A¶A¶B¶A¶B")
  Sample output:
A (4)
B (2)
C (0)

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

For each item in list 1 (uniquelist) , counts occurrences in list 2 (datalist). So for instance list 1 could include list 1 could include Seattle and Portland, and list two could be the result of a list function that accumulates the "City" field across a record set.

 

Comments

Erik Wegweiser / Intelligent Database   Erik Wegweiser / Intelligent Database, Boston, MA
Mar 17, 2011
Hi, Bruce. Handy function I used right away. Found a small fix that corrects where the value should be bold if matches > 0 rather than > 1:

Let([
v1 = GetValue( uniqueList; 1) ;
listLength = ValueCount( uniqueList ) ;
matches = ValueCount( FilterValues( dataList; v1));
listremainder = MiddleValues( uniqueList ; 2; listLength -1) ];

Case( matches > 0; TextStyleAdd ( v1; Bold ); v1 ) & " (" & matches & ")" &
Case( listLength > 1; "¶" & Distribution ( listRemainder; dataList )
)
)
 
Bruce Robertson   Bruce Robertson
Mar 17, 2011
The original cf works correctly for my intentions; it only highlights the items with more than one match.
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.