LS2013 HTML – Enhancing Table Control with Multi Column Sort and Filters

Mar 6, Here we go again! This is ANOTHER major update…

eht_final

  • Ability to sort on multiple columns
  • Easily Toggle Ascending, Descending, or no sort
  • Columns show their individual position in the sort order
  • Filtering on individual columns
  • Filter on Strings, Numerics and Dates
  • Popup filter dialog
  • Filtering can be disabled if not needed
  • Can be configured for batch processing
  • Batch mode can also be dynamically toggled
  • Compatible with latest LightSwitch update

Lets start by having you go up to http://github.com/dwm9100b/LsEnhancedTable and grab the latest code for this project. Make sure you right click on the downloaded zip, go to properties, and click on the Unblock button before unzipping.

As usual though, I’ll go thru a step by step process on how we got to the project you just downloaded. We’ve made this so easy its going to be hard to resist. So lets get to it…

  1. Create a new LightSwitch HTML Project
  2. Lets name it: EnhancedTable
  3. We’re going to use an external data source
  4. Select the OData Service option
  5. Use the following endpoint:
  6. Uncheck the ‘as read-only’
  7. Under authentication, select none
  8. When prompted to Choose your Entities
    • Select Order
    • Expand Order, Related Entities
    • Select Employee
  9. Name the data source: Northwind
  10. Click on finish, save your project

That’s all we’ll have to do with the ‘Server’ part of this project. The rest of the tutorial will be with the HTML Client.

  1. Right click on your HTML Client project
  2. Select Manage NuGet Packages
  3. Select the Online packages
  4. Search and install itgLsEnhancedTable
    • Three files get installed
      • Lo-Dash which is an awesome, lightweight alternative to Underscore.js. They’ve even included a ton of extras. Highly recommend that you check it out!
      • itgLsEnhancedTable.css (some quick styles used for the new table)
      • itgLsEnhancedTable.j
  5. Add all 3 files to your HTML Client default.htm
    • itgLsEnhancedTable.css
    • Lo-Dash.js
    • itgLsEnhancedTable.js
  6. We now automagically take care of step #5 for you when you install the NuGet package.

Great! That’s all the pre-work needed. Now lets go create our screens.

  1. Right click on your Screens folder
  2. Select Add Screen
  3. Lets use the new “Common Screen Set”
  4. Screen Data, Select Northwind.Orders
  5. Once LightSwitch does its magic, you’ll land on BrowseOrders
  6. Change the default Tile List for Orders to a Table
  7. Drag over some additional Order properties
    • Freight
    • ShipName
    • ShipCity
    • Employee
  8. Expand the Employee, change to a row layout
  9. Drag the Employee Last Name up under Ship City
  10. Delete the rest of the row layout for employee
  11. Change the Display Name for Last Name to Employee Last NameHere is how the screen should look by now
    eht_BrowseOrdersScreen_Base
  12. Good time to do a full build and test run your app. Test the out of the box functionality.
    • Notice you’ll only able to sort a single column at a time
    • Also the Search only supports strings
    • Not able to search on multiple columns
    • Unable to remove the Search input once its displayed

Ok, enough of that. Lets supercharge this table!

We start off by creating a few local properties via the Add Data Item. Uncheck the “Is Required” on all you create.

  1. Type: String, Name: ColumnName
  2. Type: String, Name: Operator1
  3. Type: String, Name: Value1
  4. Type: String, Name: Concat
  5. Type: String, Name: Operator2
  6. Type: String, Name: Value2

The following new Local Properties need to have a Choice List. So for each one:

  • Operator1
  • Concat
  • Operator2
    1. Over in Properties
    2. Click on Choice List
    3. Add a single value, “a”

We dynamically load choices, hence the reason for just a single value so LightSwitch will initialize the control

Great! Now lets go create our Filter popup

  1. Add Popup
  2. Leave as default Rows Layout
  3. Change the name to: FilterPopup
  4. Case sensitive!
  5. In order, drag the following local properties into this popup
    • ColumnName
    • Operator1
    • Value1
    • Concat
    • Operator2
    • Value2
  6. Change Column Name to be a Text control
  7. In properties for each, change Label Position to NoneYou should have a screen that looks as soeht_BrowseOrdersScreen_WithFilterProperties
  8. In your popup, add a new group, change it to Columns Layout
  9. Add a button into this group
    • Write my own method
    • Method name: SetColumnFilter
    • Change its Display Name to “Set”
  10. Add another button into this group
    • Write my own method
    • Method name: ClearColumnFilter
    • Change its Display Name to “Clear”

Here is how your final layout of your popup should be

eht_BrowseOrdersScreen_FinalPopup

