Filter by Column : Powerful filtering/searching tool for all columns

[errante]

Update: You have a valid point here. That was broken in v1.4.1. You can try v1.4.2 instead.
Thanks!

[jinsight]

A brilliant and timely fix - as usual. Thank you!

[errante]

For those interested, over the weekend I made quite some nice progress, expanding this command to work as the ultimate Excel-like filter.
A sample of what can now be done.

This one demonstrates the application of one filter over another, plus special handling for columns with multiple values (e.g. tags). And yes, the column doesn't need to be active/visible.

I'll post the new update tomorrow hopefully.
Any suggestions or requests that would be useful to implement are welcome.

[sunnyzt]

This is really great. This is the best filter button at present.
I have some ideas for improvement. Hope it helps you improve the button

• Add history to the input box and set the maximum number. It is best to make a separate dialog box dedicated to managing history.
• Create a simple filter box for column fields because there are too many invisible columns to find one by one
• Create indexes for filter entries, supports #index syntax, e.g., #2 means to check the value with index 2.
• Merge duplicate filter entries and add the duplicate number after the filter entry to facilitate counting.
• Add sort to filter entries, sort by name or sort by number of duplicates
• Add Filter Only item to the right of the filter item, click this button to uncheck other items and select only the current item
• Filter criteria and filter results Import and export supported

[errante]

A small update.
I've delayed the upload date, (hopefully I'll post a beta this week) as I had some (actual) work to do.
But I've managed to add some very cool features. Now you can select/change the column from the same dialog (with a pseudo search filter combo), and filter over the results of a previous filter. Also I've added counts for each entry and some additional buttons.
A small sample:

At the end in the video you can see that I only get the results from the last filter (even when mixing commands/modes).
I still have to test several scenarios, which is the longest part, so please be patient

[errante]

Update v1.4.9 Beta 1 :

CHANGELOG :

GENERAL :

• New option in Configuration : enable_wilcards .
  • Allows to use Opus Standard Pattern Matching (only in text type values).
  • Now Evaluator is used when enable_wilcards is enabled (Wildcard escape flag and use_filter_builtin were merged to avoid contradictions).
  • In that case, preferred_filter_mode and labels_access are ignored.
• Other minor corrections.

FAYT :

• New value for default_implicit_column : current highlighted , which allows implicit reference to the first selected column. To do this, the whole column must be selected beforehand, using right click + Ctrl (see the video below).

• New Flag : Filter only what is visible, allows to apply the filter only on the currently visible items. useful if you want to apply 2 filters in a row (one on the results of the other).

COMMAND :

• Removed ESCWILD.

• New argument USEWILD, to enable the use of wilcards via command (overrides enable_wilcards in configuration).

• FILTER has a new value : visible to use Filter only what is visible via command.

• New argument DIALOG, shows a dialog that makes the filtering process easier (The ultimate Excel-like filter function!).

  • No other arguments are valid when using DIALOG.

  • Allows simple visual filtering and access to more advanced filtering using an advanced dialog.

  • Multi values columns (tags, label, etc) allows using logical AND.

  • Supported values are :

    • filterall, to use all items in the tab (by default it only uses the ones currently visible)
    • Any column keyword (built-in,script,evaluator,shell).
    • A name previously defined in custom_column_names.
    • At least one column is needed to start, since is the first one enabled at startup. The other columns can still be accessed from the same dialog later (only the built-in ones unfortunately).

  • It has some quick filters depending on the column type (string, number, date, etc), via More Options and Customize... or by pressing F4

  • In this mode, you can change in runtime the category (string,number,etc) of a custom column, if It's not registered. Optionally allows to save the option in configuration for future use (both in FAYT and command mode).

  • Queries via command are also saved in the history (with DIALOG, only those using Search in the advanced dialog).

  • You can export the results to a collection, using the To Coll button.

    • Use configured values for collection name and create subcollections.
    • Press together with shift to store in the background and continue using the dialog.

  • The buttons (icons?) at the bottom from left to right :

    • Swap, to invert the current selection
    • Clear, to clear the selection
    • Refresh, to re-read the values (NOTE: Using this means using ALL items in the tab, not only the ones initially visible)

  • Hotkeys :

    • F3 : Focus to edit control
    • F4 : Access to the advanced dialog
    • F5 : Similar to pressing the Refresh icon, already explained.

