Welcome to NeoOffice developer notes and announcements
NeoOffice
Developer notes and announcements
 
 

This website is an archive and is no longer active
NeoOffice announcements have moved to the NeoOffice News website


Support
· Forums
· NeoOffice Support
· NeoWiki


Announcements
· Twitter @NeoOffice


Downloads
· Download NeoOffice


  
NeoOffice :: View topic - Creating a list using a combo box.
Creating a list using a combo box.
 
   NeoOffice Forum Index -> NeoOffice Releases
View previous topic :: View next topic  
Author Message
bryoman
Sentinel


Joined: Jan 07, 2009
Posts: 27
Location: Carlisle, England

PostPosted: Thu Nov 22, 2012 4:23 am    Post subject: Creating a list using a combo box.

This seems like a such basic thing to do - but I can’t find an instruction or a reference to how to do it!

In essence: I have a list of wild-plant names (about 2000 names - complex scientific names!).

After a day out recording, I want to access that list, to create a new list - a subset - of my sightings for that day, to go in my main natural history spreadsheet.

SO FAR, I’ve got a combo box, successfully referencing my list, with 20 names appearing in a dropdown window, autofilling beautifully, and the selected name appearing in a Linked Cell.

But what do I do next?? Perhaps there’s two steps: I need to clear the combo box entry with a keystroke (<rtn>?), whilst NOT losing the entry in the Linked Cell.

Then I need to select another name in the combo box, by which time the Linked Cell focus will have moved DOWN one cell… so I finish up with a list of my finds.

Or do we do this another way? Can the Linked Cell entry be bounced each time to the bottom of a new list somewhere else?

Grateful thanks for allaying my frustration!

The next natty step would be to have other combo boxes with list of birds, lichens, etc, etc, which could be similarly used - but it would be great to have them adding *into* my first list, so I finish with ONE list, made up of selections from several separate lists.

(I’ve got ca. 50 years of this stuff to try to digitise… you can see how I want a slick way of doing it! I’ve not got 50 years left to type it all up - without some help!! Very Happy )
Back to top
Lorinda
Captain Mifune


Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA

PostPosted: Thu Nov 22, 2012 6:57 am    Post subject:

I presume you are doing this in Base?

And if I understand you correctly, you are going out on "field trips" to identify plants (and birds, and animals, etc.) and want to enter all your sightings into a database?

I suggest you start by reading through the "To Base and Back Again" tutorial in the wiki. It's a (mostly complete) tutorial on creating a database designed to track bird sightings.

I will warn you, however, that it was written using an older version of NeoOffice. It's entirely possible that some things don't work the same any more.

I should also warn you that working with Base is not for the feint of heart. While I enjoyed learning what I did working on the tutorial, I no longer use a Base setup for tracking my bird sightings. I found an iOS app that was much less hassle to use.

If, after looking at the tutorial, you are still interested in using it to build your database, I'm happy to help you think through what tables you need. It's been long enough ago since I worked with the specifics of combo boxes, etc. that I may not be much help there.

Lorinda
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Thu Nov 22, 2012 11:00 am    Post subject:

Before we get into any of the potential database issues that Lorinda has mentioned, I would like to troubleshoot your combobox settings first.

From your description, I do not think I am seeing what you are seeing. Maybe I am using different steps than you?

Here are the steps I used to enter items in the combobox. Are any of my steps different than yours? Note: for this, I setup a sample spreadsheet with a combobox tied to a table in a Base database. The table has a single text column and I set that column as the "primary key" to prevent insertion of duplicate items:

1. I put the cursor into the combobox's text field and replace or edit the text. NeoOffice will find the first matching item and autocomplete the text to that.

2. If there is no autocompletion, I could insert the new item into the database and the inserted item would become the linked item and stay in the text field by pressing the "Save Record" icon (the disk drive) in the Form Navigation toolbar at the bottom of the window inserts the item into the database.

3. Alternately, if there is no autocompletion, I could insert the new item into the database, unset the linked item, and clear the text field by pressing the "New Record" icon (the green circle with a white plus sign) in the Form Navigation toolbar at the bottom of the window inserts the item into the database.

Patrick


Last edited by pluby on Thu Nov 22, 2012 12:16 pm; edited 1 time in total
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Thu Nov 22, 2012 12:13 pm    Post subject: Re: Creating a list using a combo box.

In addition to the questions in my previous post, I have some steps for you to try that might fix the following behavior that you described:

bryoman wrote:
But what do I do next?? Perhaps there’s two steps: I need to clear the combo box entry with a keystroke (<rtn>?), whilst NOT losing the entry in the Linked Cell.


Are you seeing list items get overwritten? If so, can you try changing the combobox's "Add data only" setting using the following steps?:

1. Display the Form Controls toolbar by selecting the View :: Toolbars :: Form Controls menu.

