Dropdown Filter Iterating over Multiple Column Conditions on Table Component

App Building
, ,
4

Hey @MiguelOrtiz I appreciate your feedback! And yes the Default Filters offered in the table inspector is amazing and handles most cases. In this case, though, I needed a single dropdown filter that handled more than one column condition. Each option on the select dropdown component was checking a true/false condition on 3 different columns ( APPROVED, DENIED, and PENDING_APPROVAL ) else display 'All' rows if none of the previous conditions are true.

This was the solution I ended up using:

  1. I created a table to hold the values of the options on the select dropdown component (I called the select component approvalSelect):
CREATE OR REPLACE TABLE uap.approval_status_options(
  id int not null autoincrement start 1 increment 1
  , created timestampntz default sysdate()
  , status varchar(256)
  , primary key (id)
);
  1. I inserted the values to represent each filter option:
INSERT INTO uap.approval_status_options (status)
VALUES ('All'), ('Pending'), ('Approved'), ('Denied');
  1. Pulled the needed data into Retool as a Resource Query named approvalStatusList:
SELECT id, status FROM uap.approval_status_options;
  1. Created a transformer named filterHandler, where the trick was using this as the Data Source for the table:
const contractors = {{ formatDataAsArray(getContractors.data) }};

const filterOptions = {{ approvalStatusList.data.STATUS }};

const filterBy = {{ approvalSelect.value }};

const result = contractors.filter(contractor => {
  let accepted = true;

  if (accepted && filterBy) {
    if (filterBy === filterOptions[1]) {
      accepted = !contractor.APPROVED && !contractor.DENIED;
    } else if (filterBy === filterOptions[2]) {
      accepted = contractor.APPROVED;
    } else if (filterBy === filterOptions[3]) {
      accepted = contractor.DENIED;
    }
  }

  return accepted;
});

return result;
  1. From there, all I had to do was map my select component options to the data I inserted into the select statement from my approvalStatusList query, gave the mapped value a value of {{ item.STATUS }}, and the filter works with light speed! :heart_eyes:

If anyone else has had trouble doing the same on a single select filter, this works marvelously! :pray:

2 Likes