OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Lookup Fields

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
SwissMac
General User
General User


Joined: 20 Oct 2004
Posts: 45

PostPosted: Wed Nov 08, 2006 2:25 pm    Post subject: Lookup Fields Reply with quote

How can I put a Lookup field into a Form? Eg instead of typing the name of the country every time an address is entered or typing out Telephone or Phone instead of looking the correct method of contacting up, I want a drop down list of countries/contact methods that are read from a seperate Lookup table and displayed in the form for the main form for entering personal data.

All help welcome

TIA

Mac
Back to top
View user's profile Send private message
freemc
General User
General User


Joined: 27 Oct 2006
Posts: 20
Location: Belgium

PostPosted: Thu Nov 09, 2006 9:31 am    Post subject: Reply with quote

if your form is in spreadsheet style, you can right-click the column header, and select change to... Then choose either listbox or combobox based on if you want to allow new values to be added.
_________________
Can't win if you don't play
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
SwissMac
General User
General User


Joined: 20 Oct 2004
Posts: 45

PostPosted: Thu Nov 09, 2006 1:45 pm    Post subject: Reply with quote

No, it's a Form. And they're really hard to manually edit!

Mac
Back to top
View user's profile Send private message
daddieke
General User
General User


Joined: 08 Nov 2006
Posts: 27
Location: Flanders

PostPosted: Sat Nov 11, 2006 3:54 am    Post subject: Lookup field Reply with quote

I hope I understood the problem correctly.

Link the data field to the correct field in the source table and write a query for the list content of your box.

In your case the source table might be something like t_address with several fields among which a field country. In the address form provide a combo box for the country field. If you already have a text field there is an option to replace it by another type of control. In the combo box properties select country as data field. Suppose you have a catalog table tc_country listing all countries in a field country, you then write a query like "select country from tc_country order by country" and enter it in the property list content of your combobox. As type of list contents you have to select SQL. Now you can select a country and the selection will be entered in the country field of the address.
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
SwissMac
General User
General User


Joined: 20 Oct 2004
Posts: 45

PostPosted: Sat Nov 11, 2006 5:32 pm    Post subject: Reply with quote

Thanks daddieke, your idea gave me some success but I haven't had to enter an SQL statement anywhere yet, nor found anywhere that looked like an obvious place where I should. Having said that, when I view the form the field I denoted for the combo box does show the contents of the field I wanted it to from the lookup table. I did create a relationship between the two tables though I know this shouldn't be necessary as this can be done in an SQL statement.

I selected the option "save in field of database table" rather than "show only in form" so I hope this was right.

Where should I add the SQL statement? I'm a bit lost there. And should it say something to link the two tables along the lines of:

Code:
SELECT CountryName FROM Country WHERE Country.CountryID = Address.CountryID


or something similar? I'm no SQL expert, so forgive me if I'm not making sense...

TIA

Mac
Back to top
View user's profile Send private message
daddieke
General User
General User


Joined: 08 Nov 2006
Posts: 27
Location: Flanders

PostPosted: Sun Nov 12, 2006 3:23 am    Post subject: Lookup field Reply with quote

Mac,

Now I'm a bit lost. I'll try to tell you in more detail how I proceed.

Open form in edit mode. Select the combo box and right click. Choose the option "control" which opens the properties window for the combo box. If you're dealing with a table control in a subform, choose the option "column" which gives you the same properties window. Select the tab "data".

There you find several properties :
Data field = The field where the selected value must be entered. If a value was already entered, it is displayed in the box. In your case this is the country you selected or entered in the address itself.
Empty string is null = Boolean to select if an empty string should be considered null at db level or not (never mind).
Type of list contents = What type of list represents the values presented for selection. You can select "table" if your table only has the values you need. Else you better select "sql" to enter any query you like. Tablefields gives you a list of column names for the table you select, this is not what you need.
List content = The description of the values presented for selection in the box. This is your catalog, in your case the list of countries. It can be the name of the table containing the values if you have selected "table" before or it can be an sql expression if you selected "sql". There should be no link to the destination table and field where the selected value will be stored.

I hope this helps. If it doesn't, let me know and I'll send you an example so we can discuss more specificly.

Wouter
[/list]
Back to top
View user's profile Send private message Visit poster's website Yahoo Messenger
SwissMac
General User
General User


Joined: 20 Oct 2004
Posts: 45

PostPosted: Mon Nov 13, 2006 6:47 pm    Post subject: Reply with quote

Thanks Wouter, I think I could follow what you are saying if it represented what I can see on my form, but it doesn't.

I created a new database with 2 tables and limited fields in then created a relationship between my two tables, then created the form and when I went to the properties dialogue for the form to try and recreate what you are saying there were already entries in there for some of the things you mentioned. I've now created four different databases to test out this functionality, and I can tell you I wish it were easier to format and edit the form!

Also, the kind of SQL statement that is automatically created is different to that which I am used to with MySQL. Can I have a look at your example somewhere please?

TIA

Mac
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group