LibreOffice Base - Firebird date constraint

English
#1

LibreOffice 25.8.04 (x86.64) Ubuntu 24.04
and
LibreOffice 7.6.7.2 (x86.64) Ubuntu 20.04 (The machine I’m on now)

I have a firebird external database and I’m trying to create a constraint on a number of date fields.
I have a table “Bookings” which has 3 date fields “DateRecvd”, “StartDate” and “EndDate”
These represent the date an order was “received” for a stay at our property, when the stay “Started” and when it “Ended”.
Obviously “EndDate” must be > “StartDate” and I have managed to enter a constraint using SQL:-

ALTER TABLE "Bookings" ADD CONSTRAINT "CK_StartEndDate" CHECK ("EndDate" > "StartDate")

which works just fine.

I also have a “Payments” table with a “PaidDate” and obviously payment cannot be made before the order was “received”. Because I have discovered that a default date in a subform seems to be “14th Feb 2019” (mentioned in a previous post), I have tried without success to create a restraint to ensure that “Payments.PaidDate” cannot be less than “Bookings.RecvdDate”. I’ve tried SQL:-

ALTER TABLE "Payments" ADD CONSTRAINT "CK_PaidDate" CHECK ("PaidDate" > "Bookings.RecvdDate")

But receive the following error:-

1: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE Payments failed
*Dynamic SQL Error
*SQL error code = -206
*Column unknown
*Bookings.RecvdDate
caused by
'ALTER TABLE "Payments" ADD CONSTRAINT "CK_PaidDate" CHECK ("PaidDate" > "Bookings.RecvdDate")
'
 at /build/libreoffice-Dc86jq/libreoffice-7.6.7/connectivity/source/drivers/firebird/Util.cxx:69

I’m guessing that this is because I’m trying to create a constraint in one table based on a field in another table. Is this possible? and what am I doing wrong?

As a temporary measure I’ve used SQL:

ALTER TABLE "Payments" ADD CONSTRAINT "CK_PaidDate" CHECK ("PaidDate" > ‘30/11/2019’)

to ensure that a date must be later than 30th Nov 2019, which gets round the 14/2/2019 problem.

#2

Table “Payments” knows nothing from table “Bookings”. It isn’t possible to get the content from “Bookings” in a check constraint of “Payments”.

Might be it works with a subquery, but didn’t test this:

ALTER TABLE "Payments" AS "a" ADD CONSTRAINT "CK_PaidDate" 
CHECK ("PaidDate" >
 (SELECT "RecvdDate" FROM "Bookings" WHERE "ID" = "a"."BookingsID"))

Don’t know how you connected the 2 tables so I guessed the might be more than one “PaidDate” for a “Booking” and the relation between the table is n:1.

#4

@RobertG Hi, Your assumption of n:1 is correct, there can be more than one payment date per booking (deposit/initial payment & balance), which is why I have a separate tables “Bookings” and “Payments”. These are joined by a PK “Booking_Ref” in “Bookings” and FK “Booking_Ref” in Payments.
I tried your code (changing “BookingsID” to “Booking_Ref”) to no avail

ALTER TABLE "Payments" AS "a" ADD CONSTRAINT "CK_PaidDate" 
CHECK ("PaidDate" >
 (SELECT "RecvdDate" FROM "Bookings" WHERE "Booking_Ref" = "a"."Booking_Ref"))

received error message:

1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 24
*AS
caused by
'isc_dsql_prepare'
 at /build/libreoffice-Dc86jq/libreoffice-7.6.7/connectivity/source/drivers/firebird/Util.cxx:69

Guess it didn’t like the “AS”
I also tried :-

ALTER TABLE "Payments"  ADD CONSTRAINT "CK_PaidDate" 
 CHECK ("PaidDate" >
 (SELECT "RecvdDate" FROM "Bookings" WHERE "Booking_Ref" = "Bookings"."Booking_Ref"))

which created a constraint, but didn’t work due to “multiple rows in singleton select”, presumably because the “SELECT” statement returned all entries from “Bookings” and not just the record with a matching ID.

