Forum Discussion
UrsulaLl
6 years agoQrew Assistant Captain
I made some modifications to this calculation so that it could update the teens. I am SURE there are better ways to do this, but I needed it quickly and I didn't want to waste any time cleaning it up. Posting here in case someone else needs it.
var number Value = Round([xxTHENUMERICFIELDxx],0.01);
var text Hundreds=Right(ToText(Int($Value)),3);
var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));
var text Millions = If($Value>=1000000,ToText(Int($Value/1000000)));
var text numt = ToText(Floor([xxTHENUMERICFIELDxx]));
var text cents = If(Contains(ToText([xxTHENUMERICFIELDxx]), "."), Left(Right(ToText([xxTHENUMERICFIELDxx]) & "00", "."), 2),"");
var text centDecimals = If(Contains(ToText([xxTHENUMERICFIELDxx]), "."),Right(ToText(Int($value * 100)),2));
var text teensnumcents = If($centDecimals<>"", " and " & List("/",
$centDecimals,"100")," and No/100") & " Dollars" ;
var text firstNum = Case(Floor(ToNumber(Right($numt,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, "");
var text secondNum = Case(ToNumber(Right($numt,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, "");
var text thirdNum = Case(Floor(ToNumber(Right($numt,3))/100),
1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, "");
var text firstThouNum = If($Thousands<>"",Case(Floor(ToNumber(Right($Thousands,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),"");
var text secondThouNum = If($Thousands<>"",Case(ToNumber(Right($Thousands,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));
var text thirdThouNum = Case(Floor(ToNumber(Right($Thousands,3))/100),
1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, "");
var text firstMilNum = If($Millions<>"",Case(Floor(ToNumber(Right($Millions,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),"");
var text secondMilNum = If($Millions<>"",Case(ToNumber(Right($Millions,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));
var text thirdMilNum = Case(Floor(ToNumber(Right($Millions,3))/100),
1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, "");
var text tensNum = List("-", $firstNum, $secondNum);
var text thouNum = List("-", $firstThouNum, $secondThouNum);
var text millNum = List("-", $firstMilNum, $secondMilNum);
var text teensCon = If(Contains($tensNum,"ten-")=true,Case($tensNum,
"Ten-One", "Eleven",
"Ten-Two", "Twelve",
"Ten-Three", "Thirteen",
"Ten-Four", "Fourteen",
"Ten-Five", "Fifteen",
"Ten-Six", "Sixteen",
"Ten-Seven", "Seventeen",
"Ten-Eight", "Eighteen",
"Ten-Nine", "Nineteen", $tensNum),$tensNum);
var text thouCon = If(Contains($thouNum,"ten-")=true,Case($thouNum,
"Ten-One", "Eleven",
"Ten-Two", "Twelve",
"Ten-Three", "Thirteen",
"Ten-Four", "Fourteen",
"Ten-Five", "Fifteen",
"Ten-Six", "Sixteen",
"Ten-Seven", "Seventeen",
"Ten-Eight", "Eighteen",
"Ten-Nine", "Nineteen", $thouNum),$thouNum);
var text millCon = If(Contains($millNum,"ten-")=true,Case($millNum,
"Ten-One", "Eleven",
"Ten-Two", "Twelve",
"Ten-Three", "Thirteen",
"Ten-Four", "Fourteen",
"Ten-Five", "Fifteen",
"Ten-Six", "Sixteen",
"Ten-Seven", "Seventeen",
"Ten-Eight", "Eighteen",
"Ten-Nine", "Nineteen", $millNum),$millNum);
var text teens = $teensCon;
var text Thouteens = If($thouNum<>"" and [xxTHENUMERICFIELDxx]>999, " ", "") & $thouCon;
var text Milteens = If($millNum<>"" and [xxTHENUMERICFIELDxx]>999999, " ", "") & $millCon;
var text hundredsNum = $thirdNum &" "& $teens;
var text hundredsThouNum = $thirdThouNum &" "& $thouCon;
var text hundredsMillNum = $thirdMilNum &" "& $millCon;
var text finalHund = $hundredsNum;
var text finalThou = $hundredsThouNum & " Thousand";
var text finalMill = $hundredsMillNum & " Million";
//var text finalThouOne = if(Contains($finalThou,"Ten-",$Thouteens,$finalThou));
If($millNum<>"" and [xxTHENUMERICFIELDxx]>999999, $finalMill, "")&" "&
If($thouNum<>"" and [xxTHENUMERICFIELDxx]>999,$finalThou, "")&" "&
If($tensNum<>"" and [xxTHENUMERICFIELDxx]>99, $finalHund, $teens) &
if(nz([xxTHENUMERICFIELDxx])<>0,$teensnumcents,"")
var number Value = Round([xxTHENUMERICFIELDxx],0.01);
var text Hundreds=Right(ToText(Int($Value)),3);
var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));
var text Millions = If($Value>=1000000,ToText(Int($Value/1000000)));
var text numt = ToText(Floor([xxTHENUMERICFIELDxx]));
var text cents = If(Contains(ToText([xxTHENUMERICFIELDxx]), "."), Left(Right(ToText([xxTHENUMERICFIELDxx]) & "00", "."), 2),"");
var text centDecimals = If(Contains(ToText([xxTHENUMERICFIELDxx]), "."),Right(ToText(Int($value * 100)),2));
var text teensnumcents = If($centDecimals<>"", " and " & List("/",
$centDecimals,"100")," and No/100") & " Dollars" ;
var text firstNum = Case(Floor(ToNumber(Right($numt,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, "");
var text secondNum = Case(ToNumber(Right($numt,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, "");
var text thirdNum = Case(Floor(ToNumber(Right($numt,3))/100),
1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, "");
var text firstThouNum = If($Thousands<>"",Case(Floor(ToNumber(Right($Thousands,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),"");
var text secondThouNum = If($Thousands<>"",Case(ToNumber(Right($Thousands,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));
var text thirdThouNum = Case(Floor(ToNumber(Right($Thousands,3))/100),
1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, "");
var text firstMilNum = If($Millions<>"",Case(Floor(ToNumber(Right($Millions,2))/10),
1, "Ten", 2, "Twenty", 3, "Thirty", 4, "Forty", 5, "Fifty", 6, "Sixty", 7, "Seventy", 8, "Eighty", 9, "Ninety", 0, ""),"");
var text secondMilNum = If($Millions<>"",Case(ToNumber(Right($Millions,1)),
1, "One", 2, "Two", 3, "Three", 4, "Four", 5, "Five", 6, "Six", 7, "Seven", 8, "Eight", 9, "Nine", 0, ""));
var text thirdMilNum = Case(Floor(ToNumber(Right($Millions,3))/100),
1, "One Hundred", 2, "Two Hundred", 3, "Three Hundred", 4, "Four Hundred", 5, "Five Hundred", 6, "Six Hundred", 7, "Seven Hundred", 8, "Eight Hundred", 9, "Nine Hundred", 0, "");
var text tensNum = List("-", $firstNum, $secondNum);
var text thouNum = List("-", $firstThouNum, $secondThouNum);
var text millNum = List("-", $firstMilNum, $secondMilNum);
var text teensCon = If(Contains($tensNum,"ten-")=true,Case($tensNum,
"Ten-One", "Eleven",
"Ten-Two", "Twelve",
"Ten-Three", "Thirteen",
"Ten-Four", "Fourteen",
"Ten-Five", "Fifteen",
"Ten-Six", "Sixteen",
"Ten-Seven", "Seventeen",
"Ten-Eight", "Eighteen",
"Ten-Nine", "Nineteen", $tensNum),$tensNum);
var text thouCon = If(Contains($thouNum,"ten-")=true,Case($thouNum,
"Ten-One", "Eleven",
"Ten-Two", "Twelve",
"Ten-Three", "Thirteen",
"Ten-Four", "Fourteen",
"Ten-Five", "Fifteen",
"Ten-Six", "Sixteen",
"Ten-Seven", "Seventeen",
"Ten-Eight", "Eighteen",
"Ten-Nine", "Nineteen", $thouNum),$thouNum);
var text millCon = If(Contains($millNum,"ten-")=true,Case($millNum,
"Ten-One", "Eleven",
"Ten-Two", "Twelve",
"Ten-Three", "Thirteen",
"Ten-Four", "Fourteen",
"Ten-Five", "Fifteen",
"Ten-Six", "Sixteen",
"Ten-Seven", "Seventeen",
"Ten-Eight", "Eighteen",
"Ten-Nine", "Nineteen", $millNum),$millNum);
var text teens = $teensCon;
var text Thouteens = If($thouNum<>"" and [xxTHENUMERICFIELDxx]>999, " ", "") & $thouCon;
var text Milteens = If($millNum<>"" and [xxTHENUMERICFIELDxx]>999999, " ", "") & $millCon;
var text hundredsNum = $thirdNum &" "& $teens;
var text hundredsThouNum = $thirdThouNum &" "& $thouCon;
var text hundredsMillNum = $thirdMilNum &" "& $millCon;
var text finalHund = $hundredsNum;
var text finalThou = $hundredsThouNum & " Thousand";
var text finalMill = $hundredsMillNum & " Million";
//var text finalThouOne = if(Contains($finalThou,"Ten-",$Thouteens,$finalThou));
If($millNum<>"" and [xxTHENUMERICFIELDxx]>999999, $finalMill, "")&" "&
If($thouNum<>"" and [xxTHENUMERICFIELDxx]>999,$finalThou, "")&" "&
If($tensNum<>"" and [xxTHENUMERICFIELDxx]>99, $finalHund, $teens) &
if(nz([xxTHENUMERICFIELDxx])<>0,$teensnumcents,"")
UrsulaLl
6 years agoQrew Assistant Captain
quick head's up. My coworkers were able to break this, it does not seem to work if the field being calculated on has more than 10 digits. So if you have a number with a decimal point, it will begin rounding or cutting off the last digit. You can play around to find a good solution, I decided to create two formula fields, one for the decimal, and one for the whole number and then combine the text in a third formula field. good luck!