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
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.
Thanks, 8.1.20 version
I do not know about built-in column filtering, could you tell me where I can read information and syntax
I did.
Try this:
props.columns press the + icon three times. In each of the column entries set field : city, field : country and field : population.https://docs.inductiveautomation.com/display/DOC81/Perspective+-+Table
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.
Thanks
Yes, I'm reading about that
I'm going to try that
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.
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):
data property of your perspective table. Add an element for the custom property holding the query results, and one for each of your dropdowns.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:
{
'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:
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.
Why not use a named query with parameters that are passed from the drop down?
Here is the SQL of a query I use:
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
@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.
table_data to which the table.props.data is bound. (I'll call this Query1)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 (@pgriffith) 
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:
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.
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:
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.
Your problem is very similar to this:
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).
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.