Filtering table from multiselection dropdown

[Alejandro_Alaco]

Hello,

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

I have a table

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

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

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

this one column

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

thanks

[Transistor]

(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.

[Brandon_Peterson]

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.

[Transistor]

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.

[Alejandro_Alaco]

Hello!,

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 = 'ONE'
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

Then I have my dropdown with a dataset in options

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

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

[pascal.fragnoud]

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.

[Alejandro_Alaco]

Hello, I adding a custom property on my view wich is tableFiler-1,

when I choose the whole custom property so I can filter multiple values with the dropdown the filter doesn´t work.

When I choose just one element of the array the filter works

The table where I get the binding to filter the named query is like this

and the named query is like this:

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 = 'OEA'
    AND (:tableFilter IS NULL OR Asset.businessUnit IN :tableFilter)
ORDER BY
    CASE
        WHEN SubStr(Asset.businessUnit, 1, 3) IN ('CAT', 'BAL', 'MUR', 'VAL', 'AND', 'ARA', 'AST', 'CAL', 'EUS', 'GAL', 'NAV', 'RIO') THEN 2
        WHEN SubStr(Asset.businessUnit, 1, 5) = 'MAD-F' THEN 0
        WHEN SubStr(Asset.businessUnit, 1, 7) = 'MAD-PAR' THEN 0
        ELSE 1
    END,
    Asset.businessUnit,
    Asset.parentAsset,
    Asset.asset

Basically when I choose just one item the dropdown filter works, when I choose multiple ones it doesnt.

I´m not sure why this happen, any ideas?

[pturmel]

You cannot pass lists into single JDBC parameters.

If you script your query with runPrepQuery instead of using a named query, you can dynamically generate question mark placeholders for all of the list elements, then pass the correct length argument list.

[Alejandro_Alaco]

Thanks a lot, I try it!