I have a table called wotbl with a field called plbmid and another table called comtbl. The comtbl has a list of Plumbers, and other construction companies, and I want a drop down picker to reflect the Company Name in the wotbl plbmid field, What I get is the Primary Key in that field. How do I assign the label to show me the Company Name in the drop down?
A sample is attached demonstrating the following. There are three scenarios. The forms are “Participants”, “Participants2” and “Participants3”. each form uses related tables - “Participants”(your ‘wotbl’) and “Companies”(your ‘comtbl’).
The Participants form gives you just what you asked for in your question - the Name vs the ID in the dropdown. The problem here is since the key field is different you can have multiple companies with the same name. This can be viewed in the dropdown list and the Companies table.
The Participants2 form uses the Name as its’ Primary key. Now if there are multiple companies with the same name, something must be added to the Name (used the city in the example) for it to be unique.
The Participants3 form is the same as the Participants form except the Name inserted into the Participants3 table is a concatenation of the Company Name, Address and City.
All dropdowns work basically the same. Look at the dropdown list box properties in the Data tab. The Type of list contents is SQL and the List content is the selection which is aided in generation by choosing the ellipsis (...) at the far right.
There are certainly many other ways to go. Wanted to present some potential problems and choices.
Sorry I have not gotten back to this. The last question I asked I got email notifications when answers came in, atleast I think I did, anyway didn’t get any notifications so I did not check until now. I solved the issue with a select statement but apparently because I am linked to a Postgre database things don’t work quite the same way as in a HSQL database. So I had to define the relationship in the SQL Select statement and it does work now. However I also have other issues.
I have more questions related to this, should I start more forums or ask here? After I got things working in HSQL I moved over to postgreSQL, and it is starting to give me some errors.
Based upon your comment, best to start a new question. Please include your LO version, OS and the DB you are using. Most times this comes into play.
ok, my next question should be here. Associative Table error in LibreBase and PostgreSQL I hope it is clear.
Cheers