8 Replies Latest reply on Nov 10, 2014 10:50 AM by NickLightbody

    Designing a good Portal Filter

    NickLightbody

      Portal filters are great and provide an effective method of giving the user easy access to subsets of records from all those that would otherwise be displayed through the relationship powering the portal.

       

      These days Filemaker is ever more efficient in delivering records to portals so you can safely display quite larger numbers of records in portals quickly - but on mobile devices you should consider a method of preventing too many records being displayed - for example not permitting “all” records to be shown - only those found through the filter, or only those to which the user has access.

       

      When designing for mobile devices with limited screen space it is essential to make your Ui as simple as possible, which means a limited number of items on each screen and that means only one field available for use in portal filtering - or “finding” as many users will likely think of it.

       

      So how can you give your users a little more refinement through this single entry field?

       

      For example in order to find records which cover a variety of use cases such as:

       

      (a) John or Smith

      (b) John and Smith

      (c) John but excluding Smith

      (d) John and Smith but excluding UK

      (e) John excluding Smith and excluding UK

       

      And how efficient will such a method be?

       

      Well - here is one method which works efficiently and surprising quickly even on large datasets - I call it "Multifiltering" - it uses Patterncount() as the key operator which means of course that you are searching on individual words irrespective of their order in the search string.

       

      I will post some statistics on this thread in the future on the performance I am seeing using this method in various forms of deployment.

       

      Multifiltering a Portal - an effective method - here’s how to do it

       

      This method is proven and can of course be extended to increase both its scope and its efficiency - do please suggest how you believe that this can be improved - for example could it be written as a recursive custom function and how efficient would that be?

       

      Step 1: define a text field in your data table which we will call a3summary which is set to auto enter and to replace existing with a List() of the fields that you wish to index and search/filter - i.e don't check the don't change box.

       

      Step 2: define a global numeric field which we will call zb3allornothing which acts as a switch to control whether the user can see all records or no records when the portal filter field is empty - potentially this field can be manually set by the user as a preference or auto set based on the total number of records which the user would be able to display through the relationship.

       

      Step 3: define a global field in your Ui file for the entry of the filter criteria we will call za3search

       

      Step 4: write a script which is called when a search/filter is instigated to reduce the number of words in za3search to the number you are accommodating - in this case 3 - and remember to tell the user why this has been done.

       

      Step 4a: define custom function xTrim() as follows:

       

      // xTrim()

      // v1: NL: 060319

      //Trim4 (" ¶¶Your text here ¶ ")

      //Ray Cologon

      //=============

      Let([

      C1 = PatternCount ( " ¶"; Left ( text; 1 ));

      Cn = PatternCount ( " ¶"; Right ( text; 1 ));

      Ca = C1 + Cn ];

      If ( Ca;

      xTrim ( Middle ( text; 1 + C1; Length ( text ) - Ca ) );

      Text )

      )

       

      You will note that as a matter of good practice every custom function I use includes a version history crediting where/who it originally came from and - as appropriate - how I have changed it - in this case I think I did nothing other than rename it to my convention which is that all custom functions commence with "x".

       

      Step 5: define a custom function we will call xPFilter3w as follows:

       

      /*

      xPFilter3w v3

      Nick Lightbody

      December 2012 & October 2014

      To provide filtering on up to three words - in any order

      and to exclude records containing search words commencing -

      and to inc those commencing + together with the first word

      and to default to an OR search

      comprising use cases:

      (a) John or Smith - notated as john smith

      (b) John and Smith - notated as john +smith

      (c) John but excluding Smith - notated as john -smith

      (d) John and Smith but excluding UK - notated as john +smith -uk

      (e) John excluding Smith and excluding UK - notated as john -smith -uk

       

      input:

      Fn2search [fieldname to search]

      Fnsearch [search/filter fieldname]

      allornothing [switch to control whether the portal will display all when the the search/filter field is empty)

      --------------------------------------------*/

       

      Let([

      k=Fn2search;

      // string of up to 3 words - converted to values

      x=Substitute( FnSearch ; " "; ¶ );

      // symbols used for exc and inc - so you define this to use whatever characters you wish

      exc="-";

      inc="+";

       

      // word 1

      w1=xTrim(LeftValues(x;1));

      L1=Length(w1)-1;

      x1=Case(Left(w1;1)=exc ;Right(w1;L1);"");

      y1=Case(Left(w1;1)=inc ;Right(w1;L1);"");

      px1=Case(PatternCount(k;x1)>0;1;0);

      pw1=Case(PatternCount(k;w1)>0;1;0);

      py1=Case(PatternCount(k;y1)>0;1;0);

      //summary output - exc o

      p1=Case(px1;0 ;pw1 or py1;1;0);

       

      //word 2

      w2=xTrim(MiddleValues(x;2;1));

      L2=Length(w2)-1;

      x2=Case(Left(w2;1)=exc ;Right(w2;L2);"");

      y2=Case(Left(w2;1)=inc ;Right(w2;L2);"");

      px2=Case(PatternCount(k;x2)>0;1;0);

      pw2=Case(PatternCount(k;w2)>0;1;0);

      py2=Case(PatternCount(k;y2)>0;1;0);

      //summary output - exc o

      p2=Case(px2;0 ;pw2 or py2;1;0);

       

      //word 3

      w3=xTrim(MiddleValues(x;3;1));

      L3=Length(w3)-1;

      x3=Case(Left(w3;1)=exc ;Right(w3;L3);"");

      y3=Case(Left(w3;1)=inc ;Right(w3;L3);"");

      px3=Case(PatternCount(k;x3)>0;1;0);

      pw3=Case(PatternCount(k;w3)>0;1;0);

      py3=Case(PatternCount(k;y3)>0;1;0);

      //summary output - exc o

      p3=Case(px3;0 ;pw3 or py3;1;0);

       

      // controllers

      $n=ValueCount(x);

      $nx=PatternCount(x;exc);

      $ny=PatternCount(x;inc);

       

      // No of includes in string

      $ny12=Case($ny=1 or $ny=2;1;0);

      $ny123=Case($ny=1 or $ny=2 or $ny=3;1;0);

       

      // No of excludes in string

      $nx12=Case($nx=1 or $nx=2;1;0);

      $nx123=Case($nx=1 or $nx=2 or $nx=3;1;0);

       

      // No of matching excludes

      $px12=Case(px1 or px2;1;0);

      $px123=Case(px1 or px2 or px3;1;0)

      ];

       

      Case ($n=0 ; allornothing

      ;

      $n=1 ; Case ($nx=1 and px1; 0

      ;$nx=$n; 1

      ;$nx=0 and p1;1

      ;0

      )

      ;

      $n=2 ; Case ($nx12 and $px12; 0

      ;$nx=$n; 1

      ;$nx=1; p1

      ;$ny12 and p1 and py2;1

      ;$ny12;0

      ;pw1 or pw2;1

      ;0

      )

      ;

      $n=3 ; Case($nx123 and $px123; 0

      ;$nx=$n;1

      ;$nx=2; p1

      ;$nx=1 and $ny12 and p1 and py2;1

      ;$nx=1 and $ny12;0

      ;$ny123 and p1 and py2 and py3;1

      ;$ny123;0

      ;pw1 or pw2 or pw3;1

      ;0

      )

      )

      )

      //[END of function]

       

      Step 6: add something like the following into your portal:

       

      xPFilter3w (

      data_al::a3summary ;

      Ui_al::za3search;

      zb3allornothing

      )

       

      I hope that you find this a useful technique and will choose to share your improvements on it with the rest of us,

       

      Cheers, Nick

        • 1. Re: Designing a good Portal Filter
          user19752

          The cf uses another cf xTrim(), what is the difference from Trim() ?

           

          I wonder here is a member 'xTrim'.

          • 2. Re: Designing a good Portal Filter
            NickLightbody

            Hi - user19752

             

            Thanks for pointing that out - here is cf xTrim - which you will see I have been using for a very long time without amendment - it is based Ray Cologon's cf Trim4 - I can't remember how or if I changed it - but I use it frequently to avoid values being expressed with a final <cr>

             

            Cheers, Nick

             

            // v1: NL: 060319

            //Trim4 (" ¶¶Your text here ¶ ")

            //Ray Cologon

            //=============

            Let([

            C1 = PatternCount ( "            ¶"; Left ( text; 1 ));

            Cn = PatternCount ( "            ¶"; Right ( text; 1 ));

            Ca = C1 + Cn ];

            If ( Ca;

            xTrim ( Middle ( text; 1 + C1; Length ( text ) - Ca ) );

            Text )

            )

            • 3. Re: Designing a good Portal Filter
              user19752

              Thanks.

              It seems using the custom function is historical reason, since you can use GetValue() instead of ...Values(), then triming space and CR is not need. (need to remove only tab?)

               

              I felt that total function is a bit long, so tried to rewrite it.

               

              1) change to recursive function for 4 or more words

              2) then 2nd parameter is change to list of words for avoiding Substitute() in every recursion

              3) 3rd parameter is used as another purpose in recursion, so call it with 0(nothing) or 1(all)

              4) the result is not same as original function, since every words are found acording to the order (like FM finding criteria).

              difference is appeared in such a case

              john -smith uk

              this find "john exclude smith" or "(any)uk"

               

              use as

              xPFilterWords(Fn2search; Substitute(FnSearch;" ";¶); allornothing)

               

              Let([

              exc="-";

              inc="+";

               

              k=Fn2search;

              x=FnSearch;

              an=allornothing;

              w=GetValue(x;1);

              rest=RightValues(x; ValueCount(x)-1);

               

              op=Left(w;1);

              w=Case(op=exc or op=inc; Replace(w; 1; 1; ""); w);

              r=PatternCount(k; w)>0;

              r=Case(op=exc; not r; r);

              r=Case(w=""; an; an>=0; r; op=exc or op=inc; (an+2) and r; (an+2) or r)

              ];

              Case (

                  an>=0 and w="" ; an ;

                  rest="" ; r ;

                  xPFilterWords(k; rest; r-2)

              )

              )

              • 4. Re: Designing a good Portal Filter
                NickLightbody

                Hi user19752

                 

                On xTrim() - I do use it through habit - I know it works - no doubt I should think about how whether it can be replaced :-)

                 

                On your recursive version of xPFliterWords - a great first try - but - currently it gives a different result. Do you think you can see how to get the same result - this is I think important. Not that my desired output is necessarily right but I have focused on what a user would find useable - so the idea of refining a search with + and - , in that order,  - is I think viable subject to however user experience testing shows it can be improved?

                 

                I will have a play with your version and let you know where I get to.

                 

                Meanwhile I shall look forward to hearing whether you feel you can refine your suggested approach to provide a slightly refined output?

                 

                Cheers, Nick

                • 5. Re: Designing a good Portal Filter
                  user19752

                  I don't have live implementation of these things, and wasn't sure of what is the need. So mainly try to make a shorter definition of function as first.

                  I compared time in a little test, my function seems faster only about 20% than yours.

                  I didn't have any idea for more speed, so i didn't want to replace your function with mine.

                   

                  I mensioned different result, but this is not in your use case. Do user really need

                  a -b c

                  as

                  a or c, exclude b

                  ? I think user will input that as

                  a c -b

                  • 6. Re: Designing a good Portal Filter
                    NickLightbody

                    Hi

                     

                    The use cases I designed to are:

                     

                    A = all inc A

                    -A = all exc A

                    A +B = rec inc A and B

                    A -B = rec inc A but exc B

                    A +B -C = rec inc A and B but exc C

                    A -B -C = rec Inc A but exc B and exc C

                    A +B +C = rec inc A and B and C

                    -A -B -C = All exc A exc B exc C

                    A B = rec inc A or B

                    A B C = rec inc A or B or C

                     

                    So you are correct a -b c is not required.

                     

                    So the syntax is that the exc(s) must come last if any

                    The expression works the other way so it excludes first, then deals with the ANDs and finally deals with OR.

                     

                    I am using it at present for interrogating the performance data we are collecting and in our development system - it does work quicker than I would have expected - based on appearance / length - it is proving very useful and I think that users will like it.

                     

                    The underlying reason is that I never permit the user to reach a list - everything is through relationships in portals or extracted from a list using the rec IDs from a portal.

                     

                    I use the same structure of filtered portal using this custom function throughout the system - it is the only way a user can find anything hence it has to work well - so far I am quite pleased but I have no doubt it can be improved on.

                     

                    I have actually written it for up to 4 terms but I only published the 3 term version - it gets much more difficult to figure out the logic as it deals for more terms - or it was more difficult for me anyway.

                     

                    Thank you for thinking about this

                     

                    Best regards, Nick

                    • 7. Re: Designing a good Portal Filter
                      user19752

                      Your 10 cases can be calculated left to right, so perhaps my function has same result.

                      (Also adding a missing case -A -B)

                       

                      If there are combination of 'or' and 'and', (like A B +C) things become  harder. For clearness, user want to use parentheses etc...

                      • 8. Re: Designing a good Portal Filter
                        NickLightbody

                        Hi

                         

                        Thanks again and yes - I will do some tests and compare the results of substituting your function and let you know how it looks - it would be far simpler to use your recursive function if it provides the right results.

                         

                        As you may have noticed elsewhere on this thread I have a  significant committment to simplifying the user experience - hence I have restricted the options to adding + and - as the simplest I could think of for normal users which also gave the Include - Exclude - Or functionality - most people would I think be be intimidated by parenteses - or brackets as we call then in UK?

                         

                        Cheers, Nick