1 2 Previous Next 15 Replies Latest reply on Mar 30, 2016 6:20 PM by TheNovice

    How to filter portal records alphabetically

    TheNovice

      Hello all,

      I’m using FM 14. I have created a dictionary in my database and I want to be able to filter records in a portal using  a letter of the alphabet. For instance, if I click on A, only the records beginning with A will be displayed, and so on. I have found a number of discussions on this subject on this forum but I am unable to adapt any of the suggested solutions to my own situation.

       

      I have the following relationship right now:

      Dictionary::IDWord_pk = ListDict::IDWord_fk (ListDic is a TO of Dictionary).

       

      My layout is based on Dictionary and the TO on the layout is based on ListDict. See screenshots.

       

      I know I probably need to create one or more other relationships but I’m not sure which ones. I’ve tried several things but nothing works so far. Any advice would be very much appreciated.

       

      Thanks.

        • 1. Re: How to filter portal records alphabetically
          erolst

          Note that there is a substantial difference between filtering a relationship and a portal.

           

          While the former is much more performant, it requires more moving parts; conversely, the latter is more convenient, but slower.

           

          For starters, you could try a portal filter. Assuming you have a repeating global field gSelectionLetter[26] that allows you to click a repetition and set a global variable $$selectedFilterLetter, you can filter the portal with this calculation:

           

          Left ( ListDict::thePertinentField ; 1 ) = $$selectedFilterLetter

          • 2. Re: How to filter portal records alphabetically
            TheNovice

            Thanks erolst,

             

            ok, I've created the repeating global field gSelectionLetter, Not sure however what the next step is. I'm afraid I'll need a few more explanations to get this to work. For instance, where do I set the global variable $$selectedFilterLetter. And i suppose the script must be assigned to each letter button? I am almost totally new to FM and not quite familiar yet with these concepts.

             

            Thanks again for your help.

            • 3. Re: How to filter portal records alphabetically
              erolst

              Have a look at the sample file that is attached to this post.

              • 4. Re: How to filter portal records alphabetically
                TheNovice

                Thanks again erolst, it works very well, except for one thing. How do I ensure the complete list of words is always displayed in the TO except when I click on a letter to restrict the records displayed to those beginning with that letter? Is that feasible? I understand now what you meant by filtering the portal versus filtering the relationship. My intention was really to filter the rrelationship. Sorry for not being clear in the first place.

                • 5. Re: How to filter portal records alphabetically
                  beverly

                  I had to create a button to "clear" the $$selectedFilterNumber (set to 0) and changed the filter to:

                   

                  (

                  Left ( FilterByLetter__self::thePertinentField ; 1 )

                  =

                  Char ( $$selectedFilterNumber + 64 )

                  )

                  or $$selectedFilterNumber = 0

                   

                  for all values to be shown.

                   

                  beverly

                  • 6. Re: How to filter portal records alphabetically
                    erolst

                    TheNovice wrote:

                     

                    Thanks again erolst, it works very well, except for one thing. How do I ensure the complete list of words is always displayed in the TO except when I click on a letter to restrict the records displayed to those beginning with that letter? Is that feasible?

                    See the solution suggested by Beverly.

                     

                    TheNovice wrote:

                    I understand now what you meant by filtering the portal versus filtering the relationship. My intention was really to filter the rrelationship. Sorry for not being clear in the first place.

                     

                    Well, until you run into performance problems, filtering the portal is easier.

                     

                    To filter the relationship, you could:

                     

                    • create a calc field (type text!) in the target table as Left ( pertinentField ; 1 ) & "¶allRecords"

                    • create a global text field in the source (layout) table

                    • create a relationship using these two fields as match fields

                    • use a global field like in the sample solution that serves as letter selector

                    • create a script like in the sample solution that sets the global field either to a letter or (to show all records) to "allRecords" (actually, any string that is not identical to any value in pertinentField would do)

                     

                    Also, create an extra button that calls the script and passes an arbitrary script parameter; then modify the script from the sample like so:

                     

                    Set Field [

                      Target Field: SourceField::globalSelector ;

                      Calculated Result: not IsEmpty ( Get ( ScriptParameter ) ) ; "allRecords" ;  Char ( Get ( ActiveRepetitionNumber ) + 64 )

                    ]

                    • 7. Re: How to filter portal records alphabetically
                      user19752

                      In this case "all" is limited number of characters A..Z, so how about using list of A to Z in global side instead of "allRecords" ?

                      • 8. Re: How to filter portal records alphabetically
                        BMyers

                        I'd like to suggest something out of left field.  Instead of entering a letter in a box, how about a vertical button bar that would be placed alongside the portal?  I'm thinking of the design of Apple Contacts in iOS.  Click on a letter and the list filters to those starting with that letter.  Would that work for you?  If so, read on...

                         

                        The concept here is to have a 26-buttons in the vertical button bar, A through Z.  Each button will call the same script.  The script searches on whatever you specify as a parameter.  If you have used parameters before then you know how easy it is.  If not, here's how it works: 

                         

                        It's too much work to write 26 scripts for 26 buttons.  FMP has a handy feature where you can attach a script to a button and add any text as a parameter.  When the script runs it can grab that parameter text and insert it into the script.  For example, you'll put the letter A in the parameter box, then use a script to do a search, and tell FMP to insert whatever is in the parameter box as the search text.  For the next button you'll call the same script but the parameter will be the letter B, and so on.

                         

                        You enter a parameter in the dialog box used to create a button bar; at the bottom there is a place to indicate which script is called by the button.  Next to that place is a little script icon that will take you to your list of scripts.  At the bottom of that dialog box there will be a place to insert a parameter.  For each button you will designate the same script but use the letter A for the parameter, then B, and so forth.

                         

                        Create a script that opens a Find, then filters on the field you want with the letter set by the parameter.  Use the QuickFind script step, then in the place to designate the search string, use the Get ( ScriptParameter ) calculation.  That will cause FMP to grab whatever parameter you specified in the script setup above and search on that letter. 

                        • 9. Re: How to filter portal records alphabetically
                          erolst

                          user19752 wrote:

                          o how about using list of A to Z in global side instead of "allRecords" ?

                           

                          Yes, thats much better; manipulate the front-end, rather than creating data in the back-end.

                          • 10. Re: How to filter portal records alphabetically
                            erolst

                            Here's another sample file, showing how to filter/unfilter a portal and a relationship, using the (as always) valuable input from Beverly and user19752.

                            • 11. Re: How to filter portal records alphabetically
                              beverly

                              Excellent, erolst!

                               

                              I see that you changed the filter thus:

                               

                              Left ( FilterByLetter__self_Cartesian::thePertinentField ; 1 )

                              =

                              Char ( $$selectedFilterNumber + 64 )

                              or

                              IsEmpty ( $$selectedFilterNumber )

                               

                              Whereas my "or" used a number "0". And I see your point...

                               

                              However, in my travels through the land of filtered portals, I discovered I often needed 3 'states':

                              • whatever is chosen ("a", "b", etc.)

                              • "all" (portal shows every related record - basically not filtered or the raw relationship as defined)

                              • "none" (portal is empty)

                               

                              I would call "IsEmpty" as being the state-of-none. And I would use the "or 0" as the state-of-all (the word 'all' could be used or anything to match).

                               

                              Thus using the example you show just using the relationship and the "selection" match, does not consider the 3 states.

                               

                              If it makes more sense to have "empty" filter to show all, just reverse the logic to show none.

                               

                              YMMV or YKMV

                              beverly

                              • 12. Re: How to filter portal records alphabetically
                                erolst

                                beverly wrote:

                                However, in my travels through the land of filtered portals, I discovered I often needed 3 'states':

                                • whatever is chosen ("a", "b", etc.)
                                • "all" (portal shows every related record - basically not filtered or the raw relationship as defined)
                                • "none" (portal is empty)

                                 

                                So here is another version that can toggle “all” and “none” (but only for the relationship filter; no time to adapt the portal part).

                                • 13. Re: How to filter portal records alphabetically
                                  Vaughan

                                  beverly wrote:

                                   

                                  If it makes more sense to have "empty" filter to show all, just reverse the logic to show none.

                                   

                                   

                                  Showing all if the filter field is empty means that it's easy to suddenly have the whole table being pulled down from the server. It can mean that right after the portal first displays there can be a wait of a couple of seconds (to minutes if the related record count is large and/or the network is slow) while all the data comes down. Usually this data will be wasted and the wait will make the solution seem slow.

                                  • 14. Re: How to filter portal records alphabetically
                                    beverly

                                    This is the same as showing the portal with NO filter. If you have so many related records then it will be slow. And you should 're-think' portals at that point anyway! What's the problem?

                                    beverly

                                    1 2 Previous Next