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:
- 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
- Three files get installed
Add all 3 files to your HTML Client default.htmitgLsEnhancedTable.cssLo-Dash.jsitgLsEnhancedTable.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
- Over in Properties
- Click on Choice List
- 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… 🙂
Maybe a bit late for Lightswitch and this component, but:
– If you add some buttons in the grid the component wont work… Just for your info
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!
I used to be very happy to find this web site. I wanted to thanks for your time fo this wonderful read !!
http://social.msdn.microsoft.com/Forums/vstudio/en-US/be425d6a-94d6-45f3-9efa-305e6980366d/itglsenhancedtable-filterproblem-one-screen-two-tabs?forum=lightswitch&prof=required
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?
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!
Update: for child tables, “appendQuery” should be used instead of “createQuery”
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?
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
correction: add this after
// also skip CustomControls
if (tableColumn.controlModel.id.indexOf(‘CustomControl’)>0){
return;
}
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..
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,
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??
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
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);
};
}
I have two tabs on one screen. Anyone knows how to solve this?
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?
Hi Dale, thank you for sharing this!!!
Any ideas how to fix the header row when user scrolls the table?
You mean to freeze the header row?
[…] LS2013 HTML – Enhancing Table Control with Multi Column Sort and Filters […]
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 https://blog.ofanitguy.com/2014/03/06/ls2013-html-enhancing-table-control-with-column-sort-and-filter… […]
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!
Nice! Thanks… and no worries… that’s what posting is all about… cooperation… and helping to create better “stuff”
Nice Dale! Cannot find the code on git though.
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?
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?
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.
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!
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.
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!
[…] LS2013 HTML – Enhancing Table Control with Multi Column Sort and Filters […]
Awesome Dale! Keep up the great work!
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?
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.
[…] https://blog.ofanitguy.com/2014/02/11/ls2013-html-enhancing-table-control-with-column-sort-and-filter… […]
[…] https://blog.ofanitguy.com/2014/02/11/ls2013-html-enhancing-table-control-with-column-sort-and-filter… […]
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!
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!