Base scenario

users
#1

Hi All

Some advice and comments please.

I'm running a MySQL Database (5.1.55) with Base as the Front-End (3.6.2.2) on PCLinuxOS (2012).

I have a Membership Database and have added a SubForm to show all the eMails sent and received from each Member.
The eMails are all in a separate Table.

On my Main "Membership" Form I have added a button to bring-up the eMail SubForm. (I have this part working!) But what I see as the best way to do the last step is to add a ListBox named "Find Member" to the SubForm and this will read from a Query holding the concatanated First Name and Surname of each Member and that Member's ID Key. Once these are inserted into the SubForm it will extract all the relative eMails, sort them into date order and display them.

So - Questions and Advice please on the following...

  * Is this the right way to approach this requirement?
  * Will the Query update automatically each time it is called so as to
    pick-up new eMails?
  * How do I make the ListBox 'read' the results of the Query?
  * How do I make these results automatically insert into their
    respective fields on the SubForm?
  * How do I make the SubForm do the sort as a final step? (Maybe a
    'Sort' Button??)

Any help, suggestions or comments would be greatly appreciated!!

IanW
Pretoria RSA.

#2

Hi Ian,

On my Main "Membership" Form I have added a button to bring-up the eMail
SubForm. (I have this part working!) But what I see as the best way to
do the last step is to add a ListBox named "Find Member" to the SubForm
and this will read from a Query holding the concatanated First Name and
Surname of each Member and that Member's ID Key. Once these are inserted
into the SubForm it will extract all the relative eMails, sort them into
date order and display them.

What do you mean exactly by "inserted into the Subform" ? Your e-mail
subform just contains e-mails, or does it hold something else ? I
imagine that there is a foreign key reference to the member in the
e-mail table ? I'm a bit confused about what you're trying to achieve here.

So - Questions and Advice please on the following...

* Is this the right way to approach this requirement?
* Will the Query update automatically each time it is called so as to
   pick-up new eMails?
* How do I make the ListBox 'read' the results of the Query?
* How do I make these results automatically insert into their
   respective fields on the SubForm?
* How do I make the SubForm do the sort as a final step? (Maybe a
   'Sort' Button??)

As far as I can see, the only way to achieve what you want to do is via
macros, as it looks like you want to retrieve a dataset from a filter or
query, and then inject part of the information from that dataset into
another table ?

Alex

#3

Thanks for the feed-back Alex - appreciated!!

OK - I have two Tables (Members and eMails). The "Main Form" brings up the Members Table with a button to pop-up a sub-Form with eMails that the Member has sent or I have sent to him/her. (This part I have working but at the moment it brings up ALL eMails!!)

