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

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

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…

Create a new LightSwitch HTML Project
Lets name it: EnhancedTable
We’re going to use an external data source
Select the OData Service option
Use the following endpoint:
- http://services.odata.org/northwind/northwind.svc
Uncheck the ‘as read-only’
Under authentication, select none
When prompted to Choose your Entities
- Select Order
- Expand Order, Related Entities
- Select Employee
Name the data source: Northwind
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.

Right click on your HTML Client project
Select Manage NuGet Packages
Select the Online packages
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
~~Add all 3 files to your HTML Client default.htm~~
- ~~itgLsEnhancedTable.css~~
- ~~Lo-Dash.js~~
- ~~itgLsEnhancedTable.js~~
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.

Right click on your Screens folder
Select Add Screen
Lets use the new “Common Screen Set”
Screen Data, Select Northwind.Orders
Once LightSwitch does its magic, you’ll land on BrowseOrders
Change the default Tile List for Orders to a Table
Drag over some additional Order properties
- Freight
- ShipName
- ShipCity
- Employee
Expand the Employee, change to a row layout
Drag the Employee Last Name up under Ship City
Delete the rest of the row layout for employee
Change the Display Name for Last Name to Employee Last NameHere is how the screen should look by now
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.

Type: String, Name: ColumnName
Type: String, Name: Operator1
Type: String, Name: Value1
Type: String, Name: Concat
Type: String, Name: Operator2
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

Add Popup
Leave as default Rows Layout
Change the name to: FilterPopup
Case sensitive!
In order, drag the following local properties into this popup
- ColumnName
- Operator1
- Value1
- Concat
- Operator2
- Value2
Change Column Name to be a Text control
In properties for each, change Label Position to NoneYou should have a screen that looks as so
In your popup, add a new group, change it to Columns Layout
Add a button into this group
- Write my own method
- Method name: SetColumnFilter
- Change its Display Name to “Set”
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

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

Up at the Command Bar for the Order List Tab
Add a new Button
- Write my own method
- Method name: ClearAll
- Change your icon
Add a new Button
- Write my own method
- Method name: ExecuteBatch
- Change your icon
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

Go ahead and save your solution and do a build.

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

Click on the Orders Table control
Edit PostRender Code

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

Double click on your ClearAll button

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();

Double click on your ExecuteBatch button

Add the following code to its method

// Get our table
var table = screen.findContentItem("Orders").enhancedTable;

// Execute the sort/filter settings
table.reQuery();

Double click on your ToggleBatch button

Add the following code to its method

// Get our table
var table = screen.findContentItem("Orders").enhancedTable;

// Toggle batch mode
table.setBatchMode(!table.getBatchMode());

Edit PostRender Code for popup button SetColumnFilter

Add the following code to its method

 // A check mark icon with text at the bottom
 itgLs.ui.convertToIconicButton(element, contentItem, "accept");

Edit PostRender Code for popup button ClearColumnFilter

Add the following code to its method

// A minus icon with text at the bottom
itgLs.ui.convertToIconicButton(element, contentItem, "remove");

Double click on your popup button SetColumnFilter

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();

Double click on your popup button ClearColumnFilter

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…🙂

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

Ivan says:

March 15, 2016 at 9:00 am

Hi. I have a problem. Default softing is not working with this filter control, when I visid page at second time: ckick button – page opens – soring is working correctly; go back in browser; click button again – and sorting is not working. When I comment
contentItem.enhancedTable = new itgLs.EnhancedTable({
element: element,
contentItem: contentItem
});
sorting is working again.
Please, hepl me with this problem!

coach factory store online says:

September 22, 2015 at 2:56 am

I used to be very happy to find this web site. I wanted to thanks for your time fo this wonderful read !!

Fred says:

October 1, 2014 at 4:34 am

http://social.msdn.microsoft.com/Forums/vstudio/en-US/be425d6a-94d6-45f3-9efa-305e6980366d/itglsenhancedtable-filterproblem-one-screen-two-tabs?forum=lightswitch&prof=required

Fred says:

September 22, 2014 at 8:12 am

I am using database tables, but I am getting “Cannot read property ‘filter’ of undefined”. I cant see any data at all? Any idea what went wrong?

Thomas Tong says:

September 19, 2014 at 12:48 am

Hi this is my first time using lightswitch and your extension. It is great but I am facing a problem. Please see if your guys can help, thanks!

In my project there are two tables one is “Employee” and another one is “TransactionRecods”, they are related by the PK emp_Id in employee table and an emp_id field in transaction records table as FK.

First i created a screen which includes a table to display list of employee, I can apply the enhanced table successfully, just like the example given above.

When an employee record in the table is clicked, user will be navigated to the second screen and the second table will be displayed – to show the transactions of that particular employee – I failed to convert this table into an enhanced table.

