Brian Dunning's FileMaker Custom Functions

GetValueSet_MultiLevel ( child ; parent ; startValue ; childMarker ; c

This function returns a valueset in a subsummary format within a field.

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

Don Levan   Don Levan
Small Company
http://smallco.net

Share on Facebook Share on Twitter

  Sample input:
GetValueSet_MultiLevel ( Children::Child ; Children::Parents ; 1 ; " • " ; "¶" ; "¶¶" )
  Sample output:
Sally and Joe
• Joe Jr.
• Jane
• Sally Jean
• Jimmy
• Bill

Mary and Dan
• Bill
• Barry
• Sam

Jane and Jack
• Tommy
• Moe
• Tate
• Jake

Sue and Seth
• Sarah
• Arthur

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

This function returns a valueset in a subsummary format.

This function requires that the data be denormalized so that the break field from the parent table resides within the child table. It is also required that the child table be sorted by the parent id or the parent data field.

It adapted from the "GetValueSet" function created by Ray Cologon of NightWing Enterprises (http://www.nightwing.com.au/FileMaker/).

 

Comments

Serp   Serp, Barcelona
Sep 1, 2009
I'm working with your CF, but I don't know the reason when the parent only has one value selected, always MISS an a paragraph. Per exemple:
Parent1
value1Parent2
value1
value2

When It should be:
Parent1
value1

Parent2
value1
value2

Any ideas would be very appreciated!!!
 
Katherine Simpson   Katherine Simpson, Valencia, CA
Oct 17, 2010
Great CF - thanks, Don!

I ran into the same issue and found that the parentDelimiter is only called in the result of the first argument of the base case statement. It needs to be called in *both* the first result and the default result, as part of the embedded Case statement (and then the childDelimiter removed from the beginning of the first result). I also added another argument in the embedded Case statements to skip the delimiter entirely for the last value, so as not to end with a final delimiter. Here's my modifications:

Case (
GetNthRecord (parent; startValue ) = GetNthRecord (parent; startValue - 1);
childMarker & GetNthRecord ( child; StartValue ) &
Case (
Not IsValid ( GetNthRecord ( child; startValue + 1));
"";
GetNthRecord (parent; startValue ) ≠ GetNthRecord (parent; startValue + 1);
parentDelimiter;
childDelmiter
);

GetNthRecord (parent; startValue ) & childDelimiter & childMarker & GetNthRecord ( child; startValue ) &
Case (
Not IsValid ( GetNthRecord ( child; startValue + 1));
"";
GetNthRecord (parent; startValue ) ≠ GetNthRecord (parent; startValue + 1);
parentDelimiter;
childDelmiter
)

)

&

If(IsValid(GetNthRecord ( child; startValue + 1));
GetValueSet_MultiLevel ( child ; parent ; startValue +1; childMarker; childDelimiter; parentDelimiter))
 

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.