Re: [libreoffice-users] Re: Base; Setting up relations between tables

On 11/17/2013 04:53 AM, Alexander Thurgood wrote:
> Le 17/11/13 06:32, Scott Castaline a écrit :
>
> Hi Scott,
>
> > So how would I do that in the graphical tool? I can't seem to get it to
> > work. I vaguely remember working with foreign keys and the sql statement
> > would refer to AlbumID from the parent as Album.AlbumID for both tables.
> > Wouldn't I also need the ID from the Album table as well? Maybe I'm just
> > better of using SQL Statements, but I don't see where I can do that. The
> > GUI does not seem to be able to do what I am trying to do or I have
> > forgotten more than I thought.
> If you want to use SQL DDL through LO's UI, Options > SQL (or Tools> SQL
> on the Mac) from the main menu is your friend, although the feedback is
> minimal (i.e. you get a message that the statement has executed
> correctly or not).
>
> Other than that, if you are trying to set it up so that your
> Album.AlbumID is referenced in the Track.AlbumID, then the way it should
> work in the GUI is via Tools > Relations in the LO Base file main
> window, adding the tables you want to join, setting the relation as
> 1-to-n by dragging the field AlbumID field from your Album table to your
> corresponding Track.AlbumID field in the Album table and then setting
> the UPDATE/CASCADE/DELTE options as appropriate.
>
> If this doesn't work after saving the database, then that would probably
> be a bug.
>
>
> Alex
Thanks for the info Jay & Alex. I later went and recreated the db 
inputting SQL myself and it worked. I then repeated it using the GUI and 
found my error. I would create the relation by click and drag between 
tables. Then for some reason I was clicking on the toolbar icon for 
creating a new relation instead of right clicking on the line to edit 
the one already created. So I was actually creating duplicate relations. 
It's working now. I found that I do recall quite a bit of SQL as I also 
duplicated the full db in SQLite using just SQL, just lack the 
programming skills to create the UI, and entering the info by SQL 
statements for each record is a real PITA.

I do have one more question though. I decided to add another table that 
would allow me to select existing data from this table to fill in a 
field in the main table. I created the new table with 2 fields. First 
field is an integer that is auto entered (essentially record count) and 
the second field is a text field. I then added a field in the main table 
that provides the link to this new table. In the form I changed the 
field type from Text Box to Combo Box (originally used List Box, but it 
didn't work). When I change to entry mode and enter a new record, when I 
get to this field and I click on the Drop Arrow a blank box is 
displayed. I am able to manually enter the correct data myself. I do 
have the new table populated with data.
What am I not doing? I'm thinking of changing one other field this way 
and was doing this one to figure out doing it.

Scott C.

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted