LightSwitch – Add non existent records using AutoCompleteBox

Overview of Article

The LightSwitch AutoCompleteBox allows you select records using a dropdown list or to enter some text into the control. Typing into the AutoCompleteBox filters down the selectable records.

If a non existent record entered, it’s likely that a new record is required based upon the entered text. 

The following example uses a set of Customer and Category tables. A Customer belongs to a Category and the following sample demonstrates a ‘New Data Screen’ for creating a new Customer. The screen contains an AutoCompleteBox which allows a category to be selected. If a non existent category is typed in, the user will be prompted to save the new category prior to saving the customer record.

Data

The following screenshots illustrate the schema of our tables. A ‘zero or one’ to many relationship has been set up between the Category and Customer tables.

 

image

Fig 1 – Category table schema

image

Fig 2 – Customer table schema

image

Fig 3 – Relationship between the 2 tables

Screens

Here are the steps to create our ‘Customer’ screen.

  1. Create a new ‘Editable Grid Screen’ for entering in some Category details
  2. Create a ‘New Data Screen’ based upon the Customer table
  3. In order to programmatically access the AutoCompleteBox, we need to add a reference to System.Windows.Controls.Input in the client project. To do this, switch to ‘File Mode’ in solution explorer.
    image_thumb[24]
    Fig 4 Switch to File View in Solution Explorer

  4. Right click the  ‘Client’ folder and click ‘Add Reference’. Select System.Windows.Controls.Input
    image_thumb[25]
    Fig 4 Add a reference to System.Windows.Controls.Input

  5. We need to write some code to handle the LostFocus event of the AutoCompleteBox. However, this can only be done when the AutoCompleteBox becomes available. We therefore need to write an event handler for the ‘ControlAvailable’ event and to add the handler for the LostFocus event there. Click on the ‘Write Code’ button and click and select the ‘CreateNewCustomer_Created’ event. Add the following code:

    Private Sub CreateNewCustomer_Created()
        AddHandler Me.FindControl("Category").ControlAvailable, AddressOf CategoryFieldAvailable
    End Sub
    
    Private Sub CategoryFieldAvailable(sender As Object, e As ControlAvailableEventArgs)
        AddHandler CType(e.Control, System.Windows.Controls.Control).LostFocus, AddressOf CategoryFieldChanged
    End Sub
    
    Private Sub CategoryFieldChanged(sender As Object, e As System.Windows.RoutedEventArgs)
    
        'Add a reference to System.Windows.Controls.Input.dll in the Client project
        Dim txtComboText As String = CType(sender, System.Windows.Controls.AutoCompleteBox).Text
        Me.Details.Dispatcher.BeginInvoke(
            Sub()
                If txtComboText <> "" Then
                    Dim selectedCategory As Category = Me.DataWorkspace.ApplicationData.Categories.Where(Function(Category) Category.CategoryName = txtComboText).FirstOrDefault
                    If selectedCategory Is Nothing Then
                        'Category doesn't exists
                        If Me.ShowMessageBox("Do you want to add the category " & txtComboText & "?", "Add Category", MessageBoxOption.YesNo) = Windows.MessageBoxResult.Yes Then
                            selectedCategory = Me.DataWorkspace.ApplicationData.Categories.AddNew()
                            selectedCategory.CategoryName = txtComboText
                            Me.CustomerProperty.Category = selectedCategory
                        End If
                    End If
                End If
            End Sub)
    
    End Sub
    

    Here’s how the C# equivalent looks like:

    private void CreateNewCustomer_Created()
    {
        this.FindControl("Category").ControlAvailable += CategoryFieldAvailable;
    }
    
    private void CategoryFieldAvailable(object sender, ControlAvailableEventArgs e)
    {
        ((System.Windows.Controls.Control)e.Control).LostFocus += CategoryFieldChanged;
    }
    
    
    private void CategoryFieldChanged(object sender, System.Windows.RoutedEventArgs e)
    {
        //Add a reference to System.Windows.Controls.Input.dll in the Client project
        string txtComboText = ((System.Windows.Controls.AutoCompleteBox)sender).Text;
        this.Details.Dispatcher.BeginInvoke(() =>
        {
            if (!string.IsNullOrEmpty(txtComboText)) {
                Category selectedCategory = this.DataWorkspace.ApplicationData.Categories.Where(Category => Category.CategoryName == txtComboText).FirstOrDefault;
                if (selectedCategory == null) {
                    //Category doesn't exists
                    if (this.ShowMessageBox("Do you want to add the category " + txtComboText + "?", "Add Category", MessageBoxOption.YesNo) == System.Windows.MessageBoxResult.Yes) {
                        selectedCategory = this.DataWorkspace.ApplicationData.Categories.AddNew();
                        selectedCategory.CategoryName = txtComboText;
                        this.CustomerProperty.Category = selectedCategory;
                    }
                }
            }
        });
    
    }
    
    

    In the above code, we obtain the text that has been entered using the ‘Text’ property of the AutoCompleteBox. If the text is not empty, we attempt to look up the Category in the Category table. If the Category isn’t found, we use ‘ShowMessageBox’ to prompt the user to save the new Category. After the new Category is saved, we set the Category property on the Customer entity equal to the Category which has just been created.

  6. Now run the application. Here are some illustrations showing the application in action. We begin by having just a ‘High Risk’ Category in the database.

    image

    In the ‘Create New Customer’ form, we enter a Firstname, Surname and type ‘Low Risk’ into the Category AutoCompleteBox. When we leave the Category AutoCompleteBox, the following dialog appears.

    image

    If we click ‘Yes’, the Customer record is saved into the database. If we now return to the Category screen, we see that the ‘Low Risk’ category has also been added into the database.

    image

 

