Cascading drop down box

[johnfletchertt]

Lets say I have a table A with 2 columns, City Name, City ID and then I have another table B with 3 columns, FirstName, LastName and City ID is a foreign key in this table from Table A. I want to use Dialogs and my first drop down box pulls 'City Name' from table A. But I want my second dropdown box which shows only FirstName from table B to show firstnames based on the choice of City Name from the first dropdown box. How do I do this? The videos online use an 'Argument' but in Alpha12 it's not clear how this argument is used for cascading dropdown boxes in Dialogs.

How do I achieve this?

[Davidk]

Re: Cascading drop down box

Let's say you're using the Dropdown control... and not the Textbox Lookup.

You'll be Storing a Value other than the Display value for your Parent Dropdown. The Stored Value will be CityId.

In the Child Dropdown, in the dialog where you Define your Choices... there is a property... Cascading Choices... turn this on.

Then pick your Parent Dropdown for the Parent control. Then pick Explicit for the Method for computing cascading filter.

In the builder for the Explicit Cascading Filter there's a full explanation of what needs to happen.

The UX automatically sets up a special argument for you. Let's say your Parent Dropdown is named ddCity. The special argument would be :c_ddCity

So, the filter for your Child dropdown would then be CityId = :c_ddCity where CityId is the field in your Child dropdown.

[johnfletchertt]

Re: Cascading drop down box

Before I go running off to experiment. The above instructions will filter the FirstName in the Child dropdown based on the CityId chosen in the parent drop down? Is that correct? I am asking cause I see you said, '.. where CityId is the field in your Child dropdown'. I am not displaying CityId in the Child dropdown, I am displaying 'FirstName' and CityId is a foreign key in the table from which the Child dropdown control pulls data. So based on the CityId chosen in the Parent control the Child dropdown then filters the FirstName. The instructions above will achieve this?

[Davidk]

Re: Cascading drop down box

Yes... that's right. In my particular test I have a Company dropdown (parent), ddCust, showing CompanyName and storing CompanyId. The Child dropdown, ddCustInv, is showing Invoices for the selected Company. I have not selected a separate stored value. The child dropdown is based on a table with InvNbr and CustId. My child dropdown cascading filter is CustId = :c_ddCust

[johnfletchertt]

Re: Cascading drop down box

Thanks Davidk this works as is. Just one thing to note for anyone reading, in the above example CityId = :c_ddCity, use instead CityId = :N_ddCity, as CityId is a numeric value. If it were a character value then you would use the C_ddCity. But as Davidk says this data type listing is clearly stated. I have one last question though. The entire process assumes the names of the columns are the same in both tables? Could this sort of thing be done if the column in table A was CustId and the column in table b was customer_ID? How would this be achieved if the column names were different? Cause in reality this often happens.

[Davidk]

Re: Cascading drop down box

Doesn't matter... you're supplying the table b column name... and Alpha is supplying the argument from the stored value from table a.

[johnfletchertt]

Re: Cascading drop down box

Okay, yes I see. Great. Thanks for all the help.

[TaiTai]

Re: Cascading drop down box

Further suggestion. In the onChange or OnSelect action, you may wish to include a call to refresh the cascading lists so that when you change City it refreshes FirstName list. (Nice to be back)

[Davidk]

Re: Cascading drop down box

The cascading property and setup take care of the refresh... you don't need to do anything explicit. When the value is changed in the parent dropdown, the child dropdown is automatically re-populated.