2. PHPRunner General questions
  3. Dependent Drop down
This topic is locked

Dependent Drop down

 		3/27/2007 4:34:55 PM
PHPRunner General questions
T
 Tester_1313 author

I am using PHPRunner 3.1 (build 218) and have a drop down dependent question. I have 3 tables Equipment, City, and Building. My table relationships are as follows:

Building relates to Equipment since the equipment can only reside in one building, but many pieces of equipment are in one building.

City relates to Building since that building can only be in one city, but there may be multiple buildings in a city.
So I have all my pages all set up except for some of the adds and edits. When I do an add into the equipment table I can only do a lookup from the Building table since that is where the direct relationship lies. The problem with this is I get every building in the table when I do a lookup. So the solution would be to first select the city and then the building depending on the city selected. The problem with this is that there is not a direct relationship between the city and equipment tables. I can put a lookup of the city table in the equipment add page. Then when I do the building lookup, the direct dependent option will not relate to a field that is not resident in the equipment database.
One option I am thinking but need help with, it to put the city lookup on th epage. Then when the building lookup is done, put the results from the city lookup in the "WHERE" portion of the building lookup. Problem I am having is with the syntax.
If anyone has an idea how to help, I would greatly appreciate it.
J
 		Jane 3/29/2007

Hi,
it's difficult to tell you what's happening without seeing actual data.

Please post structure of your tables here and I'll try to help you.
T
 		Tester_1313 author 4/4/2007

I will give it a try.

I have a table Equipment which is the main table of the database and it contains the following fields with an example of the data.
equipid serial vendorid descriptionid BFCid notes date

1 12345 1 2 1 FE PIC 4/04/07

2 23456 2 1 2 GE NIC 4/04/07

3 34567 1 6 1 GE PIC 4/04/07

4 45678 3 3 3 PC 4/04/07

5 56789 1 2 2 FE PIC 4/04/07
Another table in the databse is BFC which stand for Building Floor Cube and contains the following fields with an example of the data.
BFCid BFC Cityid

1 D-3 1

2 E-2-162 1

3 T1-732 2
Another table in the database is City and contains the following fields with an example of the data.
Cityid City lat long

1 New York 45.367 -127.356

2 Boston 48.005 -126.876
So now I want to add data into the Equipment table. The Equipment is located in New York and will be put in E-2-162. When I do a table lookup for BFC I get all the entries in the BFC table. Since I know the equipment is in New York, I would like to be able first enter that the equipment will be located in New York and then the BFC dropdown only display BFCid's with Cityid's of 1 (in this case). You can see how this database grows to encompass more cities, and more BFC locations where it would be helpful to be able to limit the number of BFC locations in the lookup.
Thanks for any help you can provide.
J
 		Jane 4/5/2007

Hi,
I recommend you to do the following:

  • add Cityid field to the Equipment table and set up it as dropdown box: select City in the Table dropdown, Cityid and City in the Link field and Display field dropdown boxes on the "Edit as" settings dialog.
  • set up BFCid as dependent dropdown box and select Cityid in the Category control and Category field dropdown boxes.
T
 		Tester_1313 author 4/5/2007

Thanks I will restructure things and impliment that change. I think I was probably going a little to far with my normalization.
T
 		Tester_1313 author 4/5/2007

As I was redoing my database it came to my attention that if I put Cityid in the Equipment table I still need it in the BFC table. So now I have two places with the same information.

Equipment table calls Cityid & BFCid

BFC table calls Cityid

So my city key is being called from the Equipment table and from the BFC table as well as the Equipment table calling the BFC table. When designing the database I thought was redundant since the City Equipment relationship was taken care of by the BFC Equipment relationship.