Client-side filter for table on mapped values (JSON objects)

Queries and Resources
1

Hi,

I am fairly new to Retool and have been fiddling around with filtering a specific column in my table.
The data of the table is populated using API requests on an external service and yields the following view

image
image3074×270 37.1 KB

based on the JSON result from the request

{
  "expenses": [
    {
      "id": 37762092,
      "spent_date": "2022-12-18",
      "notes": "USB-C Ladekabel (Nutzung Apple Netzteil für...)",
      "project": {
        "id": 2996730,
        "name": "Administration, Meetings, interne Prozesse, Diverses",
        "code": "00195"
      },
      "expense_category": {
        "id": 291712,
        "name": "Sonstige",
        "unit_price": null,
        "unit_name": null
      },
...

Now I would like to filter for the column Category, which is of course a mapped field for the corresponding result set:

image

However, when I try to apply this filter on the table no records are shown at all.

image
image1514×298 8.06 KB

image
image3070×310 6.63 KB

I am sure that I am probably missing something very basic but I don't get to the issue. Any help is highly appreciated.

Thanks,
Martin.

2

try using {{self.expense_category.name}}

3

Hmmm, I don't quite get it. Where shall I put this?

I kind of moved ahead and added a multi select component which contains all possible filter values:

image
image1518×850 41.5 KB

Then I added a custom script to that component that looks as follows:

var filters = []
if (multiselectExpenseCategory.value.length>=1) {
  multiselectExpenseCategory.value.forEach(f => {
    filters.push({columnName: "self.expense_category.id", 
                  filterValue: f, 
                  operator: "equals"})
  })
}
tableExpenses.setFilters(filters, "or")

Since the multi select component yields the id instead of the name, I also had to adapt the table so that it shows the id in the column Category (the name is now in a custom column category_name):

image
image3082×300 44.9 KB

Filters are correctly applied to the filter section, but they still don't work.

image
image1436×324 11.3 KB

I have tried all sorts of combinations for the columnName in the filter code: self.expense_category.id, expense_category.id, Category, Category.id, etc. But no luck.

Any ideas are highly appreciated.

4

Solved the issue by adding a custom SQL query as follows:

select
  *
from
  {{ queryExpenses.data.expenses }} e
where
  ( {{ !multiselectExpenseCategory.value.length ? 1 : 0 }} = 1 )  or ( e.expense_category.id = any ( {{ multiselectExpenseCategory.value.length == 0 ? [''] : multiselectExpenseCategory.value }} ) )
5