Perspective dropdown selection or "All"

Ignition
1

I am currently working on a system that will pull data based on a selected machine, selected Part, and in-between selected times. Would there be anyway to make an ALL option for each of these selections?

image
image810×808 36.2 KB

2

Yes.
You could build a query depending on selections.
But it's not really pretty.

Or you could pull the whole table on a custom property, then bind the table's data property to this custom prop, and filter in a transform.

I have a similar situation though much more complex, but the simplified version looks like this:
2 custom props on the table, one holds the data, the other one holds the filters.
The "data" one is simple enough, "filters" looks something like this:

[
  {
    'field': some_column_name,
    'values': [the accepted values]
  },
  {
    'field': some_other_column_name,
    'values': [the accepted values]
  },
  ...
]

It only holds filters that have been selected, those that are left with no selection do not appear there and I consider every value as valid. Build it from your dropdowns.
Now, the table's props.data is bound to both custom props in a structure binding, and I filter in a transform with something like

def filter_func(item, f):
    return item[f[field]] in f['values']

return filter(
    lambda item: all(filter_func(item, f) for f in value.filters),
    value.data
)

The filter here is very basic, but this should give you an idea.
If you need more details, let me know.

edit: If it can help, here's the actual filtering I'm using:

filter function:

from java.util import ArrayList
def check_filter(item, f):
	item = item[f.field_name]
	if f.get('type') == 'date':
		start_ok = f.value['start_date'] is None or item >= system.date.toMillis(f.value['start_date'])
		end_ok = f.value['end_date'] is None or item < system.date.toMillis(f.value['end_date'])
		return start_ok and end_ok
	elif f.get('type') == 'bool':
		return item == f.value
	elif f.get('type') in {'list', 'group'} or isinstance(f.value, ArrayList):
		return any(str(item) == str(elem) for elem in f.value)
	else:
		return str(f.value) in str(item)

transform:

return filter(
	lambda item: all(check_filter(item, f) for f in value.filters),
	value.data
)

The filters have a `type' key because everything here is dynamically generated and their actual type is lost somewhere.
image

edit: I should mention that this table is used a lot and filters change frequently. If you expect your table to be filtered once, a simple parameterized query might be better.

3

I would just

  • Add an "All machines" checkbox beside the dropdown.
  • Create an expression binding on the dropdown's enabled property. Enable the dropdown when checkbox is false.
  • Modify the SQL query based on the checkbox selected value.
4

If you are talking about making an "All" dropdown selection here is how I do it:

	SQLquery = "SELECT DISTINCT [col1] as value, [col1] as label  FROM  [TableName]"
	ds = system.db.runQuery(SQLquery)
	return system.dataset.addRows(ds,0,[['%','All']])

You could also just manually populate the dropdown options and make sure to include the All option with value %

I would then set a view level parameter that is bound to that dropdown value.
I would then bind my SQL data query to that view level parameter and write my query something like:

SELECT * FROM [TableName] WHERE [colName] like ?

and feed in the dropdown value into it. That way if "All" is selected the query returns all values (due to wildcard), and if another value was selected in the dropdown it would return a filtered down dataset.

1 Like
5

Nice trick with the wildcard, didn't think of that.
But it doesn't work for multiple select dropdown though, you'd need to build the query from all selected values... Which I guess is an okay thing to do.

6

Correct, my method would not work for multi select. In the case of multi select, I would just send a wildcard if no values were selected in the dropdown.

7

That does not solve the issue of the actually selected values.
You'll need to build the where clause dynamically from all the selected values.
Which is not my favorite thing to do, but as I said, I guess it's okay.

8

Which components do you use for your multiple selecting?

I was looking at radiogroup, one said multiple, but they are mutually exclusive even though one says multiple.

9

Checkboxes are an option, see this post for example,

1 Like