Filtering table from multiselection dropdown

Hello,

I keep looking for solutions to filter a large table. Based on previous idea from @Transistor

I have a table
image

I created a custom property custom.data so I can filter the data from the named query before I show it on the table.
image

I added a multiselection dropdownto the view with values that match the ones in one of the table columns
image

image

The column wich match the values of the multiselection dropdown is this one

this one column
image

Is there a way I can filter the table with the multiselection dropdown?

thanks

(For anyone else, this is a follow up to Checkbox filter, selected box and non selected boxes - #9 by Transistor)

Here are two ways:
First, create a custom property custom.tableFilter on the view and make a bidirectional binding to it from the dropdown props.value. (This avoids all the getParent / getSibling syntax and will make your script less "brittle".)

1: Use the custom property as a parameter in the SQL query. This will automatically refresh the binding if the dropdown selection changes.
2: Use the custom property to trigger a script transform on the SQL results. Here's how I would do that:

  • On the Table component add a custom property, custom.data. Create a query binding on that so that it retrieves all the records.
  • On props.data create a property binding to view.custom.tableFilter. (Now when the dropdown selection changes this binding will refresh.)
  • Add a Script Transform to script the filter. The data will be available from self.custom.data and the filter will be passed as value into the script.

Have fun.

3 Likes

I would slightly modify step 2 here.

We want changes from view.custom.tableFilter and the table's self.custom.data to both send updates to the table's self.props.data property.

So instead of a single property binding on self.props.data to view.custom.tableFilter, use an expression structure binding:

{
	'tableFilter': '{view.custom.tableFilter}',
	'tableData': '{self.custom.data}'
}

Then create the script transform to process the data (value.tableData) and your filter values (value.tableFilter).

This way, when your filter updates or the data itself updates, then your table will update accordingly.

3 Likes

Good point, Brandon. I was assuming the underlying data would be fairly static, had considered the expression structure but didn't follow through with it. Your way is better.

3 Likes

Hello!,

image

and I have my

Table custom.data binded to my namedQuery

SELECT DISTINCT
Asset.asset AS "Activo",
Asset.assetName AS "Descripcion",
Asset.EquipmentType || ' <' || EquipmentType.EquipmentTypeName || '>' AS "Clase equipo",
Asset.parentAsset AS "Activo padre",
Asset.businessUnit AS "Ud. negocio",
ALD.Value AS "Dato de nivel",
Asset.companyLevel AS "Nivel",
Asset.priority AS "Prioridad"
FROM
Asset
LEFT JOIN EquipmentType ON Asset.equipmentType = EquipmentType.EquipmentType
LEFT JOIN AssetLevelData ALD ON Asset.asset = ALD.asset
WHERE
Asset.company = 'ORANGE' AND EquipmentType.company = 'ORANGE'
AND (:tableFilter IS NULL OR Asset.businessUnit IN (:tableFilter))
ORDER BY
....
ELSE 1
END,
Asset.businessUnit,
Asset.parentAsset,
Asset.asset

and parameter {parent.custom.tableFilter}

then my table props.data binded to this custom.data

image

Then I have my dropdown with a dataset in options
image

and the value binded to the TabContainer.custom.tableFiter Bidirectional

Then I have my TabContainer.custom.tableFilter with one fixed value to show something in the table at the begining
image

but the filter doesn´t seem to work, any idea of something I may be missing?

  1. Why use a bidirectional binding on the dropdown's value ? Use a unidirectional binding to the dropdown's value on custom.tableFilter instead
  2. You're using in to filter in your query. Why ? The parameter is a string, use = or like, not in.
1 Like