Adding a day to a given date

[dunbar]

Hi Crew,
Just one question, you guys will probably know immediately.
I'd like to be able to input a date in one field and have a second field show that date, plus a set amount (1) of days.
eg: I input 25/06/2012 and the second field automatically shows 26/06/2012
Is there an easy way to achieve this?

Cheers,
Dunbar.

[rudolfo]

The problem is that there are many ways how to do this. But not all are appropriate for your case.
You can do this in the database backend with a trigger on your table. This depends on your backend (shown in the status line of the Base window: Embedded Database, Mysql, PostgreSQl,...) And it is done invisible for the user when the data is written from the frontend form into the database record.
If the date in the second field is rather a (default) suggestion and the user should be able to modify/overule it then you need a different and more complicated method because you will have to use macros that modify the other field's data when the field with the first date is typed or modified.

So please specify the database backend that you are using and tell us a more details about the workflow of your form.

[dunbar]

Thanks for the reply Rudulfo.
Status line says "Embedded Database" and then "HSQL Database Engine".
The second field does not need to be editable by the user, but it does have to be relative to the first field.
ie: if the first field is modified the second field has to change likewise.
Let me now if you need any more info.
Thanks for your help.
Dunbar

[rudolfo]

Okay, that's one step further. The problem with triggers on the tables is that they operate hidden from the user. If the second date field is visible but readonly for the user, a reload of the data record would be required to show the automatically added date. The second date is only generated when the user changes the record.
I am not really familiar with the HSQL Database Engine, but I think that the one that is integrated in Base doesn't support triggers. Maybe anybody else here with some clearer statement? DACM? R4zoli?
So the only solution that I see is to use a macro that is bound to the "leave field" event. If you have never worked with macros or programming languages before you have to choices: Either find someone that can do it for you or set aside some days for learning the scripting interface of OpenOffice/Base (UNO API). Might be some fun after 3 or 4 weeks, but the first days will surely be paved with frustration.

[rudolfo]

If the second field is always 1 day after the first field, meaning if the first field changes its value because the user or the admin of your application has modified it the second field is changing synchronously, then a view based on a query could do what you want to see. You don't need to have an extra column field2 in your table. With the clear rule "field2 is always field1 + 1day" the database can calculate (or auto-generate) the field 2 every time the data in the table is retrieved.
See this other thread about date functions in HSQLDB. Unfortunately HSQLDB makes it unnecessarily complicated compared to other databases like MySQL, PostgreSQL or Oracle.
Your Query:

Code: Select all

select field1, dateadd(field2, INTERVAL 1 DAY) As field2 from ...

[RPG]

Hello

I agree with rudolfo macros are difficult. If possible try to redesign your form.

Other wise study this

http://www.oooforum.org/forum/viewtopic ... ateadd+sub

Romke

[DACM]

Base does not provide GUI support for HSQLDB Triggers, but the function does exist in the built-in HSQLDB jar file and I presume it could be accessed through Tools>SQL... or a Macro.

HSQLDB 1.8.x supports external routines ("Java classes" stored as external files) accessed through the Trigger interface:

• http://hsqldb.org/doc/src/org/hsqldb/Trigger.html
  http://hsqldb.org/doc/guide/ch09.html#c ... er-section

HSQLDB 2.x adds "SQL procedures" stored internally in the database as triggered procedures/functions:

• http://hsqldb.org/doc/2.0/guide/triggers-chapt.html

Now I don't have much experience with Triggers, but I would gravitate towards Macros over Triggers in many cases because Macros offer better feedback in the user-interface, and can be limited to a specific task (Form/Dialog) as necessary. Trigger functions don't update the user-interface automatically and they apply universally to all transactions that invoke the Trigger, similar to column defaults (CURRENT_DATE), which can be good or bad. Triggers can also become a liability upon migration between engines depending on the implementation (external routine language or particular SQL utilization/syntax) -- although Macros that utilize SQL are not entirely immune to this consideration. For instance, 'date math' is not supported by the built-in HSQLDB 1.8.0 engine, so these functions can be added using available Macro-language functions -- preferably through a plug-in library -- while leveraging SQL functions as available. But Macros can be intimidating so you'll probably need an example Macro for dispatching SQL because the underlying API is spotty and complex. So there are some considerations and a learning curve either way you go.

But even before going the coded route, take a look at this link which contains a few DATEADD workarounds using standard Queries/Views for the built-in HSQLDB 1.8.0.10:

• http://www.oooforum.org/forum/viewtopic ... 695#428695