5 Replies Latest reply on Apr 4, 2013 9:03 AM by philmodjunk

    Pattern Count

    Annette

      Title

      Pattern Count

      Post

           Hi,

           I have on my database a field called TItles which is the persons job title. 

           I then have another field that combines first and last name which I had a pattern count in to only show certain titles. 

           If(PatternCount ( Title;"Title 1") + PatternCount ( Title;"TItle 2"); LName  &  ","  &   " "  &  FName; ""  )

           All well. 

           But, there are LOADS of titles now and I was wondering if there was a way to do the pattern count the opposite way now just not sure how to set it properly.  Basically if there are 50 Titles, I want to show 45 of them.  So it's easier clearly to eliminate the five and show remaining rather than list all 45 as the pattern count above would require. 

           How would it be written to omit those few?  I tried a couple options but no go.  Always told me I had too many ) or not enough or something so didn't know where I was writing it wrong. 

        • 1. Re: Pattern Count
          philmodjunk

               Does this mean that you have a single field named Title that contains multiple titles in the same field? If so, there are better ways to orgainize that information using a related table or tables than listing them all in one field. A portal can then list these titles and a portal filter can exclude the ones you do not wish to display.

               That said, you could write your expression as:

               If ( Not ( PatternCount ( Title;"Title 1") Or PatternCount ( Title;"TItle 2") ) ; LName  &  ","  &   " "  &  FName )

               But if the titles form a return separated list you might want to use this expression instead:

               If ( IsEmpty ( FilterValues ( Title ; List ( "Title 1" ; "Title 2" ; //and so forth... ) ) ;LName  &  ","  &   " "  &  FName )

          • 2. Re: Pattern Count
            Annette

                  I have a table with all the various titles on it.  Then on my "People" table I have a title field which is a drop down list referring to the titles table. 

                 A person will only have one title. 

                 Its other fields that I want to pull names from specific titles to display in drop down lists which is where I was doing the copy count. 

            • 3. Re: Pattern Count
              philmodjunk

                   Then my first suggested calculation should work for you.

                   You might consider using a relationship instead of this calculation field to produce your value lists. See the first link in the following list. You are using "option 1". "Option 2" describes the relationship based method so you can compare the two options to see which is the better option in your situation:

                   Conditional Value List Links:

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

              • 4. Re: Pattern Count
                Annette

                     Your suggestion worked like a charm.  It's what I was trying to do but clearly was missing a ) somewhere in it. 

                     I do have conditional value lists in other areas but for this one I think that the way will work ok. 

                     As always, THANK YOU VERY MUCH

                • 5. Re: Pattern Count
                  philmodjunk

                       Come to think of it, we can simplify this further:

                       If ( Not PatternCount ( "Title 1 Title 2 //and so forth..." ; Title ) ; LName  &  ","  &   " "  &  FName )

                       or, if a given title you don't want to exclude is a substring of one that you do want to exclude:

                       If ( IsEmpty ( FilterValues ( List ( "Title 1" ; "Tilte 2" ; // and so forth... ) ; Title ) ) ; LName  &  ","  &   " "  &  FName )