svySearch on Calculated Field

[Louis.Winter]

Does anyone know if there is a way to customize the svySearch module so it can do wildcard searches against a calculated field?

We have users that want the values of child records rolled up and displayed in a grid column as a comma separated list on the parent, and then be able to search the list. The real value on the child records is numeric with a valuelist for the display values so if I add the related child field it has to be searched for by the exact number, not the display value. And If I add substitute values, the user still has to enter the display text exactly vs entering only a portion of the text.

Example, the parent record may be "ABC Store" and the child records have a few items (no more than 10-15). The child records have numbers that indicate the product (1=Coffee, 2=Tea, 3= Soda, 4=Milk, etc). The parent record would display "Tea, Soda, Milk, etc" in one of the grid columns. The user wants to be able to search for "Tea" as if the calculated field is an actual DB value.

Any direction would be greatly appreciated.

Louis

[paronne]

Hi Louis,

the svySearch indeed requires an exact match for substitutions.
In the scenario you describe, an option would be to get the query generated by the svySearch ( var q1 = simpleSearch.getQuery() ) for standard columns and then combine it with an ad hoc query where you define the matching conditions.
However having the numeric "realValues" as a comma separated list is a further complication.

Below an example demonstrating how can combine svySearch query with an ad hoc query to search on the Order Status calculation for sample solution.

Code: Select all

function search(text) {
   _super.search(text)

   var q1 = foundset.getQuery();
   var q2;
   if (text && "new".match(text.toLowerCase())) {
      q2 = datasources.db.example_data.orders.createSelect();
      q2.result.addPk();
      q2.where.add(q2.columns.requireddate.isNull);
   } else if (text && "planned".match(text.toLowerCase())) {

      q2 = datasources.db.example_data.orders.createSelect();
      q2.result.addPk();
      q2.where.add(q2.and.add(q2.columns.requireddate.not.isNull).add(q2.columns.shippeddate.isNull));
   } else if (text && "completed".match(text.toLowerCase())) {

      q2 = datasources.db.example_data.orders.createSelect();
      q2.result.addPk();
      q2.where.add(q2.columns.requireddate.not.isNull).add(q2.columns.shippeddate.not.isNull);
   }
   
   if (q2) {
      var query = datasources.db.example_data.orders.createSelect();
      query.where.add(query.or.add(query.columns.orderid.isin(q1)).add(query.columns.orderid.isin(q2)))
      foundset.loadRecords(query)
   }
}

( node code snippet above is not feature complete and not optimized for perfomances, is just a possible demonstration on how query from svySearch can be used. )

Currently not so nice to add OR conditions to an existing query. Since Servoy 2023.3 Query Builder supports named conditions, an option which can be exploited to allow customization of the generated Query from svySearch.

Regards,
Paolo

[Louis.Winter]

Thanks Paolo, this gave me something to play around with. I ended up going a different direction but I can see your example coming in handy in the future.

We have a generic lookup table in the database that is used for other parameter/setup information (but not these descriptions). I added some logic to the solution load function to load the valuelist data into the lookup table. The only thing using these records is this search but there was little overhead and the table keys work well for this. I created a relation from the child records to these new lookup records and then simply added the related field (parent.parent_to_child.child_to_description) as a search provider.

BTW, I first tried creating an in memory table which would have worked better, but I received cross database errors from the search.

Louis