Forms: Entering date using drop down menu? 1

[scomfort]

Hey there,

I was wondering what is the best way to enable someone to select a date in forms like they do on most websites (i.e. by use of drop-down menus for day, month and year). In my database I have a date field which of course I would like updating from whatever the user puts in.
I'm pretty new to Forms so sorry if its simple stuff!
Cheers.

 

[lewisp]

You could build a list of dates using ALL_OBJECTS system view.

[tt]SELECT SYSDATE + (ROWNUM - 1)
FROM all_objects
WHERE ROWNUM <= 28;[/tt]

This query returns dates for the next 4 weeks.

 

[scomfort]

Thanks for the reply.
However the actual date field is going to represent date of birth so will have to show a fair few dates. I thought that there might be a way to represent this by simply using drop-down menus (as I said before when you sign up on web-sites you usually select day/month/year from separate menus). Is this possible considering the DOB is itself modelled as a date type?

 

[lewisp]

Then all you need are three drop down lists. One has the days 1-31, then next all the months and the third a range of years of your choice.

All you then need to to is concatenate the results and use TO_DATE to convert them into an actual date.

 

[scomfort]

I've tried to do as suggested but am lost as to how to go about it.
At the moment I can create the three lists and populate them with relevant data. However the next part Im not sure on.
Say the user selected 21-Mar-1980 from each respective drop-down menu how would you point the 21 to the day, the Mar to the month and the 1980 to the year for that single date field? I'm not sure on the to_date conversion and the help files are not helpful!

 

[lewisp]

Use TO_DATE:

[tt]:block.date_item := To_Dateblock.day || '-' ||
:block.month || '-' ||
:block.year,
'DD-Mon-YYYY');[/tt]

 

[scomfort]

Ok I understand most of that although what is the || '-' || for?
Where do I place this bit of code too?
Just to make sure Im following this I have three list items called day, month and year which will be used as the pop-ups and this will populate the date_item.
Sorry Im just a beginner at this and you seem to know your stuff!

 

[lewisp]

The '-' is to maintain the format of 'DD-MON-YYYY'. You don't really need them as its just my force of habit of keeping things neat. You could just as easily omit the '-' and specify your format as 'DDMONYYYY'.

You could code a WHEN-LIST-CHANGED trigger on each list item that looks like this:

[tt]IF :block.day IS NOT NULL
AND :block.month IS NOT NULL
AND :block.year IS NOT NULL
THEN
:block.date_item := To_Dateblock.day || '-' ||
:block.month || '-' ||
:block.year,
'DD-Mon-YYYY');
END IF;[/tt]

 

[scomfort]

Ok that looks good - I've put the code in each of the three list items and then when I commit it it just exits out! I created a test form just to make sure I could get it to work and created three list items called day, month and year like above.
I also tried using three list items called days, months and years (just so they didnt appear in blue!) and then I get an error saying:
FRM-40735: WHEN-LIST-CHANGED trigger raised unhandled exception ORA-01858. When looking at the error it says
SQL statement in error:
INSERT INTO TESTDATE(TD,DAYS,MONTHS,YEARS) VALUES 1,:2,:3,:4)
Error
ORA-00904: &quot;YEARS&quot;: invalid identifier

which I can understand because there should only be 3 values! TD by the way is the actual date item in the database.

 

[scomfort]

Oh and testdate is the block so the code looked like:

IF :testdate.days IS NOT NULL
AND :testdate.months IS NOT NULL
AND :testdate.years IS NOT NULL
THEN
:testdate.td := To_Datetestdate.days || '-' ||
:testdate.months || '-' ||
:testdate.years,
'DD-Mon-YYYY');
END IF;

 

[lewisp]

Set the Database Item property of each of the three drop-down lists to 'No'.

 

[scomfort]

Ok done that but unfortunately still getting the same error FRM-40735: WHEN-LIST-CHANGED trigger raised unhandled exception ORA-01858 although now when go to display error says no errors encountered so cannot localise the problem.
Any more ideas? Sorry to be a pain!

 

[scomfort]

By the way what should each of the list item's data type be. At the moment its been left on the default of char 30 for all three lists and Im not so sure that is correct.

 

[lewisp]

Make sure the datatype of each list item is CHAR, or put TO_NUMBER() around the item in the PL/SQL. eg

[tt]To_Numbertestdate.days)[/tt]

 

[scomfort]

Ok each item was a char anyway (30 maximum value). I used the To_Number too on the day and year but it had no effect.
How would you go about outputting the values of each list item to three seperate text items just to see what the output is and make sure it is correct (i.e. if I select 01 in the &quot;days&quot; list item it will appear 01 (or should!) in a text item called &quot;testdays&quot? This way I'll be able to see the output for each item and if its doing it correctly.

 

[lewisp]

In your when-list-changed trigger, just assign the list item value to your test item. eg.:

[tt]:testdate.testdays := :testdate.days[/tt]

Let me know what the output is from each list item.

 

[sem]

Do you really need to use pop-list to enter dates? Why don't you try some third-party element and embed it into your form? Some ActiveX object or Java Bean? We use MSCAL.Calendar.7.

Regards, Dima

http://seminihin.narod.ru

 

[scomfort]

Thanks for that lewisp - I managed to get the output of each one and I neglected to assign an item value to each item in the list (quite ignorant of me!) ...of course it was returning the default LIST25 etc. I've changed this to their respective return values and it works fine thanks
I think the use of a pop-up list is a better interface to select a DOB then a calendar ... after all why do most web-sites use them? Any how its working so thanks for the help!

 

[sem]

Most web sites are using them because of HTML restrictins to avoid using JavaScript or plug-ins. Forms provides far reacher GUI than HTML. Though for the cost of efficiency.

Regards, Dima

http://seminihin.narod.ru

 

[scomfort]

Yes your right. Although since I've managed to get it to work it'll do - plus theres the small matter of not knowing much about java beans etc!
Thanks for the help + info tho.