Cascading Dropdown Boxes

[josevelez]

Hello,

I've gone through the forum postings to see if I can find an answer but no luck so here goes.

I saw in one of the video tutorials (video #18) how cascading dropdown boxes could be used to filter results, BUT this example was based on a dbf.

Can anyone point me in the direction or show me how the same could be done with a mysql db? I cannot get this to work and have been struggling with this for days now. It SHOULD be something very simple but I cannot get it to work.

I have a state and county table (see attached pic). I have a grid that has two DDB's - one for state and one for county. I set the state DDB to pull from the state table the Sate name and store the idstate value. Then I setup the second DDB to pull county name from the county table where idstate matches the state DDB.

When I test it out I get nothing. I've tried everything and cant figure it out. Can anyone PLEASE point me in the right direction?

Thanks!!!!

[Pat Bremkamp]

Re: Cascading Dropdown Boxes

Jose,

I got it to work by making one table with both country and state fields, that is
Country State
USA Kansas
USA Oklahoma
Canada Alberta

and so on.

Otherwise, how would Alpha know what state goes with what country?

Pat

[josevelez]

Re: Cascading Dropdown Boxes

Jose,

I got it to work by making one table with both country and state fields, that is
Country State
USA Kansas
USA Oklahoma
Canada Alberta

and so on.

Otherwise, how would Alpha know what state goes with what country?

Pat

Pat,

Thanks for responding.

The State -> Country problem I gave was just an example of what I am trying to accomplish with cascading dropdown boxes. The problem with your solution is that your tables would not be properly normalized (you have USA, Canada, etc... showing up in more than one row).

I can do this all day long in Access with two tables joined by a primary key-foreign key relationship. That is how Alpha should know what state belongs to what country.

When I select, say the United States, I should only see states that belong to the United States on the second dropdown box because Alpha should filter the state table with only those records that match the country primary key.

Does this make more sense? Again, I am NOT using the DBF database that ships with Alpha. I'm using a MYSQL database.

Alpha 5 developers, since you guys did a video example of cascading dropdown boxes with a DBF database could PLEASE do one with MYSQL??? I think this would be of help to several other folks who are not using DBF.

Thanks!

[Pat Bremkamp]

Re: Cascading Dropdown Boxes

I am doing it using MySQL just as I described.

If you want to use two tables instead of just one, just take your tables and create a read-only view, then base both dropdowns on that view. To Alpha, that looks like a single table with both country and state in it. It works, and as usual, Alpha takes most of the work out of it and does it behind the scenes.

Pat

[josevelez]

Re: Cascading Dropdown Boxes

Thanks makes sense! I'll try that.

Thanks Pat!

[ssmoore1]

Re: Cascading Dropdown Boxes

Hi Jose,

I was able to get it working with a mysql database and two different tables.

In my case table one has a list of customers. Table two has a list of customer properties (i.e houses, vacation homes etc.). A customer can have multiple properties. When the user has selected a customer in the first drop down box, they should only see the properties associated with that customer in the second drop down box. I also show the description but store the ID field...

When I look at my tables setup (image attached) I think that might have something to do with why mine is working and yours is not.

The customer_ID field is part of the primary key of the customer table (Table 1) and is also part of the primary key of the property table. Moreover, its a foreign key in the properties table back to the customer table. So I think that's how alpha is able to make the cascade work. The other thing is the Customer ID in the property table has the exact same name as in the customer table (i.e. customer_ID) Even if you setup the customer ID in the property table as a foreign key of the customer table, if they don't have the same name I don't think Alpha makes the connection!

Looking at your tables I would suggest trying two things...

1) In the county table, change the State_idState to be just idState so it matches what's in the state table. See if that works I think it might....

2) If that doesn't work try putting idState as part of the key of the county table

Good luck!

Scott

[GGandhi]

Re: Cascading Dropdown Boxes

Hello
I have same question. I have posted same in the new developers forum. I would like to create a cascading lookup using a view not a table. Is it possible.

Thanks.
edit:
I am attaching the test file.( I know this forum is for version10. My question states version 9)

[GGandhi]

Re: Cascading Dropdown Boxes

sorry, the attached file did not show up.. another attempt:
looks like I can not upload in another forum one having uploaded to one forum. Any way the zipfile is in New developer forum under "Can I do something like this"

Thanks in advance.

[Roderick]

Re: Cascading Dropdown Boxes

How would we take this to the third level? For instance: I am using school terms.

TABLE
-------
SUBJECT GRADE CATEGORY
math, 3, adding fractions
math, 4, multiplying fractions
english, 3, reading
english, 4, writing
science, 3, earth
science, 4, biology



My user chooses a from a table: (math,english,science).
Then they choose a grade level: (3,4,)
Then a category: (fractions, reading, writing, earth, etc)

So my user chooses Math first. His next options should be grades: 3 or 4 so he chooses 4. One this last level I only want them to see 4th grade math categories but they see all of the 4 grade categories (in this case they would see biology, writing, and multiplying fractions.

I am thinking that I besides setting GRADE as the parent on the cascading options I also have to filter by the first dropdown choice.

Is that correct thinking?

In order to do that I would have to set a variable when they choose the first option. Then filter by that variable and choose the parent field for the third option.

?? Any other ideas ?? Am I way off here? Over-complicating it?

[Roderick]

Re: Cascading Dropdown Boxes

I thought this was the answer but it doesn't look like it: http://msgboard.alphasoftware.com/al...ading+dropdown

[christappan]

Re: Cascading Dropdown Boxes

It's in some other forum posts as well, but if you go the route of a textbox with an edit-combo lookup with dynamic filters rather than cascading dropdowns, it's pretty straightforward to do this. There may be reasons you prefer the dropdown, though...

[Roderick]

Re: Cascading Dropdown Boxes

Thanks. I'm going to play around with that this week and post some details here once I get it working.

[sdkrahn]

Re: Cascading Dropdown Boxes

Thank you christappan, that worked.

To summarize other postings: it sounds like the "cascading choices" feature of DropDownBox does not work if you choose to "store a value other than the display value." So use a TextBox instead of a DropDownBox; mark the checkbox for "Lookup" then set its "Lookup Type" to "Edit-Combo List" then in "Lookup Definition" you can set up a "Dynamic Filter" that links a field in the lookup table to the field in the record that you are editing (the field that you want to control the cascade).

Thanks to eskimoavenue for this post which suggests watching video number 75 here:

http://news.alphasoftware.com/v10preview/videos.htm

That video describes a technique that (1) includes the lookup table itself in the query for the whole grid, (2) uses the "Fill-In fields" property within the "Lookup Definition" to go store the selected value into the correct field in the main table, and (3) sets the "Table Alias" to "*unbound" under the "Update Settings" to avoid writing to the lookup table.