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 - Show more from this author
Restylers Choice

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)

Let (
[
// ------ BEGIN VARIABLE DECLARATIONS BELOW -----
startParam = recordNumStart ; 
start = Case ( startParam < 1 ; 1 ; startparam ) ; 
end = recordNumEnd ; 
thisVal = Case ( // this limits "?" result on empty related records
                         IsValid ( GetNthRecord ( fieldName ; start ) )  ; 
                          // this is the result
                         GetNthRecord ( fieldName ; start )
                        ) ; // END CASE
nextStart = start + 1
] ;

// ----------- FUNCTION RESULTS BELOW -----------

If( // this limits duplicate entries
              PatternCount ( THISVAL ; GetNthRecord ( fieldName ; NextStart ) ) < 1 ; 

// this returns result 1 for non unique values
thisVal &  Case ( 
                    // TEST 1: the next value exists to use
                         IsValid ( GetNthRecord ( fieldName ; nextStart ) ) and 

                    // TEST 2: Either no end Value given or next value is within the end value
                         ( ( end = "" or end = 0 ) or nextStart <= end ); 

                   // RESULT1: Return Character & Recurse again
                        "¶" & GetNthRecSetUnq ( fieldName ; nextStart ; end ) 
        ) ;// END CASE
GetNthRecSetUnq ( fieldName ; nextStart ; end )
       )//END IF
) // END LET

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, 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, 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, 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, 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 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.

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

GetNthRecSetUnq ( fieldName ; recordNumStart ; recordNumEnd )

Comments

Support this website.

Support this website.