Set Filter that Applies to Search with svyPopupFilter?

Find out how to get things done with Servoy. Post how YOU get things done with Servoy

Set Filter that Applies to Search with svyPopupFilter?

Postby john1598360627 » Wed Mar 01, 2023 3:42 am

I setup a Filter Form with some basic Button Groups.

filter_popup.png
filter_popup.png (6.61 KiB) Viewed 3108 times


onDataChange of these buttons, I want to apply a filter to the related grid.

I want this filter to be persistent. Such that it also applies to when you search something up.

MY SETUP
I'm using the 'toolbarFilter' from svyPopupFilter. This is connected to the grid & foundset.

I have a search bar setup that runs the toolbar's search.

Code: Select all
function search( text ) {
   
   // combine the toolbar filter with a free searchbox
   
   toolbarFilter.setSearchText( text );
   toolbarFilter.search();
}


I know all too well about the onSearchCommand.... but how do I created or attach a custom filter to the toolbar? Or do I just load the foundset directly like the onSearchCommand is doing?

For example, I have this sort query.
Code: Select all
/**
* @public
*
* @param {QBSelect<db:/arm_data/orders>} Query
*
* @return {QBSelect<db:/arm_data/orders>}
*
* @properties={typeid:24,uuid:"4984E184-F7B4-4D41-B04D-29F36E122F30"}
*/
function filter_OrderRank( Query ) {
   

   
   Query.sort.add(
      Query.case
         .when( Query.columns.order_status.eq( "APPROVED") ).then( 1 )
         .when( Query.columns.order_status.eq( "PENDING") ).then( 2 )
         .when( Query.columns.order_status.eq( "WEB") ).then( 3 )
         .when( Query.columns.order_status.eq( "TRANSREQ") ).then( 4 )
         .when( Query.columns.order_status.eq( "OPEN") ).then( 5 )
         .when( Query.columns.order_status.eq( "POSTED") ).then( 6 )
         .when( Query.columns.order_status.eq( "RECEIVED") ).then( 7 )
         .else( 8 )
   )
   
   
   return Query
}


And also this basic switch for filtering the value of a column.
Code: Select all
/**
* @public
*
* @param {String} Status
*
* @param {QBSelect<db:/arm_data/orders>} Query
*
*
* @return {QBSelect<db:/arm_data/orders>}
*
* @properties={typeid:24,uuid:"5A71D59D-C4F5-45D5-8386-F9A96697EA16"}
*/
function filter_ArchivalStatus( Status, Query ) {
   
   switch( Status )
   {
      case OrdersFilterOptions.archive_current:
         Query.where.add( Query.columns.order_flag_archive.eq( 0 ) )
      break;
      
      case OrdersFilterOptions.archive_archive:
         Query.where.add( Query.columns.order_flag_archive.eq( 1 ) )
      break;
      
      default:
         
   }
   
   return Query
}



HOW TO?
Is it okay if I just pass the resulting query over to the onSearchCommand?

Code: Select all
/**
* @param query
* @param fs
*
* @protected
* @properties={typeid:24,uuid:"BF2BE7B8-143E-45F1-B2BD-243098307C02"}
*/
function onSearchCommand( query, fs) {
   
   fs.loadRecords( query )
   
}


Or is there another way of setting up a filter within the toolbar itself?
john1598360627
 
Posts: 169
Joined: Tue Aug 25, 2020 3:03 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby robert.edelmann » Wed Mar 01, 2023 2:40 pm

If you use the toolbarfilter, you can define filter-items, that don't match fields in your grid:
Code: Select all
toolbarFilter.addFilter('time booked?','zeiterfassung_gebucht', scopes.svyToolbarFilter.FILTER_TYPES.CHECK);
toolbarFilter.addFilter('remove filter on project','kein_projektfilter', scopes.svyToolbarFilter.FILTER_TYPES.CHECK);
....
toolbarFilter.setOnFilterApplyQueryCondition(onFilterQueryCondition);

toolbarFilter is the filter-component, accessible via a form-variable, which handles the toolbarfilter.
The line toolbarFilter.setOnFilterApplyQueryCondition(onFilterQueryCondition) tells the toolbarfilter to use a function to modify the filter-query, this function is called for every option chosen in your toolbarfilter, and you can do all sorts of funny things:

Code: Select all
/**
* @param {QBSelect<db:/bauprocheck/tagebuch>} query
* @param dataprovider
* @param operator
* @param {Array<String>} values
* @param filter
* @return {Boolean}
*
* @properties={typeid:24,uuid:"D386D47A-6942-4825-9525-A3C105B77AEC"}
*/
function onFilterQueryCondition(query, dataprovider, operator, values, filter) {
   if (!values || !values.length) return true;
   var or, iFilter, filterValue;
   switch (dataprovider) {
   case "kein_projektfilter":
      if (!originalProjektFilter && scopes.bpcFramework.projektFilterId) {
         originalProjektFilter = scopes.bpcFramework.projektFilterId;
      }
      if (operator == 'eq') {
         scopes.bpcFramework.projektFilterId = null;
         scopes.bpcFramework.projektFilterSetzen();
         forms['bpc_main'].controller.recreateUI();
      }
      return false;
      break;
   case "zeiterfassung_gebucht":
      or = query.or;
      /** @type {QBSelect<db:/bauprocheck/zeiterfassung>}  */
      var qZeiten = datasources.db.bauprocheck.zeiterfassung.createSelect();
      qZeiten.result.add(qZeiten.columns.tagebuch_id);
      qZeiten.where.add(qZeiten.columns.tagebuch_id.not.isNull);
      if (operator == 'eq') {
         or.add(query.columns.tagebuch_id.isin(qZeiten));   
      } else {
         suchQuery = "SELECT tagebuch_id \
            FROM tagebuch\
            WHERE EXISTS(SELECT * FROM zeiterfassung WHERE tagebuch.tagebuch_id = zeiterfassung.tagebuch_id)"
         or.add(query.columns.tagebuch_id.not.isin(suchQuery, null));
      }
      query.where.add(or);
      return false;
      break;
   default:
      break;
   }
   return true;
}


in this case, the filter "kein_projektfilter" removes a foundset-filter by removing the scope-variable and running a function, the filter "zeiterfassung_gebucht" fires a custom query to filter matching records in other tables.

If you want activate toolbarfilters programatically you can use something like this:
Code: Select all
toolbarFilter.setFilterValue(toolbarFilter.getFilter('status'),[0],'^||eq');
if (uebergreifend) {
   toolbarFilter.setFilterValue(toolbarFilter.getFilter('kein_projektfilter'),[1],'eq');
   }
}
foundset.sort('datum_erledigt_soll desc, tagebuch_to_vz_ereignisprio.sortierung asc');
mit freundlichen Grüßen
Robert Stefan Edelmann
User avatar
robert.edelmann
 
Posts: 91
Joined: Wed Aug 14, 2013 6:12 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby john1598360627 » Thu Mar 02, 2023 2:30 am

robert.edelmann wrote:If you use the toolbarfilter, you can define filter-items, that don't match fields in your grid:
Code: Select all
toolbarFilter.addFilter('time booked?','zeiterfassung_gebucht', scopes.svyToolbarFilter.FILTER_TYPES.CHECK);
toolbarFilter.addFilter('remove filter on project','kein_projektfilter', scopes.svyToolbarFilter.FILTER_TYPES.CHECK);
....
toolbarFilter.setOnFilterApplyQueryCondition(onFilterQueryCondition);

toolbarFilter is the filter-component, accessible via a form-variable, which handles the toolbarfilter.
The line toolbarFilter.setOnFilterApplyQueryCondition(onFilterQueryCondition) tells the toolbarfilter to use a function to modify the filter-query, this function is called for every option chosen in your toolbarfilter, and you can do all sorts of funny things:

