"Best Practice" For Handling Currency-type of Fields ("$")

[terp]

I am trying to properly add and format a numeric field for dollar amounts/currency-type of entries.

I am assuming, given the use of $ in SQL, that is problematic, as when I tried to set the default of a text field to $ (to show the $ sign proceeds the numeric value), I get 0's in the db for that field and not the currency amount. Therefore, is there a "best practice" for handling currency?

I was going to just use a standard text box set to numeric for a currency field, but in the past, I have had some people enter the $ before the value, some put .00 after the value, etc, etc. I could give instructions in the tooltip (e.g., "Please do not use $ and use whole, real numbers" or something), but wondering how people go about handling currency entries, if this makes any sense.

I do like the $50.95 format to display in the table, as it looks better than 50.95, but not sure the best way to handle the field...welcome any suggestions!

 

[terp]

One thought I just had would be to use CSS (though I know know how) to preceed the text box displayed in the form with $ and then use .00 following the field...as text...so it would look like Please Enter Bid: $[field].00, so they know to only enter numbers...but that wouldn't solve the way the field is rendered in the table, unless there are some fancy dancy techniques to do that, as well.

 

[rob]

hi John

Could you try checking out the code from the SNV and updating your site with those files (details of how to do this are in my signature)
I think Hugh has recently fixed some issues with the default text, and I was certainly able to create a field elment with a default value of "$"

to do your "adding '.00' to the end of the field id suggest adding the following javascript action to your currency element:

new Element('span').appendText('.00').injectAfter(this.element);

that basically creates a new <span> element, inserts the text '.00' inside it and then injects the span element after the field (which is defined by this.element)

hope that makes sense
Rob

 

[terp]

hi John

Could you try checking out the code from the SNV and updating your site with those files (details of how to do this are in my signature)
I think Hugh has recently fixed some issues with the default text, and I was certainly able to create a field elment with a default value of "$"

to do your "adding '.00' to the end of the field id suggest adding the following javascript action to your currency element:

new Element('span').appendText('.00').injectAfter(this.element);

that basically creates a new <span> element, inserts the text '.00' inside it and then injects the span element after the field (which is defined by this.element)

hope that makes sense
Rob

Perfect, thanks, Rob. I've download the software and have had the SNV instructions open for days, so will take the plunge this morning and add the append text line...sound like just what I need.

For other numeric fields, though, would there also be a way to get SQL or Fabrik to not round off to the nearest whole number? Once the training wheels come off, I am going to try to have a field for latitude and longitude to pass to a Google Map extension and plot some of these data coming in from Fabrik...but initial tests suggested that all "49.99" entries are rounded to 50.

Is there a way to set it so these values are not truncated to 50, per se?

 

[terp]

I was certainly able to create a field elment with a default value of "$"

I can create a field element with a default of "$"; however, the table view will reflect the value being written in the database on submission: 0

For any entry with the $ preceeding the numeric value, 0 is being inserted in the DB, thus rendering a "0" in the table view...but does have the "$" as the default value of the text field. I also tried setting it to text, rather thqan numeric, but 0 was inserted this way, too...

You were able to get a record written with $XX into the db and displayed with the $ in the table view?

But I did download the latest and greatest SVN and it did, in fact, fic the sort issue I had with SEF enabled.

 

[rob]

hi John

your wish is my command (well i had all this done in the 2.0alpha code base, so it was relatively easy to back port into the 1.0.x SVN):
If you update from the SVN you will see that you field elements now have the following options:

Format Text / Integer / Decimal
Integer length
Decimal length
Format string

for your currency, select decimal, and enter 6 for the integer length and 2 for the decimal length.
For adding the $ before the table and readonly view, the format string would be

$ %s

(the %s gets replaced with the records data )
To put the $ before the forms field alter your javascript action to this:

new Element('span').appendText('$').injectBefore(this.element);

Enjoy

Rob

 

[terp]

To put the $ before the forms field alter your javascript action to this:

new Element('span').appendText('$').injectBefore(this.element);