Before we go wiring this all up, lets add a couple of main screen buttons to really show the flexibility

  1. Up at the Command Bar for the Order List Tab
  2. Add a new Button
    • Write my own method
    • Method name: ClearAll
    • Change your icon
  3. Add a new Button
    • Write my own method
    • Method name: ExecuteBatch
    • Change your icon
  4. Add a new Button
    • Write my own method
    • Method name: ToggleBatch
    • Change your icon

Done with the screen, here is a picture of how the final layout

eht_BrowseOrdersScreen_FinalLayout

Go ahead and save your solution and do a build.

Now on to the code, this will be the painless side of it.

  1. Click on the Orders Table control
  2. Edit PostRender Code
  3. Add the following code into its method
    // Store our enhanced table as part of our contentItem
    contentItem.enhancedTable = new itgLs.EnhancedTable({
        element: element,
        contentItem: contentItem
    });
    
    

This is the bare minimum you need to do. Go do a build and run your app. Couple of things

  • Multi column sort works
  • Click on the Column name to toggle direction
  • Notice the position number as you add more columns
  • Notice as you remove a column sort, positions adjust
  • Filter popup will display, but we’ve not wired it up yet

Ok cool eh?

Lets go wire some things up

  1. Double click on your ClearAll button
  2. Add the following code to its execute method
    // Get our table
    var table = screen.findContentItem("Orders").enhancedTable;
    
    // Clear all the sorts and filters
    table.clearAll();
    
    // If we are not in batch mode, reQuery
    if (!table.getBatchMode()) table.reQuery();
    
    
  3. Double click on your ExecuteBatch button
  4. Add the following code to its method
    // Get our table
    var table = screen.findContentItem("Orders").enhancedTable;
    
    // Execute the sort/filter settings
    table.reQuery();
    
  5. Double click on your ToggleBatch button
  6. Add the following code to its method
    // Get our table
    var table = screen.findContentItem("Orders").enhancedTable;
    
    // Toggle batch mode
    table.setBatchMode(!table.getBatchMode());
    
  7. Edit PostRender Code for popup button SetColumnFilter
  8. Add the following code to its method
     // A check mark icon with text at the bottom
     itgLs.ui.convertToIconicButton(element, contentItem, "accept");
    
  9. Edit PostRender Code for popup button ClearColumnFilter
  10. Add the following code to its method
    // A minus icon with text at the bottom
    itgLs.ui.convertToIconicButton(element, contentItem, "remove");
    
  11. Double click on your popup button SetColumnFilter
  12. Add the following code to its execute method
    // Get our table
    var table = screen.findContentItem("Orders").enhancedTable;
    
    // Call our set filter function for this column only
    table.setColumnFilter();
    
    // Close the popup
    table.closeFilterPopup();
    
    
  13. Double click on your popup button ClearColumnFilter
  14. Add the following code to its execute method
    // Get our table
    var table = screen.findContentItem("Orders").enhancedTable;
    
    // Call our clear filter function for this column only
    table.clearColumnFilter();
    
    // Close the popup
    table.closeFilterPopup();
    

Not too many lines of code now is it? Would be great if someone out there creates a screen template!

So there you have it… Go ahead and run the app and test away

  • Test multi column filtering
  • Add sorting into the mix
  • Toggle Batch
  • Set up your filter and sorts
  • Then Execute the batch
  • Try the one click clear all, less server trips!

You can pass the following properties when you create the EnhancedTable

filterPopupName
Name of the filter popup
Defaults to FilterPopup
filterPopupColumnName
Name of the ColumnName property
Defaults to ColumnName
filterPopupOperatorName1
Name of Operator1 property
Defaults to Operator1
filterPopupOperatorName2
Name of Operator2 property
Defaults to Operator2
filterPopupConcatName
Name of the Concat property
Defaults to Concat
filterPopupValueName1
Name of the Value1 property
Defaults to Value1
filterPopupValueName2
Name of the Value2 property
Defaults to Value2
batchMode
True/False
Defaults to false
filterDisabled
True/False
Defaults to false

So whats missing and left to do?

Validations on the filter input boxes
Ability to dynamically change the input boxes to a native control
Anything else I forgot… :)

