9 Replies Latest reply on Nov 21, 2009 7:56 AM by johnhorner

    Preserving fixed number of decimal places when converting to text

    johnhorner

      Title

      Preserving fixed number of decimal places when converting to text

      Post

      i have a calculation field (ItemSubtotal) which returns the numeric result of multiplying an item qty and an item price.  if i am using us dollars i can simply format the field to preserve 2 decimal places and show a dollar sign.  however, if the invoice is set up for a foreign currency (say, euro for example), i use a different layout to print the invoice and this layout contains a calculation field that returns a text result (e.g. ItemSubtotalForeignCurrency = ItemSubtotal & CurrencySymbol).  so far this all works fine.  the problem arises when the subtotal is an even amount (i.e. no cents, just dollars/euros/pounds, etc, such as 100€) and then it does not display the decimal places.  i want it to display the result as: 100.00€.  does anyone know how to do this.  any help much appreciated.

        • 1. Re: Preserving fixed number of decimal places when converting to text
          comment_1
            

          The simplest way would be to use a text object with merged fields:

           

          <<ItemSubtotal>><<CurrencySymbol>>

           

          Then format the text object to display 2 decimal places, using Format > Number… .

           

           

          Alternatively, see:

          http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=21980#M21980

          • 2. Re: Preserving fixed number of decimal places when converting to text
            johnhorner
              

            thanks for your reply.  i actually did try the merge field technique which displays the result correctly, however, it has a different problem in that when this result is displayed in a portal row, it displays the currency symbol all by itself in the empty portal row just below the last row that contains information (which looks very odd on an invoice).  i looked at the alternative solution you linked me to but if i understand correctly wont that always add ".00" even if the actual decimal amount is some other number (such as €150.47)?  i will give it a try anyway but if it is just adding ".00" any other thoughts would be appreciated.

             

            thanks - john h.

            • 3. Re: Preserving fixed number of decimal places when converting to text
              johnhorner
                

              hey comment,  i took a closer look at your solution (from the link you provided) and plugged it in to my database and, of course, it worked exactly as intended.  i didn't understand in my last post exactly what is was doing.  thanks so much!

               

              i have a follow up question: assuming that this calculation was being performed thousands of times, and since the problem case (where the last 2 digits are ".00") are relatively rare (in my situation) would it increase the efficiency of this little sub-routine to add an "if/then" function so that only if the last 2 digits actually were ".00" would you then jump to your solution, otherwise you would just simply use: = ItemSubtotal & CurrencySymbol?  to put it in other words, does running the if/then on every subtotal use more or less processing power than performing the serialincrement/mod functions on every subtotal?  or do they both tax the processing ability so little that it is trivial?  just curious about the best practice to employ here and in general.  thanks again for the solution!

              • 4. Re: Preserving fixed number of decimal places when converting to text
                comment_1
                   1.
                You should print your invoices from the LineItems table (with subsummary parts holding the invoice attributes). Printing a portal can be awkward - esp. when it allows the creation of new records.


                2.
                The calculation is rather trivial, and I don't think you will gain anything by performing a test before applying it. BTW, it needs to run every time amount has a single or no decimal digit - statistically, that's 10% of the cases.

                Anyway, if the calculation is stored, it will be performed only once for each item.




                • 5. Re: Preserving fixed number of decimal places when converting to text
                  johnhorner
                     hey comment,  thanks again for the advice.  i will do without the test.  i am curious about your suggestion to do the invoice from the lineitems table.  it seems counterintuitive in that the whole idea of the portal was to link related records (line items) to the main record of interest (the invoice).  but assuming that in this case it is better to set it up from the lineitems table, how do you actually structure the relationship?  for my current configuration, for eaxample, i have the lineitems related, of course, by invoice number so only the related ones show up.  but if i am using the lineitems table as the basis for the layout, wouldn't i have to run a search first for the related lineitems and then how would you relate that to the invoice table for the subtotals and totals?  is this method always preferable to the other way around?  what are the potential problems with using portals to print?  thanks again for the advice (i have another unrelated question about foreign currency but i will start a new thread).
                  • 6. Re: Preserving fixed number of decimal places when converting to text
                    comment_1
                      

                    johnhorner wrote:
                    the whole idea of the portal was to link related records (line items) to the main record of interest (the invoice). 

                    Not exactly: the relationship does the linking - the portal is merely a display/entry device based on the relationship. The relationship will work the same regardless of whether you have a portal on a layout or no.

                     

                     

                    The main issues with printing portals:

                    1. A portal must have a fixed number of rows; this limits the number of line items a printed invoice can have.

                    2. Portals do not always break correctly at page boundaries;

                    3. While portals can slide, portal rows do not shrink to fit; IOW, each line item has the same height.

                     

                     

                    I think your other questions can be answered by examining the demo posted here:

                    http://fmforums.com/forum/showpost.php?post/309136/


                    • 7. Re: Preserving fixed number of decimal places when converting to text
                      johnhorner
                        

                      thanks comment... i am just starting to comprehend how this works.  i noticed in that demo that even in layout mode it displays the field contents from one of the records rather than then field name as i have always seen it in the past.  how can i change it to display the field name or change my fields to display the contents in my layouts when in layout mode?  sorry to keep asking you so many questions.  greatly appreciated!

                      • 8. Re: Preserving fixed number of decimal places when converting to text
                        comment_1
                           In Layout mode, select View > Show > Sample Data.