2. In the Form Controls floating window that appears, enable design mode in your spreadsheet by pressing the "Design Mode On/Off" icon (the blue triangle).

3. Once in design mode, right-click or Control-click on your combobox and in the popup menu that appears, select the Form menu item.

4. In the Form Properties floating window that appears, select the Data tab, set the "Add data only" setting to "Yes".

5. Disable design mode by pressing the "Design Mode On/Off" icon in the Form Controls floating window.

6. Refresh the combobox by pressing the "Refresh Control" icon in the Form Navigation toolbar at the bottom of the window.

After setting the "Add data only" setting to "Yes", does your linked item no longer get overwritten?

Patrick
Back to top
Lorinda
Captain Mifune


Joined: Jun 20, 2006
Posts: 2051
Location: Midwest, USA

PostPosted: Fri Nov 23, 2012 9:17 pm    Post subject:

Re-reading your post and Patrick's comments, it looks like you are using a combo box in a spreadsheet, not a Base document. Is that right?

That may well be a better way to go given the complexities of using Base.

I also wanted to clarify that while I can help you think through the tables needed, I'm not in a position to be able to work you through all the complexities of setting up a full database. And my offer of help with the tables was from me personally as a volunteer here, not an "official" offer of support from the developers. My apologies for not being clear about that from the start.

Lorinda
Back to top
bryoman
Sentinel


Joined: Jan 07, 2009
Posts: 27
Location: Carlisle, England

PostPosted: Sat Nov 24, 2012 7:54 am    Post subject:

Lorinda wrote:
Re-reading your post and Patrick's comments, it looks like you are using a combo box in a spreadsheet, not a Base document. Is that right? That may well be a better way to go given the complexities of using Base.
Lorinda


Lorinda:

Thanks for your offers of help - yes, I am using a simple spreadsheet (at the moment) for this data entry task, and I have several years'-worth of information already entered.

All I'm wanting to do here is speed up data entry by being able to *select* those pesky long scientific names out of an existing long list of 'all possible' names (a list I have already) and shift the selections into a list in an existing column in my spreadsheet.

Thus: at the end of a 'day out' I can rapidly create a list of a few dozen species-names in my 'Names' column, and I can then add the rest of the details (date, etc., etc.) in other columns. (Yes - a database is the way to go, but that's not the way I'm doing it.... yet!)

Cheers,

Jeremy
Back to top
bryoman
Sentinel


Joined: Jan 07, 2009
Posts: 27
Location: Carlisle, England

PostPosted: Sat Nov 24, 2012 9:15 am    Post subject: Re: Creating a list using a combo box.

pluby wrote:
In addition to the questions in my previous post, I have some steps for you to try that might fix the following behavior that you described:

bryoman wrote:
But what do I do next?? Perhaps there’s two steps: I need to clear the combo box entry with a keystroke (<rtn>?), whilst NOT losing the entry in the Linked Cell.


Are you seeing list items get overwritten?


Hi Patrick:

I'm afraid I'm confused about the 'list items', above:

Are you referring to the names in my SOURCE list of names?? In that case, No, they're not getting overwritten.

Are you meaning I should be seeing some list of names building up somewhere outside the combo box, reflecting my selections? Then No; no such list is being generated. (I want it to make such a list!)

As of now, the combo box works perfectly with autocomplete, etc., and the currently-selected name appears in the "Linked cell" I chose. So far, so good - just as I expected.

Bu then clearing the combo box for the next entry simply clears the Linked Cell as well... when what I want is for the focus to be moved for my next selection (e.g. to the cell below!), so I gradually build up a LIST of my selections. (You see the problem I have with describing this very simple proposition in very simple language!!)

What I need NEXT is to be able to select a different name in the combo box, and have THAT selection saved somewhere/somehow in addition to the previous one! And so on.

BUT see also below...

pluby wrote:

If so, can you try changing the combobox's "Add data only" setting using the following steps?:

1. Display the Form Controls toolbar by selecting the View :: Toolbars :: Form Controls menu.

2. In the Form Controls floating window that appears, enable design mode in your spreadsheet by pressing the "Design Mode On/Off" icon (the blue triangle).

3. Once in design mode, right-click or Control-click on your combobox and in the popup menu that appears, select the Form menu item.

4. In the Form Properties floating window that appears, select the Data tab, set the "Add data only" setting to "Yes".

5. Disable design mode by pressing the "Design Mode On/Off" icon in the Form Controls floating window.

6. Refresh the combobox by pressing the "Refresh Control" icon in the Form Navigation toolbar at the bottom of the window.

After setting the "Add data only" setting to "Yes", does your linked item no longer get overwritten?

Patrick


Right - I have followed the above, but find the next problem at no. 6: my Form Navigation Toolbar is fully greyed out, so "Refresh Control" is inaccessible! (I AM back out of design mode, but of course can't explain why the FNT is not active.)

But ref the last item: yes - the linked item is no longer overwritten. (So altering the entry in the combo box is not now reflected anywhere.)

Big question: should I be using some OTHER control that the 'Linked Cell" thing to get my list of selections?

[At the risk of overkill: Have I made it clear just what I'm aiming for?? ... Select one name from my 4000 name-list in one (combo?) box; hit a key: name appears in another cell somewhere. Select another name; hit the key: new name added below previous name; repeat ad nauseam; finish up with a list of names.]

Thanks for your patience,

Jeremy
Back to top
James3359
The Merovingian


Joined: Jul 05, 2005
Posts: 685
Location: North West England

PostPosted: Sat Nov 24, 2012 10:36 am    Post subject:

NOTE this is an entirely different approach from your combo box suggestion…

If you already have all these names you can create what is effectively a custom dropdown list using the technique described on this techrepublic blog for Libre Office. Effectively it enables you to use a list from one sheet in the spreadsheet as a dropdown for filling cells in a different sheet.

Once you have created this dropdown feature in one cell, you can copy it to all the other cells in the column by using Edit>Fill>Down. As you place the cursor in each of the cells so filled, a little dropdown button appears to the right of the cell to bring up the values, and beginning to type the name brings up the right entry in the list.

You might just want to try it on a small scale to see how it works before you go for your 2000 name list though.
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Sat Nov 24, 2012 10:52 am    Post subject: Re: Creating a list using a combo box.

bryoman wrote:
But then clearing the combo box for the next entry simply clears the Linked Cell as well... when what I want is for the focus to be moved for my next selection (e.g. to the cell below!), so I gradually build up a LIST of my selections. (You see the problem I have with describing this very simple proposition in very simple language!!)

What I need NEXT is to be able to select a different name in the combo box, and have THAT selection saved somewhere/somehow in addition to the previous one! And so on.


I now understand what you are trying to do. Unfortunately, the Linked Cell is fixed and does not ever move or shift.

This may seem odd, but the linked cell is designed for feeding a new number or text running into whatever formulas or cells reference the linked cell. For example, if a user's combobox has quantity values of some product, a formulas in another cell can then use the value in the linked cell to show the total price for the quantity selected in the combobox. Then when they change the quantity, the formula cell recalculates its value from the new value in the linked cell.

This is the limitation that you are going to keep running into when using a spreadsheet: controls and formulas replace values so appending values onto a list is actually not a simple thing at all.

You might want to try the alternate approach in James3359's post. If that approach does not work, the only other way that I can think of to do what you are trying to do is add a button control and write a custom macro that runs when the button's "mouse button released" event fires.

Unfortunately, writing a custom macro is programming work so it may or may not be something that you have the time or skills to do. We do not have the resources to do custom programming work but if you are interested in programming a macro yourself, here is the steps that I would try to code in the macro (the OpenOffice.org Basic programming language reference is available here):

1. Position the current cell to the cell at the start of your list (don't use the combox's linked cell for your list)

2. Move the current cell down a cell until your hit an empty cell

3. Read the value in the comboxes linked cell

4. Write that value into the current cell

If James3359's approach does not work and programming a macro yourself is not something you want or are able to do, let us know and we can refund your support payment.

Patrick
Back to top
bryoman
Sentinel


Joined: Jan 07, 2009
Posts: 27
Location: Carlisle, England

PostPosted: Sun Nov 25, 2012 7:05 am    Post subject:

James3359 wrote:
NOTE this is an entirely different approach from your combo box suggestion…

You might just want to try it on a small scale to see how it works before you go for your 2000 name list though.


Hi James3359 (we seem to live nearby - within a few parsecs, anyway! Very Happy):

This dropdown list is a natty feature I was not aware of. (I used the "Approach #1", but didn't get the "Approach #2" to work, for some reason.)

Yes, it does work very nicely - but with provisos:

A) I can't see that it can be set to autocomplete, which is so handy for LONG lists in a combobox.

B) I get the "Err:512" when the list gets too long - not "512" items, but about 257 - seems to vary a little - so nowhere near my 2000 names for a plant list. I gather this limit can't be extended (?).

I could find this very useful for shorter lists (e.g. for birds, where there are far fewer that I encounter at all regularly), so many thanks for the suggestion.

Jeremy.
Back to top
bryoman
Sentinel


Joined: Jan 07, 2009
Posts: 27
Location: Carlisle, England

PostPosted: Sun Nov 25, 2012 7:16 am    Post subject: Re: Creating a list using a combo box.

Patrick:

Grateful thanks for your explanation above of the problems with trying to achieve what I'm aiming for here - and your further suggestions. I'm a long way from writing my own macros, but I'll certain have a try with your ideas as to how it might be done. It's all learning curve...

Thanks again,

Jeremy.
Back to top
James3359
The Merovingian


Joined: Jul 05, 2005
Posts: 685
Location: North West England

PostPosted: Sun Nov 25, 2012 9:31 am    Post subject:

OK Jeremy, sorry the approach didn't quite do the trick for you. You're right that the Err 512 is a built-in limit which you can only get round by handling the data differently. I suspect that it may be the sheer size of a list of 2000 long names which is the problem.

Edit by pluby: "Approach #2" does not have this limitation. See this post for steps to enable "Approach #2".

My limited experimentation with the feature is that it offers a kind of auto selection from the drop down on the basis of what you type, bit that it doesn't fully operate if your list is not fully sorted alpha-numerically, and if a number of names are the same for, say, the first ten characters the only when you type the eleventh character will it get to the item you want.

Edit by pluby: autocompletion will work if the "data validity" cell is in the same column as the cells with the list of items. See this post for an example.

Good luck with your project.

(PS I suspect that even only a few parsecs apart we might see rather different avian life and flora Wink )
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Sun Nov 25, 2012 9:34 am    Post subject:

bryoman wrote:
A) I can't see that it can be set to autocomplete, which is so handy for LONG lists in a combobox.


