Base form : How to display multiple fields from a single selection in a listbox

[kirsch]

Hello LibreOffice users

How to display multiple fields from a single selection in a listbox

As an inexperienced beginner, using a main form, I would like to get values from several fields in a town-list-table (TL_villes) different from the main form’s table (T_contacts).

As in the example, I choose a Town from a list box to fill the Town field (Ville) AND get the ID (ID_ville). OK, fine, it works. At the same time I would like to fill all the other fields concerned by the selected town (known by it’s ID), postal code field (CP) and so on… from the town-list-table (TL_villes).

3 points required to attach my example file or load an image ! So I first have to earn points !

[Ratslinger]

It is a bit difficult to understand what you are attempting to accomplish. However, after reading it several times it seems you want to fill in a contact record with various information found in a town table. If this is the case, you are defeating the purpose of a relational database. If information is to be used repeatedly, it should just be found in one place and a link created to refer to it by another. Now in your example it may not seem like a lot, but it can grow out of hand quickly.

I would recommend you go through the Base manual and examples contained therein. It can be found on the Document Foundation Publications site: click here.

This will also help with some terminology such as having a form with multiple forms and subforms (and even subsubforms).

DropDown2.odb

testTablesLieesen_V1.odb

[kirsch]

Hello. Thanks for paying attention to my case. Lets say it in a different way.

Selecting a town from the town-list, I save ONLY its town_ID, avoiding duplication. At the same time I would like to DISPLAY in the form (in textboxes ?), values of other fields from that town_ID record, for user information ONLY, postcode an so on…

Of course, selecting a other town has to update the displayed information texts with the new town_ID field-values. Is it possible in a form, an how ?

Robert.

[Ratslinger]

The answer is Yes. Explaining is not so simple. Create a subform under the main form. The subform needs to point to the Town table and linked to the Contact record on the common field (Town ID). On this subform place fields for data from the town table to be displayed. The two tables are semi-linked. The problem is the contact table is still in process. To see town data, place a button on the subform with a property Action of Refresh form. Click to see town data B4 contact complete.

[Ratslinger]

I’ve added a sample in my answer. However you never stated you LO version or OS( see this link). The sample was created from LO v5.1.3.2 and if yours is earlier it may not work. Think of Participants as your Contacts and imagine Sessions as Town. The principle is all that matters here. Notice that the subform changes with every change in the Participants. The button is only needed when working on a new Participan

[kirsch]

Hi @Ratslinger

Thanks for all the useful links an the refresh button action. Here is my odb file http://r.kirsch.free.fr/BD_LibO/testTablesLieesen_V1.odb
I am using LibO Version: 5.0.6.3 on Win XP or Win 10. Your example opens fine.

I am still unable to switch to a different table (TL_villes) for displaying CP and depmt. You do it by linking master and slave fields in the form properties. I have no such property available ;-(

[Ratslinger]

I’ve modified your sample (see post in original answer) to include a subform which is why you cannot find the linking. Please look through the Base documentation and learn about forms & subforms on Base forms. This is important based upon how you are creating your form.

[PYS]

Hi

@kirsch

Yes, you can display these fields in a subform: with your listbox you select an item and you get (and store) the id. So the idea is to create a sub-form based on the table to display, and linked to the main form with the primary key field of that table and the foreign key field of the main table.

In SubForm.odb the main form is based on Invoices. You can select a customer in a listbox and the subform displays customer fields.

• When entering new records, the subform is updated after saving the record in the main form.
• When updating a record, sub-form needs to be refreshed. To avoid this manipulation I added a macro that is optional.

@Ratslinger

• The structure of your example is not very relational (PARTICIPANTS: 3 session fields),
• The ID of PARTICIPANTS is named EVENTID (risk of confusion),
• The subform displays only the fields for the first session.
• It remains a non-functional query.

It seems to me that this base is an excerpt of a larger example that you have rapidly reduced to answer the question, many thanks to you. But these “remains” make it hard to understand. That is why I allow me to give another example.

Regards

[Ratslinger]

@PYS you are absolutely correct. I was in a hurry and tried to present a sample quickly. Thanks for your input.
One quick note, it seems @kirsch, based upon the comment, wishes to see the subform data before completing the input. Simply adding a Refresh button on your subform will accomplish this.

[kirsch]

Yes I wish to see the subform data before completing other input data later on. For now I’m exploring feasibility. So refresh button might be useful.

[kirsch]

Hello Rastlinger
Your DropDown2.odb was very helpful, the key for my understanding and training myself on a real project in the new world of database.

So, in order to link the two tables, following carefully your indications, I was able to create the needed sub-table containing the display textboxes. and here is my test file http://r.kirsch.free.fr/BD_LibO/testTablesLieesV2.odb

Thanks a lot for driving, the beginner I am, through the whole process. As you said “Explaining is not so simple”, I did my part of effort and reached the goal… :-))

Question is answered and problem solved.
Robert.

[Ratslinger]

@kirsch I’m glad you figured it out. Just keep in mind the documentation.

Also, it helps to know that the question was answered. To complete this process, it is helpful to others if you click the check mark on the upper left corner of the answer acceptable to you.