What I found so far:
– I tried to new itgLs.EnhancedTable in the postRender function of transaction table, I found that getting value of “properties.dataSourceName” produced an error because “options.contentItem.model.dataSource.member.source.target.source.member” is undefined, lightswitch seems using some other ways to manage linked tables.

– this function options.contentItem.details._entry.simpleDescriptor.createQuery are not called in the transaction screen, again i believe this is because of linked tables

I am still investigating a solution to solve this, but if you guys have faced it already please show my your way! sorry for the long post and thanks in advance!

Thomas Tong says:

September 19, 2014 at 1:26 am

Update: for child tables, “appendQuery” should be used instead of “createQuery”

Reply

tomidix says:

May 21, 2014 at 9:47 am

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:

May 21, 2014 at 11:16 am

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

Reply
- joshbooker says:
  
  May 21, 2014 at 11:41 am
  
  correction: add this after
  // also skip CustomControls
  if (tableColumn.controlModel.id.indexOf(‘CustomControl’)>0){
  return;
  }
- tomidix says:
  
  May 21, 2014 at 12:30 pm
  
  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:
  
  May 21, 2014 at 1:07 pm
  
  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:
  
  May 21, 2014 at 2:51 pm
  
  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:
  
  May 21, 2014 at 3:26 pm
  
  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:
  
  May 21, 2014 at 3:39 pm
  
  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);
  };
  }
Fred says:

September 30, 2014 at 2:57 am

I have two tabs on one screen. Anyone knows how to solve this?

Reply
Fred says:

September 30, 2014 at 6:17 am

I wonder where you should put this code?

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);
};
}

I added the code above and the code in http://joshuabooker.com/Blog/Post/8/Monkey-Patched-LightSwitch-Screen-Collection-Queries but with no luck. I dont know if this because I am using one screen and two tabs. Any ideas?

Reply

Tom says:

April 10, 2014 at 12:56 pm

Hi Dale, thank you for sharing this!!!

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

dwm9100b says:

April 17, 2014 at 12:30 pm

You mean to freeze the header row?

Reply

LightSwitch Community and Content Rollup–March 2014 (+//build/) - Visual Studio LightSwitch Team Blog - Site Home - MSDN Blogs says:

April 9, 2014 at 12:32 pm

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

joshbooker says:

March 12, 2014 at 6:58 am

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!

LS2013 HTML – Enhancing Table Control with Multi Column Sort and Filters | Blog of an IT Guy | CompkSoft says:

March 7, 2014 at 12:49 pm

[…] LS2013 HTML – Enhancing Table Control with Multi Column Sort and Filters | Blog of an IT Guy https://blog.ofanitguy.com/2014/03/06/ls2013-html-enhancing-table-control-with-column-sort-and-filter… […]

joshbooker says:

March 7, 2014 at 12:20 pm

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!

dwm9100b says:

March 7, 2014 at 1:40 pm

Nice! Thanks… and no worries… that’s what posting is all about… cooperation… and helping to create better “stuff”

Reply

joshbooker says:

March 7, 2014 at 4:13 am

Nice Dale! Cannot find the code on git though.

joshbooker says:

March 7, 2014 at 4:32 am

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?

Reply
- joshbooker says:
  
  March 7, 2014 at 5:52 am
  
  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:
  
  March 7, 2014 at 7:17 am
  
  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:
  
  March 7, 2014 at 7:55 am
  
  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:
  
  March 7, 2014 at 8:39 am
  
  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:
  
  March 7, 2014 at 11:41 am
  
  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!

LightSwitch Community and Content Rollup–February 2014 (+March Update Released!) - Beth Massi - Sharing the goodness - Site Home - MSDN Blogs says:

March 4, 2014 at 1:43 pm

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

joshbooker says:

February 26, 2014 at 8:57 am

Awesome Dale! Keep up the great work!

joshbooker says:

February 26, 2014 at 9:13 am

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?

Reply
- dwm9100b says:
  
  February 26, 2014 at 1:34 pm
  
  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.

LS2013 HTML – Enhancing Table Control with Multi Column Sort and Filters | Blog of an IT Guy | CompkSoft says:

February 24, 2014 at 10:51 am

[…] https://blog.ofanitguy.com/2014/02/11/ls2013-html-enhancing-table-control-with-column-sort-and-filter… […]

LS2013 HTML – Enhancing Table Control with Column Sort and Filters | Blog of an IT Guy | CompkSoft says:

February 12, 2014 at 10:47 am

[…] https://blog.ofanitguy.com/2014/02/11/ls2013-html-enhancing-table-control-with-column-sort-and-filter… […]

joshbooker says:

February 12, 2014 at 6:38 am

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!

joshbooker says:

February 11, 2014 at 8:28 pm

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!

Blog of an IT Guy

LightSwitch, Kendo UI, ASP.NET MVC/API… and stuff