Base scenario

[Tom_Davies]

Hi
So in the editing can you change the direction? or rather the way that the relationships works?
Regards from
Tom

________________________________
From: Girvin R. Herr <girvin.herr@sbcglobal.net>
To: Tom Davies <tomdavies04@yahoo.co.uk>
Cc: Dan Lewis <elderdanlewis@gmail.com>; "users@global.libreoffice.org" <users@global.libreoffice.org>
Sent: Thursday, 10 January 2013, 21:44
Subject: Re: [libreoffice-users] Re: Base scenario

Tom,
Yes.  I confirmed that right-clicking on a join line does allow deleting or editing.
If I understand you correctly, no, relationship definitions should not be part of the back-end.  The table relationship is defined for the back-end by the front-end through the SQL statements.  For example, I have a table of suppliers, with names and addresses and other contact information.  This table is related to almost all of my database (main) tables.  Additionally, each "main" table has its own set of table relationships with other (sub?) tables, most of which are for selecting options with a join.  Each record of these option tables contains a primary key and a text field for the option.  For example, I have a table of statuses for the item in the main table record.  An integer foreign key in the main table contains a primary key value corresponding to the text element of the statuses table record.  That way, I am only storing an integer (key value) in the main table, rather than the option text, and with no repeated option text.  It

also standardizes the option texts.  All of these multiple relationships must be defined by me - ergo it needs to be in the front-end.

Hope this helps clarify this.
Girvin Herr

Tom Davies wrote:

Hi
Can you right-click on a relationship's join-line and edit it's properties?  Shouldn't the relationships be part of the back-end rather than defined in the front-end?  Regards from
Tom

________________________________
From: Dan Lewis <elderdanlewis@gmail.com>
To: users@global.libreoffice.org Sent: Wednesday, 9 January 2013, 4:14
Subject: Re: [libreoffice-users] Re: Base scenario

Comment inline below.

--Dan

Ian,
Have you actually drawn any relationships?  Base will not do that for you.  Just adding the tables in the 'Relationships Window' will not create the relationships automagically.  You must click and hold on the one table element (remote key) and drag over to the related table's element (primary key), then release the mouse button.  Base will then draw a line between the two.  Note, the order of the drag is important.  It determines the type of join. Joins are confusing to me too, so I can't help much there.  I had to experiment with the direction to get it to work right.  I think it was remote key to primary key, but I am not sure of that any more.

Warning!  The way the SQL language is set up, if either of the ends of a join (relationship) is NULL, then the record will be discarded and not show up in your result set.  No warnings, no errors.  Data records will just be missing.  IMHO, this is stupid (my mantra is: "thou shall not lose data"), but that is how the SQL language was set up.  So, make sure any joined data elements in all of your table records are not NULL.  Note that NULL is not zero (0) and vice-versa!  NULL means that there is no data in the record element.  I use a lot of remote keys in my database main tables that point to primary keys (options) in other tables.  In those other tables, I have made it a point to make the data elements of the first record to be "-", which is my equivalent of unknown, just to have something to select that is not NULL.  You could probably use a blank (" "), but I prefer seeing the "-" in forms and reports.  Most times in reports, it is

hard

[Girvin_R_Herr]

Tom,
I can't seem to change the direction from the editing (Join Properties) dialog. If the direction needs to be changed, I have been selecting the line by right-clicking on it and selecting the "Delete" option. I then re-enter the relationship (Join) in the opposite direction as I had explained in my last posting to Dan et al.

The type of Join can be changed in the right-click "Edit" option under the "Join Properties" dialog. There is a "Type" list box, which has the Inner, Left, Right, or Cross Join options. Under that list box in the dialog, there is a table labeled "Fields involved", with two columns, left and right, corresponding to the left and right join ends. Under that panel, is a hint of sorts, that explains the Join type, the direction, and that warning about the join may not be supported, for all but when "Inner" is selected in the list box. There is another radio button labeled "Natural" for a "Natural" Join augmentation, but I am not familiar with that option.

Hope this helps.
Girvin Herr

Tom Davies wrote:

[Hank_Alper]

Ian
How are you defining your primary keys and your foreign keys ? From your
post, it looks like you have two tables, 'Members' and "eMail". Implicit in
your post it appears that the primary key of 'Members' is 'Record ID'.
Also, the primary key of 'eMail' is 'Message ID' while a key called
'Members ID' is a foreign key in the 'eMail' table. I don't see anywhere
that you have specifically designated these keys as primary and foreign
keys. If you defined your tables in MySQL, I imagine you would have to add
the foreign key constraint and designate the primary keys in your
appropriate MySQL table definitions. If you do this, the relationships
window should show a connection between Members.Record ID and eMail.Members
ID. You might try , in the relationship window, to click on Record ID in
the Members table and drag to Members ID in the eMail table.
This dragging method works when using the embedded Database but I'm not
sure if it will transmit the necessary information to the remote database.
Hank