2 Replies Latest reply on Dec 15, 2011 2:25 PM by JanHackemann

    Portal filtering with partial match



      Portal filtering with partial match



      I have table A which contains projects and volunteers working on these projects. All volunteers are listed in one field.

      And I have table B which contains all the personal details of all my volunteers.

      I am trying to install a portal on my volunteers personal-detail-page which shows all the projects this specific volunteer has been working on. I tried using tableB::name = tableA::volunteer as filter-rule which works fine as long as there is just one name in the field tableA::volunteer. But I need the portal to show me ALL projects which contain the volunteers name SOMEWHERE in the field volunteer.


      project a - smith andersen murphy

      project b - smith

      project c - anderson clark murphy

      project d - andersen smith

      On Mr Smiths personal-details-page I need the portal to show me: 

      project a 

      project b

      project d


      My current solution shows only 

      project b


      I have tried quite a few combinations with wildcards, but I´m stuck and I don´t get this to work. Thanks for yout time.


        • 1. Re: Portal filtering with partial match

          Try using pattern count:

          as in:

          PatternCount ( TableA::volunteer ; TableB::name )

          This may match to more records than you want in some casees. A person named Smith, for example will bring up records where Smithson is listed in the volunteer field.

          Thus you may need to use a more sophisticated expression or restructure your data.

          I mention restructuring because you appear to be listing multiple names in a single field and this looks like a less than optimum approach. I think you may have a many to many relationship here that should be implemented with a join table instead of a field with multiple names listed in it.

          Also, matching by names can be problematic. Names are not unique and people change their names. Plus, names can be easily misspelled during initial data entry. Thus, any name based matching should be restricted to searches of your database where you can handle issues with duplicate names and changed names and should not be used to define relationships.

          Here is a demo file that uses two different approaches for partial name matching (for search purposes) as well as a drop down auto-complete list that is scripted to enter the item's ID number when you select its name: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: Portal filtering with partial match

            Ah, thank you so much, that worked.

            And to your other comments: I actually use an (unique) ID and not the persons name, my first post is just an example to demonstrate my problems. And I thought about using a join table, but that would complicate matters in other places...

            Thanks for your help