Dropdown Filter Iterating over Multiple Column Conditions on Table Component

[AJVancattenburch]

Can anyone tell me how you would get table filters to work if the filter options rely on more than one column for a results that can be displayed on your table?

For example, I'm trying to set up a Retool JS query called approvalFilter that should define filters for a dropdown component called approvalSelect
I have the dropdown options manually set as 3 options:

• All (apply no filter, show all rows);
• Approved (show only rows with APPROVED column with value of true) ;
• Denied (show only rows with DENIED column with value of true);

I tried setting the following as an event handler on the dropdown filter, but it is not working. Refer to the image below for context on my app setup:

If anyone has some knowledge on the best practice to get this to work as expected, I'd be very grateful! Thanks in advance, guys!

[pyrrho]

Hello!

Could you give a little more context on what you need to happen with the returned data from this query? Is filterBy intended to be a value that is coming from the approvalSelect component?

[MiguelOrtiz]

Hey @AJVancattenburch,

I may be missing the point here, but Retool offers a "Default Filters" section within the table settings:

You can then map your dropdown to match the values you want to return.

I created a video on filters here, hopefully it can help!

[AJVancattenburch]

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)
);

2. I inserted the values to represent each filter option:

INSERT INTO uap.approval_status_options (status)
VALUES ('All'), ('Pending'), ('Approved'), ('Denied');

3. Pulled the needed data into Retool as a Resource Query named approvalStatusList:

SELECT id, status FROM uap.approval_status_options;

4. 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;

5. 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!

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