7 Replies Latest reply on Dec 30, 2014 9:16 AM by jcgrafted

    "Concatenate" in FileMaker with decimals

    borimard

      Hi there,

       

      I'm trying to 'concatenate' (sorry, I dont know the correct terminology in FileMaker) a number of different fields into one field.

       

      Works great except when dealing with numbers with decimal places...

       

      0.45 shows as .45

      1.90 shows as 1.9

       

      Is it possible to 'concatenate' with the zero before the decimal and in the second decimal position?

       

      Basically, I'm getting: 20 x .45x1.2m = 10.8m² - start at £42.7 when I want: 20 x 0.45x1.2m = 10.8m² - start at £42.70

       

      Sorry, am rather new to FileMaker, but any assistance would be appreciated!

       

      Cheers

        • 1. Re: "Concatenate" in FileMaker with decimals
          mikebeargie

          You could concatenate an if() statement that adds a leading zero.

           

          instead of what you're doing now:

           

          ___other stuff___ & yourfield & ___other stuff___

           

          you would do:

           

          ___other stuff___ & if ( left (yourfield ; 1) = "." ; "0" & yourfield ; yourfield ) & ___other stuff___

           

          where "yourfield" is the field returning the decimal, and "other stuff" is the rest of the string.

           

          Or if the value was entered in (raw data) as 0.45 and not .45, you might be able to simply use GetAsText( yourfield ) as well.

          • 2. Re: "Concatenate" in FileMaker with decimals

            I had deleted my suggestion since it is limited but I decided to put it back in case it is still useful.  We need to know how many places are possible in those numbers ... we aren't dealing with true numbers where they are unlimited ... with text functions we must be specific to address all possible combinations and know how much to pad.

             

            So I decided to punt and provide two possibilities.  if decimals are allowed as ##.##, use:

             

            Let ( [

            n = Right ( "00" & Int ( decimalField ) ; 2 ) ;

            m = Middle ( Mod ( decimalField ; 1 ) & "00" ; 2 ; 2 )

            ] ;

            n & "." & m

            )

             

            ... and if the format should be #.## then change n to:  Right ( "0" & Int ( decimalField ) ; 1 )

             

            EDITED:  Provided alternate calculations depending upon text format required

            • 4. Re: "Concatenate" in FileMaker with decimals
              borimard

              Thank you, I will play around with this later.  Should it be used as a custom function?

              • 5. Re: "Concatenate" in FileMaker with decimals

                It doesn't need to be used as a custom function; regular calculation would work fine.

                • 6. Re: "Concatenate" in FileMaker with decimals
                  ibrahim_bittar

                  How about this custom function:

                   

                  If ( Num < 0 ; "-" ; "" ) &

                  Let ( [ Integer = 0 + Int ( Abs ( Num ) ) ;

                            Decimal = Substitute ( Abs ( Round ( Num ; If ( Precision > 6 ; 6 ; Precision ) ) ) - Integer ; "." ; "" ) ] ;

                   

                  If ( IsEmpty ( Num ) ; "" ; Integer & "." & Left ( Decimal & "000000" ; If ( Precision > 6 ; 6 ; Precision ) ) ) )

                   

                  The parameters are Num and Precision. It can round up to 6 decimals but you can change the precision if you want.

                  • 7. Re: "Concatenate" in FileMaker with decimals
                    jcgrafted

                    I use FM Pro 12. Try turning the numbers into string variables before you concatenated.

                     

                    I wanted to make a unique record bar code value that concatenated the record number plus the zip code plus four without the dash. Here is the code I used:

                     

                    ___Code___

                    Enter Browse Mode[]

                    Go to Record/Request/Page [First]

                    Loop

                      Set Variable [$$ZipString; Vaule:Left (MyDirectory::Zip ; 5 )]

                      Set Variable [$$ZipStringPlus; Vaule:Right (MyDirectory::Zip ; 4 )]

                      Set Variable [$$ZipStringNoDash; Value:$$ZipString & $$ZipStringPlus]

                      Set Variable [$$RecNum; Vaule: MyDirectory::RecordNum + 10000]

                      Set Variable [$$SetBarCode; Value:$$RecNum & $$ZipStringNoDash]

                      Set Field [MyDirectory:BarCode; $$SetBarCode]

                      Go to Record/Request/Page [Next; Exet after last]

                    End Loop