Auto Filling Drop Down Boxes

users
#1

Hello,

I am wanting to do something in Base, but I am not sure what the “action”
(auto filling?) is called, so I am not sure what subject to research. If
you could let me know what I should be researching, or provide an example,
it would be appreciated.

I have three drop down boxes: City, State, Zip as part of an address record.
As of now, I have to make independent selections for each of these drop down
boxes. What I would like to do is when selecting a city from the drop down
box list, then related state and zip information would automatically be
filled in, in their respective drop down boxes, and the information stored
in their respective db cells.

For example:
By selecting MINNEAPOLIS from the City drop down box list, MN would
automatically be filled in, in the State drop down box, and 55407 would
automatically be filled in, in the Zip drop down box.

Or, by selecting TAMPA from the City drop down box list, FL would
automatically be filled in, in the State drop down box, and 33605 would
automatically be filled in, in the Zip drop down box.

I am moving my db from Access to LibreOffice, so I know Access much better,
but I was never able to figure this out in Access either. If you happen to
know the answer in either Access or Base I should be able to figure it out
from there.

Thanks in advance!

#2

Hi :slight_smile:
I would stay with Access for now.  There is almost no-one working on Base so it's gradually getting worse as regressions slip in. 
Regards from
Tom :slight_smile:

#3

Hi Tom,

I would stay with Access for now. There is almost no-one working on Base so it's gradually getting worse as regressions slip in.
Regards from

The poster's question is a common one, which has been answered many
times on the OpenOffice.org forums. Yes, cascading dropdown autofill
list boxes are a PITA to do on Base, but they can (or at least could) be
done with a bit of effort.

http://www.oooforum.org/forum/viewtopic.phtml?t=103213

http://user.services.openoffice.org/en/forum/viewtopic.php?f=83&t=28235

http://www.oooforum.org/forum/viewtopic.phtml?t=91166&highlight=listbox

http://www.oooforum.org/forum/viewtopic.phtml?t=59711

http://user.services.openoffice.org/en/forum/download/file.php?id=8687&sid=38470f46e1b5b28cdaccf89573dcf574

http://user.services.openoffice.org/en/forum/download/file.php?id=7306

http://www.oooforum.org/forum/viewtopic.phtml?p=386982

These should get that person started.

The LibreOffice mailing lists are too young in substance to be of much
use for more technical questions like this, the forums, on the other
hand, are replete with lots of useful information if you know where to look.

Alex

#4

Thanks for the help by providing the links and your insight.

I thought the community here was larger. I will stay with Access for now
and maybe convert to Base in the future.

The rest of LibreOffice works like a charm for me. :slight_smile:

#5

Hi :slight_smile:
LibreOffice is larger.  It's just that almost everyone avoids Base.  While Writer and Cal and the rest attract a LOT of devs they just avoid Base.
Regards from
Tom :slight_smile:

#6

Hi,

Thanks for the help by providing the links and your insight.

I thought the community here was larger. I will stay with Access for now
and maybe convert to Base in the future.

The rest of LibreOffice works like a charm for me. :slight_smile:

Other alternatives to Base and MS Access are PostgreSQL and Mariadb (a
fork of MySQL). Both are open source. With correct connectors installed
Base can be used as a front-end for either one or you can use the FOSS
tools for most of your work with either one and Base for importing into
LO.

#7

Am 21.09.2011 19:00, stripedtomato wrote:

I have three drop down boxes: City, State, Zip as part of an address record.
As of now, I have to make independent selections for each of these drop down
boxes. What I would like to do is when selecting a city from the drop down
box list, then related state and zip information would automatically be
filled in, in their respective drop down boxes, and the information stored
in their respective db cells.

This is impossible because big cities have more than one ZIP and there are ambiguous city names.
You may enter a city name and then choose the actual city and zip from suggested data.

I use to enter zip codes. Each zip code belongs to one particular city in one state.

Apart from this, big companies and public sectors have their own zip codes.

#8

Am 21.09.2011 19:09, Tom Davies wrote:

Hi :slight_smile:
I would stay with Access for now. There is almost no-one working on Base so it's gradually getting worse as regressions slip in.
Regards from
Tom :slight_smile:

These things use to work since OOo 1.0 (long before "Base") and when they break, the whole office suite would be useless for many millions of potential users.

#9

You keep dishing out this advise... that and "It's just that almost
everyone avoids Base." why?

At every turn I see you advising users on this list to 'avoid' Base,
stick with Access, use something else. Base is a primary component of
LO, and before LO, OOo. Do you have some link to facts that within LO
"there is almost no-one (sic) working on Base"?

...

#10

Am 21.09.2011 19:00, stripedtomato wrote:

#11

Am 21.09.2011 19:00, stripedtomato wrote:

I have three drop down boxes: City, State, Zip as part of an address record.
As of now, I have to make independent selections for each of these drop down
boxes. What I would like to do is when selecting a city from the drop down
box list, then related state and zip information would automatically be
filled in, in their respective drop down boxes, and the information stored
in their respective db cells.

This is impossible because big cities have more than one ZIP and there
are ambiguous city names.

Actually it's not if you have (in the US) the USPS 5 digit Zip Code
database:

https://www.usps.com/business/address-information-systems.htm#