I’m not used to writing SQL (normally using GUI). My problem is that I have no idea how to return the ID of the current record so that the comparison can be made to the single record in “Bookings.RecvdDate”.

I’ve spent virtually all day today trying various things to no avail - hence my late reply.

As the date would be entered in a FORM then I guess it may be possible to do the check in a Macro run when the field is updated on the form, but I have no idea with Macro syntax.

#5

Your constraint solution is practical as it blocks operators from inserting bugs date as “today”.
And you can perform yours – weekly perhaps (?) – cash reconciliations via relationship BOOKINGS (1) → (n) PAYMENTS, as mentioned above.
But if you insist along this line I imagine could pursue via Stored Procedure to

  1. check first (of course);
  2. INSERT or MsgBox (via a return code from the SP).

But I don’t know how to run SP’s via code :grinning:
 
EDIT
As per your hint: Macro

  1. run a select to check if date >= date_booking;
  2. if it is run the INSERT SQL;
    if not MsgBox.
     

Or, simpler yet: code just checks if inserted date >= date_booking and, if not, just raises the alert (= MsgBox).
 
Simpler than SP.

#6

@BrianStew Seems the construction with correlated subquery won’t work for a constraint. AS "a" is needed to get a correlation which will get the only one value from “RecvdDate”.

If the only problem is you couldn’t get the current date in the date field of your form you could try to set the date field as a standalone field, not bounded in a tablecontrol. Might work - couldn’t test here, because I haven’t installed gtk3.

You could also create a macro, which will read the date value from the mainform and sets the “Min. Date” in the properties of the control of the subform. Will have a look tomorrow for an example.

1 Like
#7

This is a tentative “basic” code just to alert operator for date before booking, so she/he can correct the insert.
Event = After updating Date_Sub
I don’t know if it really works in practice!

Sub Alert(evt As Object)
	Dim SF As object, TC As Object, TCDate As Object, TCMID As Object 
	' DECLARE VARIABLES FOR THE QUERY
	Dim Con As Object, Stmt As Object, Rst As Object
	' DECLARE VARIABLES FOR DATA 
	Dim dp As New com.sun.star.util.Date, db As New com.sun.star.util.Date
	Dim mid As Integer ' variable for Main_ID
	Dim sSQL As String
	REM SET REFERENCES TO CONTROLS
	TC = evt.Source.Parent ' the grid
	SF = TC.Parent ' the subform
	TCDate = TC.getByName("Date_Sub")
	TCMID = TC.getByName("Main_ID")
	REM INSERT CONTROL VALUES INTO VARIABLES
	mid = TCMID.Text
	dp = TCDate.Date 
	MsgBox mid & Chr(10) & CDateFromUnoDate(dp)
	REM CONNECT TO DB AND GET BOOKING DATE
	sSQL = "SELECT ""Date_Main"" FROM ""Main"" WHERE ""Main_ID"" = '" & mid & "'"
	Con = SF.ActiveConnection()
	Stmt = Con.createStatement()
	Rst = Stmt.executeQuery(sSQL)
	' get the quey result
	Rst.next
	db = Rst.getDate(1)
	print "Booking Date is " & CDateFromUnoDate(db)
	' check dates:
	If CDateFromUnoDate(dp) < CDateFromUnoDate(db) Then MsgBox "ALERT!"
End Sub

Alert
Alert1366×768 77.4 KB

Or the booking date can be obtained directly from the Main Form without the query. As
FMain = SF.Parent
[…]
We can try this simpler way if this “Alert” solution suits you.

#8

Here an example date. In subform the min-date for new entries will be set by the date in main form. If you choose a date before min-date it will be set to min-date. Also added the default date to current date, because there is a buggy behavior of datefields in a tablecontrol and gtk3, which sets the default to a date in the past.
FB_Date_Date_Min.odb (22.8 KB)

#9

@CRDF OK Many thanks. I’ve tried that and yes it seems to work.
I’ve not worked with Macro’s in LibreOffice and am struggling to work through the code to understand how it works (not familiar with the object structures).
I’ll have a play around to familiarise myself with it.
Thanks, It really helps me get started.

