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.

image

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.

image
Fig2 Customer table

image
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.
    image
    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:
    image
    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

About these ads

About dotnettim

Tim Leung is a Microsoft .Net / SQL Server developer based in England.
This entry was posted in LightSwitch. Bookmark the permalink.

41 Responses to LightSwitch – Fill AutoCompleteBox with Database values

  1. Yann says:

    Another excellent article Tim!

  2. J.D. Moyer says:

    Great example Tim — very helpful.

    I’m using the same screen in Lightswitch Beta2 for adding and editing records (as per Beth Massi’s example here: http://msdn.microsoft.com/en-us/lightswitch/Video/ff945359). I’ve managed to create an autofill control that looks up entries from my lookup table. The code you’ve posted above works great for updating the field value based on the control.

    Problem — when the screen is in *edit* mode, the autofill box is blank. I tried to update the control value based on the field value, basically the equivalent of:

    prpCategory.CategoryName = Me.CustomerProperty.Category

    It didn’t work — I got a NullReference error. Any idea what I might be doing wrong?

    Thanks in advance.

  3. J.D. Moyer says:

    Thanks for your response Tim. I’m having trouble setting prpCategory (prpCity in my case) to anything. My data source is MSSQL, so instead of adding prpCity as an entity, I added it as a table (my lookup table). If I try to set prpCity to a string value, I get the message “Value of type ‘String’ cannot be converted to ‘LightSwitchApplication.lookup’ ” Same thing if I try to set it to an int value.

    Any ideas?

  4. dotnettim says:

    Hi JD,
    Glad you got this to work!

    For the benefit of other readers, prpCity is of type LightSwitchApplication.lookup. For that reason, you would get that type of error if you wrote something like prpCity = “London”.

    By setting prpCity to a ‘lookup’ object, you get around this problem.

    prpCity = DataWorkspace.ApplicationData.Lookups.Where(Function(l) l.CityName = Me.CustomerProperty.CityName).FirstOrDefault()

    Tim

    • nik says:

      Hi Tim,
      The code worked flawlessly for a AddNewCustomer screen. But I am facing issues on the List and Edit screen. I am unable to find CustomerProperty. I am trying to set the value of prpCategory in the ManageCustomer_Created() function of the screen. Can you pls help here.

  5. J.D. Moyer says:

    I noticed that Tim’s method works great if the CustomerProperty field value is a string. However it didn’t work when the value is a byte (for example in my incomeLevel field I store a byte that corresponds to a value in the lookup table). I think it didn’t work because the summary field in my lookup table is the string description, so the function couldn’t find a match searching for lookup.incomeLevel = CustomerProperty.incomeLevel.

    In any case I found an alternate solution, as follows:

    1. Add a new lookup query to the screen that returns the values for income level (in my case lookupCategory = “incomeLevel”), name it qIncomeLevelValue
    2. Add a parameter to the query called theIncomeLevel
    3. Bind theIncomeLevel to CustomerProperty.incomeLevel
    4. The query will return one row — with the value that should populate the incomeLevel autofill box.
    5. In screenName_activated, add this code:
    Me.prpIncomeLevel = Me.qIncomeLevelValue.FirstOrDefault()

  6. J.D. Moyer says:

    So here’s my next question. When the screen is in edit mode, and I change the value of one of the autoComplete controls and hit Save, the value isn’t saved (because I’ve changed a control value, but not a field value). Is there a way to trigger the code that updates the CustomerProperty field from the control when the control value is changed (and no other fields are changed)?

    Thanks again!

  7. dotnettim says:

    Hi JD,

    Thanks for the info about the byte fields :)

    In answer to the question about running some code when the control value changes, you can handle the ‘LostFocus’ event of the AutoCompleteBox and write the code to update the CustomerProperty field from there.

    Here’s an article on using the LostFocus event to add non existent records using the AutoCompleteBox. Hopefully, you’ll be able to use the LostFocus technique that’s demonstrated there to set your field when the control value changes.

    http://dotnettim.wordpress.com/2011/04/20/lightswitch-add-non-existent-records-using-autocompletebox/

    The article contains a method called CategoryFieldChanged. This is the bit of code that you would adapt to suit your scenario.

    Hope that’s of some use to you!

    Tim

  8. Pingback: LightSwitch – Fill AutoCompleteBox with Database values | Kingsley A. Bainn

  9. bowlsy says:

    I am trying to find a similiar solution to pass a database value to a form for adding a new many to many relationship.
    The case might be Many Customers with Many Groups joined by a table of CustomerGroups.
    IE A user selects a Group from an existing list of groups and wants to add a New Customer to that Group (as oppose to adding a customer that already exists in the database).
    See forum question here for further explanation:

    http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/21e262a5-1f5e-48fe-af00-e0b01d9af624

    I haven’t seen any exmaples/tutorials on how to achieve this, and any help would be great.

  10. dotnettim says:

    Hi Bowlsy, thanks for the question. I take it that you found the answer via JamieH’s thread here?

    http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/60842e95-ba56-4162-9db2-2aac975dba8a

    Tim

  11. Great article! I noticed that some of the comments also touched on the same difficulties I was experiencing while attempting to use a single screen for editing and deleting. I was wondering if there is a way to update the screen name (ie. Tab) with the name of a new entity just saved in situations where a single screen is used for editing and adding? It would be great if someone on the lightswitch could create a video covering the culmination of these concepts…

  12. dotnettim says:

    Hi Benjamin, thanks for your comment.
    The DisplayName property should allow you to set text displayed in a tab. Maybe you could use this to code something up?
    this.DisplayName = “Set the Screen Name Here”; //C#
    Me.DisplayName = “Set the Screen Name Here” ‘ VB
    Tim

    • Hi Tim,

      If you don’t mind, I have another question related to the pre-populating of property entities (used for lookups into another table) when going back into a screen in edit mode (as with Beths; example)… I’m a bit new to C# which is what I need to use so my question may be (in part) due to ignorance of the synax…

      In your response to J.D. on may 3rd, you reference the code below as an example to pre-populate the lookup value for the entity property on the screen:

      prpCategory = DataWorkspace.ApplicationData.Catogories.Where(Function(cat) cat.CategoryName = Me.CustomerProperty.Category).FirstOrDefault()

      I was wondering if you can provide a C# example and also confirm where this would be coded (going back into edit mode)… I would think this would be in the Initialize Data Workspace method as per my example below???

      public partial class CreateNewRequestEdit
      {
      partial void CreateNewRequestEdit_InitializeDataWorkspace(List saveChangesTo)
      {
      // Write your code here.
      if (this.RequestID != null)

      this.RequestProperty = this.Request;

      // Prepopulate the entity property here???

      else

      this.RequestProperty = new Request();
      }

      Here’s what I have in my Saving method which seems to save properly.

      partial void CreateNewRequestEdit_Saving(ref bool handled)
      {
      // Write your code here.
      RequestProperty.Responsible = prpResponsible.Employee;
      RequestProperty.Manager = prpManager.Employee;
      RequestProperty.TechResponsible = prpTechResponsible.Employee;
      RequestProperty.TechManager = prpTechManager.Employee;
      }

      In my case, I have an entity named “Requests” which has a few colums which reference responsible employees (for certain tasks) and all the responsible employees are maintained in a single table (Responsible).

      Thanks for any insite you can provide…

  13. dotnettim says:

    Hi Benjamin,
    The C# version will look like this:
    prpCategory == DataWorkspace.ApplicationData.Catogories.Where(cat => cat.CategoryName == this.CustomerProperty.Category).FirstOrDefault();
    The InitializeDataWorkspace should work but if not, the Activated method should work too.
    Tim

  14. Aklama says:

    Hello Tim. I am a newbie of newbies in Lightswitch. I have the problem I have been cracking my head on. I am working on an app with entities where a patient in a PatientRecord table has multiple immunization entries in Immunization table. I have created a master detail with Patient record and Immunization the problem is when I want to create a new immunization entry for a patient I will like to have some of the fields in the new data screen filled up by data from the patient record. Please help

  15. dotnettim says:

    Hi Aklama,

    On your immunization screen, the patient record appears by default as an autocompletebox. At the bottom of your screen, add patient again so that you have 2 autocompleteboxes bound to patient. In the 2nd instance that you’ve added, use the dropdown box to change patient to a ‘group control’ (ie a columns layout). The remaining patient details will now be shown as textboxes. You can change these to labels to make them read only.

    Hope that helps!

    Tim

  16. Aklama says:

    Thank you so much for your quick reply Tim. I really do appreciate it. I will give a go and revert back ASAP

  17. Aklama says:

    Hi Tim,
    Thanks again for helping out.
    I implemented your suggestion but I am still not getting it to work. Perharps my question was not clear.

    The main Screen which open first is a PatientListDetail Screen which has immunization details also so that as you go from patient to patient their immunization records are displaced at the bottom right hand of the screen. My intention is to replace the default add immunization screen template with the one I created. The problem is when I click the ADDNEWEDIT button on the immunization screen, it does not preselect the patient for which I intend to create the record for. I hope it is clearer now.
    Thanks in advance

    Aklama

  18. wearoeric says:

    Hi Tim,
    I have implemented your example is very helpful.Operating in ‘the Create New Screen’ is correct,But ‘the Editable Grid the Screen’ job?Whether to give advice and assistance, Thanks!

    Eric

  19. Aw, this was an extremely good post. Spending some time
    and actual effort to create a really good article… but what can I say… I procrastinate a
    whole lot and never seem to get nearly anything done.

  20. I drop a leave a response each time I appreciate a article on a site or I have something to add to the discussion.
    It is triggered by the fire communicated in the post I looked at.

    And on this article LightSwitch – Fill AutoCompleteBox with
    Database values | Tim Leung’s Blog. I was moved enough to drop a comment ;-) I do have a couple of questions for you if it’s
    okay. Could it be simply me or do a few of the responses appear like coming from
    brain dead people? :-P And, if you are writing at additional social sites, I
    would like to follow anything fresh you have to post.
    Would you list all of your community pages like your linkedin profile,
    Facebook page or twitter feed?

  21. Heike says:

    Does your website have a contact page? I’m having trouble locating it but, I’d like to send you
    an e-mail. I’ve got some creative ideas for your blog you might be interested in hearing. Either way, great site and I look forward to seeing it grow over time.

  22. It’s a shame you don’t have a donate button!
    I’d most certainly donate to this fantastic blog! I guess for now i’ll settle for book-marking and adding your
    RSS feed to my Google account. I look forward to fresh updates and will talk about this site with my Facebook group.
    Talk soon!

  23. Thanks on your marvelous posting! I certainly enjoyed reading it, you’re a great author.I will make sure to bookmark your blog and definitely will come back at some point. I want to encourage you continue your great job, have a nice day!

  24. myspace.com says:

    Interesting web site, i read it but i still have some questions.

    shoot me an e-mail and we will speak more becasue i could have an intriguing concept for you.

  25. Sharron says:

    It’s going to be ending of mine day, however before end I am reading this impressive piece of writing to increase my experience.

  26. Very good info. Lucky me I found your blog by chance (stumbleupon).
    I’ve book marked it for later!

  27. Nicholas James says:

    I’m battling with this one, I created a new entity called “Countries” which I populated with a field “CountryName”, on the add/edit form for my contacts I dragged “Countries” over to the screen and it created a Details Modal Picker which looks great and works properly, however when i try and save the new record it does not appear to be saving the value selected from the picker to the database as the field is empty, if I make the field compulsory it brings up an error that the field is compulsory (i.e. no data is entered) how do I match this picker field up to the screen ?

  28. Chedda G says:

    Thanks for the post. I have a question: I have followed your instructions and its worked like a charm. In my table I have 8 columns that I added as a local property. When user selects a record, always the second column data is showing up in the dropdown. How do I control this behavior? I want to show another column data after the user selection or I want to combine multiple columns and show after the user selection. Please help me. Thanks!

    • dotnettim says:

      @Chedda, you’ll be pleased to know that it’s quite easy to do this. When you add an AutoCompleteBox onto a screen, the item that appears beneath your AutoCompleteBox is a ‘Summary Control’. Use the dropdown to change this to a ‘Rows Layout’. You can add/remove the items beneath your ‘Rows Layout’ to control the columns that your AutoCompleteBox shows.

      • Chedda G says:

        Thanks for your quick reply. I tried it and worked. But the problem is after the record selection from the dropdown, still the data from one field shows. There is no issue with the dropdown columns data (multiple columns), the issue is only after the user selects a row from the dropdown( user moves to next field).

      • dotnettim says:

        After the user selects a row from the dropdown, the AutoCompleteBox will show the summary property that relates to your table. So one way to customise this view is to go into the table designer and choose a different summary property. To combine multiple columns, you’d create a computed property in your table, and set the summary property to the computed property that you’ve created. However, this will modify all AutoCompleteBoxes that are bound to your table.

        Although this isn’t exactly what you’re after, I’d suggest that you add the fields of your property onto your screen, and render them as labels. That way, when the user selects a row from the dropdown, the labels will render the fields that relate to the selected row. To get the exact behaviour that you want, you’d need to create a custom control but that would involve much more work.

  29. Chedda G says:

    Thanks for the detailed suggestions. I can’t create a computed property as I should not modify this table. I will try to convince users with rendering the required fields as labels, otherwise I have to work on a custom control. Thanks for your help on this.

    • dotnettim says:

      You’re welcome! Just for info, adding a LightSwitch computed property doesn’t modify the schema of the underlying SQL table (or data source). LightSwitch creates the computed property in its data model, therefore, the impact of computed properties isn’t as significant as you might imagine.

  30. As for exercising, same issue! Customise it to suit your daily life! You are able to hike, stroll, run, bike, dance, skate, or be part of a bunch of men and women performing some thing like crossfit, zumba, at-home dvd’s (or youtube videos), be part of a gym, whatever! I’d personally recommend some mixture of cardio and soft gel slimming capsule schooling though :) Cardio will burn off calories and obtain your coronary heart rate up, although soft gel slimming capsule training will build/maintain muscle that from the long term may help develop your rate of metabolism and cause you to stronger and toned.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s