loadRecords vs addFoundSetFilterParam

Classic Servoy
1

I recently was trying to filter a contacts table (containing employees & contacts) and the 2 methods I came up with were the following:

foundset.loadRecords('SELECT contact.contact_id FROM contact WHERE type_id = 2');

and

foundset.addFoundSetFilterParam('type_id','=','2');
foundset.loadAllRecords();

On the surface they both seem to work and I was wondering if there was a right and wrong use. I also found addTableFilterParam worked also.

2

All of the above work fine but it depends on what you want to achieve.
If you need to set a permanent filter use addTableFilterParam, once the filter is in place it will be used everywhere (it will be appended to ANY query sent to the DB), every foundset created after the filter setup will be filtered.
If you need to apply a filter but just for the current foundset use addFoundSetFilterParam, any future search on that foundset will go trough the filter.
If you use loadRecords(query,args) keep in mind that any future search on that foundset will not honor the intial where condition (ex: if the user, after the intial load, searches all contacts starting with “A” he will also find contacts with type_id != 2).

As a side note: if you use loadRecords use the following syntax to take advantage of the prepared statements (faster and easier to read):

foundset.loadRecords('SELECT contact.contact_id FROM contact WHERE type_id = ?',[2]);
3

I found addFoundSetFilterParam does not work with a related foundset. If I want to filter the related foundet by “type_id” what function do I use?

Example: Table of communications and I want to have a tab that only shows notes for the related contact.

4

For that you have to create a relation from communication.contact_id to notes.contact_id and use that relation to add the tab.

5

edited: I have done this (refer attachment) it seems to work. Any problems with doing it this way?

6

Hi James,

You can use an extra (global) field in the join that links to the type. Just by setting the global you will filter the relationship instantly.

Hope this helps.

7

Hi James,

That should work.