The second Form is for me to enter the eMails into the eMail Table. For this I need to be able to select the Member to get the Key ID so that only the eMails to and from the Member are found later for the pop-up Sub Form on the Main Form. Once I have selected the correct member it would be good to extract other needed fields from the Members Table to go into the eMails Form as well. (On reflection - - I think this last step could be done away with provided I can at least get the Member's ID Key out of the Members Table and insert it into the eMail Form as the Foreign Key).

Thanks for your help and I hope this makes it clearer what I'm trying to do.

IanW
Pretoria RSA

#4

Thanks for the feed-back Alex - appreciated!!

OK - I have two Tables (Members and eMails). The "Main Form" brings up
the Members Table with a button to pop-up a sub-Form with eMails that
the Member has sent or I have sent to him/her. (This part I have working
but at the moment it brings up ALL eMails!!)

If your main form already contains the ID of the member, why don't you
use the member ID as a filter criteria for your email form ?

In form edit mode of your email subform, click on the form properties
button, and then click on the three dots button to the right of the
Filter field. This opens a filter dialog, where you can enter a Named
Parameter for your filter, e.g.

MemberID = :ID

If you then save this Filter criteria, you will notice that the Filter
field has been filled. You can then quit the Form Properties dialog and
save your form.

When you open your email subform, you will be asked to provide the ID of
the member and the form will filter the results on the basis of that ID.

The second Form is for me to enter the eMails into the eMail Table. For
this I need to be able to select the Member to get the Key ID so that
only the eMails to and from the Member are found later for the pop-up
Sub Form on the Main Form. Once I have selected the correct member it
would be good to extract other needed fields from the Members Table to
go into the eMails Form as well. (On reflection - - I think this last
step could be done away with provided I can at least get the Member's ID
Key out of the Members Table and insert it into the eMail Form as the
Foreign Key).

For this, you probably need to go and check out the OOo users forum and
ask your question there, this kind of question has been asked there many
times before and will probably require a macro.

Alex

#5

Thanks Alex

(Hope you and everyone had a great Xmas and Best Wishes for 2013!!)

Your reply to me has made me change my layout of my Forms. I have removed all fields from my Sub-Form except the two IDs, the date, Sent/Received box, Message title and the message itself. The Button on the main Form I have re-labeled as "eMails".

So all I need now is for the Sub-Form to AUTOMATICALLY pick-up the Members ID from the main Form and to use as the filter criteria for which eMails to show. (I will also use this form to enter new eMails so the automatic pick-up of the Member's ID is also important here).

I have tried a couple of syntaxes to do this with no luck - it always asks me for the Members ID!!

Is there a way to automate this?

I thought that I could use 'MembersID' (on eMail SubForm) = 'SchemaName.TableName.FieldName' would work but so far no luck.

Thanks for any advise.

IanW
Pretoria RSA

#6

Hi Ian,
I'm not sure what you mean by automatic.Would you consider a list box with
a drop down list of all the member names with the equivalent member ID
being the input value to the subform automatic enough? I've used this
approach many times on the project I'm working on.
In your form edit mode in the design view, click on the list box icon and
place a list box on your subform and follow the directions. Your source
table for the list box display will be your Member table and
the data to appear in the list box will be your "member name" attribute of
that table. Finally, you will select the matching attributes from the two
tables which links them. The Member ID number In the e-mail table and the
Member ID from your member table (I'm guessing) will be the attributes
you'll link. In this way, when you click on a name in the list box, you'll
be inserting " member ID" into that form.
Hank
.

#7

Hi Ian,
First things first. I'm assuming that if you were to manually insert the
"Member ID" in the appropriate text box on the subform, then the emails
you're searching for will appear. That is the impression I received from
reading your last post on this topic. Is this the case? If so, the list box
should be able to solve the problem. If your form doesn't behave that way,
I don't understand your problem.

As for concatenating First and Last Names, I have been working with the
embedded HSQL in LO and my MYSQL is a bit rusty. However I think using the
UPDATE key word is incorrect as you've applied it. You are , I think,
trying to add a new Column, ie. "FullName" to your member table, and
populate it with the full names of your members. . UPDATE is used to
change values. The keywords to add columns to a table is ALTER TABLE
tablename ADD [COLUMN] columname datatype. Check my syntax it's rusty! Then
you'll have to insert the full names into the new column in the altered
table. Why not create a view instead, using the CONCAT function.

Concatenating last name and first name in MYSQL and creating a view with
the concat function:
CREATE VIEW viewname
SELECT
Member ID,
CONCAT (LastName, ' ',FirstName) AS FullName
FROM TableName;

to check if this works execute this select statement:
SELECT*
FROM viewname

Your list box should work from this view if properly set up.
Hank.

#8

Hallo and a happy New Year to all,

especially for the developers of LO, and good health to them, so that they can
keep up with their marvellous work.

Debian Lenny, KDE, LO 3.5.5.3 (and earlier versions)

I encountered a reproducible error using LO-writer table:

Take a simple 4x4 writer table immediately followed by text hard formatted as
hidden. Then try to delete the contents of the rightmost cell of the last row
using <strg-shift-rightarrow>. This will delete the paragraph mark in that
cell and causes finally a LO crash with SIGSEGV.

Walther

#9

Hi Ian,

So all I need now is for the Sub-Form to AUTOMATICALLY pick-up the
Members ID from the main Form and to use as the filter criteria for
which eMails to show. (I will also use this form to enter new eMails so
the automatic pick-up of the Member's ID is also important here).

I'm a bit lost here. Surely, the field used in your main table to show the Member's ID in your main form should reference the Member ID of your e-mail table, via a FOREIGN KEY relationship between your tables ? Perhaps I have misunderstood what exactly it is that you are trying to achieve or how exactly you have structured your tables ?

If you need a value to appear in your main table, which is taken from your email table, then use the FOREIGN KEY relationship definition. If, however, you are inserting a value of MemberID into your e-mail table from your main table, then you need to have the MemberID field of your email table defined as a FOREIGN KEY reference for the main table. You can't expect both tables to know simultaneously whether to refer/receive at the same time, that won't happen as you would have created a circular reference.

Your questions seem to me to be far more related to how to normalize your data relationships than how to get Base to do what you want with them, and would probably be better asked in a forum dealing with such questions, but I could be misreading / misunderstanding the whole point.

Alex

#10

Hi Walther,

Hallo and a happy New Year to all,

especially for the developers of LO, and good health to them, so that they can
keep up with their marvellous work.

Debian Lenny, KDE, LO 3.5.5.3 (and earlier versions)

I encountered a reproducible error using LO-writer table:

pruning a new message into an existing thread is bad practice because your message is burried deep and won't get the visibility it deserves. Thus you won't get as many answers as a new thread would bring.

I suggest you open a new thread and re-post your message.

Best regards,

#11

Thanks Alex - Lets give this one more go around..........

I have the following -
A Members Table called 'Members' with ....

  * The Key being 'RecordID'. INT, Auto,
  * Plus the rest of my 70-odd fields.......... (This table comes up in
    my "Main Form")

I have now added another table called 'eMail' which has .....

  * The Key being 'MessageID', INT, Auto
  * Plus the rest of the fields for Date, Type, Title and Message.
    (All TXT)
  * The final Field is called 'MembersID', INT, NOT Auto,

I have created another Form for this eMail Table and by clicking a Button on my Main Form it pops-up on top of the Main Form. (All this is working fine). I now want to be able to open a Members Record, press the "eMail" Button and either add a new eMail or read the old ones.

For this I need to have the Sub-Form "Pick-up" the 'RecordID' from the Main Form and put it in the 'MembersID' field. Then if I add a new eMail it will have the correct MembersID with it and I can also then use this MembersID to search for just the eMails that apply to that Member.

This is what I'm trying to do and failing!! You mention a "Foreign Key' which maybe the answer to what I'm trying to do but how do I declare the RecordID/MembersID Relationship?

Thanks for your patience and help!!!

IanW
Pretoria RSA

#12

Hi All

Re - My previous post.... Have been doing some Googling etc and found the 'Relationships Window' for setting Relationships.

I can get the Window up, select my Tables but it _DOES NOT_ draw any connecting lines or set any Relationships!!??

Is this another "Gotcha" of using MySQL and Base together? As it does _NOT_ seem to work at all!!

I'm using PCLinuxOS 2012, LO Base 3.6.2.2 and MySQL 5.1.55

IanW
Pretoria RSA.

#13

I have the following -
A Members Table called 'Members' with ....

  * The Key being 'RecordID'. INT, Auto,
  * Plus the rest of my 70-odd fields.......... (This table comes up
in my "Main Form")

70 fields in one table? Sounds like this database may be de-normalised:

http://en.wikipedia.org/wiki/Database_normalization

I have now added another table called 'eMail' which has .....

  * The Key being 'MessageID', INT, Auto
  * Plus the rest of the fields for Date, Type, Title and Message.
    (All TXT)
  * The final Field is called 'MembersID', INT, NOT Auto,

You mention a "Foreign Key' which maybe the answer to what I'm trying
to do but how do I declare the RecordID/MembersID Relationship?

http://en.wikipedia.org/wiki/Foreign_key

The Libreoffice Base handbook hasn't been entirely translated yet:

https://wiki.documentfoundation.org/images/b/bb/BG3401-IntroducingBase.pdf
https://wiki.documentfoundation.org/images/6/65/PlanningDesigningYourDatabase_DEL_20120809.odt
https://wiki.documentfoundation.org/images/0/06/Data_input_and_removal_DEL_20121106.odt

Sincerely,

Wolfgang

#14

No, it is not a "Gotcha" for using MySQL and Base. It may be a "Gotcha" because of how you have created the database. Creating a database is somewhat like building a house: you need to have planned out what you want first and how to create it. This does take time, but it is well worth it. Some to several problems that you have had is because there seems to be no well thought out plan/design for the database from the beginning. Part of this involves normalizing your tables. As stated, 70 fields in one table is very often an indication that the table is not normalized. This can lead to errors in entered data.
      The Primary-Foreign key pairs in MySQL are defined in views, queries, and forms. All of these are written to the Base field (.odb). What I think may work is to create a view that uses the essential fields from your 'Member' table and the needed fields from the 'eMail' table. Then you could create a form for this view that has the things you want.

--Dan

#15

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 to see anyway. Seeing the "-" tells me the field is not NULL.
Hope this helps.
Girvin Herr

Ian Whitfield wrote:

#16

Ian,

Below, Girvin had a warning about joins. Specifically, he was talking about inner joins.

SQL has different types of joins. Below, I explain them. Note: It has been some time since I used SQL. I might have errors.

Sorry if you already know this information. But possibly it will be helpful to other people.

Suppose that we join two tables, called "table_a" and "table_b".
Suppose that we join these two tables on columns "column_a" and "column_b."
Our join statement will looking something like:

     SELECT *
     FROM table_a INNER JOIN table_b
     ON table_a.column_a = table_b.column_b;

Let "a" be any row in table_a that "satisfies the join condition."
Let "b" be any row in table_b that "satisfies the join condition."
Below, the text "INNER" and "OUTER" are optional when you type the joins in SQL.

1. An INNER JOIN gives you rows of the form (a, b).

2a. A LEFT OUTER JOIN gives you rows of the form (a, b) and (a, NULL).

2b. A RIGHT OUTER JOIN gives you rows of the form (a, b) and (NULL, b).

2c. A FULL OUTER JOIN gives you rows of the form (a, b), (a, NULL), and (NULL, b).

3. A CROSS JOIN is a Cartesian product between the rows of table_a and the rows of table_b. It gives you rows of the form (aa, bb), where aa is any row in table_a, and bb is any row in table_b.

Winston

#17

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 to see anyway. Seeing the "-" tells me the field is not NULL.
Hope this helps.
Girvin Herr

       These statements about joins do not seem to be quite correct. What you are describing is an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the primary key have a value.
      Suppose we have two tables A and B and that the foreign (remote) key is in table A and the primary key is in table B.
     Example 1: table A Left Outer Join table B. The output (result set) for this contains all the fields in table A and their values on the left side of the combined table. The right side contains all the fields in Table B. The rows in which the primary key value matches the foreign key value, data from both table appear in the output. However, where there is no primary key value in table B that matches the foreign key value in table A, all the fields from table B for that row will be NULL.
      Example 2: table A Right Outer Join table B. The output for this contains all the fields in table B and their values on the right side. For each output row in which the foreign key does not have a value that matches any value of the primary key, the fields in the left side of it will be NULL.
      Example 3: table A Cross Join table B. This is also referred to as a Cartesian Product. In this case, each row of table A is joined to all the rows of table B. This contains all of the possible combinations of combining both tables. Usually, some rows of the output will have the table A fields all showing NULL while others will have the fields of table B showing all NULL.

#18

Hi :slight_smile:
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 :slight_smile:

________________________________
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

to see anyway.  Seeing the "-" tells me the field is not NULL.

#19

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:

#20

Dan Lewis wrote:

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 to see anyway. Seeing the "-" tells me the field is not NULL.
Hope this helps.
Girvin Herr

      These statements about joins do not seem to be quite correct. What you are describing is an Inner Join: you will only see the rows of data in which both the foreign (remote) key and the primary key have a value.
     Suppose we have two tables A and B and that the foreign (remote) key is in table A and the primary key is in table B.
    Example 1: table A Left Outer Join table B. The output (result set) for this contains all the fields in table A and their values on the left side of the combined table. The right side contains all the fields in Table B. The rows in which the primary key value matches the foreign key value, data from both table appear in the output. However, where there is no primary key value in table B that matches the foreign key value in table A, all the fields from table B for that row will be NULL.
     Example 2: table A Right Outer Join table B. The output for this contains all the fields in table B and their values on the right side. For each output row in which the foreign key does not have a value that matches any value of the primary key, the fields in the left side of it will be NULL.
     Example 3: table A Cross Join table B. This is also referred to as a Cartesian Product. In this case, each row of table A is joined to all the rows of table B. This contains all of the possible combinations of combining both tables. Usually, some rows of the output will have the table A fields all showing NULL while others will have the fields of table B showing all NULL.

Dan,
Your and Winston's explanations of joins cleared up a lot of my misunderstanding of them. You can only get so much from books. The fact that my using an Inner Join requires non-null values to get a complete result set makes a lot of sense now. If I now understand correctly, I should be using a Left Outer Join. That way I always get the "table A" data in the result set, but "table B" data may be null if there is no match to "table A". That is the response I was looking for. I just tried it and it does give me the same result set information (no records missing) as the Inner Join. However, I have no Null values in "table A". It may take me a little while to test it with Nulls in the Left ("table A") side. I also recant and apologize for my "stupid" remark about SQL losing data. The reasons for the missing data are now apparent to me and that remark was premature.

While I was doing this join editing testing on my sample query, I took time to look at the order of joining in the Query Design window and it looks like in order to get the main table (with foreign key) as "table A", I had to first click on the "sub" table ("table B") element and then drag to the "table A" element. In other words, it needs to be primary key -> foreign key. My original response to Ian was not sure of this drag order. In any case, the Join Properties dialog and prompt show the correct order in the "Fields Involved" window, with "table A" in the left column and "table B" in the right column.

Thanks for the help.
Girvin Herr
<snip>