Complex Portal Filtering

Document created by ThierryGuemboura on Apr 16, 2018
Version 1Show Document
  • View in full screen mode

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

01-Portals and Filters.jpg

01a-Portals and Filters.jpg

01b-Portals and Filters.jpg

01c-Portals and Filters.jpg

 

On the screenshots above, we have:

  1. Filter 1: QUICKFIND field = Global
  2. Filter 2: Status = Global
  3. Filter 3: State = Global
  4. 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 = “ActiveVacation” 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 (4th column)
  • Filter 3 filters records based on State (9th column)
  • Filter 4 filters records based on CAT Type (15th 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.

02 Portal Setup.PNG

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:

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

05 - Formula.JPG

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.

 

06 - Formula 2.JPG

 

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,

03-Pascal Triangle.jpg

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:

  1. 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.
  2. English is not my native language so please excuse any imperfections.
  3. There might be an easier way to do the job but that is the only one I could engineer on my own.
2 people found this helpful

Attachments

Outcomes