Foundset filter on form

Questions and answers for designing and implementing forms in Servoy

Foundset filter on form

Postby steve1376656734 » Thu Jun 20, 2019 12:51 pm

Hi,

I want to automatically apply a foundset filter whenever a form is used so that only a subset of the records are displayed, to achieve this I have written the following code:

Code: Select all
   var filterName = controller.getName();

   var params = foundset.getFoundSetFilterParams(filterName);
   if (params.length == 0) {
      //apply filter so only issues display
      /** @type {QBSelect<db:/dbname/case_actions>} */
      var query = datasources.db.dbname.case_actions.createSelect();

      //Filter on case action type for only stage 1,2,3 issues
      /** @type {Array<String>} */
      var caseActionTypeId = [];
      caseActionTypeId.push(scopes.caseActions.stage1IssueTypeId);
      caseActionTypeId.push(scopes.caseActions.stage2IssueTypeId);
      caseActionTypeId.push(scopes.caseActions.stage3IssueTypeId);

      query.where.add(query.columns.case_action_type_id.isin(caseActionTypeId));

      foundset.addFoundSetFilterParam(query, filterName);
   }


The problem I having is I cannot find the right place to apply this filter because the form is being loaded on to a tab using a relation. I have used the debugger to see what is happening in each of the following:

  • In the onLoad event of the form
    The method is called when the form is loaded and the filter is added to the foundset. Unfortunately the records are not filtered and I get all the records in the relation
  • In the onShow event of the form
    The method is called when the form is loaded and the filter is added to the foundset. When the form is first shown the records are not filtered but if I hide the form and reshow it then they do get filtered (we are making progress!)
  • In the onRecordSelection event of the form
    The method is called when the form is loaded and the filter is added to the foundset. The records are all filtered and everything is as I want it to be BUT this is very inefficient as I am calling the method every time a record is selected and performing an unnecessary check
I think my problem stems from the fact that the onLoad and first onShow method get called before the form's foundset is changed to be the related foundset and so the filter gets lost as it has been applied to the wrong foundset. So my question is where should I apply this filter for the most efficient behaviour when using the form with a relation?

TIA
Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: Foundset filter on form

Postby steve1376656734 » Thu Jun 20, 2019 1:23 pm

OK - the phrase RTFM comes to mind.

Having taken a bit of time to carefully read the documentation instead of skim reading it I spotted the very important piece of information I had missed:

Code: Select all
Use clear() or loadAllRecords() to make the filter effective.

Adding that line of code after the filter is added to the foundset makes all the difference in the onShow method and it now works for the first show as well as all subsequent ones. However it still doesn't seem to affect the onLoad so I'm not sure what is happening there.

Logically the onLoad is the place to add the filter as you should only really need to do it once but I'm happy with what I have for now.
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: Foundset filter on form

Postby mboegem » Thu Jun 20, 2019 2:03 pm

Hi Steve,

steve1376656734 wrote:OK - the phrase RTFM comes to mind.

:lol: :lol:

Yes, clear() / loadAllRecords() is indeed the missing part...

steve1376656734 wrote:Adding that line of code after the filter is added to the foundset makes all the difference in the onShow method and it now works for the first show as well as all subsequent ones. However it still doesn't seem to affect the onLoad so I'm not sure what is happening there.

Logically the onLoad is the place to add the filter as you should only really need to do it once but I'm happy with what I have for now.


The onLoad might be a bit early to load records.
Referring to the bits of documentation about onLoad, I find:
Code: Select all
The method that is triggered when a form is loaded/reloaded from the repository; used to alter elements, set globals, hide toolbars, etc;

Loading records doesn't come to my mind when I read this, although not impossible, I have seen issues when doing that here. (mainly to do with the order in which different events on different forms are executed on the (obvious) initial load)

As the onShow will be triggered on each show of the form, you are much more flexible to change filters.
On the other hand: if you don't need that you can use the firstShow argument to trigger code only the first time a form is displayed
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Foundset filter on form

Postby steve1376656734 » Thu Jun 20, 2019 6:21 pm

Thanks Marc - good insights and shows I need to look deeper in to the docs. :D

Unfortunately from some more extensive testing I have found that the only place where I can put the filter and it reliably work is in the onRecordSelection :(

If I put it in the onShow, it works when the form is displayed but as soon as I change records in the parent table the filter is lost. If I then switch to another tab and back to the one containing my form the filter is re-applied. I suspect that the fact I am using a relation means that the form foundset is being replaced each time the parent record changes.

Something to have a chat with Servoy about over a beer at ServoyWorld I think.

Thanks
Steve
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: Foundset filter on form

Postby mboegem » Thu Jun 20, 2019 7:32 pm

Unfortunately from some more extensive testing I have found that the only place where I can put the filter and it reliably work is in the onRecordSelection :(


There's definitely something wrong in the design.
If you need to do this at onRecordSelection, it just means that you are constantly re-applying the foundsetFilter.
You might just as well do a find/search with the same parameters and will have the same effect.

If I put it in the onShow, it works when the form is displayed but as soon as I change records in the parent table the filter is lost

Does a change of records in the parent table mean that scopes.caseActions.stage1IssueTypeId/stage2IssueTypeId/stage3IssueTypeId get different parameters?

My guess is that this is the case, which is why you need to re-apply.
Bear in mind that the 3 caseActions variables will become constant values.
Applying this means that you just set a filter query like this:
Code: Select all
SELECT case_action_id FROM case_actions WHERE case_action_type_id IN (32, 45, 78)

Changing the variables to values 33,46,79 won't have any effect, you will still filter on 32,45,78

The query you should be using in this case is something like this:
Code: Select all
SELECT case_action_id FROM case_actions WHERE case_action_type_id IN (
     SELECT case_action_type_id FROM case_action_types
     WHERE <YOUR WHERE CLAUSE THAT CURRENTLY DETERMINES WHAT VALUES TO ASSIGN TO stage1IssueTypeId/stage2IssueTypeId/stage3IssueTypeId>
)


If this sub select can not return the result in one query you can make this a UNION select as well, like:
Code: Select all
SELECT case_action_type_id FROM case_action_types WHERE <YOUR WHERE CLAUSE FOR stage1IssueTypeId>
UNION
SELECT case_action_type_id FROM case_action_types WHERE <YOUR WHERE CLAUSE FOR stage2IssueTypeId>
UNION
SELECT case_action_type_id FROM case_action_types WHERE <YOUR WHERE CLAUSE FOR stage3IssueTypeId>


Hope this helps
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests