How to implement a cascading dropdown with 4 dropdown controls in LightSwitch

Question

Hi,

I'd like to know how can I do a cascading dropdown with 4 dropdowns controls in LightSwitch. One is for country, the second is for states, the thirdy is for cities, and the last is for neighborhoods.

I´d like to make one dropdown connect to the above, so, when I select a country, I will se only states of this country in the seconde dropdown. When I select a state, I will see only cities on the thirdy dropdown e when I select a city, I see only the neighborhoods of this state.

Whe I build the screen, th LightSwitch shows dropdowns with auto complete resource, but this solutions is the worst, cause I have tables with more than 30.000 rows, and when I try to type any name in the dropdonw, the system stops working...

I need to know if its possible cause my client has this situation and the choice of do the project with LightSwitch will depend on that.

Thanks,

Marcelo.

---

Olivertech

Answer 1

Tim has a couple of articles that cover that:

Nested AutoCompleteBox for data entry

and

Nested AutoCompleteBox for data entry Part 2

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 2

Tim's solution "Nested AutoCompleteBox for data entry" (the Make/Model/Year pattern) works, and I'm grateful to Tim for having provided it.  It could be reviewed perhaps, with a third party testing it (some of the detail was a little sparse for us LS n00bs) but on the whole, I got this critical function to work, which means we can effectively wean ourselves away from Iron Speed.

I do have a bit of a gripe, however; This process is quite possibly just a tiny bit overly complex for what will likely be a common function.  Could one of you product development heroes have a look at it (with a UI guru over your shoulder, perhaps), and see if it couldn't be made a little simpler? Or at least, expand Tim's explanation a bit? Add a bit of highlighting, circles & arrows sort of thing.

I'd be grateful.

Cheers,

Nef

(edit) Hmm... thinking about how this would work.  An autocomplete box option that says "Filter depends on" + a spot for the dragged control, with linkage happening behind the scenes.  Nice fat alt-text too :-)

Answer 3

@Olivertech, you might want to consider checking on the index structure for your database.  My bet is you don't have an index across the item you're searching for.  So you're getting a sequential scan of your 30,000 rows, instead of a nice ordered list which might cost you two or three IO's instead.

Answer 4

Hi Nef,

I'm glad you got the Nested ACB to work and appreciate your comments.

If there's any particular part of the solution that anyone doesn't understand, feel free to post something here and I'd be happy to clarify.

@Marcelo - does your system hang with a 'white screen', as documented in the connect issue below? If so, it might be worth adding a comment there to bring this to the attention of Microsoft. I can accept that the ACB will respond slowly with 30000 rows but it shouldn't 'crash' the entire application if that's the case.

http://connect.microsoft.com/site1231/feedback/details/667926/autocompletebox-with-large-number-of-rows-hangs-application

Tim

Answer 5

Hi Tim,

Yes, my system hangs, but there in no blank screen. When I type the first letter on the last autocomplete control, which points to the 30000 table, the control freezes with the silverlight circle symbol, and nothing happens.

The screen stops working and the only thing I can do is close the window.

I will try to implement an index to some ids columns of my table, but despite of that, I think that is really important to have a way to connect more than on autocomplete, passing through the ids which will filter data, reducing the number of returned rows.

Thanks,

Marcelo.

---

Olivertech

Answer 6

Hi Yann,

I am trying to replicate those Tim's examples but my results are being different from the examples. 

My case is like the second article, but I am not getting the same result. It seems to me very confused to follow those steps. See the paragraph below. Where is the query created ? Where do I need to click to insert the new query ?

In order for us to filter the towns based upon county, we need to a parameterised query to return the towns for any given county. A query called ‘TownsByCounty’ will therefore be created that will filter Town by CountyID using a parameter called ‘CountyId’. The query will look like this.

 

In those steps below, he says that by default the query is added as an autocomplete box... But for me, it is included as a datagrid control when I add the query to the screen.

 

1. Create a ‘Data Entry Screen’ based upon the Customer table
2. Click on the ‘Add Data Item’ button and add the ‘TownsByCounty’ query onto the screen.
3. By default, an AutoComplete box will have been created for the ‘Towns’ field. Set the ‘Choices’ property of this to ‘TownsByCounty’

 

Thanks

Marcelo.

---

Olivertech

Answer 7

You create the query "TownsByCountry" when you have the "Towns" table open in the LightSwitch Designer.

You assign the "TownsByCountry" query to the AutoCompleteBox on the Screen by first adding the query to the screen (using Add Data Item). (You do not drag the query onto the control tree.) You then edit the properties of the AutoCompleteBox to change the "Choices" property to point to the correct query.

---

Simon Jones

Answer 8

Hi Simon,

Thanks... Now I could finish !!! Now the four level autocomplete controls is running ;)

To help anyone who has the same problem, below I put the steps to create a cascading dropdown with 4 autocomplete controls, using the Tim exemple to help on anything more.

The situation is that I have 4 tables (entities):

- Country

- States - it has a country foreign key

- Cities - it has a state foreign key

- Neighborhood - it has a city foreign key

And the challenge is to connect those entities in a cascading, turning the autocomplete controls connected between each one. So, we need to do that:

1 - Create the screen where you need the 4 controls. After LightSwith has created the screen, delete the state, city and neighborhood autocomplete controls from the screen

2 - Right click the State entity on Solution Explorer and select 'Add Query'. Create the new query with:

- filter: Where State.Id_Country = id_country

- sort: here you can use any kink of sort

- Parameters: Parameter Id_country of type Integer

In the Property of the query, rename it to StatesOfCountry.

