Alpha Video Training
Results 1 to 13 of 13

Thread: Cascading Dropdown Boxes

  1. #1
    Member
    Real Name
    Jose Velez
    Join Date
    Oct 2010
    Location
    Dallas, Texas
    Posts
    125

    Default Cascading Dropdown Boxes

    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!!!!

  2. #2
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,629

    Default 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 Bremkamp
    MindKicks Consulting

  3. #3
    Member
    Real Name
    Jose Velez
    Join Date
    Oct 2010
    Location
    Dallas, Texas
    Posts
    125

    Default Re: Cascading Dropdown Boxes

    Quote Originally Posted by Pat Bremkamp View Post
    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!

  4. #4
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,629

    Default 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
    Pat Bremkamp
    MindKicks Consulting

  5. #5
    Member
    Real Name
    Jose Velez
    Join Date
    Oct 2010
    Location
    Dallas, Texas
    Posts
    125

    Default Re: Cascading Dropdown Boxes

    Thanks makes sense! I'll try that.

    Thanks Pat!

  6. #6
    Member
    Real Name
    Scott Moore
    Join Date
    Oct 2010
    Posts
    255

    Default 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!

    ScottAttachment 26499

  7. #7
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default 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)
    Last edited by GGandhi; 11-14-2010 at 07:47 AM. Reason: attaching file

  8. #8
    "Certified" Alphaholic
    Real Name
    Govindan Gandhi
    Join Date
    Aug 2008
    Location
    New York, NY
    Posts
    4,294

    Default 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.

  9. #9
    Member Roderick's Avatar
    Real Name
    Roderick Silva
    Join Date
    Aug 2006
    Location
    Miami, FL
    Posts
    141

    Default 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?
    Thanks,

    Roderick Silva
    rodericksilva@gmail.com

    About Me: rodericksilva.com
    Twitter: rodericksilva

  10. #10
    Member Roderick's Avatar
    Real Name
    Roderick Silva
    Join Date
    Aug 2006
    Location
    Miami, FL
    Posts
    141

    Default Re: Cascading Dropdown Boxes

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

    Roderick Silva
    rodericksilva@gmail.com

    About Me: rodericksilva.com
    Twitter: rodericksilva

  11. #11
    "Certified" Alphaholic
    Real Name
    Chris Tappan
    Join Date
    Jun 2009
    Location
    Marietta, GA
    Posts
    1,297

    Default 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...

  12. #12
    Member Roderick's Avatar
    Real Name
    Roderick Silva
    Join Date
    Aug 2006
    Location
    Miami, FL
    Posts
    141

    Default 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.
    Thanks,

    Roderick Silva
    rodericksilva@gmail.com

    About Me: rodericksilva.com
    Twitter: rodericksilva

  13. #13
    Member
    Real Name
    Steve Krahn
    Join Date
    Oct 2009
    Posts
    20

    Default 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.

Similar Threads

  1. Cascading Dropdown issue
    By ADREYES in forum Application Server Version 10 - Web/Browser Applications
    Replies: 0
    Last Post: 04-19-2010, 08:53 PM
  2. Cascading Dropdown
    By Jberry in forum Application Server Version 10 - Web/Browser Applications
    Replies: 1
    Last Post: 02-21-2010, 10:06 AM
  3. Dropdown Cascading Choices Problem
    By Steve Workings in forum Application Server Version 10 - Web/Browser Applications
    Replies: 3
    Last Post: 01-18-2010, 09:24 AM
  4. Cascading dropdown lists
    By patrick saunders in forum Alpha Five Version 6
    Replies: 5
    Last Post: 02-08-2005, 11:52 AM
  5. Values from cascading popup boxes
    By Malcolm Ransom in forum Alpha Five Version 5
    Replies: 0
    Last Post: 01-21-2004, 04:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •