Filtering a custom sql grid in Genie

[dustinmiles]

I am attempting to filter a grid in Genie that uses custom sql.

From the documentation it didn't look like native filtering was built in to Genie grids.

I am attempting to do it by changing the custom sql property on the fly when the user checks a box. The custom sql property appears to change (when I query it with the alert it is correct) but the grid still shows the original data.

What am I doing wrong? Is there a better way to do this?

Code: Select all

if (this.checked === false) {
   applyProperty("RanGrid", "custom sql", "valid select statement here");
   getObj("RanGrid").grid.refresh();
   alert(getObj("RanGrid").pui.properties["custom sql"]);
}

if (this.checked === true) {
   applyProperty("RanGrid", "custom sql", "valid select statement here also");
   getObj("RanGrid").grid.refresh();
   alert(getObj("RanGrid").pui.properties["custom sql"]);
}

[dustinmiles]

I decided to try calling setFilter and removeFilter and they do execute and apply a filter. I had to remove the order by clause and use the order by property in the grid, as stated in the documentation.

However, I need to filter where two columns in the grid are not equal to each other. Is this possible? The filters seem only applicable to one column. Can I use another column in the calculation?

Also I would like to apply the filter at page load (even though the grid won't have data in it yet until the user makes a dropdown selection). I added a call to setFilter in the onload function, but it seems to have no effect.

[Scott Klement]

You cannot change the "custom sql" property from JavaScript. When you set "custom sql" in the Genie designer, it will remember what you set and will run it on the server (it does NOT send it back from the screen via JavaScript each time.) This is done for security purposes: If you could modify this to any SQL statement at run-time, then anyone could modify the JavaScript code on the fly (the JavaScript debugger that's built-in to the browser is all that's needed) could run any SQL statement they wanted at any time.

So we've restricted it so that you can only change the SQL statement at design-time. Users without designer access will not be able to change the statement.

However, what you CAN do is change a "-parameter value" from JavaScript when the display loads. So if you have an SQL statement with a WHERE clause, you could change the parameter value used in that where clause on-the-fly. This might allow you to filter your grid differently.

As for the grid's built-in filtering capabilities, the setFilter API, etc... these work on one column at a time. setFilter() is meant to mimic the ability that the user has when they right-click a column and type a filter string. There isn't a feature for comparing to another column, sorry.

Finally, if all else fails, you can write your own web service that the grid will read from. In this case, it doesn't run an SQL statement against the database directly, but instead calls your routine. You can write it any way you wish in that case, though it is of course a bit more work.

[dustinmiles]

Using a parameter wouldn't really work for me in this case, as I need to add an entire Having clause since I'm trying to compare two column values in the SQL statement itself.

Therefore filtering wouldn't really work either based on your answer.

I ended up solving it by creating another grid which was an exact duplicate of the original grid, except for the custom sql statement. One of them begins hidden. I load them both and when the user checks or un-checks the box, I toggle the visibility of the two grids. This seems to work well for my needs.