Is there a limit to the number of table filter params that can be applied to an individual table?
On a specific search model I have I need up to 10 filters for a given table, each filtering it on different criteria.
My assumption is that the result table display will be everything else other than the filtered out criteria, but in my test rig at the moment, the filters are not being cumulatively applied. The last one seems to nullify the previous filter?
All uniquely named so I’m presuming my code is causing the problem.
It seems as if, when we add a second filter param using a custom sql query to provide values for an IN statement, the previous filter simply disappears, and we have confirmed this by reading all the filters before and after adding the second filter.
Any help would be much appreciated as this one has just killed our deployment stone dead!!! Our search routines depend on tableFilterParams.
It definetly looks as if, only the last filter applied persists.
I have made a sample solution to illustrate this
The form has three buttons, one to add this filter
var vSql = "select contact id from employees where name_first LIKE 'Y%'"
var vSuccess = databaseManager.addTableFilterParam('example_data','contacts','contact_id','IN',vSql,'filter1')
application.output('Filter 1 Success = '+vSuccess)
the second to add this filter
var vSql = "select contact id from employees where name_first LIKE 'T%'"
var vSuccess = databaseManager.addTableFilterParam('example_data','contacts','contact_id','IN',vSql,'filter2')
application.output('Filter 2 Success = '+vSuccess)
and a third to list the currently applied filters
var filters = databaseManager.getTableFilterParams('example_data')
filters.forEach(function(x){application.output(x[4])})
the console output from above is as follows respectivly
Jan Aleman:
So what do you want to do? If you want to apply multiple criteria why don’t you do that in one go?
That would be a significant re-write of the tool Jan. Right now we have a fire and we need to put it out as best we can - re-writing may be a longer term option.
Is this correct behaviour that the last filter applied removes the previous one?
We are exploring the option of getting the sqlParams in place before each filter should be loaded and (hopefully showing all those in place at any one time) and adding that as an ‘IN’ on the next filter build.
What would be your tack given our circumstance Jan. I thought this was exactly what tableFilterParams were designed for so your view will be valuable?
BTW we also use table filter params to hide ‘Marked Deleted Records’ at login (based on your suggestion some time ago Jan) so if these are being switched off we really have a problem running any other filters on those tables.
Kahuna:
Is this correct behaviour that the last filter applied removes the previous one?
Ian,
You can have multiple table filter params, even per table, but only you can have only 1 per data provider.
If you set a filter on a previously filtered data provider, the previous filter gets cleared.
This behaviour is there since we have table filters.
Kahuna:
Is this correct behaviour that the last filter applied removes the previous one?
Ian,
You can have multiple table filter params, even per table, but only you can have only 1 per data provider.
If you set a filter on a previously filtered data provider, the previous filter gets cleared.
This behaviour is there since we have table filters.
Rob
So, right now we are filtering on the pk of a specific table, using a where to filter to specific dataprovider in that table. When we add the table filter param we are using the PK as an IN sql criteria - which seems like the most sensible approach.
Like so:
var fabricSQL = "SELECT cd_id From fabric_condition where EXISTS (SELECT * FROM component_detail Where fabric_condition.cd_id = component_detail.cd_id AND cd_tag_no = "+ "'"+vQF_tag+"'"+")"
vFabricFilter = databaseManager.addTableFilterParam(databaseManager.getDataSourceServerName(controller.getDataSource()), 'fabric_condition', 'cd_id', 'IN', fabricSQL, 'fabric_tfpTag');
Are you saying that because we use the pk in several filters for the IN that they will overwrite each other?
In the above scenario we need to filter three tables, these tables have cd_id as the common columns. There are a number of filters to be applied and the only way to do that is with an IN on cd_id!
Can you suggest a way to do this without filter overwrites please Rob
Looks like you have your value parameter wrong. When using the “IN” operator with addTableFilterParam(…):
1- You need to pass in an array in the value slot.
2- If there is only one value in the array, it will fail. Need to use the “=” operator and a non-array value instead in this case.
Not sure things like this are in the documentation. It took us a long time to figure all of this out in Servoy 3 days and now have 100’s of lines of code to handle stacked filter rules and nice GUI screens to ensure we forgot about it as fast as possible
david:
Looks like you have your value parameter wrong. When using the “IN” operator with addTableFilterParam(…):
1- You need to pass in an array in the value slot.
2- If there is only one value in the array, it will fail. Need to use the “=” operator and a non-array value instead in this case.
Not sure things like this are in the documentation. It took us a long time to figure all of this out in Servoy 3 days and now have 100’s of lines of code to handle stacked filter rules and nice GUI screens to ensure we forgot about it as fast as possible
Thanks David - yes that code may not be as correct as it should be, I just grabbed an example from our Filtering form - its been through a lot of iterations, but I wanted Rob to see that we are using the IN operator, and had to use the PK/FK in three separate table several times over. And McCourt tells me I really shouldn’t be coding?? Young lads eh…
This has been a bit of a nightmare and McCourt has worked the last 24 hours almost straight (I just did the worrying and lost more hair) to get something usable up again. Very little on the forum about this, we couldn’t find any hard data on cumulative application of filters on tables, but got the impression that was how they worked. Be surprised if someone else is’nt being fooled by this right now - only through a specific testing operation we found it and then traced it back through the entire can of worms!
This massively reduces the usefulness of tableFilterParams IMHO.
rgansevles:
You can have multiple table filter params, even per table, but only you can have only 1 per data provider.
If you set a filter on a previously filtered data provider, the previous filter gets cleared.
This behaviour is there since we have table filters.
Rob
I totally agree that the rule that Rob states here is not entirely obvious. Maybe they could bring this out more in the sample code for Servoy 6. (Note: I think Rob that the ability to add multiple table filter params per table using different data providers was a later addition. Originally it was one filter param per table but I could be wrong.)
Further, knowing how to use the “IN” operator to spoof “layered” filter param applications is probably only known to those who have tried to implement this kind of security and have had to find out the hard way. Add in the fact that the “IN” operator doesn’t work under certain conditions (one value in the array) when you are assuming it should…
Then there are foundset filters you can add on top of table filters per session…
And you know that you can use the same command to apply a single filter to all the tables in a database right? But once you do that you can’t apply anymore filters to individual tables without overwriting the original filter…
And what if your requirement is to filter records by a range and the range is way too many values to be pushing through an “IN” operator? This is doable using 2 stored calculations representing your outside ranges and then applying filters to those calculations…
It’s all very very powerful but you may be looking at hair implants before you’re done
rgansevles:
You can have multiple table filter params, even per table, but only you can have only 1 per data provider.
If you set a filter on a previously filtered data provider, the previous filter gets cleared.
This behaviour is there since we have table filters.
Rob
I totally agree that the rule that Rob states here is not entirely obvious. Maybe they could bring this out more in the sample code for Servoy 6. (Note: I think Rob that the ability to add multiple table filter params per table using different data providers was a later addition. Originally it was one filter param per table but I could be wrong.)
Further, knowing how to use the “IN” operator to spoof “layered” filter param applications is probably only known to those who have tried to implement this kind of security and have had to find out the hard way. Add in the fact that the “IN” operator doesn’t work under certain conditions (one value in the array) when you are assuming it should…
Then there are foundset filters you can add on top of table filters per session…
And you know that you can use the same command to apply a single filter to all the tables in a database right? But once you do that you can’t apply anymore filters to individual tables without overwriting the original filter…
And what if your requirement is to filter records by a range and the range is way too many values to be pushing through an “IN” operator? This is doable using 2 stored calculations representing your outside ranges and then applying filters to those calculations…
It’s all very very powerful but you may be looking at hair implants before you’re done
Oooooh SHOOT… alcoholism looks very attractive at the moment!!!
I really do hope Servoy start documenting this stuff - it makes no sense to expect every one of their developers to learn all these shortcomings for themselves - after-all what is a RAD if its not taking the ‘long haul’ out of development?
Doing SaaS is hard, doing SaaS correctly is really hard, doing SaaS correctly and without spaghetti code is extremely hard, doing SaaS without spaghetti code and correctly with any non-Servoy tool – hire yourself a team of programmers…good programmers.
So maybe just the fact that you are actually doing a SaaS application is a testament to how far out ahead Servoy is compared to everything else in this department…
You should expect a learning curve and some big pain points when doing a SaaS application, even with Servoy. 24 hours to figure something out, get some answers and then retool is nothing. Just keep the beer fridge stocked
david:
Looks like you have your value parameter wrong. When using the “IN” operator with addTableFilterParam(…):
1- You need to pass in an array in the value slot.
2- If there is only one value in the array, it will fail. Need to use the “=” operator and a non-array value instead in this case.
No
You can use an array or you can use, as Ian is doing, a sql query.
No
If this was ever a limit, then this is fixed in Servoy 3.5 where I rewrote the sql generation.
I’ll have the samplecode updated to include some more scenario’s.
And in the new Programming Guide that will be released with Servoy 6 there will be a whole chapter on everything related to the datalayer, including this stuff. will make sure that a check is done to see if all things mentioned here are already covered and if not, will make sure they get added.
david:
Looks like you have your value parameter wrong. When using the “IN” operator with addTableFilterParam(…):
1- You need to pass in an array in the value slot.
2- If there is only one value in the array, it will fail. Need to use the “=” operator and a non-array value instead in this case.
No
You can use an array or you can use, as Ian is doing, a sql query.
No
If this was ever a limit, then this is fixed in Servoy 3.5 where I rewrote the sql generation.
Rob
Thanks for that clarity Rob.
However it would be really great if you could let us have your suggestions for the use of table filter params where all we have are the PK/FK’s on which to do a SQL join and therefore a filter?
Is it likely that the functionality of filter params will be updated anytime to have this ability?
We will change the table filter logic in Servoy 6, in stead of allowing only 1 table filter per table-column, we will apply both (or more) filters to the same dataprovider.
Since this is a behaviour change for existing solutions we cannot change this in released versions.
Until then you could have one filter that queries multiple tables:
var fabricSQL = "SELECT cd_id From fabric_condition where EXISTS (SELECT * FROM component_detail Where fabric_condition.cd_id = component_detail.cd_id AND cd_tag_no = "+ "'"+vQF_tag+"'"+")"+
" AND EXISTS (SELECT * FROM other_detail Where fabric_condition.cd_id = other_detail.cd_id AND other_tag_no = "+ "'"+vOT_tag+"'"+")"
vFabricFilter = databaseManager.addTableFilterParam(databaseManager.getDataSourceServerName(controller.getDataSource()), 'fabric_condition', 'cd_id', 'IN', fabricSQL, 'combined_tfpTag');
Or a bit shorter:
var fabricSQL = "SELECT cd_id From fabric_condition where cd_id in (SELECT cd_id FROM component_detail Where cd_tag_no = "+ "'"+vQF_tag+"'"+")"+
" AND cd_id in (SELECT cd_id from other_detail Where other_tag_no = "+ "'"+vOT_tag+"'"+")"
If you want to make this more dynamic you can also have a look at databaseManager.getTableFilterParams() to list the existing filters and modify the exiting ones, or keep the created ones in a global.
This is fantastic news Rob, the filters are a brilliant idea and replicate persistent queries we used in our last environment. Being able to build them cumulatively is significantly simpler than having to build the single SQL necessary to roll in filters applied all over the solution.
Shame it cant be rolled into the 5.x series of updates as it would be an embellishment rather than a limitation for current functionality and already deployed solutions. I’ll stand to be shot down, but if developers have already used the filter params they will be overwriting each other - and perhaps there could be a switch in the use of the filter that allows them to continue to overwrite the previous (set as default) but if set the filters would become cumulative?
In any case thanks for the support. it’s been a stressful week over this, and we’ve done a shed load of re-writing before our Client got to see the result on Monday, but nice to see the light at the end of the tunnel.
Kahuna:
Shame it cant be rolled into the 5.x series of updates as it would be an embellishment rather than a limitation for current functionality and already deployed solutions. I’ll stand to be shot down, but if developers have already used the filter params they will be overwriting each other
I’m using filters in many big solutions and they are working perfectly for me, an implementation change in 5.2 would break everything so: don’t stand Ian, I could be the one pulling the trigger
David is right, SaaS is very complex, even in Servoy. You need patience and some trial and error to make it right.
