[Solved] Drop Down List Box - Multiple sources

Discuss the database features
Post Reply
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

[Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

Hi,

I'm an absolute beginner, and I've managed to create a drop down list in a form, drawing data from a field in another table.

Could anybody tell me if there is a straightforward way of drawing data from multiple fields for drop down lists.

Thanks

Grant
Last edited by shalkamon on Tue Jul 19, 2011 9:50 pm, edited 1 time in total.
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Drop Down List Box - Multiple sources

Post by Villeroy »

Look at my database you already have. The "Things" form shows the related persons in list boxes.
The source of these list boxes is query "qPersonListbox":

Code: Select all

SELECT "Name" || ',' || "Name2" || ',' || "Date" AS "Person", "ID" 
FROM "Persons" AS "Persons" 
ORDER BY "Person" ASC{/quote]

I forgot to link the topic of my example database: [url=http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=40444][Example] Relations reflected by list boxes in forms[/url]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Drop Down List Box - Multiple sources

Post by DACM »

AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: Drop Down List Box - Multiple sources

Post by shalkamon »

Many thanks to both of you ... I think I may have phrased my question badly:

Let us say I am trying to construct a list box, the contents of which are drawn from two separate fields in a given table. And, let us say that at a given time each field has three entries (field one has 'red', 'orange' & 'yellow' and field two has 'green', 'blue' & 'purple'). I would like my list box to return six options .. 'red', 'orange', 'yellow', 'green', 'blue' & 'purple'.

I'm sure it must be relatively straightforward, but I can't find the solution anywhere!! :knock:

Thanks in advance :super:

Grant
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Drop Down List Box - Multiple sources

Post by Sliderule »

shalkamon:

You said / asked:
shalkamon wrote:the contents of which are drawn from two separate fields in a given table.
Let me show you the SQL ( Structured Query Language ) that will present you with your list.

In my table below, the name of the table is "TEST_75" and I have two fields of interest in the table, "COLOR_01" and "COLOR_02" . Now, depending on whether DUPLICATE values are present, and, if you want to show 'duplicates' OR NOT . . . will depend on how it is coded with the UNON or UNION ALL phrase.
  1. UNON will ELIMINATE any duplicates.
  2. UNION ALL will ALLOW any duplicates.

Code: Select all

-- Code below will ELIMINATE any duplicate values
Select
   "COLOR_01" as "Color"
From "TEST_75"

   UNION  --  Since UNION, no duplicates are presented

Select
   "COLOR_02" as "Color"
From "TEST_75"

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = 

-- Code below will ALLOW duplicate values
Select
   "COLOR_01" as "Color"
From "TEST_75"

   UNION ALL  --  Since UNION ALL,  duplicates allowed

Select
   "COLOR_02" as "Color"
From "TEST_75"
Explanation: This SQL will only run, when it is passed directly to the database engine . . . that is .. . it will result in an error IF the Base Parser is allowed. To save / run the Query directly,

Either:
  1. On the Query toolbar, press the Run SQL Command directly icon ( green check mark and the letters SQL )
  2. From the Menu: Edit -> Run SQL command directly ( so it has a check mark )
I hope this helps, please be sure to let me / us know. :bravo:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: Drop Down List Box - Multiple sources

Post by shalkamon »

Many thanks Sliderule,

Again, I wonder if I have phrased my question badly:

I'm wanting to combine the data in several fields of a given table and make them available as a 'drop down' list (as I understand it these are referred to as 'list boxes' i.e. a component of a form ... are these component parts referred to as 'form controls' as a general term? I'm still very much a beginner here ... :oops: )

So hypothetically ... 'Field 1' has so far had 'red', 'orange' & 'yellow' input, and 'Field 2' has had 'Green', 'Blue' and 'Purple' input. Now, the drop-down list in the form I'm trying to construct needs to have all six colours provided as options.

Apologies if your response already answers this ... if so, how do I make the query you demonstrate available in a drop down list?

BTW - in fact, I will need to combine SIX fields ... but I'm hoping / presuming the given principle will hold ... :?

Thanks again for your support Sliderule!

Grant
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Drop Down List Box - Multiple sources

Post by Sliderule »

I used the SQL I described in my post above . . . to create a ListBox . . . with UNIQUE Colors from two columns. The number of columns used ( UNION statementes ) is not important, that is your choice. :super:

Below, see what is presented to the used when the Form is first displayed:

Image

Below, when the drop-down arrow is pressed:

Image

Below, how the FORM was written . . . that is . . . defined . . . the General Tab:

Image

Below, how the FORM was written . . . that is . . . defined . . . the Data Tab:

