1 2 Previous Next 21 Replies Latest reply on Nov 18, 2016 4:59 PM by philmodjunk

    Calculation - Nesting Statements

    lindseym

      I have this calculation:

       

      Let ( [

        text = (Right ( Building ; 1 )) & "-" & (LeftWords ( Location Calculation ; 1 )) ;

        n = text + 0 ;

        text = Case ( n = 0 ; text ; n )

      ] ;

      Case (

        ${Copy (Side A)} = "LOC #" ;

        Substitute ( text ;"TBD" ; TextStyleAdd ( "TBD" ; HighlightYellow ) );

       

        Substitute (

        ${Copy (Side A)} ;

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

        ["LOC #" ; text ]

        )

      )

      )

       

       

       

       

      I think the issue is with (Right ( Building ; 1 )) &

       

      portion.

      I manage to get the - and the copy

       

       

       

      Screen Shot 2016-11-17 at 2.42.31 PM.png

       

       

      I want it to take the  Building  Column and only parse the building letter (always the second character) add a single dash and the take the {Copy (Side A)} contents (which is a calculation).

       

      The building Letter and Copy Column is my s practice column.

       

      The  column in the above should read

       

      G-117

      B-117

      C-117

       

      Am I just leaving out brackets or something?

       

      Thanks,

       

      -L

        • 1. Re: Calculation - Nesting Statements
          David Moyer

          Hi,

          I'm too confused by the opening Let statement to go beyond that.  It looks like you are mixing mathematical and text functions.  You might mean n = text & "0", for example.  Also, your use of {braces} is also confusing to me.

          On the surface, it looks like your calculation is over-complicated.

          • 2. Re: Calculation - Nesting Statements
            lindseym

            This calculation:

             

            Right ( Building; 1) & "-" & (LeftWords ( Location Calculation ; 1 ))

             

            Works just fine alone. So, now I am leaning towards that portion of the calculation not being the issue.

             

            -L

            • 3. Re: Calculation - Nesting Statements
              lindseym

              Ya, I am new to this.. so I had someone else help me with the calculation and it does what I want it to.. but then I have a specific project that requires a little more detail.

               

              The text = Case ( n = 0 ; text ; n ) part is I believe suppose to remove leading Zeros if they exist in the location calculation.

              • 4. Re: Calculation - Nesting Statements
                erolst

                There is no need to wrap the expressions

                 

                Right ( Building ; 1 ) and LeftWords ( Location Calculation ; 1 )

                 

                into parentheses. It doesn't do any harm, but it doesn't help, either, and just adds clutter.

                 

                I think what's foiling your success is

                 

                  n = text + 0 ;

                  text = Case ( n = 0 ; text ; n )

                 

                What is it good for?

                 

                Also, the default Case() doesn't work as expected.

                 

                And thirdly, your Copy ( Side A ) field has problematic characters, namely the parentheses, which causes the field to be wrapped into braces, and that – as you can see – causes even more confusion …

                 

                Maybe fix this and remove some of your duplicate(?) fields, then let's start over.

                • 5. Re: Calculation - Nesting Statements
                  David Moyer

                  I think the best way to approach this is to describe what you want in words.  Like, "no matter what, if the text contains 'TBD', then turn it yellow, otherwise/also ...", and so on.

                  • 6. Re: Calculation - Nesting Statements
                    lindseym

                    erolst

                     

                    Thanks for the explanation. I used the parentheses before I knew it would cause so much work.

                     

                    I will work on minimizing of the duplicates and see if I can go from there.

                     

                    I am under a deadline -- so I'm already in the process of manually changing the records, but I know I will face something similar in the future and I would like to get a jump on that

                     

                     

                    I THINK this removes leading zeroes if they appear.

                     

                      n = text + 0 ;

                      text = Case ( n = 0 ; text ; n )

                     

                     

                     

                    Thanks!

                    • 7. Re: Calculation - Nesting Statements
                      erolst

                      Adding to the above:

                       

                      In your Case(), you'll never calculate the first result, because in all your sample records, the value is <> "LOC #".

                       

                      This brings you to the default result, where you substitute strings that aren't there, so the replacement characters aren't inserted, which leaves you with what the field was in the beginning, and the nicely calculated variable isn't used at all.

                       

                      Bummer

                      • 8. Re: Calculation - Nesting Statements
                        lindseym

                        So... interestingly enough the time I posted about this issue is a nested calculation question:

                         

                         

                        https://community.filemaker.com/thread/164670?q=remove%20leading%20zeros

                         

                        I don't know enough to to question the  calculation, I just know it worked and am very grateful for it!

                        • 9. Re: Calculation - Nesting Statements
                          erolst

                          lindseym wrote:

                           

                          I THINK this removes leading zeroes if they appear.

                           

                          n = text + 0 ;

                          text = Case ( n = 0 ; text ; n )

                          I don't think it does (especially since arithmetically adding to a string doesn't make sense) - and where would they come from?

                           

                          Well, there is that calculation that you are referencing, but that we cannot see ... maybe get rid of them there?

                          • 10. Re: Calculation - Nesting Statements
                            erolst

                            lindseym wrote:

                             

                            https://community.filemaker.com/thread/164670?q=remove%20leading%20zeros

                             

                            I don't know enough to to question the calculation, I just know it worked and am very grateful for it!

                            I know that the guy who gave you this is a real nifty coder, but it still doesn't make sense to me ...

                             

                            Anyway, I think you should follow David's suggestion and tell us in prose what you want to happen when.

                            • 11. Re: Calculation - Nesting Statements
                              David Moyer

                              The  column in the above should read

                              G-117

                              B-117

                              C-117

                              Is this where leading zeroes could be a problem?  Like, you want G-7 instead of G-007?  If so, adjust this part of your calculation ...

                              GetAsText ( GetAsNumber ( LeftWords( Location Calculation ; 1) ) )

                              • 12. Re: Calculation - Nesting Statements
                                lindseym

                                I think we got off point a little and I may have confused all.

                                 

                                The leading Zeros are not the issue. Somehow the calculation above worked just fine before I added to the first line

                                 

                                Before:

                                 

                                text = (LeftWords ( Location Calculation ; 1 )) ;

                                 

                                After:

                                 

                                text = (Right ( Building ; 1 )) & "-" & (LeftWords ( Location Calculation ; 1 )) ;

                                 

                                With the After calculation:

                                 

                                I want it to return: B-117

                                 

                                It currently returns: -117

                                 

                                 

                                I'm confused why it can add the "-" but not evaluate the right calculation.

                                 

                                Right ( Building ; 1 ) works when I put it in a field all alone.

                                 

                                Thanks

                                 

                                -L

                                 

                                 

                                • 13. Re: Calculation - Nesting Statements
                                  David Moyer

                                  I can only guess that it's the outer pair of parentheses.

                                  • 14. Re: Calculation - Nesting Statements
                                    erolst

                                    lindseym wrote:

                                    Somehow the calculation above worked just fine before I added to the first line

                                     

                                    Before:

                                     

                                    text = (LeftWords ( Location Calculation ; 1 )) ;

                                     

                                    After:

                                     

                                    text = (Right ( Building ; 1 )) & "-" & (LeftWords ( Location Calculation ; 1 )) ;

                                     

                                    With the After calculation:

                                     

                                    I want it to return: B-117

                                     

                                    It currently returns: -117

                                     

                                    Parentheses considered harmful? I think not:

                                     

                                    Screen Shot 2016-11-18 at 01.00.09.png

                                     

                                    What is your "location calculation" field doing, anyway? That's the great unknown here.

                                    1 2 Previous Next