Brian Dunning's FileMaker Custom Functions

GetNthRecSetUnq ( fieldName ; recordNumStart ; recordNumEnd )

Calculates a return delimited and sorted, values list from related, or local, field data. Eliminating duplicate values fromthe list.

  Average rating: 4.0 (40 votes) Log in to vote

Doug Jacobs   Doug Jacobs
Restylers Choice

Share on Facebook Share on Twitter

  Sample input:
GetNthRecSetUnq(States,1,"") where there are multiple states

If field values are OH, OH, KY, IN, IN, IN....

it eliminates all duplicate values
  Sample output:
"OH¶KY¶IN....."

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

Creates a return delimited value list of the values from the field specified, beginning with the recordNumStart and ending with recordNumEnd. It only enters the first occurence of a value in the list. If an item appears multiple times it will only be in the list once.

The resultant value list's scope and sort are determined by the source field; thus for local fields the sort is determined by the current sort of records in the found set, for related fields it is by the relationship sort.

This single Custom Function can replace Copy All primary keys of a foundset practice as well as the use of ValueListItems() to create a value list of related values for other relationships.

This function is perfect for creating field value lists for selecting a range of records where the value list must change to reflect the data in the found set.

Parameters:

fieldName - Any local field, related field, or an expression that returns a field. NOTE: For repeating fields this Custom function will only see the first repetition, per record. Also, Globals will behave similarly and the global will be display once for each record.

recordNumStart - The record number from which you want to begin getting data. A null ("") or 0 value will set the start at the first record.

recordNumEnd - The record number after which you want to stop getting data. A null ("") or 0 value will evaluate until the the last record is reached. This parameter is inclusive, thus 10 will end the value list with the 10th record, including the contents of the field in that record.

Output Format:

A return delimited text value list of the the contents of fieldName dictated by the provided start and end record numbers.
Note: The result of GetNthRecordSet() will not be updated when the record referred to by GetNthRecordSet() is a record other than the one in which the calculation is currently being evaluated.

Examples:

Given the following related Field data for Clients::State:
Related Record 1 = OH
Related Record 2 = KY
Related Record 3 = IN
Related Record 4 = OH

GetNthRecordSet ( Clients::State ; 2 ; 3 ) = OH¶KY¶IN

GetNthRecordSet ( Clients::State; "" ; "" ) = still returns OH¶KY¶IN due to it elminating duplicates

GetNthRecordSet ( ParentFirstName ; 30 ; "" ) = Value list beginning with the 30th parent name till the last record in current found set, in sort order.

Special Thanks: To Jonathan Mickelson, for posting the GetNthRecordSet function which was used as the basis for this version.

 

Comments

Edward Lemos   Edward Lemos, Sao Paulo Brazil
Nov 1, 2009
There's an error on your CF's parameters' it's supposed to be "recordNumStart" and "recordNumStart" end instead of "start" and "end", respectively.
 
Ed Uncle   Ed Uncle, Ware, UK
Nov 2, 2011
This may be an error or my misunderstanding of how it is supposed to work.

If your related child record set is not sorted by the field you are working with, it just returns all values, not just one occurrence of each value.
 
Cody   Cody, Houston, TX
Apr 2, 2012
I can't get this function to load. As noted above, there is an error with recordNumStart. What is the fix for this?
 
Cody   Cody, Houston, TX
Apr 3, 2012
Got it working, but as Ed notes above, it does not seem to work apart from record entry order.
 
Greg   Greg, Minneapolis, Mn
Jul 3, 2012
Cody could you explain how you managed to modify the function to get it working?

I've re-defined the parameters as mentioned by Ed, yet I can't seem to get parameters property specified.
 

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.