4 Replies Latest reply on May 3, 2011 12:56 PM by artdoc

    Number formatting in a formula-build? (Q 3 of 3)



      Number formatting in a formula-build? (Q 3 of 3)


      My three pages of contract have merge fields bringing in specific numbers (Job Cost, Deposit, etc) and they show up with currency formatting just fine, showing Job Cost: $65,000.00, etc.  But since there are options that may be empty, I want to remove the items that have zero charges just to avoid empty lines or Site Prep: $0.00 entries. 

      I have had no luck finding a way to include currency, or even standard number formatting in a procedure, so my calculated content can include a dollar sign but not the comma to separate the thousands! Critical? I suppose not, but it looks bad.

      So can a formula be made to format currency correctly?

      Thanks for any insight...

        • 1. Re: Number formatting in a formula-build? (Q 3 of 3)

          You can, but the result is text rather than number so you need use the results carefully. What I'm not clear on is why the number formatting is failing for you in the merge text.

          If there is no value for site prep, your merge text should show as "Site Prep:", not "Site Prep: $0.00". The latter should appear only if the value in the field is zero, rather than blank.

          • 2. Re: Number formatting in a formula-build? (Q 3 of 3)

            I'll continue to experiment, but meanwhile I managed this arcane solution:

            If (FinalDue<1000 ; "$"& Right(FinalDue;3)&".00" ;"$"& Div(FinalDue;1000) &","& Right(FinalDue;3) & ".00")

            That results in a formatted numbers like this: $850.00 or $64,000.00

            I tried (without luck so far) to collapse a field to zero IF the field was zero. I mean the field might equal "$0.00" but that is actually not an empty field. I guess I need to leave the fields absolutely empty of any characters. I tried If(sitePrep <1..., so I'll try  If(SitePrep = ""...

            As an aside, I managed to find a way to spell out a large number (checkbook style), and I have to wonder why some of these functions are not built in to FileMaker? Why must a person spend Google time trying to find a solution?

            • 3. Re: Number formatting in a formula-build? (Q 3 of 3)

              The needs of each user vary. Spelling out numbers with the number names like you do for a checkbook is something exactly one client in over 10 years has needed. Everybody has their own list of features they'd like to see incorporated, and we all spend time griping at FileMaker Inc. for not incorporting the features that are on our personal list of "really useful" features. Wink

              I can't tell from your post whether it is signficant whether this field is empty or zero. In many systems, the presence of 0 in the field means something different than it being empty and this value would be expected to appear formatted as $0.00.

              I wouldn't use the above calculation unless I absolutely had to as I can get the same results in 99% of the situations where I need it just by selectint the appropriate currency options in the data format section of the Inspector. Best I can tell from the limited info I have to work on here, I'd probably use If ( SitePrep ; SitePrep ) as a calculation field, cSitePrep and then place cSitePrep inside my merge text in place of the original SitePrep field. That expression will return null if SitePrep is empty or zero and this will keep $0.00 from appearing.

              Is SitePrep, a field one where you enter data, is it a calculation field, or does it a number field with an auto-entered calculation?

              • 4. Re: Number formatting in a formula-build? (Q 3 of 3)

                "I wouldn't use the above calculation unless I absolutely had to..."

                Update: I have a potential need to include four dollar amounts for a contract. There will always be a deposit (a calculation based on the full job amount where there is actually an upper limit [If(JobTotal/10 <1000; JobTotal/10;1000)] and of course a final payment. The option might include none, one, or both of some installation charges. I can't use basic merge fields ["• Site prep is " & <<SitePrep>>] because I want the space to close up if there is no charge, so I use a calculation field to insert the whole statement IF there is a charge. This does work, but I still get no formatting in my results despite the correct use of currency symbol, decimal, and comma separator for thousands. If I change the result to number, naturally my field contains no text to support the financial data.

                Since this is a contract for a job that runs to many thousands of dollars, it is not my wish to leave empty spaces in the contract where it may look like something is missing! I do have a option to simply make the included line say "There is no charge for...", and maybe that is the best escape route for me.

                And finally, so far I do have to cludge the resulting text with my own version of formatting.