Ask Your Question
0

Is there any way to edit the sql which I assume is generated when a table is created using the wizard

asked 2015-07-15 23:34:26 +0200

argy gravatar image

updated 2015-08-24 16:45:55 +0200

Alex Kemp gravatar image

I've created a table using the wizard which includes a date record. I've got the data entry form working as I'd like it to work except that I now realise that, because the user is required to record data only once per day, it would be useful to prevent the user from entering multiple records for the same date.

I'm assuming that by limiting the date field to be a unique value I can achieve my objective.

Is there any way to access or otherwise edit the SQL that libreoffice must generate even when the table has been created in wizard or design mode?

I'm working with LibO 4.4.4.8 if I remember correctly...

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-07-16 03:45:06 +0200

doug gravatar image

The solution is available via Tools --> Index design. In the dialog box that appears, click the button to create a new index (image description) and then select the desired column and click the unique box, then save. This will create a UNIQUE constraint on the column selected.

Alternately, it is possible to achieve the same result using Tools--> SQL using the following command:

ALTER TABLE "Table1"
ADD CONSTRAINT "my_key" UNIQUE ("fld1")

However, it usually is safer to stick with the GUI when dealing with internal HSQLDB.

(if this answers your question, please accept the answer by clicking the check box (image description) to the left)

edit flag offensive delete link more
0

answered 2015-07-16 18:01:57 +0200

argy gravatar image

Hi Doug,

That worked perfectly thanks.

The alternative approach had to be applied because the GUI approach took issue with a system created key that it didn't want to change (probably down to something that I failed to do...). It's good to know that this facility exists. I could use the GUI to check that the change had been implemented which was nice to see.

I'm now left wondering if theres any way to send a mesage to the errant user in case they tey to duplicate a daily record along the lines that "The record for (show the date) has already been made. Please selcet another date"

Thanks again...

edit flag offensive delete link more

Comments

A form provides several ways to enforce integrity which are user-friendly. In this situation I usually would test the input by reading the .Date of the form control, running a SQL statement to check for duplicates, and then preventing the user from updating or leaving. That is a lot of work in this situation to get a custom box. Instead consider the On ErrorForm event, which hopefully passes the error to the macro, searching the message for the right text, doing MsgBox etc ...

doug gravatar imagedoug ( 2015-07-16 18:31:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-15 23:34:26 +0200

Seen: 109 times

Last updated: Jul 16 '15