Participants Database has a built-in field type called “Numeric” that is for the purpose of handling numeric field data. Normally, it will only work with integers (numbers without a decimal) but it isn’t hard to change that to a field that will accept decimal values, such as for a currency amount.
There are two things to change: the datatype as the data is stored in the database, and configuring the HTML form input so that it will accept decimal input.
Let me mention right here that it’s best to do all this before you start collecting data: changing the datatype on an active database can result in lost data in the field you’re changing if you change it to a type that is incompatible with the data that is already there. You’ve been warned!
Also please note that until version 1.7.5 is available, changing any part of the numeric field definition will “reset” the datatype, resulting in data loss in the numeric column. Don’t change the field definition once you’ve got data in the numeric field. This gets fixed in version 1.7.5
Changing the Datatype
First, your currency or decimal field should be set up in Participants Database as a “numeric” type field.
To change the datatype that is used to store the data, you will need to access your database directly so you can change a couple of values. You will need to use a database tool such as phpMyAdmin, which is usually available in your hosting control panel. If you don’t have access to something like this on your site, you’ll need to pass these instructions on to whoever handles the backend on your site.
Make sure you’re looking at the correct database for your WP site, then find the main Participants Database table (usually named wp_participants_database, but the “wp_” part could be different). You want the “structure” page for that table. Find the field that you want to put currency values in, you’ll see that it has a datatype of bigint(20), like the screenshot above.
For your typical currency field, you’ll want two places after the decimal and several places before it. You’ll need to decide how many places you need. For other decimal types, you’ll be setting these according to your specific need. When you click on the “change” pencil icon, you’ll get a little edit screen. Change the datatype to DECIMAL, and put your place allocation into the Length/Values field, as in the shot to the left here. Click “save” and the change is complete.
The configuration values of the DECIMAL field are (total length, number pf places after the decimal point) so, if you want to store values up to 99.99, you should use (4,2). In the number 99.99, the total number of numerals is 4 and the number of places after the decimal point is 2. I was initially confused about this, but this should make it clear what you need to do. Numbers outside the range determined here will be truncated.
For another example, if you wanted to store numbers up to 9999 with 4 places of decimals, you’d use 8,4 for the values. 8 as the total number of digits (on both sides of the decimal point) and 4 for the decimal part.
Configuring the HTML Form Element
Now that we have it set up so that you can store your decimal values, we need to configure the input element so it will accept the decimal values, too. The numeric element uses a HTML5 “number” input, which can be configured in various ways. Check this reference for all the details. What we need to do is add a “step” attribute that sets the minimum unit the input will accept…so for a “dollar” input, that would be one cent, or 0.01.
We set that “step” attribute in the field definition, which happens on the Manage Database Fields page in Participants Database. In your numeric field definition, in the “values” area, is where you put additional attributes, so for a currency-type field, you’d set the “step” attribute to 0.01 like this
step::0.01 and save the field definitions.
Now check your form, you will be able to enter your decimal values, and the “spinner” will increment cents.
Please note that currency symbols or units text will not be accepted into numeric field types. You should build that into your displays (such as in the help text) instead.