NOTES :

• The reading time of each column values for the dialog is directly proportional to the number of items to be processed. It also increases if the column was not currently visible, and if It's a custom column.
• You can close the dialog if it occurs to you to use it with 10000 items and you think it takes too long. For built-in columns, it should also stop the process. For custom columns it's a different story.
• The custom columns values are obtained by a trick with Evaluator (first introduced in my other script Columns Viewer), and once the operation is started it can't be stopped (at least with everything I've tried). (So it's better to use them only when they are already visible, and therefore already calculated).
• The dialog allows to interact with the Lister, so performing some actions with the dialog open could affect it in unknown ways.
  • If you close, update or change the path in the tab with the dialog open, the dialog closes.
• The filter in the dialog is NOT SIMILAR AND DOES NOT HAVE THE SAME SYNTAXIS as the command itself. It's a simple filter for the list below. If you want something similar, you can use the advanced dialog (Customize...) and click on 'Filter', or directly use the FAYT mode or the other command arguments. For example, you can set default_implicit_column to current highlighted, check on Filter only what is visible and then use it like this:

LIMITATIONS / TODO / IDEAS:

• If you use advanced dialog (via More Options), the options in the list are not updated to match the results. This is understandable, since the options were not supposed to be the necessary ones, but since it can cause some confusion I imagine that it should be updated as well...
• Currently you get the values each time a column is accessed via the combobox. The ideal would be to do that once for all items (even if they are hidden) and then in subsequent calls, filter those not currently visible based on the values already calculated. As this implies more work on my part, it can be implemented in later versions (so use/change columns or refresh the dialog list when necessary, especially for custom columns that create a cache in an exotic way).
• Implement timers for the three buttons at the bottom, this in order to avoid overloading Opus, since someone can think of pressing them continuously
• If the Lister is refreshed, update also the dialog ? (I'll have to see how complicated it would be to implement that).
• Handle the Attributes column as multi value, with more friendly options (e.g. archive instead of a). (Done in current beta)
• Add more quick filters in More Options (I'm open to suggestions ).

[sunnyzt]

It's great. It's even better than I thought it would be.
I found a bug that I hope can be fixed:
fix:Each time the dialog box is opened, it remembers the size of the last time it was closed and cannot be automatically adjusted based on filter values and columns.

I have some ideas for improvement and hope it gets better:

• Change all commands in the dialog box to buttons to save space, just like in ps
  
• Command prompt pops up when mouse moves over button
• Add filters defined in dopus settings above the input box
• Set a drop-down menu on the right side of the input box for case-sensitive/full-word matching/regular expressions/evaluator/ignore variable syllables, etc. or make small buttons, just like in vscode
  
• Column Width Optional Minimum and Maximum Width, Automatically adjust column width and window size
• Value too long Optional newline display and omission of redundant characters
• Add filter or select button switch

[errante]

@sunnyzt

I found a bug that I hope can be fixed:
fix:

That's not a bug. The dialog remembers the last position and size used, because there are people who find it useful to put it in a specific place and with a size different from the original. Just don't resize it at that width if you don't need it to.

(Also you should not change the command's name as it could cause potential errors, not to mention it's a bit rude towards the developer)

[sunnyzt]

@errante

That's not a bug. The dialog remembers the last position and size used, because there are people who find it useful to put it in a specific place and with a size different from the original. Just don't resize it at that width if you don't need it to.

Well, thank you very much for your answer.

@errante

(Also you should not change the command's name as it could cause potential errors, not to mention it's a bit rude towards the developer)

I'm sorry about the name change. Just because the command name FilterbyColumn is too long, I want to simplify it, no other purpose

[DaveM]

errante, what a very useful and full-featured script you have created. It is particularly attractive to any who often use the AutoFilter function in Excel. Hell; it is even better, as Autofilter does not allow multiple layers of filters, to arrive at just the sub-set of, in this example, the two DLLs that are of an age and size. (And Excel's regular filter's cumbersome nature dissuades regular use).

Nice work. Another great addition to a great product.

DaveM

[David]

Thank you very much for this script.
I found time to install this today and have it working.
I have much to learn about this.
It shows wonderful attention to detail.
I am also using @lxp evaluator playground.
Maybe someday they will become one script ?
I like both of them !

[Mbuck]

@errante

FAYT

Forgive my ignorance but where and do you put this code so that it becomes visible in the context menu:
@label:=format(original_label, %headername%)
@hideif:=%headerindex% == -1 or %headerkey%=="index"
FilterbyColumn DIALOG=%headerkey%

[Hardkorn]

Settings - Customize Toolbars and Keys... - Context Menu - Column Header

You can copy and paste this buttoncode with right click:

<?xml version="1.0"?>
<button backcol="none" display="both" label_pos="right" separate="yes" textcol="none">
	<label>Filter by &apos;%1&apos;</label>
	<icon1>#quickfilter</icon1>
	<function type="normal">
		<instruction>@label:=format(original_label, %headername%)</instruction>
		<instruction>@hideif:=%headerindex% == -1 or %headerkey%==&quot;index&quot;</instruction>
		<instruction>FilterbyColumn DIALOG=%headerkey%</instruction>
	</function>
</button>

[Mbuck]

Thank you for that concise explanation it is much appreciated. However, in the video, our friend shows that instead of holding down the CTRL key and right click (which over time I will forget nodoubt) he seems to have somehow included a context menu item that does the same thing i.e. highlights the entire column that one would like to filter on. That is what I now don't know how to do.

[David]

I don't know how the highlighting was introduced in the video.
Maybe we can get that part later.

To add the posted button to the context menu:

1. Enter Customize mode.
2. Within the customize GUI select the Context Menus tab.
3. Either double click the Column Header entry or right click it and select Edit.
4. Two choices:
   a) Right click the pop-up and insert a new button.
   b) Right click the pop-up and Paste the posted XML button already copied to your clipboard.
5. Move the button to your desired location in the context menu.
6. Exit Customize mode saving changes.

Then right click on the desired column header and enjoy !

[errante]

Greetings. A small teaser that I was eager to share with the people who find this script useful.

Last night and this morning, I used my free time to include some BIG improvements:

FAYT :

• Now you can check if a column has data or not, regardless of its type. To do that simply use <colname>=="" or <colname>!=""
• You can now compare the value of a column with another column (with auto suggestions for names!), using <colname><operator>{columnkey}. Eg. $scp:exiftool/exif-datetimeoriginal!={datetaken}

COMMAND :

• Redesigned DIALOG mode. Options for more advanced searches (search flags).
• Data is loaded only once and kept in cache, so data that takes a long time to read is read only once, avoiding future timeouts.
• Ported FAYT syntax to the search box in the dialog box
  A video:
  

What do you think about the new additions and how are you using the latest beta?

[Mbuck]

For some reason, although I am using the latest beta aka v1.4.9 Beta 1 : ColSearch.opusscriptinstall. See screen shot attached. Despite that, I don't get the new filter drop down but only the older "more options" hyperlink? How do I fix that? Also, in the video, you show that instead of holding down the CTRL key and right click (which over time I will forget nodoubt) you seem to have somehow included a context menu item/switch in your context menu that does the same thing i.e. highlights the entire column i.e the column that one would like to filter on. That is what I now don't know, although I have carefully done what others have suggested, I don't how to include that option in the column header flyout but I suspect you may have a separate script to enable that feature, am I correct

?

[errante]

@errante

A small teaser

Not yet published, I have to test it first.
I upload these videos mainly for ideas/suggestions.

[Mbuck]

I understand that you have a comprehensive list of filter options, which is great. However, could you add a feature that allows us to create a favourites list? This would save time and make the process less tedious for us as we often find ourselves searching for the same thing repeatedly. Having a favourites list would make it easier and quicker for us to access our most frequently used filter.

[errante]

Do you mean being able to save the currently applied filter according to the column type? Or a filter applied to a specific column?