11 Replies Latest reply on Nov 2, 2016 2:46 PM by dtcgnet

    Pattern Count, Nested IF Statement Calculation Clarification

    lindseym

      I checked out this thread, https://community.filemaker.com/thread/75773?q=Pattern%20Count%20-%20If%20statement  it gave me a better understanding, but I am still having issues.

       

      I have a column titled Space

       

      Space can contain a number of values for example, but all instances are separated by a dash, the order of the values could be in any random order: Bldg North-Level 2-Quantrant Beyond the Trees

       

       

      What I want:

       

      If Space contains the word Level; get everything (excluding the first space) after the word Level until you reach a - , if Space does not contain the word "Level" return TBD (highlighted)

       

       

      Because I am still learning I started to build my calculation one step at a time

      I began with an if statement & pattern count:

       

      If ( PatternCount ( Space ; "Level" ) ; "Yes" ; TextStyleAdd ("TBD" ; HighlightYellow ))

       

       

      I am able to get the response "Yes," however, I there is no "Level" I am getting an empty (blank) value

       

      my second question, what would be the best way to parse the level # (or value after level).

       

      Files attached if needed.

       

       

      Thanks

       

      -L

        • 1. Re: Pattern Count, Nested IF Statement Calculation Clarification
          philmodjunk

          Your calculation does not return any text other than "yes" as that is all you specify in the calculation.

           

          Use the position function instead of pattern count. It returns 0 if the specified text is not found, but returns the position of the text if it is found where pattern count does not return this needed info.

           

          There are then a number of other functions that can be used with this, such as Middle and Right that can be used to parse this text.

           

          If you have FileMaker Advanced, you might check out text parsing custom functions on a site for sharing them such as Brian Dunning's site as there are some good ones there that already combine several Filemaker Functions into a single call to parse text.

          • 2. Re: Pattern Count, Nested IF Statement Calculation Clarification
            lindseym

            If ( PatternCount ( Space ; "Level" ) ; "Yes" ; TextStyleAdd ("TBD" ; HighlightYellow ))

             

            So this does not read "If Space contains the word "Level", return "Yes", if it does not contain the word "Level", return "TBD" and highlight the word TBD" ?

             

            I'll check out Brian and see what I can gather.

             

             

            Unfortunately I do not have Advanced. I have noticed on a bunch of you tube videos that Advanced seems to have an "Evaluate Now" button to test your calculation. Am I correct that feature isn't available on Pro?

             

            Thanks

             

            -L

            • 3. Re: Pattern Count, Nested IF Statement Calculation Clarification
              philmodjunk

              Apologies, I misread your first post.

              You should not be getting a blank result and I see no reason why it would from the information provided here.

              • 4. Re: Pattern Count, Nested IF Statement Calculation Clarification
                dtcgnet

                It looks like Space is just text. It looks like you have a field called "Level C", which has an auto-enter calculation set up.

                 

                My guess is that you added the field (or the auto-enter condition) after the field called Space was already populated. If that's the case, then the auto-enter won't calculate itself. You'll need to either 1) Show all Records, then use Replace Field Contents of Space with Space (which will cause Level C to be evaluated for all records), 2) use Replace Field Contents in Level C with the formula that you have for Level C, or 3) change Level C to a calculation, then click OK on Manage Database, then go back in and change it back to a text value with your auto-enter calc. Also, Level C is currently set up as a number field, so make sure to change it to a text field.

                 

                In your case, what I would do is the change to calc--values will calculate--change back to text. Because all of the fields that you reference are in the same table, the calculation will default to stored. Once the calculation is stored, when you change the field back to a text field, the values will remain.

                • 5. Re: Pattern Count, Nested IF Statement Calculation Clarification
                  BruceRobertson

                  The calculation is doing what you specified.

                  You should turn OFF the indicated checkbox.

                  You should set the field type to Number.

                  Note also that you have defined the field as an auto-enter calc; not a calculation field.

                  Because it is an auto-enter calc, results will only change when you edit the record.

                  calc_option.png

                  field_type.png

                  • 6. Re: Pattern Count, Nested IF Statement Calculation Clarification
                    lindseym

                    This worked. and I was able to see TBD. I thought that it wouldn't run if there was on no data in the entire column, this helps me with some other issues I was having thanks!

                    • 7. Re: Pattern Count, Nested IF Statement Calculation Clarification
                      lindseym

                      Also, I have it as an auto enter calc, so it can be seen as a number and then sorted.. not sure if thats the best way, but it's the way I found for it to work for me.

                       

                      We do not enter any data in FMP, so the records will always update!  (at least I hope )

                      • 8. Re: Pattern Count, Nested IF Statement Calculation Clarification
                        philmodjunk

                        Auto-entered calcs and stored calculation fields evaluate in exactly the same way if the "do not replace..." option is not selected for the auto-entered calc. They only update if a field from the same record that is referenced in the calculation is modified.

                         

                        We often forget that as many calculations will update when an auto-entered calculation does not, but in this is when data is from a different table occurrence. That makes a calculation field unstored and the auto-enter calculation remains stored, but will not update when the value in the related field is modified.

                        • 9. Re: Pattern Count, Nested IF Statement Calculation Clarification
                          keywords

                          Re: " Advanced seems to have an "Evaluate Now" button to test your calculation. Am I correct that feature isn't available on Pro?"

                           

                          Correct. That is a function available within the Data Viewer which is part of FMAdvanced. An extremely useful tool if you are doing any serious developing.

                          • 10. Re: Pattern Count, Nested IF Statement Calculation Clarification
                            lindseym

                            I wouldn't say I'm doing any serious developing... .. my company was using FMP to import an excel file, and sort. That was it. I came on board and realized how powerful the program was-- and decided to streamline our processes. Removing very cumbersome steps that varied between each project and person.

                             

                            So I taught myself it (of course with youtube and the very generous brains on this forum). Pretty proud. For many things I have saved 99%  time.. (literally one button click and d  3 seconds later, everything is done and saved as a pdf.. and eliminated about 98% of possible user error, all while formatting data in way our clients can easily read and critique.

                             

                            If I make the FMP in Advanced, can the rest of the team who uses Pro still access the file?

                            • 11. Re: Pattern Count, Nested IF Statement Calculation Clarification
                              dtcgnet

                               

                              If I make the FMP in Advanced, can the rest of the team who uses Pro still access the file?

                              Yes.

                               

                              And as to the rest...good for you! Glad you're learning and advancing.

                               

                              In the meantime, if you want to know how a given calculation will evaluate, you can create a short script where you set a variable equal to the value of your calculation, then use Show Custom Dialog and do something like:

                              "The value of your variable is: " & $VariableYouCreated