mantral Posted January 25, 2008 Share Posted January 25, 2008 Hi Guys I have to write to a report my grand total example $1500.00 in text form. Like "One thousand five hundred" is there a way of doing this. thanks Mantral Link to comment Share on other sites More sharing options...
AHunter3 Posted January 25, 2008 Share Posted January 25, 2008 There's a nice custom function for that floating around; I see you do not have Filemaker Advanced so you can't used CFs yourself, but I'll see if I can find it and, if it's not recursive, you could use the same code in a plain-vanilla calc field. Found it, on Brian Dunning's site. And although it's long and cumbersome-looking, it's not recursive so you can use it as an ordinary field definitionl http://www.briandunning.com/cf/514 Replace the word "NumberField" with your actual field name, other than that just paste that whole thing in as your field definition. Calc field, result type = "text". Link to comment Share on other sites More sharing options...
mantral Posted January 25, 2008 Author Share Posted January 25, 2008 Thanks for the instantaneous reply Ahunter. But I do have the advanced version pro 9. My program is advance but my brain is not.smiley-smile thanks again Mantral Link to comment Share on other sites More sharing options...
AHunter3 Posted January 25, 2008 Share Posted January 25, 2008 In that case you've got a choice, and either way should work. Link to comment Share on other sites More sharing options...
mantral Posted January 25, 2008 Author Share Posted January 25, 2008 Hi Guys, Three ThousandOne Hundred Dollars this is what I get in one of my test with the moneytowords calculation. The calculation is gigantic it is giving me a headache alreadysmiley-frown Please advise where I can find the part of the calculation that could put a space between the word Thousand and One. thanks Link to comment Share on other sites More sharing options...
AHunter3 Posted January 25, 2008 Share Posted January 25, 2008 Odd. The exact number you input? 3100? The fx does indeed appear to be a bit buggy, I get this: Three Thousand, One Hundred and Dollars I'm inclined to dive in and see if I can improve it a bit but I'd like to know what number you input that resulted in the run-together 'ThousandOne' ?? Link to comment Share on other sites More sharing options...
mantral Posted January 25, 2008 Author Share Posted January 25, 2008 Hi Ahunter3; The exact number is 3100. thanks Link to comment Share on other sites More sharing options...
AHunter3 Posted January 25, 2008 Share Posted January 25, 2008 I edited the formula, the original author had & Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(NumberField / 10)>0;"and ";"") down towards the bottom, the last "and" prior to dealing with "dollars" or "cents" I changed it to & Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(Mod(NumberField;10))>0 ;"and ";"") and I no longer get "Three Thousand One Hundred and Dollars" I do not, however, get your absence of a space. Don't know why you're getting that. Here's the final formula I ended up with, it doesn't seem to break: 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 ") & Case(Int(Mod(NumberField;10^12) / 10^11)and Int(Mod(NumberField;10^10) / 10^9)>0;"and ";"") & If(Int(Mod(NumberField;10^11) / 10^10) = 1; Choose(Int(Mod(NumberField;10^10) / 10^9); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;10^11) / 10^10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10^10) / 10^9); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Int(Mod(NumberField;10^12)/10^9) and Int(Mod(NumberField;10^9) / 10^3); "Billion, "; Int(Mod(NumberField;10^12)/10^9);"Billion ";"") & Choose(Int(Mod(NumberField;10^9) / 10^8); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") // & Case(Int(Mod(NumberField;10^9) / 10^8)>0 and Int(Mod(NumberField;10^8) / 10^7)>0;"and ";"") // & If(Int(Mod(NumberField;10^8) / 10^7) = 1; Choose(Int(Mod(NumberField;10^7) / 10^6); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;10^8) / 10^7); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10^7) / 10^6); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Int(Mod(NumberField;10^9)/10^6) and Int(Mod(NumberField;10^6) / 10^3); "Million, "; Int(Mod(NumberField;10^9)/10^6);"Million ";"") & Choose(Int(Mod(NumberField;10^6) / 10^5); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") // & Case(Int(Mod(NumberField;10^6) / 10^5)>0 and Int(Mod(NumberField;10^5) / 10^4)>0;"and ";"") // & If(Int(Mod(NumberField;10^5) / 10^4) = 1; Choose(Int(Mod(NumberField;10^4) / 10^3); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;10^5) / 10^4); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10^4) / 10^3); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & // Case(Int(Mod(NumberField;10^6)/10^3) and Int(Mod(NumberField;10^3) /10^0); "Thousand" & Case(Int(Mod(NumberField;10^3) / 100)=0;" and ";", "); Int(Mod(NumberField;10^6)/10^3);"Thousand ";"") // & Choose(Int(Mod(NumberField;10^3) / 100); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") & Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(Mod(NumberField;10))>0 ;"and ";"") & If(Int(Mod(NumberField;100) / 10) = 1; Choose(Int(Mod(NumberField;10)); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;100) / 10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10)); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Int(NumberField) > 1; "Dollars"; Int(NumberField) = 1; "Dollar"; "") & If(Mod(NumberField; 1) and Int(NumberField);" and ";"") & If(Mod(Int(NumberField*10); 10) = 1; Choose(Mod(Int(NumberField*100); 10); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Mod(Int(NumberField*10); 10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Mod(Int(NumberField*100); 10); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Mod(NumberField; 1) > .01; "cents"; Mod(NumberField; 1) = .01; "cent";"") Link to comment Share on other sites More sharing options...
mantral Posted January 26, 2008 Author Share Posted January 26, 2008 Hi Ahunter3, Got it and will try it this time. Looks promising... I do not know how it works or the mechanics of the codes but it is doing it's job. You are just overwhelmed by the size of it but it works. Thanks a lot for your time and patient's. Mantral Link to comment Share on other sites More sharing options...
Dickh Posted January 27, 2008 Share Posted January 27, 2008 AHunter3 I'm lost as to where you add this information to convert number to text? I've been using FMP for years with little knowledge of it super powers. Can you help me? I edited the formula, the original author had down towards the bottom, the last "and" prior to dealing with "dollars" or "cents" I changed it to and I no longer get "Three Thousand One Hundred and Dollars" I do not, however, get your absence of a space. Don't know why you're getting that. Here's the final formula I ended up with, it doesn't seem to break: 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 ") & Case(Int(Mod(NumberField;10^12) / 10^11)and Int(Mod(NumberField;10^10) / 10^9)>0;"and ";"") & If(Int(Mod(NumberField;10^11) / 10^10) = 1; Choose(Int(Mod(NumberField;10^10) / 10^9); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;10^11) / 10^10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10^10) / 10^9); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Int(Mod(NumberField;10^12)/10^9) and Int(Mod(NumberField;10^9) / 10^3); "Billion, "; Int(Mod(NumberField;10^12)/10^9);"Billion ";"") & Choose(Int(Mod(NumberField;10^9) / 10^8); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") // & Case(Int(Mod(NumberField;10^9) / 10^8)>0 and Int(Mod(NumberField;10^8) / 10^7)>0;"and ";"") // & If(Int(Mod(NumberField;10^8) / 10^7) = 1; Choose(Int(Mod(NumberField;10^7) / 10^6); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;10^8) / 10^7); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10^7) / 10^6); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Int(Mod(NumberField;10^9)/10^6) and Int(Mod(NumberField;10^6) / 10^3); "Million, "; Int(Mod(NumberField;10^9)/10^6);"Million ";"") & Choose(Int(Mod(NumberField;10^6) / 10^5); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") // & Case(Int(Mod(NumberField;10^6) / 10^5)>0 and Int(Mod(NumberField;10^5) / 10^4)>0;"and ";"") // & If(Int(Mod(NumberField;10^5) / 10^4) = 1; Choose(Int(Mod(NumberField;10^4) / 10^3); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;10^5) / 10^4); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10^4) / 10^3); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & // Case(Int(Mod(NumberField;10^6)/10^3) and Int(Mod(NumberField;10^3) /10^0); "Thousand" & Case(Int(Mod(NumberField;10^3) / 100)=0;" and ";", "); Int(Mod(NumberField;10^6)/10^3);"Thousand ";"") // & Choose(Int(Mod(NumberField;10^3) / 100); ""; "One Hundred "; "Two Hundred "; "Three Hundred "; "Four Hundred "; "Five Hundred "; "Six Hundred "; "Seven Hundred "; "Eight Hundred "; "Nine Hundred ") & Case(Int(Mod(NumberField;10^3) / 100)>0 and Int(Mod(NumberField;10))>0 ;"and ";"") & If(Int(Mod(NumberField;100) / 10) = 1; Choose(Int(Mod(NumberField;10)); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Int(Mod(NumberField;100) / 10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Int(Mod(NumberField;10)); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Int(NumberField) > 1; "Dollars"; Int(NumberField) = 1; "Dollar"; "") & If(Mod(NumberField; 1) and Int(NumberField);" and ";"") & If(Mod(Int(NumberField*10); 10) = 1; Choose(Mod(Int(NumberField*100); 10); "Ten "; "Eleven "; "Twelve "; "Thirteen "; "Fourteen "; "Fifteen "; "Sixteen "; "Seventeen "; "Eighteen "; "Nineteen "); Choose(Mod(Int(NumberField*10); 10); ""; ""; "Twenty "; "Thirty "; "Forty "; "Fifty "; "Sixty "; "Seventy "; "Eighty "; "Ninety ") & Choose(Mod(Int(NumberField*100); 10); ""; "One "; "Two "; "Three "; "Four "; "Five "; "Six "; "Seven "; "Eight "; "Nine ")) & Case(Mod(NumberField; 1) > .01; "cents"; Mod(NumberField; 1) = .01; "cent";"") Link to comment Share on other sites More sharing options...
AHunter3 Posted January 27, 2008 Share Posted January 27, 2008 a) Copy the above formula to your clipboard b) Assuming that your number field in your database has a name different from "Numberfield", paste the above formula into a text editor or a word processor that has a Find All/Replace function; and replace all occurences of NumberField with the appropriate field name. c) Go into Define Fields. Create a calculation field. Result type needs to be text (set that down at the bottom under the formula area. Pase from clipboard to insert the formula. Click "OK". OR, alternative, if you wish to use it as a custom function, Open Define Custom Functions. Click the button to create a new one. You'll have two places you can enter info, aside from where you enter the custom function's name. Start at the top, which is where you enter the named parameters. Type the string "NumberField" and click the "+" to add it to the list of parameters. Then paste the formula into the big formula area. That's it, you've inserted it as a custom function. The original author named this function "MoneyToWords". Assuming you keep that as the custom function's name, you would deploy it anywhere in this file that you need it, like so MoneyToWords(AnyFieldName) or for that matter MoneyToWords (Invoice::Subtotal + Invoice::AdjustedTax - Invoice::Discount) Link to comment Share on other sites More sharing options...
Jack Rodgers Posted January 27, 2008 Share Posted January 27, 2008 The last line should be: Case(Mod(CHECK Amount; 1) > .01; "Cents"; Mod(CHECK Amount; 1) = .01; "Cent";" and Zero Cents") Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts