Filter & Temporary Sort rows

The Filter and Temporary Sort rows tools of Banana Accounting Plus allow rows in a table to be filtered and temporarily sorted, without changing their permanent order. These functions are very useful for immediately searching for data in both text and amounts.
Once the sorting or filtering criteria have been removed, the table rows return to their original order.

To change the sorting permanently, use the Data menu > Sort rows command.

The Filter and Temporary Sort Rows are new functions. Kindly give us your feedback on how to improve them.
They are only available in the Advanced plan of Banana Accounting Plus.

The Temporary Sort feature

This tool makes it possible to quickly sort the content according to the column from which the Sort command is given:

• Right-click on the column header where you need sorting.
• Position yourself on the small arrow and select ascending or descending sorting criteria.

Advantages

• You can sort the displayed rows, ascending or descending, according to the column you want.
• Unlike Excel, when you remove the temporary sort, you return to the original order of the rows without having to undo the operation.

An arrow will appear in the header of the column chosen to sort the data.

Important Note

The Temporary Sort row feature is different from the Sort rows command (from the Data menu):

• The Temporary Sort row feature is a temporary sort of the rows you see on video conceived to speed the review and editing of previously entered data; you can easily lift the sorting criteria to go back to the original rows order. This feature is only available with the Advanced plan.
• The Sort rows command will permanently change the rows order of the table; it can only be undone with the Undo command. This command is available for all plans.

The Filter rows feature

The rows filter feature allows to display only those rows of the table that include the keyword entered as a search criteria. This saves considerable time for the following reasons:

• You can find the transactions you are looking for within seconds.
• You can directly edit filtered transactions.
• The Temporary Filter is present in all tables (Accounts, Transactions,Exchange rates ...) and to use it, you can simply type your key words in the Filter box.
• Easily lift the Filter to go back to the original rows order.

The green text highlight is for explaining purposes only; it doesn't appear in the software.

Filter features and syntax

The filter allows you to find rows simply by entering text separated by space. While you enter the text the program filters all transactions and only shows the ones that contain all the specified space separated text.

• mario invoice 
  Will show the rows that contain in any position the words, "mario" and  "invoice"
• mar inv
  Depending on the number of rows you have, you could probably get the same result just by entering an abbreviated text.
• "mario invoice" 
  Search for text that includes spaces: if you enter the text within quotes, the program will consider the quoted text as a single word. 
• "!~Mario"
  Normally the search is case insensitive.
  You can specify the search to be case sensitive with a special command.

Space is a command separator.
Never include spaces in search unless they are between quote characters "mario invoice".

Special characters

You can use almost any character in the search.
Only the exclamation mark "!" and the vertical bar "|" at the begin of a text sequence have a special meaning.

• "|" OR specifier
  At least one of the elements that are preceded by the "|" must be present on the row.
  In the following case the search will display rows that include "mario" and either "invoice" or "fee".
  mario |invoice |fee
• "!" command specifier. 
  Any text preceded by the exclamation point is considered a command and has a special meaning. 
  For example to exclude a text use the command sequence "!-"
  mario !-invoice

 Advanced search syntax

The Advanced search syntax is only experimental: it might be subject to changes and improvements.

The "!" character at the begin of a text means this is a special search command.

• !
  when at positioned before or after a space, it starts a command sequence
• !!
  is considered as a simple "!" and not a command sequence
• !-
  the minus sign "-" is used to exclude content, and it can be used with any other operator
  !-mario !-1000 !-=1000 !-^payment !-~10
• !~
  The tilde "~" must come immediately after the "!" or the "!-" characters
  With the "~"  the search is case sensitive
  !~mario
  !-~mario

Command characters

They are the characters that immediately follow the command begins "!", "!-","!~" or "!-~":
They can also be used with the "-" exclude and "~" case sensitive character, but here are explained without.
The examples are within quotes but

• !=
  Finds exact, the cell must contains exactly the specified text. Useful for accounts.
  !=1000"
  It can be used with multiple values with the | operator, Search for any of.
  !=1000|2000|30000
• !<>
  Different, the cell text must be different from the one provided.
  !<>1000
• !.
  "." wildcards (*,?), same as exact but with wildcards. Supports multiple values.
  !.100*
  !.100*|2?0*
• !^
  Starts with. The cell text must start with the specified text. Supports multiple values.
  $mar
  $mar|hom
•  !$
  Ends with. The cell text must end with the specified text. Supports multiple values.
  !$rio
• !+
  Contains (used for search that start with the command character. Supports multiple values.
  Search "$100"
  !+$100|$200
• !_
  Whole word search. Does not match with "1000". Supports multiple values.
  !_100
  !_100|200
• !:
  Regular expression. The text following ":" must be a regular expression.
  !:\bmario\b

Greater than, Less than and range characters

They are characters that are used for comparing values.
For this the xml value of the content is used.

• Date in the format yyyy-mm-dd
  2024-12-31
• Amount with the "." as decimal separator and without thousands separator.
  1999.99

Commands characters

• !>
  Greater than. Cell content that is greater than 100.
  !>100
• !>=
  Greater or equal than.
  !>=100
• !<
  Less than.
  !<100
• !<=
  Less or equal than.
  !<=100
• !<>
  "><" is a range and it need two elements separated by "|".
  It select the values between, including the specified elements.
  !<>100|199.99

Columns specifier

It allows specify the column to use as a search.

Column based filter Search only within the column specified between the two exclamation point.

• !description!
  It include all the columns header that match the entered text. You can use wildcard and separators
  • !description!mario
    Search "mario" on column header "description"
  • !des*!mario
    Search "mario" on all column header starting with "des"
  • !des*;doc!mario
    Search "mario" on the column header starting with "des" and column header starting with "doc"
  • !description:xml!mario
    Search "mario" on a  column with with column name XML "description" .
  • !debit*!=1000
    Search in column with header starting with "debit" for the exact content "1000"

Row color

They are used for searching for formatting or color.
Currently only a row search is implemented

• !_co! or !_color!
  Color search. Upper case.
  Show all lines that have a color.
• !_co!2
  Will show all rows with color 2
• !_co!2|3
  Will show all rows with either color 2 or 3.

Row format

They are used for searching for formatting or color.
Currently only a row search is implemented

• !_fo! or !_format!
  Show all lines that have a specific row format.
• !_fo!b
  Show all rows with format bold
• !_fo!i
  Show all rows with format italic
• !_fo!14
  Show all rows with format point size 14
• !_fo!b|14
  Show all rows with format bold or point size 14
• !_fo!b !_fo!14
  Show all rows with format bold and point size 14

Combining different filter

Enter the filter text separated by the space.  For example

• 2022 !des*!mario !debit*!=1000
  Will show rows that contain "2022" in any column and "mario" in any column starting with "des" and with all column starting with "debit" that contains exactly "1000".

Rationale for the filter syntax

The advanced search syntax look strange, but we wanted  a syntax that does not interfere with the natural search, so that the user could accomplish most searches naturally, combining texts, exactly how people are used to search within a smartphone application.
We have evaluated different other search syntax but they all have special uses for normal characters like the minus "-" or "+" marks or other "\", "(", "*", "?, frequently used in accounting.
So we had no choice other than creating our special syntax where the explanation mark "!" that is used as a command starter. The "!" is normally used at the end of phrase, so it is unlikely to interfere with search.
We plan to let the user specify the filter with in a way similar to Excel, and the filter text will then be created by the software. We wanted the syntax to support all the necessary advanced searches. 
All feedback and suggestions are welcome!