Forum OpenACS Q&A: ad_form and currency widget

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 {
    project_id:key
    {name:text
        {label "Project Name"}
    }
{value:currency,to_sql(sql_number),to_html(display_currency)
        {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">
    <querytext>
      select name,
            value
        from my_projects
      where project_id = :project_id
    </querytext>
</fullquery>

Anybody knows what's wrong?

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".

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:

{price:currency,optional,to_sql(sql_number)
{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:

select
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.