8 Replies Latest reply on Sep 12, 2013 2:58 PM by philmodjunk

    help with how to compile a field calculation

    shae1725

      Title

      help with how to compile a field calculation

      Post

           I was given this calculation to format a number field but as im a novice I dont know how to compile it in the calculation panel

           help would be appreciated

            

           TextFormatRemove ( Self )

           Let(xyz = left(xyz;3) & "_" & middle(xyz;4;3) & "-" & middle(xyz;7;3))

        • 1. Re: help with how to compile a field calculation
          philmodjunk

               The syntax for that is incorrect as it depicts two different calculations with no operator to link them.

               Perhaps the person helping you wanted you to use:

               TextFormatRemove ( Self )

               As the auto-enter calculation for a field named xyz. If so, you'll need to enter it in Field Options and also clear the "do not replace existing value..." check box.

               Then perhaps the second line was suggested as a calculation for a separate field that takes the data from the field named xyz and inserts the underscore character into two specific locations.

          • 2. Re: help with how to compile a field calculation
            shae1725

                  

                 The calculation was  put "_" between every 3 numbers  ie (123123123 into 123_123_123)  or  (12312   into 123_12_  )  I dont think there will ever be more than 9 digits !

                 I need this format to add to an url to retrieve pictures! Mybe I should create another field and populate with the result instead of replacing?

                 any ideas - 

            • 3. Re: help with how to compile a field calculation
              philmodjunk

                   That would appear to be what your suggested calculation was supposed to do though the let function as posted here, is incomplete. It isn't necessarily your only option. Your example only shows numeric digits. If that's the case, we can borrow a page from the trick many of us use to auto-format phone numbers:

                   Let ( T = Filter ( TextFormatRemove ( Self ) ; 9876543210 );
                            left( T ; 3) & "_" & middle( T ; 4 ; 3 ) & "-" & middle( T ; 7 ; 3 )
                          )

                   You'd find your field in Manage | Database | Fields and double click it. Then select the calculation option on the auto-enter tab to open up a dialog box where you can enter the above expression.

                   Then you'd also need to clear the "do not replace existing value..." check box.

                   If the allowable input includes other characters, you'd need to include them in the filter expression. (The filter expression has to filter out the underscores so that if you edit the field to change a previous value, you don't get two sets of underscore characters and a screwed up result.)

              • 4. Re: help with how to compile a field calculation
                shae1725

                     Thats great many thanks yet again :)

                      

                     BTW is it possible to run the calculation in fields already populated?

                      

                     Also because i also will be displaying the number without the "_" should i have 2 fields one for the original and one for the result?

                      

                • 5. Re: help with how to compile a field calculation
                  philmodjunk

                       because i also will be displaying the number without the "_" should i have 2 fields one for the original and one for the result?

                       That's the only way that will work and in that case, I'd just define the new field as the calculation field that inserts the underscores instead of as an auto-enter expression.

                       If you do that you won't need to update existing records, but here's a thread on how to do that: Updating values in auto-enter calc fields without using Replace Field Contents

                  • 6. Re: help with how to compile a field calculation
                    shae1725

                         Hi just discoverd a problem with the calculation

                         seems to be only working with 3  4  5 and 6 digit numbers!  123123123 does not calculate at all

                    • 7. Re: help with how to compile a field calculation
                      shae1725

                           ignore last message! it works when i change the field from number to text!

                      • 8. Re: help with how to compile a field calculation
                        philmodjunk

                             When I paste this into the FileMaker Advanced Dataviewer:

                             Let ( T = Filter ( TextFormatRemove ( 123123123 ) ; 9876543210 );
                                      left( T ; 3) & "_" & middle( T ; 4 ; 3 ) & "_" & middle( T ; 7 ; 3 )
                                    )

                             I get:

                             123_123_123

                             which would appear to be correct and certainly is not "Does not calculate at all"...

                             It should work the same in your auto-enter calculation. A field of type calculation would need to replace "Self" with the name of the field where the data is typed in.