csaurel
Posts: 6
|
| Posted: 04/05/2009, 8:37 AM |
|
Hi!
I have a simple question. I have 2 tables. One for partners and one for locations. The first table has an id field, fields for lastname and firstname and a field that corresponds with the id field of the locations table. That's simple. On my page there is a record form which shows the actual record from the partners table indentified by a parameter (GET variable). It shows input fields for firstname, lastname and a dropdown listbox with locations. This listbox has the selected value of the actual location of the partner showed. The locations table has following fields: id, location_name, location_country_name (string field). Now my problem is that i want to filter the listbox's options so it shows only items (location names) that are in the same country. For example: the person has an id of 1, any lastname and any firstname and the listbox shows his home city in Hungary. Now the listbox should list in its options only hungarian locations and none of the other places in other countries.
Has someone an idea how to do this?
Thanks
I have tried a lot of methods but none of them worked for me...
|
 |
 |
damian
Posts: 838
|
| Posted: 04/05/2009, 6:33 PM |
|
add another field to partners table for location_country_name
or
add another field to partners table for location_country_id
and
add change the field locations.location_country_name to country_id
and
add a third table
country
country_id
country_name
set the where value on your drop down list to reference the value you are storing in the partners table or use dependant dropdown lists
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
csaurel
Posts: 6
|
| Posted: 04/06/2009, 1:30 PM |
|
Thanks for your answer but there is something i do wrong...
So my tables:
partners (ID, lastname, firstname, locationID)
locations (ID, location_name, countryID)
countries (ID, country_name)
query for record form:
SELECT partners.ID, partners.lastname, partners.firstname, locations.location_name, countries.country_name FROM (partners INNER JOIN locations ON partners.locationID=locations.ID) INNER JOIN countries ON locations.countryID=countries.ID
form has corresponding text fields
form shows record given by a GET parameter 'partner'
now i add a listbox with data source query:
SELECT ID, location_name FROM locations WHERE countryID= ???
what exactly comes there in the WHERE clause???
how can i refer to the countrID of the actual record? should i use a SELECT expression using the GET parameter 'partner' to retreive the actual countryID? How?
Or should i extract the countryID from a control to get the value? How this?
Please tell me how to reference the value that i need!
Thanks!
Sorry for asking so a newbie question!
|
|
|
|
damian
Posts: 838
|
| Posted: 04/06/2009, 7:00 PM |
|
have you tried doing any of the tutorials/examples?
what you are trying to do is quite straightforward but there is a lot to explain.
you will find it easier if you include an extra field in your partners though you could do it without....
partners (ID, lastname, firstname, locationID, countryID)
anyways - do a tutorial/example and then come back to your project and try using dependant list-boxes
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
csaurel
Posts: 6
|
| Posted: 04/07/2009, 4:38 AM |
|
Thanks! Of course i have tried tutorials and examples. I also made dependent listboxes but they work only when i alter the master listbox. What i need is an INITIAL filter for the slave listbox without changing the master listbox. So dependent listboxes don't resolve my problem. OK, i included in the record form query the locations.countryID so my form has the desired value in the result data already. What i still don't know, how to reference that value in the WHERE clause of another listbox element of the same record form... Did i miss something while examinig the examples?
Option 1:
the WHERE clause i set to 'countryID equals(=) Expression' where Expression is: '$Component->country->GetValue()' where country is the name of the form control containing the country.ID of the current record
Result viewing the page: Fatal error: Call to a member function GetValue() on a non-object
Option 2:
the WHERE clause set to 'countryID equals(=) Form' where parameter is set to 'country' which is the same control of the form as in option 1
Result: listbox not filtered anyway
Option 3:
i try to query the database for the desired countryID by the 'partner' GET parameter setting the WHERE clause to 'countryID equals(=) Expression' where Expression is: combination of CCDLookUp() and CCGetParam() functions
Result: Database connection error (it seems i don't know how to address the actual database without being able to set a global database variable since it is just an expression...)
Option 4:
as the WHERE clause i set a SELECT mysql statement giving {partner} as parameter
Result: it doesn't work either...
Option 5:
i give up trying... 
|
|
|
|
csaurel
Posts: 6
|
| Posted: 04/08/2009, 6:57 AM |
|
So no solution for my problem... 
Quote damian:
have you tried doing any of the tutorials/examples?
what you are trying to do is quite straightforward but there is a lot to explain.
you will find it easier if you include an extra field in your partners though you could do it without....
partners (ID, lastname, firstname, locationID, countryID)
anyways - do a tutorial/example and then come back to your project and try using dependant list-boxes
Which example do you think contains a method like i need? Dependent listbox isn't what i need since my lisbox depends on form record data and not on a changed master listbox...
|
|
|
|
damian
Posts: 838
|
| Posted: 04/12/2009, 8:49 PM |
|
ok - are these new or existing records?
if they are new records how can you pre-populate the records with the righ country when you dont know any details of the new record?
if it is an existing record then the dependant list-boxes should be appropriate?
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
csaurel
Posts: 6
|
| Posted: 04/18/2009, 11:23 AM |
|
Dependent listboxes work for me but:
If there is a new record the first listbox should initially contain a list of all countries actually showing the default country and the second listbox a list of the locations in that country.
When editing an existing record the first listbox should again initially contain a list of all countries actually showing the country from the actual record and the second listbox a list of the locations in this country.
Dependent listbox only works if the master listbox is changed but in this case i would like to set initial values.
Simply using dependent listboxes it is OK that i first have to choose country than location when adding a new record. But when i open a record for editing the master listbox shows the right country and the slave listbox lists all locations so it is possible to choose a location not belonging to the country that master listbox is actually showing and this causes a database error.
Do you understand what i mean? That's why i have to filter the second listbox without changing the first one.
|
|
|
|
damian
Posts: 838
|
| Posted: 04/19/2009, 1:34 AM |
|
when you edit an existing record how often do you find that your partner has actually changed addresses to a different country?
i have a similar page and the edit page does not use dependant listboxes for this reason. they cannot actually change the country once they create their entry. they can only change the location field.
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
csaurel
Posts: 6
|
| Posted: 04/19/2009, 3:28 AM |
|
So simply said: it can't be done with where clause filter.
And yes it occurs sometimes. Because i live in Hungary about 6km from the Austrian border and have a lot of partners who live actually here but move over for a time for work. So their contact addresses change from time to time from one country to the other. 
It seems i have to find another solution...
|
|
|
|
