Trying To Filter Only Rows That Meet Two Criteria

Question

I promise I have read through the Query information page, but obviously I am missing/misunderstanding something.

I have a Table that has the statuses for multiple departments (the fields are Strings). When a user loads that table I want App Maker to hide jobs that have been finished.

The way we categorize a job as finishes is when:

The Inventory Status = Complete and when the The Delivery Status = Delivered.

Both these conditions need to be met.

Example:

Inventory (Complete) + Delivery (Delivered) = hide

Inventory (In Progress) + Delivery (Delivered) = don't hide

Inventory (Complete) + Delivery (Scheduled) = don't hide

I tried the following, however it hides all the example listed above, not just the first one.

var datasource = app.datasources.SystemOrders;
var inventory = ['Complete'];
var delivery = ['Delivered'];
    datasource.query.filters.InventoryStatus._notIn = inventory;
    datasource.query.filters.DeliveryStatus._notIn = delivery;
    datasource.load();

I have also tried this:

var datasource = app.datasources.SystemOrders;
    datasource.query.filters.InventoryStatus._notIn = 'Complete';
    datasource.query.filters.DeliveryStatus._notIn = 'Delivered';
    datasource.load();

But I get this error:

"Type mismatch: Cannot set type String for property _notIn. Type List is expected. at SystemOrders.ToolBar.Button2.onClick:2:46"

Any help would be greatly appreciated.

Answer 1

Vasyl answer my question perfectly, but I wanted to add a few details in case anyone needs to do the same thing and aren't familiar with using the Datasource Query Builder.

All I did was click the Database I was using and then clicked the Datasources section at the top.

I clicked Add Datasource, named it a new name and pasted Vasyl's code into the Query Builder Expression box.

Two new boxes appear below it allowing me to enter the desired statuses that I wanted to filter out.

Lastly I went back to my Table and changed its datasource to my newly created datasource.

Since you are changing your datasource, if you have any extra code on there it may need to be updated to point to the new datasource.

Example: I had some buttons that would filter entries for the various departments.

So this:

widget.datasource.query.clearFilters();
var datasource = app.datasources.SystemOrders;
var statuses = ['Complete'];
     datasource.query.filters.WarehouseStatus._notIn = statuses;
     datasource.load();

had to change to this:

widget.datasource.query.clearFilters();
var datasource = app.datasources.SystemOrders_HideComplete;
var statuses = ['Complete'];
     datasource.query.filters.WarehouseStatus._notIn = statuses;
     datasource.load();

Answer 2

Filters are using AND operator. Please consider switching the Datasource Query Builder and applying the following query:

"InventoryStatus != :CompleteStatus OR DeliveryStatus != :DeliveredStatus"

Set CompleteStatus variable to Complete Set DeliveredStatus variable to Delivered

Explanation: Filter you want to apply is "NOT(InventoryStatus = Complete AND DeliveryStatus = Delivered)" which is equivalent to "InventoryStatus != Complete OR DeliveryStatus != Delivered".

Answer 3

You can use multiple run and then concatenate their results something like following

/**
 * Retrieves records for ActionItems datasource.
 * @param {RecordQuery} query - query object of the datasource;
 * @return {Array<ActionItems>} user's rating as an array.
 */
function getActionItemsForUser_(query) {
  var userRoles = app.getActiveUserRoles();

  query.filters.Owner._contains = Session.getActiveUser().getEmail();
  var ownerRecords = query.run();
  query.clearFilters();
  query.filters.AddedBy._contains = Session.getActiveUser().getEmail();
  var addedByRecords = query.run();
  return addedByRecords.concat(ownerRecords);
}