Conclusion

This article illustrates how records can be added using an AutoCompleteBox. The sample solution could benefit from some refinement but highlights various principles required in order to put together a working screen.

The example solution can be downloaded from the following link.

http://cid-7a042700ee409d7b.office.live.com/self.aspx/LightswitchFiles/CustomerCategory3.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.

31 Responses to LightSwitch – Add non existent records using AutoCompleteBox

  1. Yann says:

    Hi Tim,

    What an EXCELLENT article!

    It’s a pity that some type of functionality like this isn’t “baked into” the control, & it’s also a pity that we have to jump through so many hoops to get this working. But a fantastic workaround!

    I tried to incorporate it with my preferred method of detecting changes (INotifyPropertyChanged), but the AutoCompleteBox control doesn’t change the underlying property unless a valid selection is made, so sadly I couldn’t.

    Great work Tim,

    Yann

  2. dotnettim says:

    Hi Yann,

    Many thanks for trying this out with INotifyPropertyChanged and reporting back!

    I too would have liked to have used INotifyPropertyChanged but as you’ve probably seen, there are still various nuances that I’m still figuring out.

    Tim

  3. JaimeH says:

    Good job Tim

    I just want to ask why it works to “Create new customer” and not in “Editable Grid Customers”?

  4. dotnettim says:

    Hola JamieH,

    As Yann has commented, it would be ideal if would could subscribe to the INotifyPropertyChanged event on the entity – in the case of an editable grid, we would want to capture INotifyPropertyChanged on the SelectedItem of the grid. As Yann says, this isn’t possible because INotifyPropertyChanged is only raised when an existing item in the AutoCompleteBox is selected.

    In order to work around this, we have used the ‘LostFocus’ event of the AutoCompleteBox in the ‘Create New Customer’ screen. In order for us to use this method in a grid, I think we would need to loop around the grid and to add the ‘LostFocus’ event handler onto each instance of the ‘AutoCompleteBox’ on the grid.

    Although this doesn’t completely answer your question, I hope it helps!
    Tim

  5. JaimeH says:

    Thank you very much for your response Tim

    So if INotifyPropertyChanged is only raised when to an existing item is selected in the AutoCompleteBox. I have the following question: You could create a record that is called for example: “Add Record” inside the list to select and if the data user is looking for does not exist, select it and shoot well, INotifyPropertyChanged?

  6. Yann says:

    Tim,

    My guess (& it’s a guess because I haven’t tried it) is that you might be able to add the LostFocus handler to whatever controls in the grid row you want to “monitor” for loss of focus, by using Me.FindControlInCollection(CONTROL_NAME, collection.SelectedItem) in a Collection.SelectionChanged event handler (where you remove the existing handler(s), then add the new handler(s), each time the selection changes).

    I’ve used this SelectionChanged technique for INotifyPropertyChanged in a grid before, & I don’t see why it wouldn’t work here as well.

    Yann

  7. dotnettim says:

    Hi Yann,

    Thanks very much for the suggestion :)

    @JamieH – it’s worth trying out Yann’s technique as it should to work in theory.

    Tim

  8. JaimeH says:

    Hello Tim

    I ask you please to help me to adapt these sample for the case of control with the new record this in a window detail (table related).

  9. JaimeH says:

    Hello Tim

    I ask you please to help me to adapt these sample for the case of control with the new record this in a window detail (table related).

    Have great day.

  10. Pingback: LIGHTSWITCH REINVENTANDO LA RUEDA? | Probando LightSwitch Beta 2

  11. Pingback: LIGHTSWITCH REINVENTING THE WHEEL? | Probando LightSwitch Beta 2

  12. Dave C. says:

    Thanks, Tim for a great article.
    What would I do if the control is in a grid? I have to use the this.FindControlInCollection method, but cannot get the rowData parameter right. Any ideas?

    Thanks, Dave

  13. Şükrü Tulga says:

    i cant find autocompletebox under system.windows.controls, do i have to add a reference ? if yes can you please tell me wihch one

  14. Şükrü Tulga says:

    where do i have to add it ? under servergenerated or clientgenerated ?

  15. cncsimple says:

    Ciao,
    e se ho una combobox editor dentro la datagrid, come faccio ad ottenere lo stesso comportamento ?

  16. cncsimple says:

    Hello,
    and if I have a combobox editor inside the datagrid, how do I get the same behavior?

  17. Hi Tim,
    I’ve been looking for an ‘elegant’ solution to this issue recently and I like your option. However it requires a database read whether or not the item you typed is already in the underlying lookup table of course. This is because of the problem mentioned by your good self and Yann regarding the fact that the autocomplete box doesn’t trigger any notification unless there’s a valid selection.

    I started a thread last week at http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/140dd053-9560-4e44-8ace-94aec662b15a which outlines my thoughts for how such a solution might ultimately avoid the issues but as you will see if you read it I’ve hit a brick wall!

    Perhaps you would be kind enough to comment?

    Many Thanks,

    Ian MacPherson

  18. russ says:

    Hello, new to programming here and also new to LightSwitch as well. I built a small application using LightSwitch and I have an issue with adding an auto complete box that also allows me to just add text. I have a table called ScriptsSet and in this table is a category called Drugs. I made the Drugs table have a choice list. How can I use this choice list as a reference only list, so that a person can use the drop down box OR just type plane text without it giving a validation error?
    Thanks!

  19. Yanko Spassov says:

    Hi,
    Great Article.
    Just want to extend the scope, if you have already inserted a new item (passing true Autocomplete Box) and for some reason you go back to FirsName or whatever, after passing again true Categories, “LostFocus” is fired again.
    My workaround proposal is to add another for-each loop below the “If txtComboText “” Then”
    like this:
    For Each newCategory in me.DataworkspaceApplicationData.Details.GetChanges.AddedEntities.OfType(of Categories)()
    if newCategory.CategoryName = txtCombobox Then ExitSub

    Best regards
    Yanko

  20. Efrain says:

    This design is incredible! You obviously know how to keep a
    reader amused. Between your wit and your videos, I was almost moved to start my own
    blog (well, almost…HaHa!) Fantastic job. I really loved what
    you had to say, and more than that, how you presented it.
    Too cool!

  21. Everyone loves what you guys are usually up too. This type of clever work and coverage!
    Keep up the superb works guys I’ve incorporated you guys to my personal blogroll.

  22. I noticed your website while looking around on http://dotnettim.

    wordpress.com/2011/04/20/lightswitch-add-non-existent-records-using-autocompletebox/.
    Have you got any kind of information on the way to get posted in http://dotnettim.
    wordpress.com/2011/04/20/lightswitch-add-non-existent-records-using-autocompletebox/?
    I’ve been working on it recently but they still won’t
    respond to me. Thanks

  23. Thank you, this is a matter that is near to my heart.
    Where are your contact information though? My name’s Peggy Treadwell and I’d get into
    this more deeply.

  24. obviously like your web-site but you need to check the spelling on
    several of your posts. Many of them are rife with spelling problems and I to find it
    very troublesome to inform the truth however I will certainly come
    again again.

  25. Hi, I do think this is a great blog. I stumbledupon it ;) I am going to return yet
    again since I bookmarked it. Money and freedom is the greatest way to change, may
    you be rich and continue to guide other people.

  26. Rafael says:

    Thanks for the article. Very useful information !

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