I have set up this test database to try to have a conditional value list of zip codes so that when I enter City I go to States and the choices are reduced only to those states where there are corresponding cities and then when I go to zip codes only those zip code(s) are a choice. I have played with this for hours and hours and this is the best I could do. It works, I think, but, I'm concerned with the relationships and how I have them set up. I have tried every possible relationship I could think of.

The test DB only contains zip codes for NY, CA and FL.

So here's my questions:

Have I set it up correctly or is this an accident waiting to happen?

When tabbing from State to Zip Code I must commit the record before I can get the zip code VL to show data, why?

All assistance is greatly appreciated.

ZipCode_Test.zip

Try it this way?

ZipCode_Test.fp7.zip

Nice, Michael. Now I would have made the zipcode value list 'only related from ZipCodes_City' - theory being always go back up one relationship (I've never go back to the origin). I get the same results as your spec of 'only related from Contacts' and I'm unsure if I would EVER get differing results.

Might there also be speed differences between the two conditional relationships, ie, there would be fewer Contacts but it's another relationship away (grand parent) vs. more cities but parent? :smile2:

Edited October 12, 200718 yr by Guest

Only thing I question is starting with the city. If no city, no state, no zip. It may work in some applications but I would think starting with the state would be better. Just my 2cents. (I have 25+ users enter this kind of information and experiance tells me all kinds of miscommunication can happen that prevents the city from being accurate, state on the other hand is fairly easy to get, and If the goal is to make data entry faster and more accurate, I would start there.)

(edit; Oh, the commit is needed to make the next relationship valid.)

Edited October 12, 200718 yr by Guest

I would have made the zipcode value list 'only related from ZipCodes_City'

I don't think you can do that, since there might be two cities with the same name in different states. You select the state to narrow down the zipcodes to the selected city in the selected state only. Or maybe I didn't understand your point.

Only thing I question is starting with the city

That's for aldipalo to consider. I only fixed the relationships and value lists so that it works the way he wants.

the commit is needed to make the next relationship valid.

Is commit needed?

Thanks Comment>

The relationships work fine, and without the commit, in the test DB, however, when I tried to move it to my app I was unsuccessful. I have three tables that I want to use this in.

Company - The Corporate address

Contacts - Work Address

Candidates - Home Address

I know, as an after thought, I should have made an addresses table. Maybe in my next version. At my age if I'm still working.

Anyway, I made 3 sets of relationships:

Zipcodes_Company_City

Zipcodes_Company_States

Zipcodes_Company_ZipCodes

Zipcodes_Contacts_City

Zipcodes_Contacts_States

Zipcodes_Contacts_ZipCodes

Zipcodes_Candidates_City

Zipcodes_Candidates_States

Zipcodes_Candidates_ZipCodes

I first attempted to use just zipcodes for all 3 tables, but, that didn't want to work.

Getting the city data is not a problem, of course, it's the states and zipcodes. After choosing the city I tab to states and there is no data.

Any ideas as to what I may be doing wrong?

Thanks,

Al

Is commit needed?

comment your file works very well without the commit. You only used 2 relationships in it. If you added a third (based on city, st and zip) the commit would be needed.

Afterthought: I think that is aldipalo's problem, to many relationships, the city value list only needs a table occurance.

Edited October 12, 200718 yr by Guest
afterthought

If you added a third (based on city, st and zip) the commit would be needed.

Why do you think so? (It's too much work to set up a test.)

Edited October 12, 200718 yr by Guest

OK, so the commit isn't needed.(I just tested and could not get it to fail.) (I swear I've had that problem, I think I had the same problem as aldipalo at one time, tring to use TO's that were too far away, when you do that it is needed, just like his org file.)

TO's that were too far away

That would be my guess too.

I appreciate all the replys. but, I still can not get the thing to work. I have set the 2 TO's directly to the correct table and still nothing. What could I possibly be doing????

Compare the definitions of the value lists?

I think I found the problems, but, only time will tell.

I hate it when I get something working, but, don't really understand what I did to make it work.

As usual Comment your solution was simple but elegant. Thanks.

Al