RangePairsFilter ( ListOfPairs ; ValueToFilter )

To determine if a value (e.g. a date) falls into a list of intervals

Average rating: 4.3 (34 votes) Log in to vote

Maarten Witberg - Show more from this author
http://www.maatwerkdbs.nl

Sample input:

RangePairsFilter (3-5-2005¶4-5-2005¶6-5-2005¶8-5-2005¶27-6-2006¶28-6-2006 ; 7-5-2005 )

Sample output:

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

/* 
maarten witberg©2007

RangePairsFilter will recursively determine if ValueToFIlter falls into a list of intervals; the result is boolean (1 for true null for false). 

in its current incarnation it treats range pairs as pairs of dates.

The required format for ListOfPairs is
Pair1min¶
Pair1max¶
Pair2min¶
Pair2max¶
...
Painnmin¶
Pairnmax

This format can be obtained e.g. by using the List() function for both lineitems and the min and max pairs if they are individual fields:

ListOfPairs = List(LineItems::ListPair)
ListPair = List (MinField ; MaxField)
*/


Case 
(
Mod(ValueCount(ListOfPairs);2);  // error trap for list with odd number of items
"malformed list"
;

Let
(
[
count = ValueCount(ListOfPairs);
Min = GetAsDate(LeftValues ( ListOfPairs ;1));
Max = GetAsDate(MiddleValues (ListofPairs ;2;1));
include =  ValueToFilter ≥ MIn and ValueToFilter ≤ Max
];
 Case
   ( 
     count>0;
       Case
         (
           include ; 
           include ;
           RangePairsFilter ( RightValues ( ListOfPairs ; Count-2) ; ValueToFilter )
          ) //end case
   ) //end case
)//end let


) //end main case

RangePairsFilter will recursively determine if ValueToFilter falls into a list of intervals; the result is boolean (1 for true null for false).

in its current incarnation it treats range pairs as pairs of dates.

The required format for ListOfPairs is
Pair1min¶
Pair1max¶
Pair2min¶
Pair2max¶
...
Painnmin¶
Pairnmax

This format can be obtained e.g. by using the List() function for both lineitems and the min and max pairs if they are individual fields:

ListOfPairs = List(LineItems::ListPair)
ListPair = List (MinField ; MaxField)

Comments

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

RangePairsFilter ( ListOfPairs ; ValueToFilter )

Comments

Support this website.

Support this website.