Given the form below, how can I show the Unit Price in currency format, but store the actual value in the database as a number:
The Extension is a calculation of the Unit Price * Quantity, so I can’t format it with the Currency formatter because that would be a text value and I think that would try to send the formatted value to the server anyway. Anyone know how to display it as currency but still retain the numeric value.
What value do you want to store in your database, for example: 190 or 190.00?
It could be either as long as it’s a numeric value and can be used in calculations. In my case it’s the 190.00 example.
You can store the values in the database like that, then on the page you can convert to number using the data formatters.
For displaying data on a page that works fine, but when you’re working with inputs on forms and editing and submitting data, the formatting gets in the way. The dollar symbol is part of the value when the input takes focus, as well as when the form is submitted. Then the database rejects the submit because it doesn’t accept a non-numeric value.
I’ve seen forms where the value is displayed in an input as currency, but when the input receives focus, it’s just a number. The formatting disappears. I was looking for something like that.
Well, you can use some hidden field for the numeric data and submit it instead. Otherwise you can use the formatter on insert in the database to format the value as you need it.
I’m playing with the on focus and on blur events to see if I can manipulate the values that way. I’m not sure what function to use to convert it back to a number from currency. The .toNumber() function returns NaN so that doesn’t work. Is there another function that would work?
Not sure why would you want to do anything on blur. You can just cobvert the values on inserting in the db.
Because I want to remove the currency formatting when the control gets focus and then add it again when it looses focus. I’ll have to also convert it at the db.
I don’t understand what you are trying to do. You can do whatever you like with that value when inserting it in the database.
Dan, I have used this method a few times to display something in a text field but submit a different value.
This may well be quite irrelevant, but I don’t remember having this problem, despite having created umpteen forms including prices over the years. I don’t think I would ever include the currency symbol within a field.
Would it not be an option to put the symbol outside the field or perhaps use Bootstrap’s input-group-prepend class, eg:
?
I've never figured out how to do that in Wappler. I do like that option.
That’s certainly an option @TomD and may just be the best one. I was just exploring what the options were and seeing what I could get to work.
On using a hidden field, I understand the concept but I’m not sure how you would get the users input to the hidden field so the correct value would be submitted to the database. Otherwise the changes the user made to the field would be lost. In order to do that, you’re going to have to manipulate the data somehow, so don’t know if there is much advantage. But maybe I’m missing something.
I was able to get it to work using the on focus and on blur events:
dmx-on:focus="inp_UnitPrice.setValue((inp_UnitPrice.value.replace('$','').replace(',','')).toNumber()).toFixed(2))"
dmx-on:blur="inp_UnitPrice.setValue((inp_UnitPrice.value.toNumber()).formatCurrency('$', '.', ',', 2))"
Still have to deal with the value when it gets to the database, but it’s working on the client as expected. If you want to see it, go to:
https://cam-testing.azurewebsites.net/orders
And click any row to edit, then click the Details tab. The price column shows the currency format but when you click into the field, it changes it to numeric and when you tab back out, it changes it back to currency. The only thing I don’t like about it is, I had to use type of text on the input and that allows any alpha numeric character to be typed into the field. Not sure how to get around that, so don’t know if this will be my final solution or not.
It would be nice if the option were built into Wappler, but it’s quite easy to add, following the docs here.
As long as the hidden field is assigned the relevant name, there shouldn't be a problem submitting the correct field:
<form id="form1">
<input id="inp_hidden" name="submit_me" type="hidden" class="form-control" dmx-bind:value="inp_display.value">
<input id="inp_display" type="text" class="form-control">
<button id="btn1" class="btn" type="submit">submit</button>
</form>Oh, I see, you’re binding the value of the hidden field to the value of the visible one. That could work.
I think it's the simplest solution.