[Solved] Lookup Field with checkboxes

[RichPella]

Greetings!

I've looked through the forum in search of this answer. So far I haven't found the answer, though I'm willing to admit that I might have missed it.

I'm interested in creating a field similar to a "Lookup Field" in MS Access. I'd like the ability to use checkboxes to assign the field one or more of several pre-assigned values. And of course, the ability to add/change/delete the available values in the future also would be nice.

How do I do this?

[Villeroy]

I'd like the ability to use checkboxes to assign the field one or more of several pre-assigned values. And of course, the ability to add/change/delete the available values in the future also would be nice.

A lookup field in Access is not what what you describe: http://www.brainbell.com/tutorials/ms-o ... _Field.htm
And quite often such a field is misleading since it displays content which is not the actual content column: http://www.mvps.org/access/lookupfields.htm

What you describe is a many-to-many relation. It requires 2 lists of items and a 3rd table mapping the 2 different kinds of items to each other.
Mapping a list of persons to zero, one or more items:
tblPersons: ID, Name, Birthday,...
tblP_I: Person_ID, Item_ID
tblItems: ID, Name, Price,...

Then you create a form for the persons with a subform of items and/or a form for the items with a subform of persons.
Same with movies and genres: http://user.services.openoffice.org/en/ ... hp?id=2879

[RichPella]

A lookup field in Access is not what what you describe

Actually, it is. It's called a Multivalued Lookup Field. The link you provided skipped over this part of the Lookup Wizard:




When applied, it looks like this in Datasheet View:




Your response leads me to believe that there is no way to duplicate this feature in OOo. I guess I'll just have to go with the form/subform idea. I find that entering data by that method is slower than entering it in the Datasheet. Apparently, though, there's no alternative.

[Villeroy]

Look at form "Movies" in the linked database file. There you see a kind of "nested list box" where you can select items from inner list boxes into the outer list box (which is a table control actually). The inner lists show all availlable items, the outer list shows the selected items only. The functionality is exactly the same. Mapping a many-to-many relation to form controls.

[RichPella]

Thanks for the info.

[RichPella]

Okay, I see the result of what you've done on the form in the sample "Movies" file. I have no idea how to create it. Can you help me?
The only resource I have found is the "Getting Started with Base" guide, which refers me the the "Database Guide." I have been unable to locate the "Database Guide" anywhere.

[RPG]

http://documentation.openoffice.org/man ... thBase.pdf

Google is your friend in this case

[RichPella]

Good attempt, RPG.

The link you provided leads me to the "Getting Started with Base" guide. As I mentioned in my previous post, I already found that guide (using Google, thank you very much).
As I also mentioned in my previous post, the "Getting Started with Base" guide contains no instructions on creating forms and subforms in Design View. Instead, it tells me that those instructions are outside the scope of the "Getting Started with Base" guide, and that I should refer instead to the "Database Guide." It is this very same "Database Guide" that I have been unable to locate.

When I Google for "Database Guide," the only thing I find is a bunch of people telling me that it doesn't exist yet. However, all of those posts are 1 to 2 years old. Therefore, I am willing to accept any assistance on this subject that anyone can provide.

Forgive me for being new to the software. The only other database program I've ever used is dBASE IV, and that was many years ago. I recently took a class in which I learned about MS Access. Since I have no desire to use that software, I'm attempting to learn Base. Without documentation, learning Base is proving somewhat difficult.

[Villeroy]

