Where’s That Record? (Rohit Agrawal)

Today we will be exploring some new features introduced in the Office Developer Tools – March 2014 Update that make filtering rich sets of data easier – Table Sort, Search bar and auto-complete support for modal picker. These features work with both LightSwitch HTML client apps and O365 Cloud Business Apps.

For the following example, I created a new LightSwitch HTML application and attached it to Northwind SQL database and imported the Customers and Orders entities. I also added common screen set with screen data as Orders. On the BrowseOrders screen, I changed the control type for Orders from ‘Tile List’ to ‘Table’ and reset the Table Rows to default so as to include all the properties belonging to Order.

Table Sort

Table Sort is now supported out of the box. With Table Sort, a Table can be sorted in ascending or descending order based on a Table column. First click on the Table Column header will sort the table in ascending order and the second click will sort the table in descending order.


In the above snapshot, Orders Table is sorted in ascending order based on Customer column. You might have already guessed that the up pointing arrow shown next to ‘Customer’ column header in the screenshot denotes sorting in ascending order while a down pointing arrow denotes sorting in descending order.

Sorting can be performed on property of any data type – (Boolean, DateTime, Decimal, Double, Email Address, GUID, Integer, Money, PhoneNumber, String, Web Address, etc). Sorting can also be performed on a navigation property. In the example above, ‘Customer’ (the far right column) is a navigation property which is related to Orders. ‘CustomerID’ is the summary property for Customers entity and that is what is shown in the table.


Search can be performed on any String type property provided that the property is marked as isSearchable. The ‘Is Searchable’ property can be found on property sheet for each field on the entity designer in the server perspective. Once you create a new BrowseScreen you will now notice a ‘Search’ command bar button is automatically added on the BrowseScreen. On runtime when this search button is clicked, a search box will appear on top of the collection control (List/Tile List/Table).

This is how the search bar looks


Search operation will be carried out on the string properties of the primary entity. Please note, search will not be performed on navigation properties. Thus in the above example, when a search string is provided on the Orders table, the ‘built-in’ search will show results from Orders table that match with the search string. In order to perform search on navigation properties, you can use a parameterized query.

Here is how the WHERE clause on the database query looks like when the search string is ‘madrid’:

WHERE ([ShipName] LIKE N’%madrid%’) OR ([ShipAddress] LIKE N’%madrid%’) OR ([ShipCity] LIKE N’%madrid%’) OR ([ShipRegion] LIKE N’%madrid%’) OR ([ShipPostalCode] LIKE N’%madrid%’) OR ([ShipCountry] LIKE N’%madrid%’)

If I attach the application to an OData service, then this is how the request sent to the OData service looks like:


To speed up the search operation, you can limit the number of properties that must be searched by setting some properties as non-searchable.

Disable search for specific fields belonging to an entity

You might want the search operation to be carried out on some specific String fields of an entity and not all the fields. You can disable search for specific fields by setting those fields as non-searchable. For example, if I do not want the search operation to be performed on ShipName field of Orders then I can do so by unchecking the ‘Is Searchable’ checkbox located on the property sheet for ShipName field in the server perspective:


Add new search button

For existing screens, a search button can be added through the ‘Add Button’ dialog. Below are the steps to add a new search button. The steps assume a BrowseOrders screen is being modified.

1. Click on ‘+ Add’ under command bar node.

2. On the ‘Add button’ dialog, select ‘Choose an existing method’

3. On the dropdown list, select Orders.showSearch


By default, the search command bar button will show a search icon on runtime and each time the search button is clicked, the visual collection is scrolled up to the top and focus is moved to the search bar.

Customize search bar placeholder

By default a search bar has a placeholder set as ‘Search’. One can easily change the placeholder by writing a line of code in the post-render method of the collection control on the screen.

Following are the steps to do so:

1. Select the collection control on the screen designer and click on ‘Edit PostRender Code’. The collection control could be a Table/TileList/List control.

2. Set the placeholder attribute value of the input tag to ‘My Custom Placeholder’

myapp.BrowseOrders.Orders_postRender = function (element, contentItem) {
            $(element).find("input").attr("placeholder", "My Custom Placeholder");

Please refer, ‘Localizing a LightSwitch Application’ in order to add a localized string.

Show search bar without clicking the search button

There could be scenarios where you may want to show the search bar each time a screen is opened without having to click on the search button. You can do so by calling the <visual collection>.showSearch() method within the screen created method.

In the above example, where my screen is called BrowseOrders, I added the following line of code.

myapp.BrowseOrders.created = function (screen) {

Search API

By using the search property of a visual collection, a developer can initially set the search text in the screen created method or the post-render method of the collection control.

So if I want to see all the Orders that contain string ‘Tom’ to be shown each time a screen is opened, I can do so by adding the following code in the postRender method for Orders.

myapp.BrowseOrders.Orders_postRender = function (element, contentItem) {
    //Set search string as 'Tom'
    contentItem.screen.Orders.search = "Tom";
    //Set value in the search bar
    $(element).find("input").attr("value", contentItem.screen.Orders.search);

Note: Setting the search text through code will not set the text on the search bar at runtime and thus we had to explicitly set the value for the search bar in the code above.

Auto-Complete support for modal picker

A details modal picker is normally used to select a related entity at runtime. With the newly introduced improvements, users will now be able to see an auto-complete box in place of a drop down for the details modal picker.

In the example we used above, at runtime once you open the AddEditOrder screen you will now be able to see an auto-complete box for ‘Customer’ property instead of just a dropdown menu.


Behind the scenes, search on the auto-complete box works the same way as that on the search bar for a visual collection control. Developers can thus set the ‘isSearchable’ property accordingly for each field belonging to Customer entity in order to incorporate that field in the search operation.

Search string on the auto-complete box require a minimum of 3 characters in order to trigger the search operation and first 15 results show up on the results pane. In case a user wishes to see all the entries within an entity (Customer in our case), they can do so by clicking on the ‘+’ button which will bring up the traditional details modal picker dialog.

Turn off auto-complete support for modal picker

One can turn off the auto-complete support for modal picker by setting the ‘isSearchable’ property of an entity as false.

In the above example, go to Customer entity designer, select the entity and set ‘Is Searchable’ property on the property sheet as false.


Now at runtime, the AddEditOrder screen will only show the dropdown menu for customer just like before.

Wrap Up

With the latest improvements it is easier to locate a record. Try out these improvements – sort capability on a Table control and search capabilities on a visual collection control/ modal picker. Please remember to upgrade your existing projects to Office Dev Tool for VS2013 by right-clicking on your project and selecting ‘Upgrade’.

If you have any feedback or would like to share something, please leave a comment below or visit the LightSwitch forum. Your feedback is always greatly appreciated.

– Rohit Agrawal, Software Developer in Test, Cloud Business Apps.