3 - Return to the screen. Click on 'Add Data Item' button, select 'Query' and locate the query StatesOfCountry. Click Ok. The query will be add to the left panel, where you see all the screen properties.

4 - Click on 'Add Data Item' button again. Select 'Local Property'. Locate the nameofyourcontextDATA.State (Entity). Rename it to StatesOfCountryProperty and click Ok.

5 - Now you have two new components. One is the query, the other is the property. Drag the StatesOfCountryProperty to the screen. The property will create a new autocomplete control.

6 - Select this new autocomplete control on the screen and in its property (right panel), change the dropdown 'Choices' from Auto to StatesOfCountry, which is your new query.

7 - Finally, select the StatesOfCountry Query (left panel) and click on the query parameter field, in this case, the Id_country. In the right panel you will see the textbox 'Parameter Binding'. Click inside the control. The LightSwith will shows the properties of the screen. Select the main property of the screen.. something like 'NameOfEntityProperty....

Using intellisence, when you type '.', select the Country entity, type '.' again, and select 'id_country'.

Done !!!

Repeat those steps for the other 2 controls (Cities and Neighborhood). Pay attention only with step 7... The Parameter binding with a autocomplete control is pointing to the property of the autocomplete control above it. So, the City looks to StatesOfCountryProperty, the Neighborhood looks to CitiesOfStateProperty and so on...

Good Look and Thanks to the list.

 

---

Olivertech

Answer 9

Hi Marcelo,

Thanks for sharing your answer and thanks to Simon for clarifying what I was saying in my article :)

I've been so busy this week so apologies for not replying to you earlier.

Tim

Answer 10

Hi Olivertech,

Great explanation on how to work the cascading dropdown lists. The way you explains it works but when you reopen or refresh the screen the values in the dropdown boxes are erased and set to null. Is this the normal behavior or are there any setings or code to prevent this behavior?

Also do you know a way to apply this in a Parent --> Child situation. For instance I have a parent table named Connection and a child table ConnectionBuilder. The Connection table contains a foreign key field named Connection Type. Every Connection Type can have many ConnectionFacets. The values in the dropdown for the ConnectionFacets in the ConnectionBuilder table depend on what Connection Type has been selected in the Connection Table.

Is it possible to configure the above scenario?

Thanks

---

Erik

Answer 11

Looking for something cleaner than this

partial void SelectedCompany_Changed()
        {
            SelectedDepartment = null;
            FacilitiesByCompanyProperty = null;
           
        }

        partial void FacilitiesByCompanyProperty_Changed()
        {
            SelectedDepartment = null;
           
        }

sort of logic to clear the facility and department dropdowns when the one above them is changed (hierarchy here is company facility department)

Any ideas?

Answer 12

Hi Tim,

Unfortunately, I cannot get this solution to work if the dependant columns are columns in side a grid view. Also, the XXX_Changed, XXXX_Validate and XXXX_Loaded event for a Auto Complete Box inside a grid column fires only once - no matter how many rows you add to the grid. Do the controls behave differently inside a grid row as opposed to when they are placed on the screen directly?

Thanks

-Vimal

---

vk

Answer 13

AAAARRRRGHHH!

   I'm pulling what hair I have left out!  I've been going over this solution for a week and it just seems to be eluding me. 

I have a Products table, a Roles table, and a Product_Roles table. 

Relationships: Products 1-many Product_Roles, Roles 1-many Product_Roles

I create a parameterized query (product_RolebyProductID) against my "product_role" table (ID,Product,Role) and filter on Product.ID against ProductID parameter.  I bind the parameter to my collection.selecteditem.Product.Id but somehow, when I add the Role from the product_RolebyProductID query to the screen, it still shows me ALL roles in my roles table?  I have tried every iteration of Tim's example and cannot get this cascading dropdown to work?

Can anyone please provide some insight on this? 

Scott

Answer 14

Did you change the data source of the combobox to point at the correct query?

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 15

Yann,

     Thanks for the reply.  The only selection I get under Choices is "Auto".  :(

Scott

Answer 16

That possibly means that you've not created the query on the right entity.

Is your AutoCompleteBox tied to Products, Roles or Product_Roles?

You can only set the Choices to a query that returns the correct type of entities.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 17

Simon,

    Thanks for the reply.  I don't know if what I'm doing is even possible?  I have 3 tables (Products, Products_Roles, and Roles) to allow me to have multiple roles per product.  

The relationships are Products <- 1:many -> Product_Roles <- many:1 -> Roles.  When I create a products editable screen, I can create a tab for adding/editing/deleting roles for the product. 

Another table (request details) has the following relationships: RD <- many:1 -> role and RD <- many:1 -> product.  When I create a collection of RD, each RD needs a product and a role.  When I select a role, it gives me the entire row table to choose from and what I need is only the roles associated with the product in the RD selecteditem.  I am not sure how to filter on this, since it makes sense to me to filter on the product_role where productID = rd.selecteditem.product.id, but that doesn't seem to work?

Any thoughts would be appreciated!

:)

Scott

Answer 18

Ok, there's your problem. Your Request Details table is related to products and to roles but you are trying to set the auto complete box to read from the product_roles table to which it is not related.

You should either change the relationship or filter the list of Roles not the list of Product_Roles.

---

Simon Jones
If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

Answer 19

AAAAHHHHH!!!!!

          I got it with mental promptings from Simon's post.  I created a relationship between RD and Product_Roles (as Simon recommended), added the Product_Role to the screen, but it was showing the "ModifiedBy" field as the display, so I had to create a calculated field (result = Role Name) and use that as the Summary for the table.  Having already created a Product_Roles custom query by productID, now I get a dropdown with the Role name filtered by the Product ID! 

Thanks Simon!