# How to use a drop-down list to filter data in a table? **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [June 20, 2023, 8:32pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305 "2023-06-20T20:32:39Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Cristian\_Gomez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cristian_gomez/32/49040_2.png) [@Cristian\_Gomez](https://forum.inductiveautomation.com/u/Cristian_Gomez)\ **Post date:** [June 20, 2023, 8:32pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/1 "2023-06-20T20:32:39Z")
I would like to know if there is a way to filter the data that I have in a Dataset with an external dorpdown to the table, inside the data of the table there is one or several columns with the information to filter, that is to say, the value corresponding to the one you want to filter is repeated in many rows and this way you already have a search indicator for each row. Thanks ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [June 20, 2023, 8:39pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/2 "2023-06-20T20:39:23Z")
What version are you running? Are you aware of the built-in column filtering? `columns.0.filter.enabled : true`. `columns.0.filter.visible : always` or `on-hover`. etc. ---
**Author:** ![Cristian\_Gomez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cristian_gomez/32/49040_2.png) [@Cristian\_Gomez](https://forum.inductiveautomation.com/u/Cristian_Gomez)\ **Post date:** [June 20, 2023, 8:44pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/3 "2023-06-20T20:44:29Z")
Thanks, 8.1.20 version ---
**Author:** ![Cristian\_Gomez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cristian_gomez/32/49040_2.png) [@Cristian\_Gomez](https://forum.inductiveautomation.com/u/Cristian_Gomez)\ **Post date:** [June 20, 2023, 8:47pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/4 "2023-06-20T20:47:35Z")
I do not know about built-in column filtering, could you tell me where I can read information and syntax ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [June 20, 2023, 8:59pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/5 "2023-06-20T20:59:23Z")
> [@Cristian\_Gomez](#): > > could you tell me where I can read information and syntax I did. Try this: - Drop a table onto a view. It will show the default city, country, population sample data. - In `props.columns` press the + icon three times. In each of the column entries set `field : city`, `field : country` and `field : population`. - Set the filter properties as described earlier. - Switch to ► Preview mode. You can now click the filter icons on the top of each column and see the results. [https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table) ---
**Author:** ![Cristian\_Gomez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cristian_gomez/32/49040_2.png) [@Cristian\_Gomez](https://forum.inductiveautomation.com/u/Cristian_Gomez)\ **Post date:** [June 20, 2023, 9:08pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/6 "2023-06-20T21:08:18Z")
That is the problem ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/5/9570a9fb9b60981d2f3da869e1b7666dddcaaf1d.png) My version is 8.1.20 ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [June 20, 2023, 9:16pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/7 "2023-06-20T21:16:24Z")
OK. Then you are stuck until you upgrade. There is the general `props.filter.enabled : true` which gives you a very basic filter. If you really need to you can use Text or Dropdown components to your view and add these as inputs to a script transform on your table binding. ---
**Author:** ![Cristian\_Gomez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/cristian_gomez/32/49040_2.png) [@Cristian\_Gomez](https://forum.inductiveautomation.com/u/Cristian_Gomez)\ **Post date:** [June 20, 2023, 9:20pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/8 "2023-06-20T21:20:01Z")
Thanks Yes, I'm reading about that I'm going to try that ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [June 30, 2023, 4:50pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/9 "2023-06-30T16:50:15Z")
You could use a named query that takes a parameter from the drop down. In the `onActionPerformed` event, use a script to change the table's data to the new filtered query, then refresh the binding. FWIW - I am attempting to use four drop downs here: [https://forum.inductiveautomation.com/t/drop-down-on-select-event/75747/3] Where each one runs the query with an additional parameter, i.e. the first one sends one param, the second sends the first and second params, etc. However, experimenting with the `onSelect` and `onActionPerformed` events I have not been very successful in replicating the action I desire, so I may just have to live with what I've got - it works, just not how I want it to. Anyway - back to your situation: named query, send parameter, refresh table binding. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [June 30, 2023, 6:38pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/10 "2023-06-30T18:38:36Z")
> [@mdemaris](#): > > [[Drop down on select event - #3 by mdemaris](https://forum.inductiveautomation.com/t/drop-down-on-select-event/75747/3)] Yea, I should have asked why you were trying to do what you were trying to do. If you want to filter a table with dropdowns, I suggest this (with a few variations depending on your exact use case - that's for you to define): 1. Query the whole database table. Or at least everything that can be displayed there. Alternatively, you can run the 'base' query based on the first dropdown. 2. Bind a custom property to that query 3. Use a structure binding on the `data` property of your perspective table. Add an element for the custom property holding the query results, and one for each of your dropdowns. 4. Add a script transform, and filter your data there based on the dropdowns values. I'm using something like this for a case that's a bit more complex: The number of filters are arbitrary, and they're not all dropdowns. Also, the dropdowns allow multiple selections, and filters are optionals. So, I have a `filters` custom property that's a list of dicts where each dict represents a filter, and looks like this: ```json { 'field_name': "foo", 'value': 42 } ``` Where `field_name` is the name of the column to check, and `value` is what I want to keep. Then the table's `data` property uses a structure binding on the source data and the `filters` custom prop, with this transform: ```python from java.util import ArrayList def check_filter(item, f): if isinstance(f.value, (ArrayList, list)): return any(str(item[f.field_name]) == str(elem) for elem in f.value) elif isinstance(f.value, bool): return str(item[f.field_name]).lower() == "true" else: return str(f.value) in str(item[f.field_name]) return filter( lambda item: all(check_filter(item, f) for f in value.filters), value.data ) ``` Yes I know, the bool part doesn't look good, but I didn't have much choice there ;( Alternatively, depending on your exact use case, you could maybe gather all your filters then build the query from them. Or a I can think of a few other solutions, again depending on exactly what you want to do. None of them involves using events on dropdowns. It's up to you to fill us in on your goal, if you want more accurate help. ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [July 7, 2023, 6:55pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/11 "2023-07-07T18:55:35Z")
Why not use a named query with parameters that are passed from the drop down? Here is the SQL of a query I use: ```auto IF :p_Aisle = 0 SELECT * FROM [inv].[v_Bin_Crud_DS] WHERE StorageAreaID = :p_Area ELSE IF :p_Section = 0 SELECT * FROM [inv].[v_Bin_Crud_DS] WHERE StorageAreaID = :p_Area AND AisleID = :p_Aisle ELSE IF :p_Shelf = 0 SELECT * FROM [inv].[v_Bin_Crud_DS] WHERE StorageAreaID = :p_Area AND AisleID = :p_Aisle AND SectionID = :p_Section ELSE SELECT * FROM [inv].[v_Bin_Crud_DS] WHERE StorageAreaID = :p_Area AND AisleID = :p_Aisle AND SectionID = :p_Section AND ShelfID = :p_Shelf ``` ---
**Author:** ![mdemaris](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mdemaris/32/54639_2.png) [@mdemaris](https://forum.inductiveautomation.com/u/mdemaris)\ **Post date:** [July 7, 2023, 10:06pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/12 "2023-07-07T22:06:46Z")
@Cristian_Gomez I hope you don't mind me jumping on your train, but rather than posting a new thread on the same topic, and I hope this helps you and others and helps me to solve my current issue. Imagine four drop downs and one table. This feature allows for creating and deleting multi-part locations for our system. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/a/da264be0ad814f62fdabc1ddfa4cb2be72ea3285.png) Each drop downs' options are bound to a query. The table has a custom prop, `table_data` to which the `table.props.data` is bound. (I'll call this **Query1** ) The first three drop downs will enable the drop down below it, get the selected value of the previous drop downs, and send those parameters to a named query, (I'll call this **Query2** ) then set the `table.custom.table_data` to the result set of the named query. This is done via scripting on the `onSelect` event of each drop down. The results in the table are filtered down each time the event fires, so that when I want to add a new location I can see if that location already exists. This setup works, as I mentioned in an above post, I just don't like the way the events (@paul-griffith) 🙂 work, as I am used to a slightly better event setup in MS Access (at least for this type of component). Example of the first drop down script: ```auto def runAction(self, event): namedQuery = "INV/v_Bin_Crud_Filtered" area = self.props.value params = {"p_Area":area} self.parent.parent.parent.getChild("flx_TableArea").getChild("Table").custom.table_data = system.db.runNamedQuery(namedQuery, params) self.getSibling("cbo_Aisle").props.enabled = True ``` My frustration is that this same setup, with four drop downs and one table, is not working in another view. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/d/ad8a3e6db39ae960ecfcb2cf2f8ba8cbdb32172e.png) This is another location system used for something else. Each drop down is set up the same way, as is the table. Here is the script from the first drop down: ```auto def runAction(self, event): namedQuery = "AST/v_Asset_Locs_Cbo_Filtered" bldg = self.props.value params = {"p_Bldg":bldg} self.parent.parent.getChild("coord_Right").getChild("Table").custom.table_data = system.db.runNamedQuery(namedQuery, params) self.getSibling("cbo_Asset_Area").props.enabled = True ``` I have set print statements in the `onSelect` events of each drop down, and the variables are getting the correct values. When I test the named query directly, the correct result set is returned. For some reason the result set is not returning to the table. BTW - both `table.custom.table_data` are set to `Return Format: Auto`. This works for the first example just fine, and changing it to `dataset` has no affect, which I expected. So, in the second example, to test further, I went to both of the named queries and in the `SELECT` statement, named each column in the same order as the table column names. This had no affect, which I expected. I also, just for grins and giggles, added a text area bound to the return set and, as expected, when the table has an empty set, so does the text area. To clarify the operation here, after selecting the value from the first drop down, the table is empty. Anyway, when I click the second drop down, the table has a data set and lastly, when I select an item from the third drop down, the data set is empty again. _( I just realized that the first and third drop downs are set to `onActionPerformed`, but I forgot to change the second one. After setting them all to `onActionPerformed` I get no data sets returned until I hit the "Clear" button.)_ That's all I have for now. [https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/7/97b7123b536e59de07c458098c35755a11c0ec02.mp4](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/7/97b7123b536e59de07c458098c35755a11c0ec02.mp4) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 7, 2023, 10:13pm UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/13 "2023-07-07T22:13:39Z")
Your problem is very similar to this: > [@Controling bindings evaluation order](https://forum.inductiveautomation.com/t/controling-bindings-evaluation-order/75842/23): > > Try this: > > > **View JSON** > > > > { "custom": { "isFresh": true }, "params": { "category\_id": "", "family\_id": "", "item\_id": "" }, "propConfig": { "custom.isFresh": { "access": "PRIVATE", "binding": { "config": { "expression": "coalesce({view.params.family\_id}, {view.params.category\_id},… Just differs on what to do with the dropdown values at the end. Note how little scripting is actually needed. Certainly not needed to run the named query (and probably shouldn't be, to keep the flow of data smoothly asynchronous). ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [July 8, 2023, 7:46am UTC](https://forum.inductiveautomation.com/t/how-to-use-a-drop-down-list-to-filter-data-in-a-table/75305/14 "2023-07-08T07:46:43Z")
If you're asking me, about MY use case: Because it's a lot more complex than what it looks like. The data displayed comes from several tables, some of them processed in ways that I wouldn't want to script in SQL. The filters are entirely dynamic (each column has user configured filters, stored in a database), and there are a lot of them. I need the table to refresh for each filter selection - I wouldn't want to run a query after each user click. Especially considering the filtered results might only be marginally smaller than the full dataset, and might change a lot in short time periods - might as well fetch the whole thing once and for all.