Ask Your Question
1

Base needs to Auto populate 3 different cities and states when zip code it entered for each?

asked 2018-11-19 18:11:17 +0200

jawspro gravatar image

updated 2018-11-27 20:53:59 +0200

EasyTrieve gravatar image

I have a database that contains Attorney zip, city and state; then client zip, city and state; and last subject zip city and state.

I have no idea how to make this happen. I watched a tutorial on Access auto populating for one zip, city and state, however I am not sure if any of that info works with LibreOffice. I have the Zipcodes in a table and the main table/ Base written but not the relationships.

Do I need to create separate tables for Attorney, client and subject? How do I establish the relationships? How do I auto populate the cities and state for each one of these (people)?

Any help would be greatly appreciated. I have not used Databases in 20+ years.

edit retag flag offensive close merge delete

Comments

Thank you for responding. I am trying to create a relationship with the zipcode table to client table (no problem), with the Subject table (no problem), and the plaintiff attorney table (PROBLEM). Relationships will not allow me to create this third link.

I am using the zip code as the common element?

Ultimately the database hold all my records.Have my form letters pull out certain records to print the letter for specific person addressed to client ref plaintiff att. is this possible?

jawspro gravatar imagejawspro ( 2018-11-19 19:56:27 +0200 )edit

@jawspro,

I'm sure you didn't realize, but your comment was actually only a comment to the original question. Had I not opened the question again, I would not have known you responded. The comment should have been to the answer & thus I would be notified.

Please keep this in mind in future.

Ratslinger gravatar imageRatslinger ( 2018-11-19 21:01:15 +0200 )edit

Sometimes a custom pull of data is adequate too. For example, while I did find free databases of every U.S. city, state, county and zip code for my Base/MySQL project... it was HUGE and unwieldy for my relatively simple and small needs. So I grabbed a data subset for the geographical areas I was most likely to do business, and it became smaller and manageable. An online search for "free zip codes database" or "US cities database" will give you multiple options if you need it all.

PhLo gravatar imagePhLo ( 2018-11-20 06:07:53 +0200 )edit

There are also APIs out on the internet that feed information based on an input. Like, give it a city, and it will return a zip code. Separate question, but it would be interesting if Base could interface with those external APIs. Then you wouldn't have to reinvent the wheel and maintain enormous data for the whole country. I'd be interested if such things were possible. Tax information is WAY more complicated and in need of an API connection.

PhLo gravatar imagePhLo ( 2018-11-20 06:09:46 +0200 )edit
1

I'm going ahead and keep doing this as a little tutorial, now that I see that I kind of missed the object on the first go round.

DrewJensen gravatar imageDrewJensen ( 2018-11-20 06:53:17 +0200 )edit

"Part 2" of question in this thread continues here.

PhLo gravatar imagePhLo ( 2018-11-22 15:37:51 +0200 )edit

3 Answers

Sort by » oldest newest most voted
3

answered 2018-11-19 22:57:35 +0200

DrewJensen gravatar image

updated 2018-11-20 07:13:13 +0200

The first part of an answer to your question should be on finding multiple municipalities for a given zip code.

So given a table full of addresses you want, eventually, to group on unique zip code entries and list all records the unique CITY entries in each group, right. Unless you are acquiring an actual database with that information already broken out.

I'm going to use a simple database with the Contacts template table.

And begin first with query of Contacts table to show for each zipcode in the table how many uinque cities are included

Will do this with query designer:

image description

Which generates the following SQL select statement

SELECT 
    "PostalCode" "Zipcode", 
    COUNT( "City" ) "Cities" 
FROM "Contacts" 
GROUP BY "PostalCode" 
ORDER BY "Zipcode" ASC

---- will come back in a few minutes with the next part of the answer back!

I saved that query definition as 'qryZipcodeCities'

Now use query designer to start a second query, wich includes both qryZipcodeCities (added this first) and the Contacts Table and the query definition.

Then linked the column ZipCode in qryZipcodeCities to PostalCode in Contacts.

Now I would like to get a result that would show me each uniqe set of zipcodes with city.

So I click the Distinct Values button on the query designer tool bar. image description

The SQL select statement produced is this

