How do I validate a currency field not to have a dollar sign ($) ?

There are a couple ways to do this.

1. You could use a validation rule on the text field:

contains({Self},"$")

2. Or, the validation could be set in the control's settings using regex:

The problem with these two types of field validation is that they will not fire until the user submits the form. Once they do that, the form will display all errors for the form.

3. If you want the validation to be as soon as the user exits the field, you could use JavaScript.

I don't have the JavaScript code at the moment, but if you would like it, let me know and I'll whip something up. There are many options with it. You could fire the error message on each keypress or only when the exit the field. And then you have options for how you can display the error too.

Hi Chad,

Thank you!  Yes, please, if you would be able to provide a javascript snippet that fires as soon as the field is exited, along with a pop up message explaining the error, I would be very appreciative! :-)

if you'd like a non-javascript solution, you could update your formula like

1*replace(sourceControl,"\$","")

so that it removes  $ sign and calculates  correctly regardless it's there or not

Hi Emha,

Thank you for replying.  I have a few questions.  Where are you placing this formula, in a rule, under formatting?  I am assuming that "sourceCode" in your example is a variable?  If so, why would you not use the "named" field?

I tried applying this formula under a rule, under formatting and it did not work.  Perhaps I do not understand where this formula should be applied?

yes, "sourceControl" is meant a named control that holds your currency value and that appears in your validation formula.

you should use my snippet in place of your "sourceControl" reference.

so if eg. your original validation formula looks like 

sourceControl < 100

it should now look like

1*replace(sourceControl,"\$","") < 100

It is not behaving as expected.  See below.  Now the validation flags the field, regardless if there is a dollar sign or not.

1. validation error at the right-most picture doesn't seem to be caused by this rule. if it was there would be an error message just like on middle one

2. your validation formula doesn't seem to be correct. validation formula has to evaluate to boolean TRUE/FALSE. your formula evaluates to a number entered in ShipmentCost field. and anything but zero is regarded as TRUE, hence you always get validation error for a number different from zero.

if you want to have a validation formula which just check for presence of $ sign, you'd better use contain() runtime function.

1. the validation error message was the same across, I just did not display it twice.  I disgaree, the error message is indeed being caused by the validation.

2. "if you want to have a validation formula which just check for presence of $ sign"  That is the question that I was asking.  The $ sign is interfering with the calculation of the "Total" field below.

I think I will wait for Chad to provide the javascript snippet.  Thank you for trying to help.

then you should apply the same to the 'Total' calculation formula (and you do not need validation rule that check $ occurence)

eg.

1*replace(ShipmentCost,"\$","") + 1*replace(TaxCost,"\$","")

Do you mean add it here?

first, your formula is bit overdone, you need not first to add up 3 figures to a total and then sum() over the total...

but yes, you should update this formula as mentioned above. so it might look like 

1*replace(SubTotal,"\$","") + 1*replace(ShipmentCost,"\$","") + 1*replace(TaxCost,"\$","")

or, if if you want to make it cleaner and more understandable, you could create a form variables like 

SubTotalClean = 1*replace(SubTotal,"\$","")

ShipmentCostClean = 1*replace(ShipmentCost,"\$","")

TaxCostClean = 1*replace(TaxCost,"\$","")

and then simply add up form variables into a total

SubTotalClean +ShipmentCostClean +TaxCostClean 

I apologize for taking so long to get back to you.

Also, @emha's solution is much better so, definitely use that.

However, I did say that I would include the JavaScript so, in case it's helpful others in the future, I'll still add it here.

First, here is the layout of my form:

Make sure to set a Client ID JavaScript Variable Name:

On the error message label, set a CSS Class:

These steps setup your controls to be used by the JavaScript.

Lastly, paste the following code into the Form Settings Custom JavaScript field:

NWF$('.lblErrorMsg').css('visibility', 'hidden');
NWF$('#' + Currency).on("change keyup",function(){	
	if(NWF$('#' + Currency).val().includes('$')){
		NWF$('.lblErrorMsg').css('visibility', 'visible');
		NWF$('#' + Currency).css('border','2px solid red');
	} else {
		NWF$('.lblErrorMsg').css('visibility', 'hidden');
		NWF$('#' + Currency).css('border','1px solid #ababab');
	}
});

And here it is in action:

The code allows for someone pasting a value into the field as well as typing it.

I should note that this code does not stop the form from being submitted. You would need to add in code to disable the Save button.

Hello Dina,

On your Sharepoint list, you can change the field to not be a currency field but only a number field.  This will remove the need for javascript and will prompt the user that no special characters are allowed.

In your calculated fields you can add the prefix $ to display the value as dollar.

Let me know if this is unclear.

Kind Regards,

Francois Crous

---

@emha wrote:

1*replace(SubTotal,"\$","") + 1*replace(ShipmentCost,"\$","") + 1*replace(TaxCost,"\$","")

This is a good solution but there may be a few cases where it results in an error.

If someone enters a comma in the field i.e. $2,000.00, the comma won't be removed and it will cause an error in the math operation. Another instance I had issues in testing this was when the field was connected to a list field with no default value. That seems to cause an error that prevents the form from loading.

The following formula will strip anything except for numbers and the decimal point and doesn't error when there is no default value.

sum([replace(SubTotal,"[^0-9.]",''),replace(ShipmentCost,"[^0-9.]",''),replace(TaxCost,"[^0-9.]",'')])

This is a good solution but there may be a few cases where it results in an error.

@bzebarth, there're not few but plenty of cases when it may error out...

it addresses just the problem asked.

numeric formats are very different in different locales/regions, some eg. use coma as decimal separator, some use single apostrophe, etc. so your formula is as well valid just for some specific numeric format(s).

as well, one might need to sum up negative numbers, which wouldn't work with your formula either :smileyhappy:

@emha I didn't mean to suggest my solution was perfect just that it addressed a couple of issues I ran into into testing the one you proposed. Using the sum function instead of multiplying by 1 and using the addition operator seems to be a little more fault tolerant, at least in my testing, there may be other cases where its the other way around. :smileyhappy:

You are right about the negative numbers so this should address that.

sum([replace(SubTotal,"[^0-9.-]",''),replace(ShipmentCost,"[^0-9.-]",''),replace(TaxCost,"[^0-9.-]",'')])

Hi Emha,

what happen if you just want to validate the filed based on the value, for instance if I want only the value more than $100 will be acceptable, how do you show this.

Regards