5 Replies Latest reply on Jun 18, 2010 10:10 AM by philmodjunk

    Numer to Text (FM supplied calculation)

    DrWill

      Title

      Numer to Text (FM supplied calculation)

      Post

      Hi,

      I am trying to use the Converting Number to Text calculation supplied in the FM help. I am using FM 11 Pro.

      When I paste the formula into the calc field I get an error, “ A number, text constant, field name or “(“ is expected here." and references me to the first line in the calcu. I highlight it in red below. 

      What I am trying to do is convert a number ($100,016.50 or simliar) to "One Hundred Thousand, Sixteen Dollars and Fifty Cents"

       

      Any help would be greatly appreciated!

      Thanks

      Dave

       

      Here's the full calculation for reference.

      Choose(Int(Mod(Loan Amount,10^12) / 10^11), , One Hundred , Two Hundred , Three Hundred , Four Hundred , Five Hundred , Six Hundred , Seven Hundred , Eight Hundred , Nine Hundred ) & 
If(Int(Mod(Loan Amount,10^11) / 10^10) = 1, 
Choose(Int(Mod(Loan Amount,10^10) / 10^9), Ten , Eleven , Twelve , Thirteen , Fourteen , Fifteen , Sixteen , Seventeen , Eighteen , Nineteen ), 
Choose(Int(Mod(Loan Amount,10^11) / 10^10), , , Twenty , Thirty , Forty , Fifty , Sixty , Seventy , Eighty , Ninety ) & 
Choose(Int(Mod(Loan Amount,10^10) / 10^9), , One , Two , Three , Four , Five , Six , Seven , Eight , Nine )) & 
Case(Int(Mod(Loan Amount,10^12)/10^9) and Int(Mod(Loan Amount,10^9) / 10^3), Billion, , Int(Mod(Loan Amount,10^12)/10^9),Billion ,) 
& 
Choose(Int(Mod(Loan Amount,10^9) / 10^8), , One Hundred , Two Hundred , Three Hundred , Four Hundred , Five Hundred , Six Hundred , Seven Hundred , Eight Hundred , Nine Hundred ) & 
If(Int(Mod(Loan Amount,10^8) / 10^7) = 1, 
Choose(Int(Mod(Loan Amount,10^7) / 10^6), Ten , Eleven , Twelve , Thirteen , Fourteen , Fifteen , Sixteen , Seventeen , Eighteen , Nineteen ), 
Choose(Int(Mod(Loan Amount,10^8) / 10^7), , , Twenty , Thirty , Forty , Fifty , Sixty , Seventy , Eighty , Ninety ) & 
Choose(Int(Mod(Loan Amount,10^7) / 10^6), , One , Two , Three , Four , Five , Six , Seven , Eight , Nine )) & 
Case(Int(Mod(Loan Amount,10^9)/10^6) and Int(Mod(Loan Amount,10^6) / 10^3), Million, , Int(Mod(Loan Amount,10^9)/10^6),Million ,) 
& 
Choose(Int(Mod(Loan Amount,10^6) / 10^5), , One Hundred , Two Hundred , Three Hundred , Four Hundred , Five Hundred , Six Hundred , Seven Hundred , Eight Hundred , Nine Hundred ) & 
If(Int(Mod(Loan Amount,10^5) / 10^4) = 1, 
Choose(Int(Mod(Loan Amount,10^4) / 10^3), Ten , Eleven , Twelve , Thirteen , Fourteen , Fifteen , Sixteen , Seventeen , Eighteen , Nineteen ), 
Choose(Int(Mod(Loan Amount,10^5) / 10^4), , , Twenty , Thirty , Forty , Fifty , Sixty , Seventy , Eighty , Ninety ) & 
Choose(Int(Mod(Loan Amount,10^4) / 10^3), , One , Two , Three , Four , Five , Six , Seven , Eight , Nine )) & 
Case(Int(Mod(Loan Amount,10^6)/10^3) and Int(Mod(Loan Amount,10^3) / 10^0), Thousand, , Int(Mod(Loan Amount,10^6)/10^3),Thousand ,) 
& 
Choose(Int(Mod(Loan Amount,10^3) / 100), , One Hundred , Two Hundred , Three Hundred , Four Hundred , Five Hundred , Six Hundred , Seven Hundred , Eight Hundred , Nine Hundred ) & 
If(Int(Mod(Loan Amount,100) / 10) = 1, 
Choose(Int(Mod(Loan Amount,10)), Ten , Eleven , Twelve , Thirteen , Fourteen , Fifteen , Sixteen , Seventeen , Eighteen , Nineteen ), 
Choose(Int(Mod(Loan Amount,100) / 10), , , Twenty , Thirty , Forty , Fifty , Sixty , Seventy , Eighty , Ninety ) & 
Choose(Int(Mod(Loan Amount,10)), , One , Two , Three , Four , Five , Six , Seven , Eight , Nine )) & Case(Int(Loan Amount) > 1, Dollars, Int(Loan Amount) = 1, Dollar, ) 
& 
If(Mod(Loan Amount, 1) and Int(Loan Amount), and ,) & 
If(Mod(Int(Loan Amount*10), 10) = 1, 
Choose(Mod(Int(Loan Amount*100), 10), Ten , Eleven , Twelve , Thirteen , Fourteen , Fifteen , Sixteen , Seventeen , Eighteen , Nineteen ), 
Choose(Mod(Int(Loan Amount*10), 10), , , Twenty , Thirty , Forty , Fifty , Sixty , Seventy , Eighty , Ninety ) & 
Choose(Mod(Int(Loan Amount*100), 10), , One , Two , Three , Four , Five , Six , Seven , Eight , Nine )) & 
Case(Mod(Loan Amount, 1) > .01, cents, Mod(Loan Amount, 1) = .01, cent,)

        • 1. Re: Numer to Text (FM supplied calculation)
          philmodjunk

          Choose expects an integer from 0 to N. The first parameter, Int(Mod(Loan Amount,10^12) / 10^11), produces that integer and the 2nd parameter is the value returned for 0, the 3rd parameter, the value for 1 etc. You need some value after the first comma such as Choose(Int(Mod(Loan Amount,10^12) / 10^11) , "", "One Hundred" ...

           

          You will also need to put each subsequent value such as One Hundred in quotes as well.

          • 2. Re: Numer to Text (FM supplied calculation)
            DrWill

            Perfect!! Thank you!

             

            Wonder why FM doesn't fix their calculation to include the required text, so it's truely cut and paste as their instruction imply? Oh Well.

             

            Thanks Again

            • 3. Re: Numer to Text (FM supplied calculation)
              philmodjunk

              Where did you find this example? I couldn't find it in the Filemaker Help system. Is this from a knowledge base article here on line?

               

              I'd like to look at it and bring it to the attention of filemaker inc. folks if appropriate.

               

              By the commas, which are replaced with semi-colons in recent versions, it appears to be written a format appropriate for a much older version of filemaker.

              • 4. Re: Numer to Text (FM supplied calculation)
                DrWill

                Phil,

                On the Filemaker Pro 11 Help screen, I searched for "converting numbers to text" and it came up at the bottom of the search under Support articles. The answer ID at the bottom of the page is 2860.

                Thanks again for the help!

                Dave

                • 5. Re: Numer to Text (FM supplied calculation)
                  philmodjunk

                  This is a knowledge base article from the web--not a filemaker help article pulled up after pressing F1--which is why I couldn't find it earlier.

                   

                  I think you must have had some trouble copying and pasting from the article or in how you edited it after the paste.

                   

                  Pasted from the article:

                   

                  Choose(Int(Mod(NumberField,10^12) / 10^11), "", "One Hundred ", "Two Hundred ", "Three Hundred ", "Four Hundred ", "Five Hundred ", "Six Hundred ", "Seven Hundred ", "Eight Hundred ", "Nine Hundred ") &
                  If(Int(Mod(NumberField,10^11) / 10^10) = 1,

                   

                  Note the quotes that are there that weren't in the text you posted at the beginning of this thread.