3 Replies Latest reply on Jul 7, 2015 3:15 PM by LeilaRegan

    Calculation Function that checks for partial text

    LeilaRegan

      Title

      Calculation Function that checks for partial text

      Post

      Hello,

      In my database I have the following calculation to indicate security deposit amounts required upon booking we have three companies that do not require a security deposit:

      Case ( 
      ValueCount ( FilterValues (proposal_CONTACT::CompName; "ABC" )); 0;
      ValueCount ( FilterValues (proposal_CONTACT::CompName; "DEF Event Technology" )); 0;
      ValueCount ( FilterValues (proposal_CONTACT::CompName; "GHI Media Services" )); 0;

      The issue I am running into is the second and third line are fine but for company "ABC" we have about 80 different variations. ABC, ABC Presentation Services, ABC Orlando, ABC Las Vegas, ABC Media, etc.

      Is there a calculation function I can use to indicate if the company name contains "ABC" to make the security deposit 0? I have tried PatternCount, ValueCount, Count but it seems that unless it matches "ABC" exactly, it won't make the security deposit 0.  

       

      Thank You!

       

        • 1. Re: Calculation Function that checks for partial text
          TSGal

          Leila Regan:

          Thank you for your post.

          PatternCount is the correct function to use.  PatternCount returns the number of occurrences of a text string inside a field.  Therefore, If CompName is "ABC Las Vegas", PatternCount (Proposal_CONTACT::CompName ; "ABC" ) will return 1.  Your calculation could then be modified to:

          Case (
          PatternCount (proposal_CONTACT::CompName ; "ABC" ) > 0 ; 0 ;
          ValueCount ( FilterValues (proposal_CONTACT::CompName; "DEF Event Technology" )); 0;
          ValueCount ( FilterValues (proposal_CONTACT::CompName; "GHI Media Services" )); 0;
          ...

          See FileMaker Pro Help ( Reference -> Functions reference -> Text functions -> PatternCount ) for more complete information about the PatternCount function.

          TSGal
          FileMaker, Inc.

          • 2. Re: Calculation Function that checks for partial text
            philmodjunk

            But I'm a bit concerned that you are using filtervalues at all here. What data do you have in compname? if this is a single company name as I would expect to see here, FilterValues really isn't the correct function to use at all here.

            • 3. Re: Calculation Function that checks for partial text
              LeilaRegan

              Thank you. I was initially using PatternCount and will probably convert the other two back to it. I was just having a hard time figuring out why it had to be an exact match but now it makes PERFECT SENSE. Thank you so very much TSGal.