The "data validity" combobox's "text area" is a normal Calc cell so you only see normal Calc autocompletion. Calc only autocompletes using the data on cells in the same column as the cell you are editing so if you put your "data validity" combobox in the same column as the cells that contain your list of items, autocompletion should work.

For example, if you list is in cells B10 through B5000, putting your data validity comboxes in cells B1 through B9 will enable autocompletion.

bryoman wrote:
B) I get the "Err:512" when the list gets too long - not "512" items, but about 257 - seems to vary a little - so nowhere near my 2000 names for a plant list. I gather this limit can't be extended (?).


Use "Approach #2" as it does not have this limit. With "Approach #2", you first select all of the cells with your list of items plus some extra empty cells to allow space for new items in the list (cells B10 through B5500 in my previous example) and then select the Data :: Define Range menu. In the dialog that appears, enter a name that is easy for you to remember (for example, "MyListOfStuff"), and press the OK button.

Then, go to a cell where you want you "data validity" combobox (cell B1 in my previous example), select the Data :: Validity menu, and in the dialog that appears, select the Criteria tab. Set the "Allow" setting to "Cell range", set the "Source" setting to the name you entered in the Data :: Define Range dialog., and press the OK button.

Patrick
Back to top
bryoman
Sentinel


Joined: Jan 07, 2009
Posts: 27
Location: Carlisle, England

PostPosted: Sun Nov 25, 2012 3:16 pm    Post subject: Re: Creating a list using a combo box.

Thanks both:

Yes I can get "Approach 2" working now - not certain why not before... yes - that's very neat indeed - I can run a hundred dropdown cells above the list of names, and can scroll through each very rapidly - great with the trackpad rather than mouse-scroll. I think this is workable for what I want, so grateful thanks are in order!

(A down arrow gets me to the next cell down for my next entry... Could I make a *keystroke* open the dropdown list?? - the target is a bit small - or can the target be enlarged? (If you enlarge the window view, or the cell, the target doesn't enlarge!).

What isn't working yet though is autocomplete. No idea why. I followed James' hints, and of course my dropdown cells ARE in the same column as the name list. So not certain - nothing else needs activating, does it?

J. (near Carlisle)
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Sun Nov 25, 2012 3:50 pm    Post subject: Re: Creating a list using a combo box.

bryoman wrote:
(A down arrow gets me to the next cell down for my next entry... Could I make a *keystroke* open the dropdown list?? - the target is a bit small - or can the target be enlarged? (If you enlarge the window view, or the cell, the target doesn't enlarge!).


Unfortunately, the arrow button is a fixed size. Hopefully we can get autocompletion working so that you can avoid having to click the arrow most of the time.

bryoman wrote:
What isn't working yet though is autocomplete. No idea why. I followed James' hints, and of course my dropdown cells ARE in the same column as the name list. So not certain - nothing else needs activating, does it?


Open the Tools :: Cell Contents and see if the AutoInput menu item is checked. If not, select that menu item to enable autocompletion. Does that enable autocompletion?

Patrick
Back to top
Display posts from previous:   
   NeoOffice Forum Index -> NeoOffice Releases All times are GMT - 7 Hours
Goto page 1, 2  Next
Page 1 of 2

 
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
You cannot attach files in this forum
You cannot download files in this forum

Powered by phpBB © 2001, 2005 phpBB Group

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest © Planamesa Inc.
NeoOffice is a registered trademark of Planamesa Inc. and may not be used without permission.
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.