itgLsEnhancedTable - filterproblem (one screen, two tabs)

Question

Hi all,

I have tried to use the itgLsEnhancedTable with one database table (filter on query), one screen and two tabs, but I am struggling to get the filter working. I am able to filter the data, but when I hit the "Clear All" button, the result will none, not even "No Items" will appear. I am using the suggested code below to save the old query and filter (replaced the old one)  

    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 am able to use the filters and everything looks the same as the example. Anyone knows what's causing this problem? 

Answer 1

Something is wrong with the code.

If I maximize or minimize the window, the data will be visible 

If you clear a column set, the previous condition is still valid for the query. 

None of those errors are present with the old code, however the filter will not be included. 

Answer 2

Phero,

I'll try to work through this with you, but as I said in the blog comments, the code is working perfectly with the sample project from Dale's GitHub.  If the sample project is not working for you then perhaps we should debug that first.

What version of VS are you using?  Could you tell us more about your query?  Does it has a parameter filter, literal, preprocess query?

TIA,

Josh

Answer 3

Josh, 

I am using Microsoft Visual Studio Pro 2013, Version 12.0.30501.00 Update 2

I am using one table only (database) and one filter using literal. 

When I use the old code everything works besides from the excluded filter literal. 

The pasted function gives this back: 

function () {
                    return this.dataWorkspace.Northwind.Orders.filter("CustomerID eq 'VINET'").expand("Employee");
                }

Phero_

Answer 4

Phero,

I think I was able to repro your issue.  The problem happens when your table data is based on a query defined in the screen designer (edit query) rather than a query designed in the server project (Solution Explorer | Right-click on Table | Add Query) 

The easiest fix is to recreate your screen query in the server project like so:

1) In solution explorer, right-click on the table and then select 'Add Query'.  Design the same query with literal filter and save with a meaningful name.

2) In your screen design, select the query in the left pane. Then in the properties pane change 'Query Source' Property to your new query from step #1.

The reason for this is because of how LS builds the screen query with a literal filter.  It does so in the viewModel.js using the odata .filter method (same method being overridden by the enhanced table code).  This prevents the override from applying the query filter.  We could prolly change the code to handle this case, but creating queries on the server has benefits too so I wouldn't bother.  In contrast, the server query is built into the data service as a function so the literal filter doesn't use the odata .filter method on the client, Thus the override works great.

Here are snips from viewModel.js which illustrate the diff:

//screen query in viewModel.js
//notice odata .filter method is hardcoded in the screen

BrowseOrders: $defineScreen(BrowseOrders, [
    {
        name: "Orders", kind: "collection", elementType: lightSwitchApplication.Order,
        createQuery: function () {
            return this.dataWorkspace.Northwind.Orders.filter("substringof('A', CustomerID)").expand("Employee");
        }
    },

//server query having same filter in viewModel.js
//notice a odata method by the same name as your query - the filter logic happens on the server

BrowseOrders: $defineScreen(BrowseOrders, [
    {
        name: "Orders", kind: "collection", elementType: lightSwitchApplication.Order,
        createQuery: function () {
            return this.dataWorkspace.Northwind.OrdersContainingA().expand("Employee");
        }
    },

Let us know how it goes. 

HTH,

Josh

Answer 5

Josh, 

You solved it! Thanks a lot for your time! 

regards

Phero

Answer 6

Phero,

Happy to help.  Welcome to the LS community.  You'll find there are plenty of helpful folks around here.

The moral of this story is:

Always Use Server Queries rather than 'edit query' in screen designer.  Sever queries are reusable between screens and even reusable between clients.  If we never use 'edit query' then it's not necessary to drill in there when I'm trying to debug a filter sort or parameter issue.

For others reading this thread the referenced blog posts with related comments are here:

http://blog.ofanitguy.com/2014/03/06/ls2013-html-enhancing-table-control-with-column-sort-and-filters/

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

Have a great day!

Josh

Answer 7

Josh, 

I have one more problem. I am using two tabs, but if I click on "Clear All" on the second tab and then return to the first tab, I will see the same data as in second tab when I hit the "Clear All" button. 

I was thinking of using some logic to find which tab the user has been activated and then reQuery the table: 

var tab = $($.mobile.activePage
        .find(".msls-screen-tab-active"))
            .find(".ui-btn-text")
            .text();

if (tab == "Unchecked") {

        var table = screen.findContentItem("UncheckedTable").enhancedTable;
        if (table != null) {
            // Clear all the sorts and filters
            table.clearAll();

            // If we are not in batch mode, reQuery
            if (!table.getBatchMode()) table.reQuery();
        }
}

Could that solve the problem? 

Answer 8

Phero,

That would probably work.  I haven't tried this, but it appears all the button actions use the table var set to contentItem.enhancedTable.  Since every tab has it's own command bar, instead of working through the activeTab solution, I'd just add a new set of buttons for each tab.  These would have separate button _execute methods which refer to the respective table for that tab.

HTH,

Josh

Answer 9

Josh, 

There is a problem using two tabs. When I hitting the Clear All button on the second tab and then goes back and hitting the Clear All button on the first tab, I will get the same data as in the second tab. Hard to debug this or I lack the knowledge to understand why this happening. 

I have the same problem when I clearing a specific column filter. The first tab is showing the other tab data. 

Answer 10

You may have to add the query twice to your screen. Once for each table HTH, Josh

Answer 11

Josh, 

I have done that. 

myapp.TransactionsEnhancedTable.Checked_postRender = function (element, contentItem) {

  // Store our enhanced table as part of our contentItem so we can get at it later
  contentItem.enhancedTable = new itgLs.EnhancedTable({
      element: element,
      contentItem: contentItem
  });



};

myapp.TransactionsEnhancedTable.Unchecked_postRender = function (element, contentItem) {
    // Store our enhanced table as part of our contentItem so we can get at it later
    contentItem.enhancedTable = new itgLs.EnhancedTable({
        element: element,
        contentItem: contentItem
    });

};

Answer 12

What do you have for screen data(left pane). You may need to have your query in there twice. So they are filtered independently

Answer 13

Are you refering to the "Edit Additional Query Code"? on the query I added on the table? 

Answer 14

No I'm referring to 'Add Data Item' in screen designer. 

Your query should be listed in the left pane twice.  Say for example, your entity is Customers.  You should have both Customers and Customers1 in the left pane of screen designer.  Data source for Table1 is Customers while data source for Table2 is Customers1.

I don't have time right now to try this to see if it's your problem, but that's what it sounds like to me.

HTH,

Josh

Answer 15

Josh, 

I have two queries, one for unchecked and one for checked. The unchecked table is using the query for the unchecked and the checked table is using the query for the checked. I think this may be something else as the data is showing properly when switching the tabs forth and back, but when I starting hitting the "Clear" on column level or the "Clear All" button, the data on the first tab will be strange. From time to time I am also getting this error: Unable to get property 'enhancedTable' of undefined or null reference

Answer 16

Okay I understand you have two queries on the screen already. 

That's odd behavior.  Maybe a question for Dale.  I've not used it on a screen w multi-tabs\tables.

If I get around to testing that, I'll post back, but it won't be anytime soon.

Have a great day!

Answer 17

I haven't solved this issue yet and I was thinking of using two screens instead of two tabs.

I have tried to debug the reQuery function in the itgLsEnhancedTable.js file

function reQuery() {

            // Update our different OData strings
            updateFilterString();
            updateSortString();
    
            // Refresh the visual collection, resulting in the table refreshing
            properties.visualCollection.refresh();
       


        };

But I haven't found anything which I can relate to this error. I would be happy if anyone could solve this error.

I wonder if this is causing any trouble. When I have updated the data in the database, I want to update the screen, which seems to works from time to time, which also relates to the issues I have right now: 

myapp.TransactionTable.created = function (screen) {

    myapp.onsavechanges = function (e) {

        e.detail.promise = myapp.activeDataWorkspace.Transactions.saveChanges().then(function (e) {     
           screen.Transactions.refresh();       

        });

        e.detail.promise = myapp.activeDataWorkspace.Transactions1.saveChanges().then(function (e) {
            screen.Transactions1.refresh();      
        });
    };
  
};

Answer 18

Phero,

I havn't had time to test this with 2 tables on the screen, but I wonder if the problem is that the filter popup is shared by both tables thus the filter is being applied to both.  I would try created a second popup with the required contentItems and initializing the second table using the second popup.

// Store our enhanced table as part of our contentItem
contentItem.enhancedTable = new itgLs.EnhancedTable({
    element: element,
    contentItem: contentItem,
    filterPopupName: secondPopupName
    //filterPopupColumnName: blah,
    //etc
    //etc
    //see Dale's blog
});

HTH,

Josh

Answer 19

Josh,

What I really need to know right now, is how I could verify that the query used in the refresh function is the right one. I have been looking into the msls-2.5.0.js for answers, but I haven't found anything that I could use to get the query. I am able to verify that the table and datasource is the right one, by simply put a messagebox before the refresh is taking place. 

properties.visualCollection.refresh();

Answer 20

Phero,

The .refresh() method is built into msls.  The query is the one that we overwrite using the code in your original post.

The problem you are having, I believe is due to the fact that the filter popup has buttons which are hardcoded to affect one table but not the other. 

I emailed Dale and his solution was to identify the active tab and alter the code to find the respective contentItem (table on the active tab).  Here's his code:

myapp.BrowseOrders.SetColumnFilter_execute = function (screen) {
// Get our current tab
var tabName = lsWire.getActiveTabName(screen);
// Identify which table based on the tab name
var contentItemName = tabName == "OrderList1" ? "qryOrders1" : "qryOrders2";

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

// Call our set filter function for this column only
table.setColumnFilter();

// Close the popup
table.closeFilterPopup();
};

myapp.BrowseOrders.ClearColumnFilter_execute = function (screen) {
// Get our current tab
var tabName = lsWire.getActiveTabName(screen);

// Identify which table based on the tab name
var contentItemName = tabName == "OrderList1" ? "qryOrders1" : "qryOrders2";

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

// Call our clear filter function for this column only
table.clearColumnFilter();

// Close the popup
table.closeFilterPopup();
};

Now the problem becomes he's using a library of his called lswires that he's not made available yet to do things like

lsWire.getActiveTabName(screen);

So your options are these:

1) Create a function that gets the active tab name to do like he's done.

Or

2) Add a second popup with buttons hardcoded to affect the second table as I suggested above.

HTH,

Josh

Answer 21

I have allready solved that part. I think the code below should do the same: 

myapp.TransactionsEnhancedTable.ClearColumnFilter_execute = function (screen) {

    var tab = $($.mobile.activePage
         .find(".msls-screen-tab-active"))
             .find(".ui-btn-text")
             .text();

    if (tab == "Checked") {

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

        // Call our set filter function for this column only
        if (table != null) {
            table.clearColumnFilter();

            // Close the popup
            table.closeFilterPopup();
        }
    }

    if (tab == "Unchecked") {

        // Get our table

        var table = screen.findContentItem("Transactions1").enhancedTable;

        if (table != null) {
            // Call our set filter function for this column only
            table.clearColumnFilter();

            // Close the popup
            table.closeFilterPopup();
        }
    }


};

The Clear All button executing the following code depending which tab the user has been entering: 

myapp.TransactionsEnhancedTable.ClearAllChecked_Tap_execute = function (screen) {
    
    var table = screen.findContentItem("Transactions").enhancedTable;
    if (table != null) {
        // Clear all the sorts and filters
        table.clearAll();

        // If we are not in batch mode, reQuery
        if (!table.getBatchMode()) table.reQuery();
    }

};
myapp.TransactionsEnhancedTable.ClearAllUnchecked_Tap_execute = function (screen) {
    
    var table = screen.findContentItem("Transactions1").enhancedTable;
    if (table != null) {
        // Clear all the sorts and filters
        table.clearAll();

        // If we are not in batch mode, reQuery
        if (!table.getBatchMode()) table.reQuery();
    }

};

The screen is set to "editable" by the way. I am not sure if that could cause any problems. 

I have also tried to include auto-update after the data has been commited to the database using the following code: 

myapp.TransactionsEnhancedTable.created = function (screen) {

    myapp.onsavechanges = function (e) {

        e.detail.promise = myapp.activeDataWorkspace.TransactionsService.saveChanges().then(function (e) {

            screen.Transactions.refresh();
           

        });

        e.detail.promise = myapp.activeDataWorkspace.Transactions1Service.saveChanges().then(function (e) {

            screen.Transactions1.refresh();
           

        });

    };

}

I think I have read something about a setting in msls, which you could use to achieve something similiar, but I cant find it now. 

If I click on the Clear All button on the first tab - Works! The corresponding data will be loaded

If I click on the Clear All button on the second tab - Works! The corresponding data will be loaded

If I click on the Clear All button on the first tab for a second time I will get the same data as in tab 2. When this stage has been reached, you will not be able to get the correct data in the first tab. It doesn't matter what you do. 

Answer 22

Good day folks... hope you don't mind that I jump into the conversation.

There is no hard coded relationship between the filter popup and a particular table or query.

All the functions for the enhanced table work on a particular instance of a table.  This is why I save the instance into the contentItem.  Along with returning the instance back to the dev when its instantiated.

With that said.  The filtering does work on tables and queries.  So if you are trying to work off 1 query you will end up with the situation you are having. 

As I mentioned to Josh, you should have two queries.  One for each instance of the table control.  The methods in your screen are based on table instance.  So for your clear all, you need to call this off of the instance for that particular contentItem.  

Even without the new lsWires, I believe you can easily do this.  Just need to identify which table instance to call the methods off of.  Which in your case is tab based.  You can easily identify which tab is active and find the instance off the contentItem based on the tab.  In the code that Josh posted the only additional wrapper is really the one to get the active tab.

Let me know if you'd like me to work the code out or how I can be of assistance to get your project back rolling.

Forgot... you can see a working example using the above code here:  http://lswtable.samplebuild.com/htmlclient/

Answer 23

Hi Dale, 

Welcome aboard!

I don't think I am following you, because I am using two queries (Transactions, Transactions1) which in turn are using two datasources (pointing to one oracle database table). I looked at your example, but it's hard to see if it's working, because you have the same data in your two tabs. If you could turn on filters on both tabs, we might be able to see if it's really working; for instance only showing "shipvia eq 3" in tab1 and "shipvia eq 1" in tab2) and then give it a try; Click on Clear All1, Click on Clear All2 and then again Clear All1. Are you seeing anything in my code which is pointing out that I am using one query? I believe I am doing the same thing as you are telling me to do :)

I have been replacing the enhancedTable.js with the lswires.js instead, but I have the same problem, the first tab will showing wrong data, if the user clicked on the Clear All button on the second tab. 

I am still using the code below to get the original query to run with the filter (replaced the one in the lswires.js). I suspect that the VisualCollection pointer or the query isn't working as it should with two tabs. Overwritten somehow? 

   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 could now for sure confirm that LS is choosing the wrong query when the user has been clicking on the "Clear All" button on the second tab. I have discovered it by looking into the logfiles in Documents\IISExpress; choosing the wrong svc file along with the incorrect query. 

Answer 24

Hi Dale, 

I have solved the problem with the tab synchonization as well as the problem with the refresh issue after save. I have added some code into your enhancedTable.js to attach the filter correct, both in the init phase as well in the phase where the filters are removed. Please try to accomplish what I am trying to do. I am not sure that the current implementation is the best way of handle this kind of functionality. Is there a way of automatically refresh the visual collection somehow?