Code: Select all
/**
* @param {QBSelect<db:/bauprocheck/tagebuch>} query
* @param dataprovider
* @param operator
* @param {Array<String>} values
* @param filter
* @return {Boolean}
*
* @properties={typeid:24,uuid:"D386D47A-6942-4825-9525-A3C105B77AEC"}
*/
function onFilterQueryCondition(query, dataprovider, operator, values, filter) {
   if (!values || !values.length) return true;
   var or, iFilter, filterValue;
   switch (dataprovider) {
   case "kein_projektfilter":
      if (!originalProjektFilter && scopes.bpcFramework.projektFilterId) {
         originalProjektFilter = scopes.bpcFramework.projektFilterId;
      }
      if (operator == 'eq') {
         scopes.bpcFramework.projektFilterId = null;
         scopes.bpcFramework.projektFilterSetzen();
         forms['bpc_main'].controller.recreateUI();
      }
      return false;
      break;
   case "zeiterfassung_gebucht":
      or = query.or;
      /** @type {QBSelect<db:/bauprocheck/zeiterfassung>}  */
      var qZeiten = datasources.db.bauprocheck.zeiterfassung.createSelect();
      qZeiten.result.add(qZeiten.columns.tagebuch_id);
      qZeiten.where.add(qZeiten.columns.tagebuch_id.not.isNull);
      if (operator == 'eq') {
         or.add(query.columns.tagebuch_id.isin(qZeiten));   
      } else {
         suchQuery = "SELECT tagebuch_id \
            FROM tagebuch\
            WHERE EXISTS(SELECT * FROM zeiterfassung WHERE tagebuch.tagebuch_id = zeiterfassung.tagebuch_id)"
         or.add(query.columns.tagebuch_id.not.isin(suchQuery, null));
      }
      query.where.add(or);
      return false;
      break;
   default:
      break;
   }
   return true;
}


in this case, the filter "kein_projektfilter" removes a foundset-filter by removing the scope-variable and running a function, the filter "zeiterfassung_gebucht" fires a custom query to filter matching records in other tables.

If you want activate toolbarfilters programatically you can use something like this:
Code: Select all
toolbarFilter.setFilterValue(toolbarFilter.getFilter('status'),[0],'^||eq');
if (uebergreifend) {
   toolbarFilter.setFilterValue(toolbarFilter.getFilter('kein_projektfilter'),[1],'eq');
   }
}
foundset.sort('datum_erledigt_soll desc, tagebuch_to_vz_ereignisprio.sortierung asc');

Thanks Robert for the reply!


That example of yours is quite complex! But if I'm understanding correctly you are adding to the query based upon the switch statement correct? Where does that query end up? Does the toolbar automatically load the query at the end?




Hmmm, I suppose I'm fundamentally confused on how the toolbarFilter is meant to be used.

In my case, I want to do something similar to your example but more simplified. I want to apply multiple custom queries at once. Some are filtering, others sorting.

If I'm understanding correctly, the toolbarFilter is meant to be used like this?

Code: Select all
toolbarFilter.addFilter( FilterName, ColumnName, FilterType )

toolbarFilter.setFilterValue( FilterName, Value, Operator )


So this creates a filter onto a column, then you can just set the value, and I assume the corresponding query operator?

Hmmm, but what if I want to apply multiple values? Do I 'addFilter' and 'setFilterValue' multiple times?

I should mention too. I'm not using the specific UI elements that Servoy provided for the toolbarFilter, atleast not right now. Like the Custom Lists and such. I want to go for a more simpler linear approach where I have that Filter Form I create with buttons, and the user clicks stuff on that form, which in turn applies and changes filters depending on what was clicked. Hence why I want to setup the filtering manually.



And then comes the case of Sorting. I noticed in your case you're just calling the foundset.sort. Am I supposed to use the foundset sort only? Can I make a query that sorts and load that into the foundset instead or will that break the toolbarFilter? Like, can I use that 'onFilterQueryCondition' to load a query that sorts for every time a filter is applied?
john1598360627
 
Posts: 169
Joined: Tue Aug 25, 2020 3:03 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby robert.edelmann » Thu Mar 02, 2023 4:42 am

The toolbarfilter offers a simple way to filter based on the rows in a grid, like a valuelist or a a date, which can be defined in the filtertype of the columns of your grid. This just works out of the box, no further modifications needed.

If you need more complexity, like if you need to filter on related data or on a calculation, you can extend the filter in the onFilterQueryCondition.

The function is called for each filter, and gets the current filter via the "query"-Parameter.

Normally you just add another filter with query.where.add(or) but you can do other things. If you return true, the filter is passed to the "internal" handler, if you return false, the next filter is processed.

The query is automatically loaded when you pick a filter, enabling you to drill down on your data.

The usual way to use the toolbarfilter is interactive, you create a control to pick the fields, a space to show and modify the active filters and the user starts filtering.

But since you can add filters via code which the user can change you have a really clever tool at hand.

I assume you could hide the visual parts by setting them as visible=false since you can also control the filter via code. This enables you to "add" the filter later quickly if your users want to filter manually.

I have an example for a demonstration for the german-speaking usergroup here https://github.com/RobertEdelmann1974/dSUG_2022_06, you can see how to build a simple filter. (It's in german, no i18n. Sorry)

I use toolbarFilter.addFilter( FilterName, ColumnName, FilterType ) once per form in onLoad() to announce which fields / values you want to add. You only need this if you want to use special filters, all columns in the ngGrid with a filterType set should be recoginzed an can be used.

You use toolbarFilter.setFilterValue( FilterName, Value, Operator ) in you onAction-Method of your Filter-Buttons. "FilterName" is the datasource of the row if you don't change anything.

You can do multiple setFilterValue(), but you can't chain them.

If you want to sort your data, I'm not sure if you can use a combined query, you could try to modify the "query" in onFilterQueryCondition() via query.sort.add(...), I'm not sure if the order will be retained, you should test that.
mit freundlichen Grüßen
Robert Stefan Edelmann
User avatar
robert.edelmann
 
Posts: 91
Joined: Wed Aug 14, 2013 6:12 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby john1598360627 » Fri Mar 03, 2023 1:51 am

robert.edelmann wrote:The toolbarfilter offers a simple way to filter based on the rows in a grid, like a valuelist or a a date, which can be defined in the filtertype of the columns of your grid. This just works out of the box, no further modifications needed.

If you need more complexity, like if you need to filter on related data or on a calculation, you can extend the filter in the onFilterQueryCondition.

The function is called for each filter, and gets the current filter via the "query"-Parameter.

Normally you just add another filter with query.where.add(or) but you can do other things. If you return true, the filter is passed to the "internal" handler, if you return false, the next filter is processed.

The query is automatically loaded when you pick a filter, enabling you to drill down on your data.

The usual way to use the toolbarfilter is interactive, you create a control to pick the fields, a space to show and modify the active filters and the user starts filtering.

But since you can add filters via code which the user can change you have a really clever tool at hand.

I assume you could hide the visual parts by setting them as visible=false since you can also control the filter via code. This enables you to "add" the filter later quickly if your users want to filter manually.

I have an example for a demonstration for the german-speaking usergroup here https://github.com/RobertEdelmann1974/dSUG_2022_06, you can see how to build a simple filter. (It's in german, no i18n. Sorry)

I use toolbarFilter.addFilter( FilterName, ColumnName, FilterType ) once per form in onLoad() to announce which fields / values you want to add. You only need this if you want to use special filters, all columns in the ngGrid with a filterType set should be recoginzed an can be used.

You use toolbarFilter.setFilterValue( FilterName, Value, Operator ) in you onAction-Method of your Filter-Buttons. "FilterName" is the datasource of the row if you don't change anything.

You can do multiple setFilterValue(), but you can't chain them.

If you want to sort your data, I'm not sure if you can use a combined query, you could try to modify the "query" in onFilterQueryCondition() via query.sort.add(...), I'm not sure if the order will be retained, you should test that.

Thank you very much Robert for the explanation! I'll see if I can parse the example you posted too.

I'll have to experiment with filtering more and see what more questions manifest. :mrgreen:
john1598360627
 
Posts: 169
Joined: Tue Aug 25, 2020 3:03 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby john1598360627 » Wed Mar 15, 2023 2:58 am

I tried to do a simple setup and it sadly didn't work and just errors out.


First of all, I'm using a central class that I extend all my grids from. For some reason I can't call the 'toolbarFilter' at all. I can create functions in the class to get around this, but it's odd I have to go through several loops to use it with form extension.

Then, getting around that hurdle I make a filter and try to set the value. Sadly it just errors out.


Code in Form Extension
Code: Select all
   
// archive
   
   add_toolbarFilter( 'archive', 'order_flag_archive', scopes.svyToolbarFilter.FILTER_TYPES.NUMBER )
   
   set_toolbarFilter( 'archive', [0], scopes.svyPopupFilter.OPERATOR.EQUALS )


Code in Class
Code: Select all
/**
* @param {String} Text
* @param {String} Provider
* @param {String} [Type]
*
* @protected
*
* @properties={typeid:24,uuid:"A09E378C-D905-43C7-AA9F-D375D958046B"}
*/
function add_toolbarFilter( Text, Provider, Type ) {
   
   toolbarFilter.addFilter( Text, Provider, Type )
   
}

/**
* @param {String} FilterName
* @param {Array} Value
* @param {String} Operatr
*
* @protected
*
* @properties={typeid:24,uuid:"4183AD40-079A-428C-9D9C-7B696171725A"}
*/
function set_toolbarFilter( FilterName, Value, Operatr ) {
   
   toolbarFilter.setFilterValue( toolbarFilter.getFilter( FilterName ), Value, Operatr )
   
}



Error Message
Code: Select all
ERROR com.servoy.j2db.util.Debug - TypeError: Cannot read property "text" from ...\svyPopupFilter\svyToolbarFilter.js#2430)
...\svyPopupFilter\svyToolbarFilter.js:2430


What is displayed.
Screenshot 2023-03-14 174722.png
Screenshot 2023-03-14 174722.png (2.85 KiB) Viewed 2767 times



The wiki has no example of how to set the value of a filter so I have no idea what I could be doing wrong and can only guess. I'm not sure what the proper procedure here even is. Very confused.
john1598360627
 
Posts: 169
Joined: Tue Aug 25, 2020 3:03 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby mboegem » Thu Mar 16, 2023 11:28 am

Hi John,

I'm doing a similar thing and it does work.

The difference is that instead of doing the lookup of the filterobject by name (which you do in set_toolbarFilter), I use the filterobject directly when setting the values.

So if you change the 'add_toolbarFilter' to return the filterobject, then you can pass the filterobject as first argument to the set_toolbarFilter function and you no longer have to do the lookup by name.

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

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby john1598360627 » Thu Mar 16, 2023 7:00 pm

mboegem wrote:Hi John,

I'm doing a similar thing and it does work.

The difference is that instead of doing the lookup of the filterobject by name (which you do in set_toolbarFilter), I use the filterobject directly when setting the values.

So if you change the 'add_toolbarFilter' to return the filterobject, then you can pass the filterobject as first argument to the set_toolbarFilter function and you no longer have to do the lookup by name.

Hope this helps

Hmmm... I may be misunderstanding your post, as there's multiple filter related object. Could you post an example?

What I'm assuming is, since there isn't a way to grab the toolbarFilter object from my 'class' form, making a function return the toolbarFilter object and then do the set and add from that?

Or are you talking about another filter object that isn't the toolbarFilter itself? Like some object of a filter added to the toolbar?
john1598360627
 
Posts: 169
Joined: Tue Aug 25, 2020 3:03 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby mboegem » Fri Mar 17, 2023 10:18 am

