6 Replies Latest reply on Dec 30, 2016 2:24 PM by lindseym

    Substitute Calculation "Contains" vs "Equals"


      Howdy Folks:


      Working on calculation and I ran across this issue. I can't seem to figure out how to do a  Case + substitute calculation when my test does not = something specific.


      Here is what I have:


      Screen Shot 2016-12-29 at 3.28.38 PM.png






      ${Copy (Side A)} ? "LEVEL#" ; Substitute ( ${Copy (Side A)} ; "LEVEL#" ; Level from Space ) ;


      What I am looking for is:


      ${Copy (Side A)} contains but does not equal  "LEVEL#" (performed in the first test of case) it performs the substitution. I have the substitute calculation working fine, just not sure how to do an operator of "contains" within a case calculation.





      I realized that I could just use ≠ .... but because the case function goes down the list until it evaluates true it would stop at that test. I want it continue down the list if "LEVEL#" isn't contained in the field.


      Any thought on the best way to do this?






      Working on FMP 14.0.6




        • 1. Re: Substitute Calculation "Contains" vs "Equals"

          You're probably looking for PatternCount( ): FileMaker Pro 15 Help


          Note that as a check of presence, you can simply say:


          If ( PatternCount ( someField ; "some text" ) ;


          because you're not interested in the actual count, and any result > 0 is True.


          btw, please rename the field 'Copy (Side A)' to something like copySideA, or copy_side_A - anything that doesn't cause a warning and will wrap a reference to the field inside confusing braces.

          1 of 1 people found this helpful
          • 2. Re: Substitute Calculation "Contains" vs "Equals"

            I knew pattern count was going to help somehow... I found this: FileMaker Function of the Week: PatternCount


            I was mindful of the order I placed the tests in and did:


            PatternCount ( ${Copy (Side A)} ; "LEVEL#" ) > 0 ; substitute ( ${Copy (Side A)} ; "LEVEL#" ; Level from Space);


            And that part of the calculation is working just fine. Now on to why the test about it is acting all funky now!

            Screen Shot 2016-12-29 at 4.39.23 PM.png


            Thank you



            • 3. Re: Substitute Calculation "Contains" vs "Equals"

              Thank you!


              Oh I set up the Copy (Side A) when I first started using FMP and didn't know better.



              Now I am worried that if I do change it in FMP  ( & in the program that gives me my data), it will make things go bonkers in my department. It IS on my to do list... You aren't the first to suggest the column change and If I knew a way to make sure I changed everything in all my calculations and scripts I would for sure tackle it sooner rather than later.

              • 4. Re: Substitute Calculation "Contains" vs "Equals"

                If you are only using FileMaker, then changing the name of the field won't affect anything in FileMaker calcs, scripts, etc. since FileMaker doesn't use the text name of a field but its FileMaker internal id, probably a number. The text names are looked up from a db, probably. They are a convenience for the developer.


                Having said that, I will now explain why that isn't always true.


                This ease of correction makes FileMaker the choice of many of us lazy developers who used dbs that went bonkers after such a change.


                Now, if you extend FileMaker to use plugins, javaxxxx, etc. where you have to type in the name of fields rather than point and click. don't change the name unless you are ready to change every such instance.


                Oh, and FIleMaker will also fail in those instances where you enter the field, table, layout or file name manually after changing just the name in the editor.

                1 of 1 people found this helpful
                • 5. Re: Substitute Calculation "Contains" vs "Equals"

                  Take a moment and consider using a carefully crafted substitute(). Your code gives me a bit of a headache mostly because I don't know what you are trying to do.


                  Substitute will do a CASE and IF of its own since it seems to make the first substitution which overrides later considerations. For Instance:


                  $word = "note"

                  Substitute ($word; [ "note; "yes" ] ; [ "not; ; "OK'' ] )

                  Result: "yes"




                  Substitute ($word; [ "not; "yes" ] ; [ "note; ; "OK'' ] )

                  Result: "yese"


                  I've found that the substitution can be a calculation which makes it even more fun.

                  1 of 1 people found this helpful
                  • 6. Re: Substitute Calculation "Contains" vs "Equals"

                    I had 2 threads going on and realized that the Let Function with text + 0 worked very well before I began modifying it. So I looked for another alternative and then got help with that calculation. Pattern count is not lost as I learned something useful for the future. 


                    Your not and note example was very useful and easy to understand! Thank you


                    The thread is here:


                    Combining or nest calculations


                    But basically this is what it now looks like and functions properly.  It was hard for me to understand the ordering of substitutions, but I believe I have it (fingers crossed).



                    Let ( [

                      t = LeftWords ( Location Calculation ; 1 ) ;

                      x = Filter ( LeftWords ( t ; 1 ) ; "0123456789" )  = LeftWords ( t ; 1 ) ;

                      l = Length ( LeftWords ( t ; 1 ) ) ;

                      n = GetAsNumber ( LeftWords ( t ; 1 ) ) ;


                      locationCalculationWithoutLeadingZeroes =

                        Case (

                          x ;

                          n & Right ( t ; Length ( t ) - l ) ;



                      ] ; 


                      // And the second: (substitutes)


                      Substitute (

                        ${Copy)} ;

                          [ "XLEVEL#" ; "*" & Level from Space ] ;

                          [ "LEVEL#" ; Level from Space ] ;

                          [ "STAIR#" ; Stair from Space ] ;

                          [ "BLDG#" ; Building from Space ] ;

                          [ "LOC#" ; locationCalculationWithoutLeadingZeroes ] ;

                          // This is the substitution that would need the leading zeros removed

                          [ "TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) ]