if you have a table with items shopname and shopplace (so there are shops with the same name, but in a different town. In the form I select the name via a listbox. How can i in the 2nd listbox see only the places where these shops can be found, so I can select the right shop?
So these cells would have to be connected also.
Hello,
It is usually helpful to know what specific version of LibreOffice you are using, your OS, and what database is being used in Base (and connector type when applicable).
Now it is not clear but it appears you are looking to create progressive list boxes - results of one based upon selection of another. Is this correct?
Is also seems this can be done in one list box by concatenating the name and place for the list. Have you considered this?
Hello,
I run LibreOffice version 6.0.7.3 on Ubuntu and created a HSQLDB embedded.
The way you mention is correct. Did not no what the word would be but the way you explain progressive list boxes it is.
I have considered concatenating the name&place in the beginning and use this also as primairy key. I want to solve it in another way though as I have more situations, where progressive list boxes are helpfull to fill forms with the needed data.
Hello,
After further reading of the question and comment, your goal is not quite clear. My first impression was that the list boxes are to select records for display - filtering. For that see this post and its’ many samples → Filter/Search with Forms (leveraging SubForms)
A reading of the comment leads me to think this is for record entry. If so, this requires writing macros specific to your needs. Additionally there are two different methods in doing so. One if the records are on a table grid and another if these are individual fields. This post Filtering dropdown boxes in base, Table control contains another link to samples of both. Code is included in the samples.
It is clear to me now that I have not been clear 
Indeed I want to fill a form (Promo_Distribution_Form) partly with data from tables. For what I would like to know, I believe 1 table is sufficient as example:
Customer_table
Shop_ID
Shop_name
Shop_place
There are Shops that carry the same Shop_name, but are located in different places (and of course have different Shop_ID)
In order to fill this form I need to add the correct customer to identify the shop. In the first listbox I select Shop_name, when opening the 2nd listbox, I would like to see only the locations where shops with this name can be found, for me to select.
Basically it is indeed a filter function I am looking for, but can not find the way to solve this in the form.
In examples where you have 2 tables connected with a 1-n relation I could follow as the key was used to identify. Here there is no key for this as it is 1 table.
If I for listbox2 could somehow refer to the selection made in listbox 1 (Shop_name), it would be a step in the right direction, no?
It really doesn’t matter how many tables are involved. But the real situation here is why you need two fields for this? A list box can display one thing and store another. With this you can display Name - Town and when selected store the ID.
With doing so, anytime you want to retrieve either name or town you can use this ID and the appropriate column.
I believe this is called concenate no? Does this go if you do not concenate? The reaon why I want to do it that way is that I also want this to work for entering a list with promo. Because of the long list and the amount of text per line. I would 1st want to select promotype, so the next drop down list is already shorter and then have him in another box choose the product from the product description. I believe to have this in one cell would make it very time consuming if you need to enter a lot of promo items.
Am I thinking in the wrong way?
The problem lies that I can not get it how I can get a selected variable x to become filter criteria for the second listbox. At leat that is my tought but maybe I am looking in the wrong direction.
No it is not concenate but rather:
con·cat·e·nate
/kənˈkatnˌāt/
verbformal•technical
verb: concatenate; 3rd person present: concatenates; past tense: concatenated; past participle: concatenated; gerund or present participle: concatenating
link (things) together in a chain or series.
"some words may be concatenated, such that certain sounds are omitted"
As for your remarks, the only way to accomplish as you want is to write a macro specific to your needs. The basis of the macros is in the linked samples in the answer. Cannot give you specifics as code depends entirely upon many of the naming conventions in your Base file and database.
When all is in the form is filled this would be the selection made of right customer (from table customer), selections of the right promo table Promo, date and name team member. All the data except the date will be stored in the form of their ID as foreign keys.
TABLE CUSTOMER TABLE PROMO TABLE PROMO_DISTRIBUTION
–shop_id - Promo_ID - Prmo_distr_id
oeps lined it all up nicely but does not loo that good now.
TABLE CUSTOMER
TABLE PROMO
from this + Date and Team_memeber_name table needs to be created
TABLE PROMO_DISTRIBUTION
Do not understand what these last two comments are about. Appears you do not know how to write any macros. Have seen no code from you after giving you examples. If you do not know how to write macros how are you going to repair when something goes wrong or a change needs to be made? And you cannot just post something and expect people to write code for you. It takes time and resources. You should present what code you have tried already and where you may be having problems. In most cases the entire Base file is needed (without personal or confidential information).
I was not sure if code needs to be written for this, as I thought there would possible be a way in Base to do so. I defenitely do not ask for somebody to do the job for me. I thought maybe this has been done before and I could folow the analogy and learn from it. Worked for me in the past. Anyway it feels a little lke I am a burden here and do not want to be. Sorry for the trouble and thanks for your help.
Had mentioned multiple times that what you wanted needs macros (code) and even gave you links to examples.
We are here to help but it seems as if you are asking the same question over & over again and wanting someone to do this for you.
If I wanted you to right the code for me I would have not started with asking I have 2 listboxes and 1 table. I want the first listboxes choice to function as filter for the 2nd. I went through all the examples. Every time it concerned 2 different tables where the id’s have the function to retrieve the data.As you mentioned everytime I do not understand what you mean I tried to explain better. The suggestion to concatenate, Is how I started of in the beginning, but was not a solution.
I concatenated it in calc first. In calc by the way I manage quite well. Yes its true I am not very strong in the programming language used. I found visual basic a lot easier to handle and learn. Open source is relatively new to me. In calc via recording macro’s I learned it a little. i feel somewhat offended you mentioning I want you to write to code for me. I spent hours trying to figure this out on my own, watching you tube video’s on base, sql etc. nothing there. last thought was the forum.
I always hesitated, because first I wanted to do this myself and second because I thought this is only for people that now a on the subject. Well i guess my feeling was right and I will go trying to figure it out on my own.I time I will. You say you are here to help, but apperantly in this case you were not able. Except for teaching me to spell concatenate. So thank you for that.No hard feelings from my side and wih you all the best with the forum.
You did ask a question in your second comment which I should have answered:
If I for listbox2 could somehow refer to the selection made in listbox 1 (Shop_name), it would be a step in the right direction, no?
To be clear, have given you all the information you need but you seem to not be able to comprehend piecing it together.
If you want progressive list boxes your one table has two columns beside the key. This is just like the two table example. You can base the first list box on SQL select of Distinct Shop_Names. Then use this selected item as the basis for the second list box:
Select Shop_Place, Shop_ID from Table where Shop Name = selection from first list box
Obtaining the information and putting it together is where the macros come in - examples given. You do have the two necessary tables - just in the form of one.
The selection from the first list box is useless as far as saving the data in the record. The second list box selection has all you need
Thanks I will give it some more effort. Meanwhile as a quick solution, i want to conatenate in base. It works with this command:
SELECT CONCAT( “Promo_type”, “Promo_description” ), “Promo_id” FROM “Table_Promo_Items”
I can’t find the way to enter a dash for having some space between the 2 tables. In calc it is no problem, but in base using symbols like ‘’- " between comma’s does not work as it would refer to a table. The same with ’ - '. I looked at SQL commands and examples, but nothing works.
Hello,
Use:
SELECT "Promo_type" || ' - ' || "Promo_description" As "MyConcat", "Promo_id" FROM "Table_Promo_Items"Thanks tworks that gives me time to figure out the previous. Still working on last the tip you gave:
Select Shop_Place, Shop_ID from Table where Shop Name = selection from first list box
Will figure out. Thanks again