I recently developed an interface with multiple portals, each having multiple filters.

On the screenshots above, we have:

**Filter 1**: QUICKFIND field = Global**Filter 2**: Status = Global**Filter 3**: State = Global**Filter 4**: Type = Global

Each filter is setup either with a value list, or buttons when multiple keys separated by carriage return must be entered in the global (see above Active + Vacation = “Active¶Vacation” inserted by script, and finally an ALL button inserting the value “ALL” in the global. Some might say I could have used the value list to insert ALL but I have dynamic value list based on the filtered result so I found the ALL button easier to deal with.

- Filter 1 is used to dynamically filter the portal to quickly find a Last Name
- Filter 2 filters records based on Status (4
^{th}column) - Filter 3 filters records based on State (9
^{th}column) - Filter 4 filters records based on CAT Type (15
^{th}column)

The problem resides in the fact that the portal records must be filtered on any combination possible using the four filters. To do so, the filter section of portal setup is used to enter the conditions (or equations) for which each record will be displayed.

Problem: How many possible combinations (in Mathematics, selection of items from a collection), I use the word combination as it is important to recognize that the order of each filter when applied to a given record has no importance whatsoever otherwise it would be called a permutation.

Knowing that another portal with seven filters was coming, I spent some time to make sure, I would be able to capture/design all possible combinations.

Conventions used for the sake of this document:

1 = Filter 1

2 = Filter 2

3 = Filter 3

4 = Filter 4

X means targeted field in given record matches filter (or vice-versa)

ALL = Any value from record is good (in other words the filter is temporarily deactivated)

Solely with brain power, all the combinations with 4 filters can easily be found:

Full Filtering = None of the fields is set to “ALL” (1 COMBINATION) meaning:

- 1X - 2X - 3X - 4X ==> Each four fields of a given record must match their respective filter value

ONE ALL (4 COMBINATIONS) meaning combinations where only one filter = “ALL”

1A - 2X - 3X - 4X

1X - 2A - 3X - 4X

1X - 2X - 3A - 4X

1X - 2X - 3X - 4A

TWO ALL (6 COMBINATIONS)

1A - 2A - 3X - 4X

1A - 2X - 3A - 4X

1A - 2X - 3X - 4A

1X - 2A - 3A - 4X

1X - 2A - 3X - 4A

1X - 2X - 3A - 4A

THREE ALL (4 COMBINATIONS)

1A - 2A - 3A - 4X

1A - 2A - 3X - 4A

1A - 2X - 3A - 4A

1X - 2A - 3A - 4A

FOUR ALL (1 COMBINATION)

1A - 2A - 3A - 4A

In FileMaker language, the translation is:

Case (

**/* Full Filtering */**

**/* 1X */ **PatternCount ( CHILD_TABLE::Name_Last ; PARENT_TABLE::gQUICKFIND ) > 0 and

**/* 2X */ **PARENT_TABLE::gSTATUS = CHILD_TABLE::Status and

**/* 3X */ **PARENT_TABLE::gSTATE = CHILD_TABLE::State and

**/* 4X */ **PARENT_TABLE::gTYPE = CHILD_TABLE::Type; 1; /* This **1** means true = display record */

**/* ONE ALL (4 COMBINATIONS) */**

**/* 1A */ **IsEmpty ( PARENT_TABLE::gQUICKFIND ) and /* when the QuickFind field is empty, it is not relevant */

**/* 2X */ **PARENT_TABLE::gSTATUS = CHILD_TABLE::Status and

**/* 3X */ **PARENT_TABLE::gSTATE = CHILD_TABLE::State and

**/* 4X */ **PARENT_TABLE::gTYPE = CHILD_TABLE::Type; 1; /* This **1** means true = display record */

… some equations not displayed as they have the form described above.

**/* TWO ALL (6 COMBINATIONS) */**

**/* 1A */ **IsEmpty ( PARENT_TABLE::gQUICKFIND ) and /* when the QuickFind field is empty, it is not relevant */

**/* 2A */ **PARENT_TABLE::gSTATUS = "ALL" and

**/* 3X */ **PARENT_TABLE::gSTATE = CHILD_TABLE::State and

**/* 4X */ **PARENT_TABLE::gTYPE = CHILD_TABLE::Type; 1;

… some equations not displayed as they have the form described above.

/* THREE ALL (4 COMBINATIONS) */

**/* 1A */ **IsEmpty ( PARENT_TABLE::gQUICKFIND ) and /* when the QuickFind field is empty, it is not relevant */

**/* 2A */ **PARENT_TABLE::gSTATUS = "ALL" and

**/* 3A */ **PARENT_TABLE::gSTATE = "ALL" and

**/* 4X */ **PARENT_TABLE::gTYPE = CHILD_TABLE::Type; 1;

… some equations not displayed as they have the form described above.

**/* FOUR ALL (1 COMBINATION) */**

**/* 1A */ **IsEmpty ( PARENT_TABLE::gQUICKFIND ) and /* when the QuickFind field is empty, it is not relevant */

**/* 2A */ **PARENT_TABLE::gSTATUS = "ALL" and

**/* 3A */ **PARENT_TABLE::gSTATE = "ALL" and

**/* 4A */ **PARENT_TABLE::gTYPE = "ALL"; 1;

0 ) /* When none of the 16 combination is true then there is no match and the record is not displayed.

Some might wonder why I tried to express the FMP equations with mathematical variables. The reason is that it is very easy to lose your mind when you write so many equations. So, my trick: I use Notepad++, write my variables expressions (1A – 2X etc.…) and use the replace function to replace all dashes by **and**, all 1A by the equivalent filter 1 field = “ALL” and so on and so forth.

Now, a little bit of Math to be able to find for sure how many equations or combinations for any number of filters.

The formula is

where n is the number of objects (4 in our case) from which you can choose, and k is the number to be chosen.

Some of you might recognize factorial. By definition,

N! = n.(n-1).(n-2) …1

And 0! = 1 In the case of FileMaker, no choice or no filtering is a true combination.

So, if you add all results together and you get: 1+4+6+4+1 = 16 which is exactly what the brain power above found.

Now, is there a much easier way to find the results for as many filters you could dream of? Yes, thanks to a French Mathematician named Pascal who developed the Pascal Triangle. In this triangle, each row represent n and entries in each row are k. In other words, we need to add all the entries in n=4 rows, thus,

You will realize right away that the easy solution is in base 2 ==> 2 ^{n} where is n is the number of filters.

I hope it is helpful for some of you.

Feel free to comment or ask questions. The subject is vast especially due to the fact that I incorporated the QuickFind feature which filters one letter at a time, not a new thing, but some scripting involved to refresh and make the user experience seamless. Also, the case where the Status field might have 2 value separated by a carriage return calls for an equation with an OR where you say the record must be equal to Status = Value One or record must be equal to Status = Value Two for instance:

IsEmpty ( ADJUSTER::gQUICKFIND ) and **ADJUSTER::gADJUSTER_STATUS = "Active¶Vacation"** and **ADJUSTER_ALL_CAT::Adjuster_Status = "Active"** and ADJUSTER::gADJUSTER_STATE = ADJUSTER_ALL_CAT::Adjuster_Primary_State **or** IsEmpty ( ADJUSTER::gQUICKFIND ) and **ADJUSTER::gADJUSTER_STATUS = "Active¶Vacation"** and** ADJUSTER_ALL_CAT::Adjuster_Status = "Vacation"** and ADJUSTER::gADJUSTER_STATE = ADJUSTER_ALL_CAT::Adjuster_Primary_State; 1;

Best regards,

**Thierry Guemboura**

PS:

- I did not have time to prepare a sample file but I will in the near future as I cannot provide the real file because of data.
- English is not my native language so please excuse any imperfections.
- There might be an easier way to do the job but that is the only one I could engineer on my own.

If I get it correctly, there is no need to make combinations, use OR for each AND like as

( IsEmpty ( PARENT_TABLE::gQUICKFIND ) or PatternCount ( CHILD_TABLE::Name_Last ; PARENT_TABLE::gQUICKFIND ) > 0 ) and

(PARENT_TABLE::gSTATUS = "ALL" or PARENT_TABLE::gSTATUS = CHILD_TABLE::Status ) and

( PARENT_TABLE::gSTATE = "ALL" or PARENT_TABLE::gSTATE= CHILD_TABLE::State ) and

(PARENT_TABLE::gTYPE = "ALL" or PARENT_TABLE::gTYPE = CHILD_TABLE::Type )

/* This returns boolean value for display record */