Editing Number Currency Rule

[AllegraSptbg]

Newbie here. Thanks for any help.

 

I currently have 10 fields within a database that I will need to convert to currency once imported. Is there anyway to modify the "Number to Currency" rule so that when the number = "0", "$0.00" is returned without affecting any of the numbers that do not = "0"?

[esmith]

Not quite sure I understand. Is this what you are trying to accomplish:

var amount = Field("YOUR FIELD");
var result = "$0.00";
if (amount != "0") result = "$" + FormatNumber("#,###.##", amount);
return result;

[step]

Convert the rule to JavaScript and replace the first line with this:

 

var Var1 = (Field("[color="Red"]YourField[/color]") == "0") ? "0.00" : Field("[color="red"]YourField[/color]");

 

Make sure you edit the portions highlighted in red with the name of the field you're intending to format.

[ThomasLewis]

Here's another one just to add my $0.02

 

return FormatNumber("$#,###.00", Field("Field Name").replace(/[^\d\.-]/g,''))

Edited January 31, 2013 by ThomasLewis

[AllegraSptbg]

Thanks Eric that worked perfectly.

 

Step,

 

When I tried adding your argument in the Number to Currency Javascript it returned everything as $0.00.

[AllegraSptbg]

Also, would there be any way to set up the script to add .00 after the number if comes in without the 2 decimal places as well?

[step]

Oh, I know why my code didn't work. It's because "Var1" is the field name not a variable called by the rule.

 

You can update Eric's code to do what you want by replacing the last two "#"'s in his formatting rule:

 

var amount = Field("YOUR FIELD");
var result = "$0.00";
if (amount != "0") result = "$" + FormatNumber("#,###.[color="Red"]00[/color]", amount);
return result;

 

Or you could just use the rule that Thomas posted.

[Dan Korn]

Oh, I know why my code didn't work. It's because "Var1" is the field name not a variable called by the rule.

 

You can update Eric's code to do what you want by replacing the last two "#"'s in his formatting rule:

 

var amount = Field("YOUR FIELD");
var result = "$0.00";
if (amount != "0") result = "$" + FormatNumber("#,###.[color=Red]00[/color]", amount);
return result;

Or you could just use the rule that Thomas posted.

Isn't that the same as simply this?

return FormatNumber("$#,###.00", Field("YOUR FIELD"));

That returns "$0.00" for a field value of "0". You don't need the "if/else" statement. You don't even need all the other complicated code in the "Number to Currency Rule" template.

Edited January 31, 2013 by Dan Korn
added dollar sign

[AllegraSptbg]

Thanks Step. Changing it to 00 worked. This may be a stupid question, but is there any way to import the rules that I created for this job into another job?

[AllegraSptbg]

This is an example of what I currently have:

 

var Var1 = "CALCULATION";

var Format = "Format1";

var CurrencySymbol = "Symbol1";

var amount = Field("CALCULATION");

var result = "$0.00";

if (amount != "0") result = "$" + FormatNumber("#,###.00", amount);

return result;

symbol = "";

symbol2 = "";

if (CurrencySymbol == "Symbol1")

{

symbol = "$";

symbol2 = "USD";

}

if (CurrencySymbol == "Symbol2")

{

symbol = "?";

symbol2 = "EUR";

}

if (CurrencySymbol == "Symbol3")

{

symbol = "£";

symbol2 = "GBP";

}

if (CurrencySymbol == "Symbol4")

{

symbol = "¥";

symbol2 = "YEN";

}

 

function SwitchDelimiter(x)

{

x = ReplaceSubstring(x, ".", "a");

x = ReplaceSubstring(x, ",", ".");

x = ReplaceSubstring(x, "a", ",");

return x;

}

 

if (Format == "Format1")

return symbol + FormatNumber("#,###.##", Field(Var1));

 

if (Format == "Format2")

return symbol + SwitchDelimiter(FormatNumber("#,###.##", Field(Var1)));

 

if (Format == "Format3")

return symbol + FormatNumber("####.##", Field(Var1));

 

if (Format == "Format4")

return symbol + SwitchDelimiter(FormatNumber("####.##", Field(Var1)));

 

if (Format == "Format5")

return symbol2 + "" + symbol + FormatNumber("#,###.##", Field(Var1));

 

if (Format == "Format6")

return symbol2 + "" + symbol + SwitchDelimiter(FormatNumber("#,###.##", Field(Var1)));

 

if (Format == "Format7")

return symbol2 + "" + symbol + FormatNumber("####.##", Field(Var1));

 

if (Format == "Format8")

return symbol2 + "" + symbol + SwitchDelimiter(FormatNumber("####.##", Field(Var1)));

 

if (Format == "Format9")

return symbol + FormatNumber("#,###.##", Field(Var1)) + "" + symbol2;

 

if (Format == "Format10")

return symbol + SwitchDelimiter(FormatNumber("#,###.##", Field(Var1))) + "" + symbol2;

 

if (Format == "Format11")

return symbol + FormatNumber("####.##", Field(Var1)) + "" + symbol2;

 

if (Format == "Format12")

return symbol + SwitchDelimiter(FormatNumber("####.##", Field(Var1))) + "" + symbol2;

[ThomasLewis]

You don't need any of that. Just replace it all with this:

 

return FormatNumber("$#,###.00", Field("CALCULATION").replace(/[^\d\.-]/g,''))

 

You don't technically need the .replace(/[^\d\.-]/g,'') bit, but it cleans the data so it's only fed numbers, periods, and hypens. I've had problems in the past when a comma was included in the number so I try to clean up the input.

[AllegraSptbg]

Thanks. I am starting to learn some of the fundamentals of JavaScript again. I was able to replace all of that junk script with just that one line.