#10

Hi @RobertG .
I loaded your sample and it seems to work OK.
The “buggy behaviour of datefields in a table control and gtk3” I know about, It was me that brought it up last week.
I’ve looked at the event Macro you created and am trying to understand how it all works. LibreOffice Macro’s are new to me and I’m not familiar with the object structure, so I’ll have a play around to get myself more familiar.
Many thankd for your help.

#11

Of course @RobertG 's is the “perfect” solution.
But as you also manifest interest in learning, here is the simple “Alert” code.
I adapted a code from here and only afterwards realized that as booking date is already displayed in the Form, would be absurd to run a query for it.

Option Explicit
Sub Alert(evt As Object)
	Dim MF As Object, SF As Object, TC As Object, TCSubDate As Object, MainDate As Object
	REM DECLARE VARIABLES FOR DATES 
	Dim dp As New com.sun.star.util.Date, db As New com.sun.star.util.Date
	REM SET REFERENCES TO CONTROLS
	TC = evt.Source.Parent ' the grid
	SF = TC.Parent ' the subform
	MF = SF.Parent ' the Main form
	TCSubDate = TC.getByName("Date_Sub")
	MainDate = MF.getByName("datDate_Main")
	REM INSERT CONTROL VALUES INTO VARIABLES
	db = MainDate.Date ' date of this booking
	dp = TCSubDate.Date ' date of this payment
	REM CHECK THE DATES:
	If CDateFromUnoDate(dp) < CDateFromUnoDate(db) Then MsgBox "ALERT!"
End Sub
#12

@BrianStew : Here a little bit more about the macro in the example database.
SUB DateDefault(oEvent AS OBJECT) → Event is “on focus” from the data field inside the tablecontrol of the subform
DIM oFormMain AS OBJECT, oSubDate AS OBJECT → Declaration of variables
DIM daMainDate AS NEW com.sun.star.util.Date → Special for dates: A struct with Year, Month and Day
oSubDate = oEvent.Source.Model The field where the event happens. You could change many properties of the field.
oFormMain = oSubDate.Parent.Parent.Parent Main form is set as 3 * parent → tablecontrol → subform → form
daMainDate = oFormMain.getDate(oFormMain.FindColumn("Date")) → Field name of data source of main form. The field in the table has name “Date”. Has nothing to do with the name of a form control.
oSubDate.DateMin = daMainDate → Minimum value you could enter to date field, here the field inside the tablecontrol of subform. See above
.
All other content is about setting the current date as default to the same field.

#13

@CRDF That’s Ok. It all helps my understanding - slow brain cells here after 70 years!

#14

@RobertG I had worked out how most of it works. I am having difficulty understanding the nested Parents, don’t understand the 3 generations, thought it was just form and subform (2 gens?).
Also having difficuly understanding the uno structure of dates. I’d also like to calculate the number of days between 2 date fields, but obviously doesn’t seem to be a subtraction to get an integer difference.
I have adapted your code to a copy of my main database, but there are a few strange bugs where sometimes the default date seems to come from a different record in the main table. This also happens in your example database, but I find it difficult to pin down when it happens.
I’ve worked with software for around 50 years, but my background has been mostly procedural rather than object based. At over 70 I’m struggling to get my brain cells around this.
It’s all very much appreciated. Thanks.

#15

(1) evt .Source.Parent = the TableControl (Grid);
(2) .Parent = the Sub Form;
(3) .Parent = the Main Form.
 
As for date differences, a priori you should get it from the DB via query. Not via user code.
 
Also take notice that @RobertG 's code takes date directly from the DataSet (Form’s Data Source) and not from the control proper. It’s simpler.

#16

For Calculating with UNO-Dates (Struct with Day, Month, Year) you will need the functions CDateFromUnoDate, DateAdd and CDateToUnoDate. With DateAdd you could add days, months, quarters, years … to a date, which isn’t a struct.

These functions could be found in the help of LibreOffice. Look here:
https://help.libreoffice.org/latest/en-GB/text/sbasic/shared/03030112.html?DbPAR=BASIC

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.