Forum OpenACS Q&A: ad_form and dates

Collapse
Posted by Cathy Sarisky on
Hi All,

This is the first time I've played with ad_form. Mostly, it's neat, but dates are making me a little bit crazy. (Ok, after about 4 hours of fighting with dates, dates are making me REALLY crazy.) This is the 4.6.2-beta tarball, with PostgreSQL 7.2.3.

Here's what's in the db:

select * from ptk_reports ;
report_id | user_id | report_text | filed_date | reported_date 
-----------+---------+-------------+------------+---------------
        13 |         | new         |            | 1988-02-07

Here's the ad_form code:
ad_page_contract {

        Simple add/edit form

    } {
        report_id:optional
        {reported_date ""}
    }

ad_form -name ptk_report -form {

report_id:key(ptk_report_seq)

{report_text:text(textarea)             {label "Activity"}  {html {rows 4 cols 50}}}

{reported_date:date,to_sql(linear_date_no_time),from_sql(linear_date_no_time)
    {label "Date of activity"}
    {format "MONTH DD YYYY"}
    {value {$reported_date}}
}

    } -select_query {
        select report_text, reported_date from ptk_reports where report_id = :report_id
    } -validate {
        {report_text
         {[string length $report_text] >= 3}
         "\"Your report\" must be a string containing three or more characters"
        }
    } -new_data {
        db_dml do_insert "
            insert into ptk_reports
              (report_id, report_text, reported_date)
            values
              (nextval('ptk_report_seq'), :report_text, :reported_date)"
    } -edit_data {
        db_dml do_update "
            update ptk_reports
            set report_text = :report_text,
             reported_date = :reported_date
            where report_id = :report_id "
    } -after_submit {
        ad_returnredirect "/"
        ad_script_abort
    }

ad_return_template
This works fine for putting data into the db, but when I view existing data (by adding ?report_id=13 to the url), I don't get anything pre-selected for the dates. I also tried changing to from_sql(sql_date), which gets me the date string (as 1988-02-07) in the year entry, and nothing selected for month or day. Is this something ad_form is supposed to be able to do, and if so, how? I think I see how to do it with the form builder code (I had a peek at the calendar package), but how to do it with ad_form has me stumped. TIA.
Collapse
2: Re: ad_form and dates (response to 1)
Posted by Jon Griffin on
http://jongriffin.com/static/openacs/ad_form/using-ad-form
Collapse
3: Re: ad_form and dates (response to 1)
Posted by Cathy Sarisky on
Thanks Jon. Actually, I already had the page open, which got me as far as I've gotten. When I use
to_sql(linear_date),from_sql(sql_date_no_time)
as in your example (for expiration date), the insert for new data doesn't work, with the error:
Bad date external representation '1995 02 05 00 00 00'
): '
            insert into ptk_reports
              (report_id, report_text, reported_date)
            values
              (nextval('ptk_report_seq'), 'new post, following Jon''s ad_form', '1995 02 05 00 00 00')'
(I'm also still not getting values for existing data editing.)
Collapse
4: Re: ad_form and dates (response to 3)
Posted by Tilmann Singer on
I don't know exactly how to do this in ad_form, but here's what works for me in a plain form builders if { [form is_request] } ... block:

db_1row ... { select to_char(start_date, 'YYYY-MM-DD') ... }

element set_value edit start_date [util::date acquire clock [clock scan $start_date]]

Maybe you can put that in an -on_request block of ad_form and modify it somehow so that it works?

Side note: I think the {reported_date ""} line should be removed - the form builder takes care of variables declared with it automatically. Otherwise you risk producing a "Move back and correct your error" message instead of the inline error message for that field.

Collapse
5: Re: ad_form and dates (response to 3)
Posted by Tilmann Singer on
Maybe simply removing the {value {$reported_date}} element from the field definition will do the trick - wouldn't that always set it to an empty value? Maybe it overrides whatever -select_query would have set.
Collapse
6: Re: ad_form and dates (response to 1)
Posted by Cathy Sarisky on
Ah ha. Found it. I had an issue with my -select-query statement.

This gets the date back out of the database and into the date selection fields of the form:

    } -select_query {
        select report_text, 
        to_char(reported_date,'YYYY MM DD') as reported_date
Collapse
7: Re: ad_form and dates (response to 1)
Posted by Don Baccus on
Here's some code from dotLRN which I rewrote to use ad_form when fixing a bug (it originally didn't check to see if the term start date preceeded the term end date, and if not much of the rest of dotLRN got very confused.) dotlrn_term::edit takes the formbuilder date type and does get_property manually, if writing from scratch I'd suggest doing it the form using to_sql(linear_date) if a full timestamp's needed. For dates truncated to the day, the formbuilder date type doesn't provide particularly user-friendly support.

a form snippet (things like term_id removed for simplification):

ad_form -name edit_term -export term_pretty_name -select_query_name select_term_info -form {

    {start_date:date              {label "Start Date"}
                                  {format {MONTH DD YYYY}}}

    {end_date:date                {label "End Date"}
                                  {format {MONTH DD YYYY}}}

} -validate {
    {start_date
        { [template::util::date::compare $start_date $end_date] <= 0 }
        "The term must start before it ends"
    }
} -edit_data {

    dotlrn_term::edit \
        -term_id $term_id \
        -term_name $term_name \
        -term_year [string trim $term_year] \
        -start_date $start_date \
        -end_date $end_date

    ad_returnredirect $referer
    ad_script_abort
}

ad_return_template
And the select query:
            select term_name,
                   term_year,
                   to_char(start_date, 'YYYY MM DD') as start_date,
                   to_char(end_date, 'YYYY MM DD') as end_date
            from dotlrn_terms
            where term_id = :term_id
Collapse
8: Re: ad_form and dates (response to 1)
Posted by Michael Bluett on
Is it possible to default to ANSI date format in PostgreSQL as Philip Greenspun suggested for Oracle in his Oracle Tips? Should we be setting this as default for OpenACS?
Collapse
9: Re: ad_form and dates (response to 1)
Posted by Don Baccus on
We do ... but the form builder date type likes to work with a linear list of date parts, which get translated to a string of date parts separated by spaces.

Frankly the form builder date datatype is ummm ... lacking.  It was incomplete and not working when we inherited ACS 4.2, I whacked at it until the bits that were there worked and fleshed it out a bit but it needs a lot more work.

Volunteers are welcome, of course! :)

Collapse
10: Re: ad_form and dates (response to 1)
Posted by Lamar Owen on
The ad hoc query tcl procs in AOlserver 2.3.3 had procedures to split dates up and such.  If you have an AS2.3.3 tree lying around, see ns_dbformvalueput in modules/tcl/nsdb/forms.tcl

Nathan Folkman mentioned a few weeks ago in the AOLserver chat (or maybe it was by e-mail, I can't recall) that it may be possible for the old TCL procs in 2.3.3 (which did some really neat things) might be released GPL/APL at some point.

I know this because I still have an AS2.3.3 tree lying around, which I use on occasion for CSV import and ad hoc form queries (which it does very well).  I just updated my local copy of forms.tcl for PostgreSQL 7.2.4 which is running my OACS instances.