2. PHPRunner General questions
  3. limit the lookup values based on another field in the same table
This topic is locked

limit the lookup values based on another field in the same table

 		8/2/2011 3:23:19 PM
PHPRunner General questions
P
 pim author

Hi,
I hope you can help me with this:
I have this table:
table name [Gear]

field 1: GearID / int

field 2: TourSchID / varchar -> field is a lookup to table TourSch (looks up field TourSchID and displays TourSchNr. There are 35 records in this table)

field 3: GearNr / varchar -> field is a lookup to table Gear (looks up field GearNr. There are 20 records in this table)
For each of the 35 records of TourSchID that is selected in field 2, the selection of field 3 must be limited to a unique GearNr (1 of the 20 records is only possible).
Or in other words:
If for example TourSchID '1' is chosen at field 2, the user can select only once for example GearNr 'S1' at field 3.
Thank you in advance for answering,
Pim
C
 		cgphp 8/2/2011

pim,
for the Gear table keeps a foreign key that points to a specific TourSch table record.
When you set the lookup for the Gear table, enable the option This dropdown is dependent on... and select for Category control and Category field the new foreign key.
P
 		pim author 8/4/2011

Hi,
I've tried this, but it seems to me that I'd have to manually link each GearID with a TourSchID in a separate table which I then would use as the lookup table.
With each new GearID and also TourSchID I'd have to do that again.
Isn't there a way in coding with a condition where / if or something or am I totally misunderstanding your answer of how to achieve this?
Thank you so much for answering me,
Pim
C
 		cgphp 8/4/2011



Hi,
I've tried this


Did it work ?



but it seems to me that I'd have to manually link each GearID with a TourSchID in a separate table which I then would use as the lookup table.


Not necessary for a 1-to-1 relation (one gear element is associated to one TourSch element).
P
 		pim author 8/6/2011

Hi,
Thanks for your answer. No it didn't really work until now.
I need to create that 1 to 1 relationship. I didn't need this kind of relationship in the project until now. I've searched the forum, but can find info about one to many and many to may relationships. How can I setup a 1 to 1 relationship between these tables?
Table 'TourSch'

TourSchID (int)

StartDate (date)

EndDate (date)
Table 'Gear'

GearID (int)

GearNr (varchar)
P
 		pim author 8/6/2011

Hi,
Thanks for your answer. No it didn't really work until now.
I need to create that 1 to 1 relationship. I didn't need this kind of relationship in the project until now. I've searched the forum, but can find info about one to many and many to may relationships. How can I setup a 1 to 1 relationship between these tables?
Table 'TourSch'

TourSchID (int)

TourID (int)

StartDate (date)

EndDate (date)
Table 'Gear'

GearID (int)

GearNr (varchar)
And then this table would look up following values

Table 'GearSelect'

field 1: GearSelectID (int)

field 2: TourSchID (varchar) (should look up TourID from table TourID, and displays from there TourName)

field 3: GearNr (varchar) (should look up GearNr and limit the variables based on TourSchID)