LO Base: Add a record with a default value in a many to many relationship?

[Dycius]

Hi all,

I will post the tables I am referring to in SQL form so you know what they are like.

CREATE CACHED TABLE "Store"(
  "PK_Store_Num" SMALLINT NOT NULL PRIMARY KEY,
  "Branch" VARCHAR(20) NOT NULL,
  "CityTown" VARCHAR(50) NOT NULL
)
CREATE CACHED TABLE "Employee"(
  "PK_Employee_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
  "FirstName" VARCHAR(100),
  "LastName" VARCHAR(100)
)
CREATE CACHED TABLE "StoreEmployee"(
  "FK_Store_Num" SMALLINT NOT NULL,
  "FK_Employee_ID" INTEGER NOT NULL,
  PRIMARY KEY("FK_Store_Num","FK_Employee_ID"),
  CONSTRAINT SYS_FK_59 FOREIGN KEY("FK_Store_Num") REFERENCES "Store"("PK_Store_Num"),
  CONSTRAINT SYS_FK_63 FOREIGN KEY("FK_Employee_ID") REFERENCES "Employee"("PK_Employee_ID")
)

As you can see it’s a simple structure to practice on before working on the main database.

There is a predefined list of stores and shouldn’t be editable. Ideally, I would like to select a store, see all the employees for that store and then add new ones. I couldn’t figure out how to make that work right, as in make a new employee and at the same time add it to the store table as well.

The best I can come up with from the examples I read is to make the employee table the main form, then the link table the sub form with the store as a drop down list. It works, however, when adding a new employee, the record needs to be entered, and then selected again and then the store selected from the drop down list. This is way too cumbersome if a) there are a lot of employees to add and b) if the store list is long.

Is there a way to have a store selected, and then a button that adds the employee record to the employee table and at the same time adding the correct linking IDs to the link table?

Also, I need to do my original idea of filtering the employee table so that only records from the preselected store are shown.

I can use macros if need be.

Thanks,
Jon

[iplaw67]

One way to do this would be via a trigger. Unfortunately, LO Base doesn’t support creation of triggers via the UI, and additionally, the version of hsqldb that comes as default with LO Base only supports trigger creation via Java code, which would require creation of a Java class and execution of a corresponding jar (including setting of the classpath on load) which would be loaded at the same time the ODB file is loaded (as it isn’t stored within the ODB file). This is apt to cause all sorts of problems within LO itself depending on the OS and is certainly not trivial.