Does Base Have a "Lookup List" Option?

[jdanniel]

Hi everyone.

I'd like to know if Open Office Base has a Lookup List option, similar to the Lookup List/Lookup Field option in Access.

Thank you! Jd

[Villeroy]

Well, I remember that Access had such a lookup, but I do not remember why.
On functions and missing operators: http://user.services.openoffice.org/en/ ... 29&p=11140

Edit: Forget it. I remembered a lookup function, whereas the question refers to a combo box

[DrewJensen]

Base and Access implement this functionality in two different ways.

Under Access it is part of the table design process ( can be and is the most common I believe )

In Base you do this in forms using Combo or List box controls.
Base also allows you to create Foreign Key relationships to enforce the lookup list integrity and this is done in the relationship dialog ( Tools > Relationships )

[jdanniel]

Hi Drew.

I want to create a drop-down menu that has a pre-designed list of items. I know how to do this, but I'm not sure how to add to the list at a future time.

With Access, if you just create a drop-down menu, and want to add items to it later, it's a little complicated; it involves editing tab unlimited text.

However, with a Lookup Table, you simply create the table, add the items of the list, and then link the list to another field. (Okay, maybe I shouldn't have used the word SIMPLY.)

Does Base work similarly? Or is there a different method of adding items to the list that's fairly simple?

Thanks!

Jd

[DrewJensen]

Yes - since the list box is getting the display values from a separate table then to add to the list simply insert a new record in that other table. Tip - if you have an open form in a Base file and you hit F4 this opens the Datasource window at the top of the form - this window in other type of documents, say a Text document, lists all registered data sources ( base files ) in your configuration. However, when you do this for an embedded Base form only the tables and queries from the current database are listed. You can open the lookup table in that browser window and add a record right there. Close the browser window and refresh your form, the new record is now in the drop down list.

Also - you can use it in a flat table scenario:

Given a table Table1 fields ( Col1, Col2 ) where Col2 is a text type. You could create a form with a grid lets say. Then in the Col2 replace the text control created by default with a COMBOBOX ( not a listbox ). Then for the items source in the list box use this query:
SELECT DISTINCT "Col2" FROM "Table1"

Ok now when you enter data in Col2 the combo box will be filled with all distinct values in the column, as you enter the data a key by key lookup happens in the list. If you enter a value that is not in the list it is written to the table and on the next record the new value is available. A nice quick way to aid in data entry and 'help' with consistency.

[slc193]

OK, I have a form upon which I enter the info for an invoice. The form has the following fields:

Invoice - Text - VARCHAR
Vendor - Combo Box - Looks up from Vendor Table
Date of Purchase - Date Field
Entered By - Text - VARCHAR
Entered On - Date Field (How do you make this enter the current date?)
Cardholder - Combo Box - Looks up from Cardholder Table

It then has a sub form that is in datasheet view to enter each line of a purchase with the following fields:

Quantity - Number
Item - Text - VARCHAR
Catalog # - Text - VARCHAR
Purchase Price - Currency (dollar)
Show - I need this to be a combo box lookup WITHIN THE DATASHEET VIEW of my subform. How do I do that?
Department - I need this to be a combo box lookup WITHIN THE DATASHEET VIEW of my subform. How do I do that?
Account - I need this to be a combo box lookup WITHIN THE DATASHEET VIEW of my subform. How do I do that?

If I cannot have this in datasheet view and have lookups, how should I create multiple puchase lines (records) within a single invoice?

While we are at it, how do I create a field on the form that rests above purchase price column that sums up the column?

TIA.

[kabing]

In a Data Sheet layout, you right click on the column heading you want to change and choose "Replace with" and select the type of control you want to replace it with.

Then right click again on the column header and select "Column..." and the properties box will appear. Why the menu name is Column in the data sheet view and Control in other views leads to no end of confusion, but they are the same commands.

kabing

[jdanniel]

Hmmmm..... It worked at first, but now it doesn't. I have to figure this out.

But now I have another question: The tab order is screwed up, and I'm not sure how to fix it.

Where's the Tab Order option?

[r4zoli]

Your second question not connected to this topic. Next time plese submit a new separate topic.

Answer to your taborder:
Open Form Navigator from Form Desing toolbar, right click on Main Form>Tab Order.....

[DrewJensen]

well first the list box issues - and that is what it may be. If you are using the latest 2.4 release of OOo three is a bug that can render list boxes inoperable. This morning the developer on the project released a patched file for testing, that is supposed to fix the issue. ( haven't tried it yet..that is why I said supposed to, but I trust it is fixed )

Combo boxes by the way seem to not be affected by the bug.

[jdanniel]

Drew,

I think I got this figured out.

I'll let you know if I experience any problems.

Jd

[DrewJensen]

great...I'll look forward to 'not' hearing form you.. meaning that it is all working for you..LOL