Forum OpenACS Q&A: ad_form and currency widget

Posted by Robert Gaszewski on
I have problem with currency widget in ad_form.

When I add new record to db via ad_form, it works ok. But if I want edit existing record, field with currency widget is empty.

DB Field 'value' is type 'numeric'.

## project-edit.tcl

ad_form -name my_project -cancel_url "index" -form {
        {label "Project Name"}
        {label "Project value"}
    {format "$ 12 . 2"}
} -select_query_name project_query -new_data {
    db_1row do_insert {}
} -edit_data {
    db_dml do_update {}
} -after_submit {
    ad_returnredirect "index"

## project-edit.xql

<fullquery name="project_query">
      select name,
        from my_projects
      where project_id = :project_id

Anybody knows what's wrong?

Posted by Jon Griffin on
where is your value? You are missing that parameter.
i.e. {value {$myvalue}}
Posted by Robert Gaszewski on
It doesn't work :/

Imo error in my code is in declaration of form element "value" or/and in query "project_query". I think this form element should be filled automagically by ad_form thanks to query "project_query".

Posted by Jon Griffin on
Your field is named value, that is probably the problem.
For a tutorial see
Posted by Jack Ramsey on
This is only a couple of years late, but I was running into the same problem and figured out an answer.  Maybe someone else working with ad_form and currency will benefit from this answer.

In the database I created the following field:

price numeric(12,2)

In the -form part of ad_form I used the following:

{label "Price"}
{value {$price}}}

This should be fine to insert or update the price into the DB without conversion:

update db
set price = :price
where something = :this

When selecting the value to be displayed in the form you have to do some to_char trickery:

to_char(price, 'L 999999999999 . 99') as price
from db
where something = :this

Hope this saves someone some time.  It would have saved me a couple of hours.

Posted by Mark Aufflick on
And a couple of years later...

I always had a problem with this where 0 values would end up with 00 in the cents box and a decimal point in the dollar box, because the output of this postgres to_char format is:


which confuses a regexp somewhere.

All you need to do is tweak the format:

to_char(price, 'L 999999999990 . 99') as price

Note the last value is a 0 - that means that char should be 0 padded (ie. use a 0 instead of blank) which 9 defaults to only after the decimal place.