LibreOffice Base previously copying data from Calc to Base)...

users
#1

Using LibreOffice Base v6.4.5.2 on Linux Mint v20.0

Thanks to the assistance given by members of this Mailing List, I have made progress in creating a database using LibreOffice Base.

I have created a database Reactor_Videos which has the fields

(1) Reactor_Videos Table 1
ID
Name (of Reactor)
Location (Country of residence)
Gender
Number (of times an artist has been featured)
First (date artist first featured)
Last (Last date artist featured)

and the contents of the spreadsheet I used to record data was copied to the Table Reactors_Video Table 1.

I created a form - Reactor_Videos_Form from that Table - and have used that to enter or update new information. It is quick and I am now getting to grips with creating Queries.

The Table has many Reactors but at present the information relates only to one artist and the only information is the first and last date a particular Reactor reacted to her.

(2) My next step is to have the ability to not only
(a) choose a Reactor

(3) but also to
(b) choose from a list of artists
(c) choose the song the artist is singing
(d) choose the type of venue at which the performance took place

Bearing this in mind, I created three more Tables.

(4) Artist (which has fields)
IDArtist
Name
Date of birth
Country of birth
Country of residence

(5) Songs/Performances (which has fields)
IDPerformance
Title

(6) Venue (which has fields)
IDVenue
Concert - indoor
Concert - outdoor
Studio - home
Studio - recording
Private gathering

My first thought was to have the entire database in one Table, differentiating the sectors by using different titles for the ID field, e.g. ID as an Integer (for the Reactor), IDArtist for the artist as an Integer etc. I created the IDArtist as an Integer but when I tried to save it I got the error message.
Column constraints are not acceptable in Statement [ALTER TABLE "Table1" ADD "IDArtist" INTEGER NOT NULL IDENTITY] When I click on More I see SQL Status:S1000

Searching on the internet does not help me as it reveals information current about 2015.

I then created Forms from the Tables Artists, Songs/Performances, and Venue with the intention place them on the main form (Reactor_Videos_ Form) and link them, to achieve my objective in (3) above but despite searching the LO Base Manual and watching and listening to tutorials on YouTube, I have been unsuccessful in seeing a way forward.

Any help from members to set me on the right path to achieve what I am attempting will very welcome.

Zed

#2

Hi Zed,

My first thought was to have the entire database in one Table,
differentiating the sectors by using different titles for the ID field,
e.g. ID as an Integer (for the Reactor), IDArtist  for the artist as an
Integer etc.  I created the IDArtist as an Integer but when I tried to
save it I got the error message.
Column constraints are not acceptable in Statement [ALTER TABLE "Table1"
ADD "IDArtist" INTEGER NOT NULL IDENTITY] When I click on More I see SQL
Status:S1000

You have created a table with ID as primary key (IDENTITY).
Now you have to add als the foreignkeys like "IDArtist", "IDPerformance"
und "IDVenue" as foreignkeys by setting the fieldtype to the same
fieldtype as you have choosen in this special table, for example
INTEGER. Don't set this field to key-fields in "Table1". This table
already contains such a field ("ID") and contains also data, which will
need a keyvalue.

Also you couldn't add an (empty) field to a table and set this field to
NOT NULL, if the table aleady contains data.

Hope it helps a little bit.

Regards

Robert