7 Replies Latest reply on Aug 3, 2012 8:43 AM by philmodjunk

    Truncate Issue

    Matty_1

      Title

      Truncate Issue

      Post

      Hello,

       

      I have a text calculation field and I would like to have it display "CAD - $15487.98".

       

      In some cases that total only has one or no decimal numbers, I tried adding zeros in quotations to the end of the total and using the truncate function and it removes all the zeros.  Can someone please give another suggestion?

      This is the formula I thought would work but doesn't:

      "CAD - $" & Truncate((Total_price  &  "00");2)

      Thank you!

        • 1. Re: Truncate Issue
          philmodjunk

          Why do you need to include "CAD - " in the same field? I can guess that it stands for Canadian Dollars or some such, but why include it in the field with the dollar amount? If you can put it in a separate field, you can use normal currency data formatting and this will be much simpler.

          I've also experimented with this and have found that I can specify CAD $ as the currency symbol as data formatting in the inspector. Can't use enough characters to include the hyphen and spaces like you have though.

          If you must use a calculation (and there are cases where this cannot be avoided), use a calculation such as This:

          Let ( i = Int ( Total_price ) ;
                  "CAD - $" & i & "." & Left ( round ( ( Total_Price - i ) * 100 ; 0 ) & "00" ; 2 )
                )

          • 2. Re: Truncate Issue
            Matty_1

            I have it this way strickly for asthetic purposes.  The detail I left out and the reason I can't trick this by using the inspector is that CAD sometimes changes to USD but that wasn't the issue I was having it was with the total.

             

            As always, thank you Phil!

            • 3. Re: Truncate Issue
              philmodjunk

              I still wouldn't use the calculation for that case. I'd use a separate field for the CAD and USD info and not put it in this field in the first place.

              • 4. Re: Truncate Issue
                Matty_1

                but if I don't do it this way then I can't have the total perfectly centered as the number shrinks or grows.  The total can be anywhere from 3 to 8 digits in length and I want to maintain a uniform looks throughout.

                • 5. Re: Truncate Issue
                  philmodjunk

                  Option 1:

                  Right Justify the text field and left justify the currency field.

                  Option 2:

                  Use merge text for the two fields

                  • 6. Re: Truncate Issue
                    Matty_1

                    Thank you, embarrassed to admit I'd never used a merge field before,always coded merges together.  This is obviously far superior and efficient.

                    • 7. Re: Truncate Issue
                      philmodjunk

                      For some uses, but there are cases--such as when exporting data to meet the requirements of the receiving application, where you do need the calculation.

                      Also, when you have a large block of text with multiple merge fields, you can only specify one number format for all the number fields in the block of text. if one field is a simple number that should be formatted as entered and another is currency, you end up using the calculation field as your are otherwise forced to specify currency format or "as entered" for both fields.