![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
Gene Young wrote (23-09-11 16:16)
The solution is obvious.
<PLONK>
I know. Alas that does not help the ones that occasionally visit this list.
Hi 
I think Andreas was just pointing out that given a zip code it might be fairly easy to find the city, State and such but given a State or even a specific city it might not be easy to guess which of the zip codes is right. I think you can get part of the zip code that way buit not all of it? In England the first 2 letters give the 'State'/County (well, approximately).
Anyway the technical part of the question is how to get one drop-down to look-up and fill in a value based on the entry made elsewhere on the page. Is Chapter 8 or 10 in the Calc Guide helpful?
http://wiki.documentfoundation.org/Documentation#LibreOffice_Calc_Guide
Regards from
Tom
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.
Am 21.09.2011 19:00, stripedtomato wrote:
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.
All this is impossible to explain in terms of software "features". Having an "official" list of unique ZIP codes and city names and your own address list with a ZIP field, you create a list box for the ZIP code with source type "SQL" and source
SELECT "ZIP" ||'-'||"PONAME"||'-'||"State" AS "Visible", "ZIP" FROM "tblZIPCODES" ORDER BY "Visible"
This fills a list box with visible values:
00210-PORTSMOUTH-New Hampshire
00211-PORTSMOUTH-New Hampshire
00212-PORTSMOUTH-New Hampshire
00213-PORTSMOUTH-New Hampshire
00214-PORTSMOUTH-New Hampshire
00215-PORTSMOUTH-New Hampshire
00501-HOLTSVILLE-New York
00544-HOLTSVILLE-New York
00601-ADJUNTAS-Puerto Rico
00602-AGUADA-Puerto Rico
00603-AGUADILLA-Puerto Rico
00604-AGUADILLA-Puerto Rico
00605-AGUADILLA-Puerto Rico
00606-MARICAO-Puerto Rico
00610-ANASCO-Puerto Rico
and the zip codes in a hidden second field.
Now you type the ZIP code into the focussed list box until you get the right city. The second field puts the ZIP code only into the field. Since each zip code identifies exactly one location in one state, you have a reference pointer to the city names and states in the other table. No need to put any more information into the form
A report or mail merge uses a simple cross-table query
SELECT D."this",D."that",D.ZIP,Z."CityName",Z."State"
FROM "Data" AS D, ZIPCODES AS Z
WHERE D.ZIP = Z.ZIP
Am 21.09.2011 19:00, stripedtomato wrote:
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.
All this is impossible to explain in terms of software "features". Having an "official" list of unique ZIP codes and city names and your own address list with a ZIP field, you create a list box for the ZIP code with source type "SQL" and source
SELECT "ZIP" ||'-'||"PONAME"||'-'||"State" AS "Visible", "ZIP" FROM "tblZIPCODES" ORDER BY "Visible"
This fills a list box with visible values:
00210-PORTSMOUTH-New Hampshire
00211-PORTSMOUTH-New Hampshire
00212-PORTSMOUTH-New Hampshire
00213-PORTSMOUTH-New Hampshire
00214-PORTSMOUTH-New Hampshire
00215-PORTSMOUTH-New Hampshire
00501-HOLTSVILLE-New York
00544-HOLTSVILLE-New York
00601-ADJUNTAS-Puerto Rico
00602-AGUADA-Puerto Rico
00603-AGUADILLA-Puerto Rico
00604-AGUADILLA-Puerto Rico
00605-AGUADILLA-Puerto Rico
00606-MARICAO-Puerto Rico
00610-ANASCO-Puerto Rico
and the zip codes in a hidden second field.
Now you type the ZIP code into the focussed list box until you get the right city. The second field puts the ZIP code only into the field. Since each zip code identifies exactly one location in one state, you have a reference pointer to the city names and states in the other table. No need to put any more information into the form
A report or mail merge uses a simple cross-table query
SELECT D."this",D."that",D.ZIP,Z."CityName",Z."State"
FROM "Data" AS D, ZIPCODES AS Z
WHERE D.ZIP = Z.ZIP
Hi
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.
He provided you with a link to query the work being done in LO on Base:
http://cgit.freedesktop.org/libreoffice/core/log/
Enter 'dbaccess in the search window & you'll see the dev work on Base.
That results in:
<http://cgit.freedesktop.org/libreoffice/core/log/?qt=grep&q=dbaccess>
Another is:
<http://cgit.freedesktop.org/libreoffice/core/log/connectivity>
I'm not sure what others may be involved, but it is clear that there is
development work on Base in LO.
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.
And that person is? It seems to me that there are several people working
on Base.
Disclaimer: I've just learned about
http://cgit.freedesktop.org/libreoffice/core/log/ as well and am still
working my way through it. However your continued FUD regarding Base &
advising new users on this list to stick to Access et al seems to be
just that: FUD. Before you continue posting the like, how about actually
trying to find out the facts?
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
Actually time would be better spent assisting the user with Base rather
than making such statments as: "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." and "It's just that almost everyone avoids Base.
While Writer and Cal and the rest attract a LOT of devs they just avoid
Base.". If you can't back up those statements with fact, then they are
better left unsaid.
Gary Lee
From: Cor Nouws <oolst@nouenoff.nl> Subject: Re: [libreoffice-users]
Auto Filling Drop Down Boxes To: users@global.libreoffice.org Date:
Friday, 23 September, 2011, 13:58Tom Davies wrote (23-09-11 14:27)
Hi
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.-- - Cor - http://nl.libreoffice.org
-- For unsubscribe instructions e-mail to:
users+help@global.libreoffice.org Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
Got it & agree. Sorry for the noise/misunderstanding.
Gary
Hi
Thanks for those links. Some useful information that does indeed contradict what i have previously heard about Base. It makes me wonder about the accuracy of other information i have seen about Base in a variety of places. No, of course i am not going to name names or divulge sources after the way i have been treated. I will quietly ask a few people to see how the misunderstandings arose and see what they say about the links you just gave.
More importantly it gives me enough confidence to try out Base myself at work to see if migration is possible after all. Given that this is the first good news i have had about Base i still wouldn't recommend it to other people but it's obviously worth testing if there is enough time. For myself i will make time to test it.
Regards from
Tom
Hi Tom,
When there have been posts about this or that in Base, generally there have been quite a few people who have participated. It may be used more than most people realize. Shortly after Base became a part of OpenOffice, I converted, successfully, all of my data from Paradox to Base. If I had been using Access, it would have been slightly easier. Base has been an extremely dependable program for me. The only issue I've had is the one with Java. Yes, I'm aware of a couple of bugs, but none of those affect me in any meaningful way. I've been using it for about 8 years and would not change to anything else since it has worked so well for me, and it serves my needs.
Don
Quick and dirty draft of an embedded HSQLDB with US zip codes:
The form's dummy text field stands for all those name, address, phone, email fields that may be in a list of addresses.
The list box lets you select one particular ZIP code which stands for one particular entry in the list of zips with ZIP, city, county, state.
Query "qReport" merges the info of all tables:
Am 24.09.2011 15:27, Don C. Myers wrote:
Hi Tom,
When there have been posts about this or that in Base, generally there
have been quite a few people who have participated. It may be used more
than most people realize. Shortly after Base became a part of
OpenOffice, I converted, successfully, all of my data from Paradox to
Base. If I had been using Access, it would have been slightly easier.
Base has been an extremely dependable program for me. The only issue
I've had is the one with Java. Yes, I'm aware of a couple of bugs, but
none of those affect me in any meaningful way. I've been using it for
about 8 years and would not change to anything else since it has worked
so well for me, and it serves my needs.Don
I have hardly any Writer or Calc document that is not bound to some type of database. At least they include some copied database dump.
All my commercial spreadsheet models use real-world data. I would never again store vital data in spreadsheets. Storing record sets in spreadsheets has not a single advantage but massive draw backs.
Most of our Writer documents are either db reports or serial letters, label print templates, business cards.
I use Draw documents as carriers for external database forms.
What has been introduced under the name "Base" in version 2.0 of OOo is completely obsolete. It is broken beyond repair and adds no extra value to the office suite.
I would not bother if all of this would be removed very soon, leaving the mere connectivity, SQL SELECTs and the data aware forms attached to ODF documents as it used to be there in version 1.
Am 24.09.2011 15:27, Don C. Myers wrote:
Hi Tom,
When there have been posts about this or that in Base, generally there
have been quite a few people who have participated. It may be used more
than most people realize. Shortly after Base became a part of
OpenOffice, I converted, successfully, all of my data from Paradox to
Base. If I had been using Access, it would have been slightly easier.
Base has been an extremely dependable program for me. The only issue
I've had is the one with Java. Yes, I'm aware of a couple of bugs, but
none of those affect me in any meaningful way. I've been using it for
about 8 years and would not change to anything else since it has worked
so well for me, and it serves my needs.Don
I have hardly any Writer or Calc document that is not bound to some type of database. At least they include some copied database dump.
All my commercial spreadsheet models use real-world data. I would never again store vital data in spreadsheets. Storing record sets in spreadsheets has not a single advantage but massive draw backs.
Most of our Writer documents are either db reports or serial letters, label print templates, business cards.
I use Draw documents as carriers for external database forms.
What has been introduced under the name "Base" in version 2.0 of OOo is completely obsolete. It is broken beyond repair and adds no extra value to the office suite.
I would not bother if all of this would be removed very soon, leaving the mere connectivity, SQL SELECTs and the data aware forms attached to ODF documents as it used to be there in version 1.
Quick and dirty draft of an embedded HSQLDB with US zip codes:
The form's dummy text field stands for all those name, address, phone, email fields that may be in a list of addresses.
The list box lets you select one particular ZIP code which stands for one particular entry in the list of zips with ZIP, city, county, state.
Query "qReport" merges the info of all tables:
Hi All
Does anyone know of a good Tutorial to lay-out a Report in 'Write' that Auto-Updates from my Database in 'Base'??
Thanks in advance.
Ian Whitfield
Pretoria
(OO 3.2.1 on Linux Mint 10 KDE)