Hi John,

as per your code:
Code: Select all
   
// archive
   
   var filter = add_toolbarFilter( 'archive', 'order_flag_archive', scopes.svyToolbarFilter.FILTER_TYPES.NUMBER )
   
   set_toolbarFilter( filter, [0], scopes.svyPopupFilter.OPERATOR.EQUALS )


which requires changes to the other functions:
Code: Select all
/**
* @param {String} Text
* @param {String} Provider
* @param {String} [Type]
*
* @protected
*
* @properties={typeid:24,uuid:"A09E378C-D905-43C7-AA9F-D375D958046B"}
*/
function add_toolbarFilter( Text, Provider, Type ) {
   
   return toolbarFilter.addFilter( Text, Provider, Type )
   
}

/**
* @param {String} FilterName
* @param {Array} Value
* @param {String} Operatr
*
* @protected
*
* @properties={typeid:24,uuid:"4183AD40-079A-428C-9D9C-7B696171725A"}
*/
function set_toolbarFilter( Filter, Value, Operatr ) {
   
   toolbarFilter.setFilterValue( Filter, Value, Operatr )
   
}
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: Set Filter that Applies to Search with svyPopupFilter?

Postby john1598360627 » Tue Mar 21, 2023 2:07 am

mboegem wrote:Hi John,

as per your code:
Code: Select all
   
// archive
   
   var filter = add_toolbarFilter( 'archive', 'order_flag_archive', scopes.svyToolbarFilter.FILTER_TYPES.NUMBER )
   
   set_toolbarFilter( filter, [0], scopes.svyPopupFilter.OPERATOR.EQUALS )


which requires changes to the other functions:
Code: Select all
/**
* @param {String} Text
* @param {String} Provider
* @param {String} [Type]
*
* @protected
*
* @properties={typeid:24,uuid:"A09E378C-D905-43C7-AA9F-D375D958046B"}
*/
function add_toolbarFilter( Text, Provider, Type ) {
   
   return toolbarFilter.addFilter( Text, Provider, Type )
   
}

/**
* @param {String} FilterName
* @param {Array} Value
* @param {String} Operatr
*
* @protected
*
* @properties={typeid:24,uuid:"4183AD40-079A-428C-9D9C-7B696171725A"}
*/
function set_toolbarFilter( Filter, Value, Operatr ) {
   
   toolbarFilter.setFilterValue( Filter, Value, Operatr )
   
}


Thank you for replying!

I tried out that code and it sadly didn't work. It just errors out still.

Screenshot 2023-03-20 162645.png
Screenshot 2023-03-20 162645.png (18.38 KiB) Viewed 2600 times


Code: Select all
ERROR org.sablo.eventthread.EventDispatcher - [dispatch()] Exception happened in dispatch() java.lang.NoClassDefFoundError: com/servoy/extensions/plugins/scheduler/ExecuteScriptMethodJob
   at com.servoy.extensions.plugins.scheduler.SchedulerProvider.createJob(SchedulerProvider.java:368) ~[?:?]


Clearly something isn't setup right but I'm just not sure what!

I also get a warning when passing in the Filter as Servoy doesn't detect 'Filter' as a parameter type even though that's what the setValue is using.


Not sure what else I can do. I guess I'll have to make a sample solution and submit this problem to Servoy?
john1598360627
 
Posts: 169
Joined: Tue Aug 25, 2020 3:03 pm

Re: Set Filter that Applies to Search with svyPopupFilter?

Postby john1598360627 » Fri Mar 24, 2023 3:29 am

I just realized what was going on.

I was putting the initialization of these filters in the onLoad event of the form. Putting them into onShow fixed the issue!

Since the toolbar is initialized in the onLoad, I was mistakenly attempting to add filters before the toolbar existed. Oops!
john1598360627
 
Posts: 169
Joined: Tue Aug 25, 2020 3:03 pm


Return to How To

Who is online

Users browsing this forum: No registered users and 7 guests