displaying all row on form when a tablefilterparm is applied

Classic Servoy
1

Hi There.

If I have a table ‘customers’ (say it has 1000) rows and there is a tablefilterparm set up on customers earlier on in processing that reduces the resultset to only 200 rows - is there a way to still show all 1000 rows in the form that is associated to that customers table? I need to do this WITHOUT removing the previously applied tablefilterparam.

I tried doing some custom SQL which populates a dataset correctly with all 1000 rows

var sql = "SELECT customer_id from customers";
master_list  = databaseManager.getDataSetByQuery(<DB>,sql,null, -1) ;

However when I load it back to the form:

success = controller.loadRecords(master_list);

only the 200 rows are displayed and Im assuming this is because of the previously set tablefilterparam is applied upon load. So 2 questions

  1. Just to confirm - the above behavior is expected right? tablefilterparms are applied when programatically loading a dataset with “controller.loadRecords()”?
  2. is there a way to accomplish this without using solutionmodel (i’ve seen a few posts referencing using solution model to do similar)?

I even tried duplicating the foundset, (thinking that a duplicated foundset would not be bound to previous tablefilterparams ) and then loading the dataset into the duplicate foundset for display but even that seems to still preserve the previous tablefilterparam. - Is this also expected behavior?

temp_foundset = foundset.duplicateFoundSet();
result = temp_foundset.loadRecords(master_list);

is there a way to easily do this?
Kevin

2

Use foundset filters instead of table filters.

3

Hi David.

Unfortunately my code is a small part of the big picture and I cannot change (i.e. remove and convert to foundset filters) the previously applied tablefilterparams.

K.

4

You can remove and add table filters during a session. However, removing a filter situationally is probably not a good idea if the filters are part of a larger context.

Look at creating a view in your database and basing your form on the view.

5

Hi David.
How would a view in my database help me here?

Would I be correct to assume you mean to create a view something like " select * from customers " → and then attach the form to it. then the existing tablefilterparams on the ‘customers’ table would not apply to and I could have the complete 1000 recordset loaded into my foundset and form display?

If thats the way it works then that would be pretty neat. please let me know if this is what you were thinking?

There is an added complication that I hadn’t mentioned yet… it was part2 of my issues. And that is that I need to access a ‘calculated’ value on a table that is related to the customers table. Its an aggregate sum of rows related to that customer in a related table. If I created a view to solve part1 like you suggested, how would I access this calculated value? can I create a relationship from a VIEW to another TABLE rather then TABLE to TABLE ?

Kevin

Kevin

6

kevin1379077093:
If thats the way it works then that would be pretty neat. please let me know if this is what you were thinking?

Yup.

kevin1379077093:
If I created a view to solve part1 like you suggested, how would I access this calculated value? can I create a relationship from a VIEW to another TABLE rather then TABLE to TABLE ?

Don’t think you can create relations on views. Calculations maybe. The view SQL can munge data together however you want though so I’d do it all right there.