SELECT DISTINCT
     "Contacts"."PostalCode", 
     "Contacts"."City" 
FROM "qryZipcodeCities", "Contacts" 
WHERE "qryZipcodeCities"."Zipcode" = "Contacts"."PostalCode" 
    AND "qryZipcodeCities"."Cities" > 1

But it didn't do quite what I wanted did it.

--- back in a minute

Back

Turns out the query does in fact do what I wanted and what it shows me is that I made a typo when I entered the data in the Contacts table, spelling Gaithersbug vs Gaithersburg.

-- so onto the next part of the answer and back in a few

After further comments and realizing I missed what you where really getting at I backed up to the database tables again, creating a Base file using the Firebird database, as an aside.

I'll start with three tables:

Firm - which has some information about the law firm

Attorney - a table for the attorneys in the firm

Client - the firms clients

Attorneys have a list of clients and each Client has one Attorney, for our simple example.

Here is the single relationship created in Base for the three Tables

image description

Going to make a simple form that displays all the clients for the firm, and uses the relationship to pull the attorney data, including a phone number for the Attorneys office, not the general firm number which is displayed in the header of the form.

image description

That form is based on the following query which builds the multi-line strings for display and later printing. The query designer also automatically created the join in the query, because there was a relationship created between Client and Attorney records.

image description

---- in the morning will come back and use this simple database to generate a mail merge.

edit flag offensive delete link more

Comments

Thank you for all the work you have done. It's amazing how much time and effort you put into this.

I think my whole idea for my database boils down to this: Can Writer and Base communicate? I want select information from the database to mesh with my form letter. Only the information I call for from say record # 750 or record # 12. The letters include affidavits, invoices, etc. Does Writer work with Base in this manner?

jawspro gravatar imagejawspro ( 2018-11-20 00:25:24 +0200 )edit

Absolutely. You can come at that either from the Writer interface or the Base interface. You can build forms with fields in Writer and use queries to fill them during a mail merge operation. You could design reports in Base that at run time produce a Writer or Calc file per whatever level of detail you want, an affidavit or invoice, etc.

DrewJensen gravatar imageDrewJensen ( 2018-11-20 00:42:19 +0200 )edit
1

answered 2018-11-19 18:48:11 +0200

Ratslinger gravatar image

Hello,

Your question is a number of questions in one. This forum is designed to ask one question at a time. This aids in others looking for answers to their questions.

With regard to:

Do I need to create separate tables for Attorney, client and subject?

In order to avoid duplication of data - Yes. This is a reason for using a relational database.

How do I establish the relationships?

There needs to be a common element. In your particular situation, it's probable that this might be a Case # (another table). Much depends upon a complete analysis & design of what you are attempting - beyond the scope of this forum.

How do I auto populate the cities and state for each one of these (people)?

First the data needs to be present - Records of ALL possible zipcodes along with the associated city and state. Another quite large table. Now with this amount of information you should probably be using a database other than the default HSQLDB embedded. This enters into a topic which can be discussed literally in books rather than an answer here.

Saving the city and state information within each of the three mentioned tables doesn't seem to be necessary. If you have the previously mentioned zipcode table, the data can be retrieved anytime from there.

Watching or reading tutorials using Access is not going to help as Base and Access are quite different in in how to make each perform specific tasks. Deal with Base documentation found here -> LibreOffice Base Handbook.

edit flag offensive delete link more

Comments

Can't explain why your third relationship didn't work when the other two did. If everything is equal there is no reason. Only details can reveal where problem may be. Field types are important here.

I believe my original understanding of you relationship question was incorrect. Thought you wanted to relate all tables.

Don't understand:

Have my form letters pull out certain records to print the letter for specific person addressed to client ref plaintiff att. is this possible?
Ratslinger gravatar imageRatslinger ( 2018-11-19 21:06:50 +0200 )edit
0

answered 2018-11-19 22:08:49 +0200

jawspro gravatar image

Ultimately I want my database, which holds all my records to communicate with Writer. I want my form letters to pull out certain records (I specify) to print the letter for a specific person, addressed to the specific client and referencing a specific plaintiff's attorney. Is this possible between Writer and Base?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-11-19 18:11:17 +0200

Seen: 96 times

Last updated: Nov 20 '18