How to create an advanced search/filter using Google App Maker's Query Script?

Question

I'm making an app with an advanced search feature which can help users filter data from dropdowns and textboxes (Dropdown to choose column and clause, Textbox for entering search parameter) like this one:

Advanced Search page sample:

I tried to bind the Column's name dropdown to @datasource.query.parameters.Parameter and changed the Query part of the datasource like this:

Datasource's Query Script and Parameters:

However, I keep getting errors like:

Parameter 'Column' is used in 'where' clause but not defined in property 'parameters'

Could you please tell me how can I resolve this problem?

Markus Malessa Markus Malessa · Accepted Answer · 2019-01-28T15:07:01

You literally have to construct your 'Where' clause in this case and then set the parameter of the where clause equal to your parameters. So lets say your first set of parameters is Column1: Name, Query1: contains, Parameter1: John, then your datasource needs to have the following parameters Column1, Query1, and Parameter1 and your bindings on your dropdown1, dropdown2, and textbox1 should be:

@datasource.query.parameters.Column1
@datasource.query.parameters.Query1
@datasource.query.parameters.Parameter1

respectively.

Then your query script needs to be as follows:

if (query.parameters.Field1 === null || query.parameters.Query1 === null) {
  throw new app.ManagedError('Cannot complete query without Parameters!');
}

switch (app.metadata.models.MaintenanceManagement.fields[query.parameters.Field1].type) {
  case 'Number':
    query.parameters.Parameter1 = Number(query.parameters.Parameter1);
    break;
  case 'Date':
    query.parameters.Parameter1 = new Date(query.parameters.Parameter1);
    break;
  case 'Boolean':
    if (query.parameters.Parameter1 === 'True' || query.parameters.Parameter1 === 'true') {
      query.parameters.Parameter1 = true;
    } else {
      query.parameters.Parameter1 = false;
    }
    break;
  default:
    query.parameters.Parameter1 = query.parameters.Parameter1;
}
query.where = query.parameters.Column1 + " " + query.parameters.Query1 + "? :Parameter1";
    return query.run();

So your where statement essentially becomes a string that reads 'Name contains? :Parameter1' (i.e. John) that then becomes your query. Hope this makes sense, feel free to ask follow up questions.

How to create an advanced search/filter using Google App Maker's Query Script?

1 Answers