Rob, thanks again; the format string worked perfectly and I downloaded the latest and greatest SVN and the decimals are godsend! (I won't push my luck and ask you to consider adding commas to integers over 3 digits (e.g., 1,000)).


I am not sure, however, how to go about adding the javascript action properly, as I added it to the element in the javascript tab, but didn't see an onsubmit or anything, so not sure which action to choose...tried half a dozen, but none seemed to work.

So I went in and reinserted the "$" in the default window, but that definately does not work right, as the field (and subsequent fields) will be blank in the table...have to remove the "$" to get the value inserted.

 

[cheesegrits]

The onload element should be the one you want.

-- hugh

 

[piri314]

Hello,

Is that possible to modify the "." for the decimals by a "," and to apply a kind of style for the big numbers : ex. 2609890.50 > 2 609 890,50 ?

Thank you,
PIRINA

 

[rob]

hi
Only by creating a custom template

the bit of PHP code you would need to use is something like this:

echo number_format( $row->tablename___elementname, 2, ',', ' ' );

 

[piri314]

Thank for the reply, but I'm really lost with the PHP code :

where to put your instructions ? (this is a custom form "projets" template" based on the "mint" one)

<?php /**
* @package fabrik
* @version 1.0.4
* @Copyright (C) Rob Clayburn
* @license GNU/GPL http://www.gnu.org/copyleft/gpl.html
*/
/* MOS Intruder Alerts */
defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' );
?>
<style type="text/css">
<!--
@import url("template.css");
-->
</style>


<h1><?php echo $this->form->title;?></h1>
<?php echo $this->topJS;
echo $this->form->intro;?>
<?php echo $this->form->error;?>
<form class="fabrikForm" action="<?php echo $this->form->action;?>"
<?php echo $this->form->js;?> method="post"
name="<?php echo $this->form->name;?>"
enctype="<?php echo $this->form->encType;?>"><?php if($this->showEmail){
echo $this->emailLink;
}?> <?php if($this->showPDF){
echo $this->pdfLink;
}?> <?php if($this->showPrint){
echo $this->printLink;
}?>

<?php foreach( $this->groups as $group ){?>
<h3><?php echo $group->title;?></h3>
<?php if($group->canRepeat){?>

<?php }?>
<div class="fb_group" id="group<?php echo $group->id;?>" style="<?php echo $group->css;?>">
<table>
<?php if($group->canRepeat){
$subgroupCounter = 0;
foreach($group->subgroups as $subgroup){
if($group->canRepeat && $this->task != 'viewTableRowDetails' ){?>
<div><a class="addGroup" href="#" id="<?php echo $group->addId;?>"><?php echo _ADD;?></a> | <a class="deleteGroup" href="#" id="<?php echo $group->delId;?>"><?php echo _DELETE;?></a></div>
<?php }?>
<div class="fb_sub_group" id="subgroup<?php echo $group->id . "_" . $subgroupCounter;?>">
<?php foreach( $subgroup as $element ){?>
<?php if($element->error != ''){?>
<tr class="fb_element" id="fb_element_<?php echo $element->int;?>">
<td colspan="2">
<div class="fabrikerror"><?php echo $element->error;?></div>
</td>
</tr>
<?php }?>
<tr class="fb_element">
<td width="100" valign="top"><?php echo $element->label;?></td>
<td><?php echo $element->element;?></td>
</tr>
<?php }?>
</div>
<?php $subgroupCounter ++;
} ?>
<?php }else{?>
<?php foreach( $group->elements as $element ){?>
<?php if($element->error != ''){?>
<tr class="fb_element">
<td colspan="2">
<div class="fabrikerror"><?php echo $element->error;?></div>
</td>
</tr>
<?php }?>
<tr class="fb_element" id="fb_element_<?php echo $element->int;?>">
<td width="100" valign="top"><?php echo $element->label;?></td>
<td><?php echo $element->element;?></td>
</tr>
<?php }
}?>
</table>
</div>
<?php }?>

<?php echo $this->hiddenFields;?>
<div class="fabrikActions"><?php echo $this->form->resetButton;?> <?php echo $this->form->submitButton;?>
</div>
</form>
<?php echo $this->jsActions; ?>

 

[cheesegrits]

Rob was talking about a table template.

I don't think there's any easy (or even hard) way of doing this in a form template. It would require changes to the Fabrik code itself. We'd have to add a new Element type, "currency", and provide full support for currency symbols and number format.

I don't know if this will happen in 1.04. I believe it is on the roadmap for 2.0.

-- hugh