menu:Tools>Relationships reveals the relations between the list of movies and the list of genres through a third table mapping both lists.
The form is a main form for the list of movies and a subform for the genres mapped to the currently selected movie. No, the form wizartd can only produce the main form. You have to draw the subform manually.
Open the Movie_Genre form for editing.
Get toolbar form design and push button #5 for the form navigator which reveals the hierarchy of main form, subform, table control.
The data-properties of the subform reveal that the form is bound to the 2 ID-fields of the linking table with the movie-ID linked to the parent form's movie-ID, so the subform filters all records with the same movie-ID as the main forms currently selected movie.
Get the properties of the table control's column header which reveals that the column wraps a list box control.
The data-properties of the list box control reveals a 2-column list, a visible column of genre names (colomn #0) and a bound and hidden column of genre-IDs.
The small extra form where you can enter a new genre name is totally independent from the above form and subform. Normally it would be on a separate form document, but I tried to demonstrate that it can be done this way as a third extra form on the same page.

How to build up such an animal:
1. Having the 3 database tables (2 lists, one glue table with IDs of both lists), you create a simple new form for one of the lists.
2. Get the form navigator and add a subform to the form, bound to the glue table and link the subform through the ID it has in common with the parent form. Set the subform's data properties "Link master" and "Link slave". Click one of the small [...]-buttons to get field lists to pick from.
3. Select the subform in the navigator and draw a table control (toolbar "Form Controls", button "More Controls", on the next bar you find the tool to draw a table control). If the table control happens to land on the main form, you can use the form navigator to move it back onto the subform where it belongs. If a wizard pops up, cancel the wizard. Base wizards have a tendency to be unhelpful. You can turn off the table's row selector and navigation bar to make the table look more like a list box wrapping other list boxes. There is nothing to bind the table with. The table control is a container for bound controls to appear as table columns.
4. RIght-click the empty column header of the table and insert a new list box as a column of the table.
Get the list boxes data properties (right-click>Column...tab:Data).
4a, Data field: The other ID field you want to write into the glue table (in the above example the genre-IDs of a movie).
4b, Type of contents: SQL
4c, List content: SELECT "Name", "ID" FROM "SecondList" ORDER BY "Name"
gives a sorted list of sub items, names and corresponding IDs.
4d, Bound field: 1 [we write the IDs of hidden column #1 into the data field, column# #0 is the first column of names to pick form]
You can compose the query in the graphichal designer when you hit the tiny [...]-button right of property "List content". The query gets saved in the form and not as separate query. Alternatively you could define a separate query, choose 4b, Type of contents=Query and 4c, List content=the query name. That could be useful if you need the same item pick list several times.

[RPG]

Hello

Sorry If I understand you wrong.

Maybe you can mean this guide.
http://documentation.openoffice.org/ser ... lderID=778

It is about database design and forms.

You can see forms more as queries. And with the forms you can connect one or more queries.
The word form is used for a document and also for a query in a form.

When you design a form as document then you can insert more then one form as query. For understanding the form design there is a form navigator. The form are organize in the same way as map in the explorer.
This means also you can have more then one main form in a form as document. The subforms help you for selecting data.

I think there is no difference in designing your tables with other database. And OOo-base is a frontend for databases with the HSQLDB as builtin backend.

It is important to understand the master/slave field in master and subform. Also useful are the listbox, combobox and gridcontrol.


Romke

[RichPella]

Wow, that's a bunch of information, and it looks really complex. I don't have the time available to sit down and understand all of that, much less implement it. Therefore, I'll just assume that you two know what you're talking about, and mark this thread as Solved. I'll store my information in a Calc spreadsheet, because I understand that. Later, when I have time to sit down and understand everything you've told me, I'll go back and look at it. That probably will be 1-2 years from now.

[Villeroy]

I'll store my information in a Calc spreadsheet,...

Trying the roller blades because the pickup can not compete with the truck?

... because I understand that.

Sorry, a spreadsheet can not do anything like that.

[RichPella]

I didn't say it could do anything like that. I said it would store my information until I have time to wade through the process of understanding Base, so I can store my information the way I want.

I haven't the first clue what you mean by roller blades, a pickup, and a truck. The statement does appear to be derogatory in nature, though.

I did NOT say that I am giving up on Base. I DID say that while I'm in college and running a business from home, I don't have time to dedicate to a project that provides only minimal benefit. Cataloging my DVD collection the way I prefer is something that can wait until I'm out of school. There is no pressing need for it. Just because Base comes naturally to you, or you have more time to devote to understanding it, does not mean that someone who doesn't have time to work with it is stupid or deserves to be insulted.

[Villeroy]

Truck=Access
Pickup=Base
Blades=Spreadsheet

Your problem is completely unrelated to Base as a particular database tool. I promise, a spreadsheet will waste by far more of your precious time. You have Access installe and you know how to do something with it without understanding the underlying database logic. Why don't you use MS Access?
A database is not naturally at all. It is highly abstract.

[RichPella]

I do not wish to use Access because I do not favor Microsoft products. Within a few months, I will transition to a Macintosh computer and be rid of Windows. Even if I did want to use Access, my trial license will expire in 2 weeks and I'm not able to purchase a full license right now.

I just completed a computer course in which I learned the underlying database logic you seem so certain that I do not understand. It is only the external workings of Base that I haven't figured out yet. The reason I have MS Access in the first place is because it was part of the course.

The spreadsheet will not waste any of my time. I can enter the most important information in a very short time. Later, I can import that information into Base, and then add the things that Base can do that the spreadsheet cannot. That is the only thing I was saying.

[Villeroy]

Yes, sure. A spreadsheet may collect unrelated lists of movies, actors and directors as indicated in your Access screenshot. But I don't see any reason why a calculator can be a better list keeper than the worst database.
Collect the unrelated lists of items in Base to prevent common errors due to wrong types, missing data and duplicate entries. Later you won't have to import from a lose structure (sheets) into a rigid structure (database). The opposite is possible at any time. Database data fit all office documents with simple drag&drop methods.
Filtering and sorting works far better in Base than in Calc.
The most simple input forms for flat tables with combo boxes, date controls (birth days) are not complicated to produce.
Later you may start with one-to-many relations editable through list boxes (one director per movie) before you map multiple actors to multiple movies as outlined.

[RichPella]

I may do that.

[qaz1qaz1qa]

Sorry for posting to a dead thread but the topic relates to what I am currently researching
The file http://user.services.openoffice.org/en/ ... hp?id=2879 contains no database and therefore I cannot open any of the forms.
Possibly you could link to the full file?

Thank you

[Villeroy]

The link belongs to a very, very long thread about many-to-many relations and it downloads a database named "Movies3.odb". Try again.