Re: [libreoffice-users] LibreOffice base - cannot make a convenient form to add data

I think I can help...

On 08/05/2011 01:24 AM, 3flight wrote:
> I'm trying to make a small base with a couple of forms to view and add data.
> For instance, I have a table "Objects" and it has two fields "location" and
> "assigned to" of type INTEGER, because they are linked to ID fields of
> tables "locations" and "people" respectively. It is inconvenient to enter
> digits in table editor, so I wanted to make a form which will show linked
> values instead. Normal thing, just like in MS Access.
Very normal. Your main table has a primary key field in each record, and 
so do your secondary tables - locations and people. The keys of records 
in the secondary tables are to be referenced by fields in the primary 
table. The latter fields "point to" the secondary table records desired. 
The number in the primary table field which "points" is called a 
"foreign key", as it is the primary key of ANOTHER table. All very 
customary way of relating tables in a "relational" database.
> I created a from, modified columns "location" and "assigned to" to be
> comboboxes, and that's it.
Yes, that's it, which is to say that's your problem. I haven't worked 
with Access for about three years, but as I recall in Access a combo box 
can be used to display multiple fields from multiple records in the 
secondary table. One selects a record and the primary key of that record 
gets placed in the linked field of the primary table.

Base won't do this with combo boxes. Read your documentation. Base will 
put not the KEY but the actual DATA from the secondary table in the 
primary table field. Not what you have in mind at all, and not a linked 
relationship. It's critical to understand this.

You want a list box, which WILL do the placement of the foreign key in 
the primary table.

So, replace combo box with list box, and see if that doesn't work. It 
works perfectly for me.

However, I haven't been able to set up the list box manually (don't know 
enough SQL, etc.). Just use the wizard and it should work.

Let us know what happens.

Tom
> 1. Can't find where to select which column to use from a linked table. Good
> to me is that I have only one column in each of these linked tables. But
> there should be an option somewhere, maybe I just can't find it?
> 2. Now I have "location" combo showing proper content, while "assigned to"
> does not show anyting. The only reason I can think of is that in linked
> table "locations" the needed field is called "location", the same as in main
> table. And in second linked table the needed field is called "person" and
> not "assigned to". I'll try to rename it to have equal names but that is
> somewhat inconvenient. Again this leads to Q1 - how do I select a column,
> from which I take linked data?
> 3. Even though "location" shows linked data correctly in combobox, when I
> select anything I just get a 0 in the field. I really want to keep seeing
> linked data and not ID or whatever integer there is. And I don't understand
> why it puts 0 whatever I choose in the box.
>
> Pls hlp. =) Thank you!
>
> --
> View this message in context: 
> http://nabble.documentfoundation.org/LibreOffice-base-cannot-make-a-convenient-form-to-add-data-tp3227878p3227878.html
> Sent from the Users mailing list archive at Nabble.com.


--
For unsubscribe instructions e-mail to: users+help@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