Jump to content

Editing Number Currency Rule


AllegraSptbg

Recommended Posts

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"?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Dan Korn
added dollar sign
Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...