Five-Digit ZIP® Product

Provides 5-digit ZIP Code data that can be appended to computerized
mailing lists via address-matching software.

With that (or similar) I think that you could easily build a dropdown
that links to that. You'd probably need to fine tune by address as each
city, street, business may have differing Zip/Postal Codes

Unfortunately the USPS doesn't offer an easy way to order:
For more information on the AIS View, please contact the National
Customer Support Center at 1-800-238-3150, Option 6 and then Option 2 to
speak to a customer service representative.

#12

Am 21.09.2011 19:09, Tom Davies wrote:

Hi :slight_smile:
I would stay with Access for now. There is almost no-one working on Base so it's gradually getting worse as regressions slip in.
Regards from
Tom :slight_smile:

These things use to work since OOo 1.0 (long before "Base") and when they break, the whole office suite would be useless for many millions of potential users.

#13

Am 23.09.2011 04:26, NoOp wrote:

This is impossible because big cities have more than one ZIP and there
are ambiguous city names.

Actually it's not if you have (in the US) the USPS 5 digit Zip Code
database:

https://www.usps.com/business/address-information-systems.htm#

Five-Digit ZIP® Product

I have this list of all USPS zip codes in a .dbf file.

http://www.mediafire.com/?cb2tw57jxw32khg

A database can help you to find the zip by full address. For logical reasons you can not get the right zip from a city name, but you always get the city name from the zip.

#14

Am 23.09.2011 04:26, NoOp wrote:

This is impossible because big cities have more than one ZIP and there
are ambiguous city names.

Actually it's not if you have (in the US) the USPS 5 digit Zip Code
database:

https://www.usps.com/business/address-information-systems.htm#

Five-Digit ZIP® Product

I have this list of all USPS zip codes in a .dbf file.

http://www.mediafire.com/?cb2tw57jxw32khg

A database can help you to find the zip by full address. For logical reasons you can not get the right zip from a city name, but you always get the city name from the zip.

#15

Hi Tom,

Tom Davies wrote (21-09-11 19:09)

There is almost no-one working on Base so it's gradually getting worse as regressions slip in.

I think it would be very helpful, if you will enlighten the situation a bit.
There is not only BugZilla, but also full access to the repository (1) where results of developer activities can be seen.

Would you please be so kind to write us the details that you will find, when you spend some time on investigating:
- what is being done on development wrt base;
   - fixes, new items, various improvements, ..
- what's on with the bugs, e.g.
   - status (new, duplicate, not a bug, resolved, ...)
   - regression (from when, where)
   - area (forms, drivers, ...)
   - severity (blocker, workaround available, cosmetic, ...)

#16

Hi :slight_smile:
If you are familiar with those tools then i am sure you could find an answer faster.

I am not a dev and am not familiar with those tools.  I do trust devs and others that are familiar with those tools to give true and fair information, especially when very different such people give roughly the same information in different lists for different reasons (usually to try to prove me wrong about something).

Information that does keep being repeated is that there are well over a hundred devs that have worked on LibreOffice but at most only 3 have touched Base.  Apparently a few hundred bugs to fix including regressions that make it impossible to guess which quirks a particular release of Base might suffer from.

If someone already has a database that does already work then now is not the time for them to migrate to something that might well have serious problems for them later on.

People in here have been great at solving one-off problems but there is no guarantee they would be able to fix all the issues the op requires and there is no commitment from TDF's SC/BoD to supporting Base properly.

Regards from
Tom :slight_smile:

#17

Tom Davies wrote (23-09-11 14:27)

Hi :frowning:
If you are familiar with those tools then i am sure you could find an answer faster.

Irrelevant. *You* make the statements - lots of well reading sentences that seem to make sense but may as well be tendentious or misleading.

So if you do not give a solid ground to your statements, or even want to make real effort in trying to do so, then please stop making them.

#18

Hi :slight_smile:
Lol.  Cor appears to be accusing me of being wrong but with no proof to support the accusation.  The only link he did give appears to bolster my assertions.  It
appears to show a lot of work going into Calc and the other apps but
none into Base.  Of course it is pure geek and difficult to understand but the few i clicked seemed to mention something about Calc and one mentioned wizards.  None mentioned Base.

The other accusation Cor appears to make is that i have not provided the type of proof he wants, which is a bit hypocritical imo as there is no proof that my assertions were inaccurate or misleading at all.

I do know 1 person that has been trying to work on Base and a few people tell me there is another one but the work is not easy.

None of this is relevant to the original question and i think the time would be better spent helping people rather than wasting everyone else's time with this sort of nonsense so publicly.  It is possible to send emails to individuals off-list if you think they are wrong in some way. 
Regards from
Tom :slight_smile:

#19

The solution is obvious.

<PLONK>

#20

Tom Davies wrote (23-09-11 15:55)

Hi :slight_smile: Lol.

I see no reason for joy here, nor any fun.

Cor appears to be accusing me of being wrong but with no
proof to support the accusation.

Wrong again.
*You* make statements that are at least tendentious and too generic. People need good information, and the project does not really need 'information' that can lead to a wrong image.

So I kindly repeat my request: if you do not give a solid ground to your statements, or even want to make real effort in trying to do so, then please stop making them.