30 thoughts on “LS2013 HTML – Enhancing Table Control with Multi Column Sort and Filters

  1. tomidix says:

    Hi,
    I would like to know if it is possible to add functionality for ignoring custom controls in table.. I have a table which is a read only, has some javascript calculated values and I use custom controls to show them.. The problem is that the filtering won’t work when there is custom control in table.. Is it possible to fix?

    • joshbooker says:

      tomidix, You can try to fix this by making a change to itgLsEnhancedTable.js. Find the following:
      // We only skip command (button) types, all others get passed for processing
      if (tableColumn.kind === “Command”) {
      return;
      }

      then add this after:

      // also skip CustomControls
      if (tableColumn.controlModel.id.indexOf(‘CustomControl’)){
      return;
      }

      I did not test this, but it should be close. tableColumn.kind would work too, but for CustomControls it could be several kinds to including ‘ScreenContent’, ‘Collection’, ‘Details and ‘Value’ depending on what the cc is bound

      HTH,
      Josh

      • joshbooker says:

        correction: add this after
        // also skip CustomControls
        if (tableColumn.controlModel.id.indexOf(‘CustomControl’)>0){
        return;
        }

      • tomidix says:

        First of all, thank you for your help and very quick reply..
        I managed to do it a similar way like you suggested but there is another problem which has raised now.
        Never mind the method, yours or mine, the result is the same and that is: All columns, except custom control column, have the filter button and that’s perfectly fine. But when I choose one of the columns to filter.. Popup opens.. It looks normally, I can input the filter values but when I click either set or clear button I get the following error:
        “Cannot read property ‘filter’ of undefined”.. This happens only when custom control column is in table. If I delete it from table, everything works perfectly so I assume that there is something more to do..

      • joshbooker says:

        Yeah, there are prolly a bunch of places to restrict contentcontrol columns. I just picked a spot where Dale was restricting buttons, thinking that may work. I’ll let Dale chime in to help. I’d guess preventing ContentControls from being in this array might do it:

        // Pointer to our array of table columns
        tableColumns: options.contentItem.children[0].children,

      • tomidix says:

        Ok, I managed to adjust script according to my needs and also found some bugs..

        // We only skip command (button) types, all others get passed for processing
        if (tableColumn.kind === “Command”) {
        return;
        }
        The above code should be right below the following line to avoid some filter error..
        // Get the column header contentItem based on the index
        var tableColumn = properties.tableColumns[i];

        I also added suggested code right below the mentioned if as I have to ignore the custom control..
        // also skip CustomControls
        if (tableColumn.controlModel.id.indexOf(“CustomControl”) > 0) {
        return;
        }

        After that I was going through code and figured that because of those returns, their columns stays with undefined enhancedTable atribute.. so I surrounded the content of the following foreach.. around Ln709
        _.each(properties.tableColumns, function (item) {
        _code_
        });

        the “_code_” was surronded with:

        if (item.enhancedTable!==undefined) {
        _code_
        }

        Now this part works fine but there is one more problem which I can now see..
        When I first load table.. it has its query with custom parameters set.. When I first set or clear any filter.. those custom parameters just disappears..
        To explain on the example.. Let’s say I have the Entity People and when the screen is loaded I load only People who are 18+… and that works with the custom query made with designer.. When I set the filtering, it just filters the whole datasource, ignoring my 18+ condition. Does anyone have a hint about the problem??

      • joshbooker says:

        It has to do with this line:

        options.contentItem.details._entry.simpleDescriptor.createQuery =
        function () {
        return this.dataWorkspace[properties.dataSourceName][properties.dataTableName].filter(properties.filterString).orderBy(properties.sortString);
        };

        It’s a trick to override the collection query in order to append filter and orderBy methods. See my blog for a way to monkey patch the query which will retain the parameters:

        http://joshuabooker.com/Blog/Post/8/Monkey-Patched-LightSwitch-Screen-Collection-Queries

      • joshbooker says:

        While I haven’t tested, something like this might work:

        query = options.contentItem.details._entry.simpleDescriptor.createQuery;
        //only override once – have we done this already?
        if (!query.old) {
        //save the old query
        options.contentItem.details._entry.simpleDescriptor.createQuery.old = query;
        //override the query
        options.contentItem.details._entry.simpleDescriptor.createQuery =
        function () {
        return query.old.apply(this, arguments)
        .filter(properties.filterString)
        .orderBy(properties.sortString);
        };
        }

  2. Tom says:

    Hi Dale, thank you for sharing this!!!

    Any ideas how to fix the header row when user scrolls the table?

  3. joshbooker says:

    Hey again Dale! Thought you might like to see this:

    http://joshuabooker.com/Blog/Post/8/Monkey-Patched-LightSwitch-Screen-Collection-Queries

    It’s a way to override the collection query while still maintaining the parameters from the screen designer so it works on parameterized queries as well. For example, your enhanced table can be used on a table based on a query.

    Cheers!

  4. joshbooker says:

    last comment, I promise… msls.js exposes a function that I think may be a superset of your function getODataString

    msls._toODataString(parameter, dataType)

    Have a great weekend!

  5. joshbooker says:

    Nice Dale! Cannot find the code on git though.

    • joshbooker says:

      Found it here:
      https://github.com/dwm9100b/LsEnhancedTable

      Really nice, work!

      Q: Filters across columns are ‘And’ style correct?
      Meaning you can do:
      Name eq dale AND Date eq 3/7/2014
      but cannot do
      Name eq dale OR Date eq 3/7/2014
      Correct?

      If you leave a filter val blank does it handle nulls?

      • joshbooker says:

        I can’t say enough how great this is! Couple suggestions:

        1 – It would be swell to have an init option to set the base filter. Say you want to use this on a screen that only displays Open Orders. Since you’re overriding the base query from the designer, you’d have to append ‘Shipped eq false’ to the dynamic query. The ‘base filter’ would stick when you clear the dynamic filter. Don’t know if poss, but it would be really cool to get the base filter from the designer query.

        2 – Make the filter popup display the entire filter across all fields. This would be a bit of work, but you’re so close. You know, like the query designer…display a list with one or more rows having the property names in pickers. Add another row preceded by AND\OR, etc. This would enable ‘OR’ across fields. Complexity of Grouping wouldn’t be necessary to replicate the current capabilities of your control.

        Your work is really great, keep it up!

        PS…To OData spec: why is order of arguments in substringof backwards?…it’s silly isn’t it?

      • dwm9100b says:

        Thanks for the feedback Josh. Sorry about the git issue, I’ve fixed the link.

        Currently across columns I’m only doing an “And”. Grouping gets to be quite messy and have not found a good working method on accomplishing it.

        I’ve thought about the bigger popup and will think how to do inline editing.

        Re nulls… yes its handled. If a value field is null (blank/empty) we skip the filter processing for that field. Are you looking for null filtering?

        And yes that OData spec is a mess… thanks again for the feedback… holler with bugs that popup.

      • joshbooker says:

        Yeah grouping is messy if you want to do filter like
        (this field and that field) OR (that field and another field)

        but forcing parenthesis around each field filter should at least allow OR across fields.

        ie:
        (Name eq ‘josh’) OR (State eq ‘Maine’)

        http://www.odata.org/documentation/odata-version-3-0/url-conventions/#url5.1.2.3

        I haven’t tried it but I think the literal Null is ‘Distinguished value meaning the absence of a value’
        http://msdn.microsoft.com/en-us/library/ff478141.aspx

        Wonder if LS dataservice would translate that properly(?)

        base filter init option should be easy enough, no? except the part about getting filter from designer query, I don’t think that’s even in the model since queries become function in the data service (I think?).

        Thanks again you rock!

      • dwm9100b says:

        We already do grouping of the column filters, so its a matter of the UI and where to put the conditional between columns.

        I’ll be updating later today an update that will include setting a default filter and sort.

      • joshbooker says:

        These look neat:

        http://kindohm.com/posts/2013/09/25/knockout-query-builder/
        http://redquerybuilder.appspot.com/

        …I’ll leave you alone now ;)

        keep it up!

  6. joshbooker says:

    Awesome Dale! Keep up the great work!

    • joshbooker says:

      PS… I havn’t tried this yet, but the Name and State query parameters are not required for dynamic table filtering, correct? I presume they were present in the query for other reasons, but the enhanced table only needs two parameters sortstring and filterstring, correct? also, why is filterStringPropertyName not required in step 19?

      • dwm9100b says:

        Correct… The query parameters are not required. They were left in to show how easy it is to remain with query parameters for filtering yet have the multi column sorting. Step 19 shows this and hence no filterStringPropertyName is required. Its more about options and not forcing the dev to totally abandon what they know.

  7. joshbooker says:

    Appreciate the thanks, but attribution not necessary.

    Just please vote on uservoice if only so team samples don’t have to include so much complicated linq expression building code.

    http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/4458683-enable-dynamic-queries

    We all know how to filter and sort using T-SQL and this is a RAD tool, right? Hope dynamic linq allows you to expand this to enable multi-column sort and filter!

    Keep it up!

  8. joshbooker says:

    Nice Blog! I really like the enhanced table javascript and look forward to giving a spin.

    I found you can use the Linq Dynamic Query Library in the server project rather than the code from team blog. This extends OrderBy and Where methods to take ‘T-SQL-Like’ strings of fields for sort and filter respectively. It also works for nav props so you can eliminate the switch in preprocess query as well as use a single query param to filter on any number of fields like so (sorry…vb):

    Private Sub SortContacts_PreprocessQuery(stOrderBy As String, AccountName As String, stWhere As String, ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Contact))
    If Not stWhere Is Nothing AndAlso Len(stWhere) > 0 Then
    query = query.Where(stWhere)
    End If
    If Not stOrderBy Is Nothing AndAlso Len(stOrderBy) > 0 Then
    query = query.OrderBy(stOrderBy)
    End If
    End Sub

    Using these extensions, you can sort and filter by a list of fields.

    For example:
    stWhere = “City.State = ‘Washington’ AND Population > 10000″
    stOrderBy = “Population DESC”

    I think it would be swell if these extensions were included in LS OOB.

    http://visualstudio.uservoice.com/forums/121579-visual-studio/suggestions/4458683-enable-dynamic-queries

    Keep up the great work!

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

Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: