LightSwitch – Fill AutoCompleteBox with Database values

Overview of Article

LightSwitch automatically creates dropdown boxes if relationships are correctly set up between tables. It steers you towards creating correct data relationships which is a good thing.

However, there are exceptional circumstances when relationships have not or can not be set up. Connecting to legacy data is a good example of such a scenario. One option is to set up a ‘Choice List’ at the entity level but this isn’t necessarily appropriate if there are many items or if the items already exist in a database table.

Fig1: Select the column, view the properties and click on the ‘Choice List’ hyperlink.

The following article addresses how to fill an AutoCompleteBox with database values when relationships have not been set up at the table level. We’ll be creating an ‘Add Data’ form as part of this example. The following example uses a Customer table containing a string column called Category. The screen contains an AutoCompleteBox with a constrained list of category values. The selected string value will be saved into the Customer>Category field.

 

Data

The following screenshots show the tables that have been set up. Notice that no relationships have been set up between these 2 tables.

Fig2 Customer table

Fig3 Category table

 

Screens

We now come on to create our screen…

1. Create a ‘New Data Screen’ based upon the customer table
   
2. Click on the ‘Add Data Item’ button and create a ‘Local Property’ of type ‘Category (Entity)’. Name it prpCategory.
   
   Fig4 Creating prpCategory
   
   
3. Delete the Category textbox which is created by default.
4. Drag prpCategory from the left hand navigation pane onto your screen. Your screen should like this:
   
   Fig5 This is how the screen should appear

5. Click on the ‘Write Code’ button and select the ‘CreateNewCustomer_Saving’ option.
6. Write the following code:

   Private Sub CreateNewCustomer_Saving(ByRef handled As Boolean)
       Me.CustomerProperty.Category = prpCategory.CategoryName
   End Sub
   

   The C# equivalent would look like this:

   partial void CreateNewCustomer_Saving(ref bool handled)
   {
     CustomerProperty.Category = prpCategory.CategoryName;
   }
   

 

Conclusion

Now run the solution and you’ll be able to use the Category drop down to fill the Customer > Category field. The source for this example can be downloaded through the following link.

http://cid-7a042700ee409d7b.office.live.com/self.aspx/LightswitchFiles/CustomerCategory.zip

Technorati Tags: LightSwitch Fill AutoCompleteBox,AutoCompleteBox non releated data