Important: The Type of list content: is SQL [Native] ( this is because the SQL uses the UNION clause and Bound field is 1.

The SQL I used ( change it to match the names of YOUR table / fields ) is:

Code: Select all

SELECT 
   "COLOR_01" as "Color" 
FROM "TEST_75" 
Where "COLOR_01" >= '' 

   UNION 

SELECT 
   "COLOR_02" 
FROM "TEST_75" 
Where "COLOR_02" >= ''
Image

I hope this helps, please be sure to let me / us know. :super: :bravo:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: Drop Down List Box - Multiple sources

Post by shalkamon »

Hi Sliderule,

Thanks so much - that's really helpful! :D

Still problems however: using your terms, I can SELECT "COLOR_1" FROM "TEST_75" okay and I can SELECT "COLOR_2" FROM "TEST_75" with no problems, but ...

...as soon as I put UNION between them I get an 'unexpected token' error for the second field, "COLOR_2" in your example. :roll:

Completely stumped ..!?

Once again, thanks for your ongoing help!

G.
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Drop Down List Box - Multiple sources

Post by Sliderule »

You have NOT provided the EXACT Query ( SQL ) you attempted that caused the error.

Additionally, re-read my FIRST post to you . . . the red Explanation part and dark-red Either part. That is why, the red Important note in my graphic post above was repeated and explained again. :knock:

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Drop Down List Box - Multiple sources

Post by Villeroy »

Type of list content: SQL [native] which passes the entire query to the underlying database engine. Base itself does not support UNION.
If your list content comes from a separately stored query, open that query in SQL mode, turn on menu:Edit>"Run SQL directly" and save the query.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: Drop Down List Box - Multiple sources

Post by shalkamon »

Ok:

EXACT SQL ...

SELECT "Criterion 1" as "Unit Name 1" FROM "Unit > Criterion" Where "Criterion 1" => ' '

UNION

SELECT "Criterion 2" FROM "Unit > Criterion" Where "Criterion 2" => ' '


Forgive my confusion: as a 'newbie' I am struggling with terminology like 'database engine' and 'base parser'

Further, (and this may be a confusion going from PC to MAC) I can find neither a 'query toolbar' nor 'Run SQL command directly' in the Edit menu. I DO however have 'Sql [Native]' selected in the 'Type of list contents' dialogue box (your RED important note).

Ah..! Villeroy ... thank you once again: I had been looking for the 'query toolbar' and 'Run SQL command directly' in the Edit menu In the Form editor, I have now found it in the Query editor and have seen how to switch it on!! :lol:

How then do I 'link' the listbox's List of contents to an existing query ... Sliderule's exemplar SQL seems to stand alone (... it goes without saying that I could be mistaken here!) :crazy:

As always, with gratitude for your patience!

G.
OOo 3.3.0

MAC OS X 10.5.8
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: Drop Down List Box - Multiple sources

Post by shalkamon »

Gentlemen (or Ladies ... or both for all I know!),

Please disregard the above post - I've cracked it! :fist:

Thanks so much for your help.

I final query: information on learning Base in a structured, step-by-step way, from basic to advanced tasks seems very hard to come by. I have found tutorials on the net, but they are very 'hit and miss'.

Any suggestions?

A final thanks (... for now, no doubt!) :bravo:

G.
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Drop Down List Box - Multiple sources

Post by Sliderule »

shalkamon:

You said, assuming the name of your table is "Unit > Criterion", and it contains two fields:"Criterion 1" and "Criterion 2" :

Code: Select all

SELECT 
   "Criterion 1" as "Unit Name 1" 
FROM "Unit > Criterion" 
Where "Criterion 1" => ' '

   UNION

SELECT 
   "Criterion 2" 
FROM "Unit > Criterion" 
Where "Criterion 2" => ' '
You also said:
shalkamon wrote:Further, (and this may be a confusion going from PC to MAC) I can find neither a 'query toolbar' nor 'Run SQL command directly' in the Edit menu.
Therefore, since following instructions is not possible, and you said, you cannot find a 'query toolbar' nor an Edit menu, I cannot help you.( In my code, the WHERE clause says: >= NOT => ) Perhaps, other can help.

Sliderule
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: [Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

Hi Sliderule,

I can find an Edit menu, I couldn't find the 'Run SQL command directly' in it, however ...
Ah..! Villeroy ... thank you once again: I had been looking for the 'query toolbar' and 'Run SQL command directly' in the Edit menu in the Form editor, I have now found it in the Query editor and have seen how to switch it on!! :lol:
Thanks for the pointer on my typo ...

... also, please reread my previous post - and thanks once again for your assistance!

Grant :super:
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Drop Down List Box - Multiple sources

Post by Villeroy »

Put your selectable colours in one single source table and let the other tables' color fields reference this single source of availlable colors.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: [Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

Hi Villeroy,

Ah .. an interesting 'reverse' solution: actually the database I'm constructing would require too many hundreds of 'colours' (academic criteria in my actual database) for this to be practical. A really good idea however .. thank you!

I seem to have it working now however - thanks so much for your help! :D

I don't know if you saw my enquiry above regarding the best way to learn this stuff ... there are plenty of tutorials about, but they are very 'patchy' and often in no particular order (very basic, very advanced .. not always well written etc.). Any advice would be gratefully received ..

Thanks again Villeroy!

G.
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Drop Down List Box - Multiple sources

Post by Sliderule »

In a prior post here, you said:
shalkamon wrote:
Many thanks to both of you ... I think I may have phrased my question badly:
I think the same is true when you said:
shalkamon wrote:
there are plenty of tutorials about, but they are very 'patchy' and often in no particular order (very basic, very advanced .. not always well written etc.).
Perhaps, you could write your own for the world . . . or better yet . . . the universe . . . or . . . be perhaps, you could specific what you mean by patchy.

Yes, I did read your post ( you said I should "re-read" it ) . . . you posted it while I was writing my post ( 2 minute difference ) . . . but . . . there was NOTHING in my post that needs to be changed. Everything I wrote is accurate and to the point, detailed, including graphics to explain the solution. But, I have learned . . . learned no need for me to respond to any other of your posts. :bravo:

Sliderule
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Drop Down List Box - Multiple sources

Post by Villeroy »

This is the most complete documentation by Mariano Casanova: http://openoffice.org/projects/document ... 20tutorial
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: [Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

Hi Sliderule,

Yes I will: coming to Openoffice for the first time is both inspiring and daunting - as an absolute beginner to this sort of software it hard to get started; unlike much other software (... and largely, I suppose, as it is 'open source') as far as I am aware there is no documentation. Thus, someone in my position relies on the generosity and patience of people such as yourself and Villeroy. You have both been extremely generous with your guidance and patient with my confusion.

Not wanting to presume on the forum's patience and generosity of time more than necessary I have naturally sought and found other online tutorials to guide me: some are excellent some are less so, some are more basic than I have required at times .. some more advanced. This is what I mean by 'patchy'.

The idea that a novice such as myself could write a tutorial for 'the universe' is, of course, ludicrous, the suggestion springing it appears from the idea that I have somehow offended you Sliderule. If so I apologise - I am genuinely unsure how: indeed your explanations have been accurate, to the point and hugely detailed (I hope my gratitude was well expressed at each turn) and I have at no point suggested that anything would need to be changed - again how could I, as a novice. My drawing your attention to my previous post was to inform you that (in no small part as a result of yours and Villeroy's advice) I had solved my problem and ... once more .. was very grateful.

You have stated that you have 'learned not to respond' to any more of my posts - perhaps you would make an exception and do me the courtesy of briefly explaining what has prompted this unfortunate acrimony?


Grant .. a newcomer
OOo 3.3.0

MAC OS X 10.5.8
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: [Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

Thanks Villeroy ... you're a gentleman (.. or lady, possibly!)

Grant
OOo 3.3.0

MAC OS X 10.5.8
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] Drop Down List Box - Multiple sources

Post by Sliderule »

shalkamon:

You said / asked:
shalkamon wrote:perhaps you would make an exception and do me the courtesy of briefly explaining what has prompted this unfortunate acrimony?
No exception, no reason to waste my time with any 'exception'.

I see no acrimony in any of my posts.

Just expressing the facts.

Sliderule
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: [Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

You see no acrimony in:
Perhaps, you could write your own for the world . . . or better yet . . . the universe . . . or . . . be perhaps, you could specific what you mean by patchy.

Yes, I did read your post ( you said I should "re-read" it ) . . . you posted it while I was writing my post ( 2 minute difference ) . . . but . . . there was NOTHING in my post that needs to be changed. Everything I wrote is accurate and to the point, detailed, including graphics to explain the solution. But, I have learned . . . learned no need for me to respond to any other of your posts.
... perhaps I overestimate your capacity for courtesy.

I had assumed this forum to be a friendly supportive site .. perhaps you have become the exception Sliderule.

I think you're right, I think responses are a bad idea from this point on.

I thank you for help so far, and wish you well.

Grant
OOo 3.3.0

MAC OS X 10.5.8
User avatar
byslexic
Posts: 8
Joined: Mon Jun 27, 2011 2:37 am
Location: Chicago

Re: [Solved] Drop Down List Box - Multiple sources

Post by byslexic »

Shalkamon,
I just wanted to back you up re: difficulty in finding a decent tutorial online. I have begun reading thru the PDF that Villeroy linked to (and a number of others), and it is pretty well written, fairly thorough, informative, and nearly 200 pages long. Daunting, to say the least. A newbie myself, though by no means new to the concepts of computing, I have found it extremely challenging to even begin to structure the most rudimentary database and get the expected outcome. I've looked through a number of sources online and found nearly all 'patchy', as you have. It makes one feel like Goldilocks, but without the "just right".

Anyway, you are not alone. Take heart; and if you do find a good tutorial, post it here!
~= byslexic =~

OpenOffice 3.3 /WinXP SP3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Drop Down List Box - Multiple sources

Post by Villeroy »

byslexic wrote: Daunting, to say the least
Go to the IT section of your next book store and see how thick those books are (mere basics of Photoshop on 400 pages, MS Excel 200 pages, any programming language: 200 at least).
That PDF is an introduction on databases in general. Most of it applies similarly to almost every relational database on the market.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
byslexic
Posts: 8
Joined: Mon Jun 27, 2011 2:37 am
Location: Chicago

Re: [Solved] Drop Down List Box - Multiple sources

Post by byslexic »

Villeroy:
the "From Newbie to Advocate in a One, Two... Three!" is undoubtedly a valuable work, and I'm learning from it. In fact my only criticism is of the title, which at first led me to believe that creating a basic functional database really would be a 3 step, or chapter, process. Not surprisingly, it is not. It is 80 pages before you are instructed to turn on your computer.
And even though that is as it should be, it must be acknowledged that it is not easy as 1,2,3. It is not easy at all, at least not to most of us, which is why we come to this forum for the expert advice of the moderators... for which we are grateful... and even then we may struggle to understand the answers, because they are beyond our current knowledge.

Newbies like myself compare this (wrongly) to other applications. When starting out, I could make a useful Calc spreadsheet after only a couple hours. I fooled with Impress for less than an hour before I started making a decent presentation. Using Write is as easy as a typewriter for a beginner. Using Base is nothing at all like the others. It takes a while for us to discover this fact, and that's what is daunting.
And that's why I sympathize with Shalkamon!
~= byslexic =~

OpenOffice 3.3 /WinXP SP3
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: [Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

Many thanks to the both of you for your support ....

... the converse of the feeing of 'being daunted' is the slightly humbling generosity of time and energy forum members demonstrate, this is what makes the attitude exhibited several posts above so disappointing (and, actually, unexpected). But enough said about that I think.

I will certainly post here if I find anything Byslexic.

Thanks once again!

S.
OOo 3.3.0

MAC OS X 10.5.8
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Drop Down List Box - Multiple sources

Post by RoryOF »

shalkamon wrote:... the converse of the feeing of 'being daunted' is the slightly humbling generosity of time and energy forum members demonstrate, this is what makes the attitude exhibited several posts above so disappointing (and, actually, unexpected).
A suggestion was made that you, finding the documentation inadequate, should write a tutorial based on your own solution, which might be of wide use. You took offense at this. You also disregarded the suggestions made by some of the posters to the discussion. You may not be aware, but writing advisory posts is not a trivial matter; most of the regular posters will work through a problem and detail the steps they took to reach a solution, giving freely of their time; no doubt you feel your time is valuable - so also is theirs. For this advice to be ignored is disheartening, and it is not surprising if they decline further involvement in such a thread.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
shalkamon
Posts: 17
Joined: Mon Jul 11, 2011 1:40 pm

Re: [Solved] Drop Down List Box - Multiple sources

Post by shalkamon »

Forgive me, you are mistaken RoryOF:

i) It was my lack of ability that was the impediment to my writing documentation not the value of my time (further, I do not regard posts as trivial - I hope my explicit and repeated expressions of gratitude have been a testament to this). Neither do I find the documentation 'inadequate' (I was unaware of the existence of documentation), I found some internet tutorials more helpful than others ... I expect you do too.

ii) Secondly and more importantly (and this, I hope, is where the perspective of a newcomer may be of value to the forum) - at no point did, or would I 'ignore' anybody's advice .. of course I understand the time and effort (as I seem to have repeated ad nauseam at this point) given to these posts. I have at one point conceded that a member's advice was simply more advanced than I was able to deal with at my early stage (my apprecation goes to Villeroy on that one ...) thanked the contributor and moved on. At other times it may be confusion that has given the impression of ignoring posts - more seasoned users may have forgotten (or potentially never experienced) just how bewildering it is newly coming to this software, and having to process and often combine multiple contributions of advice, and of course things will be missed and errors made - that is the nature of learning.

Any forum thrives on 'new blood' .. and as 'new blood' to this forum I have rarely experienced on a forum such extremes of generosity and in one case discourtesy. I have no wish to cause offence to any member, but I would be less than honest if I did not offer this frank and objective perspective, again, as a newcomer.

S.
OOo 3.3.0

MAC OS X 10.5.8
Post Reply