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 ( from the Data Menu) 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 Flter rows 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 always considered as an AND command separator.

• The space means that the conditions are additive.
• Never include spaces in search unless they are between quote characters "mario invoice".
• Examples
  • mario invoice
    The program consider the rows that contain the words, "mario" and  "invoice"
  • "mario invoice"
    The program will consider the quoted text as a single word, space included. Therefore it will search all rows that contain exactly the "mario invoice" (in this order).

Special characters

You can use almost any character in the search.
Only the exclamation mark "!" and the vertical bar "|" at the beginning 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
  The vertical bar is the character Alt-124. If you can’t find it on your keyboard, search on Google for
• "Keyboard layout windows/mac italy/switzerland/usa" and check the images section.
• "!" command specifier 
  Any text preceded by the exclamation point is considered a command and has a special meaning. Text that contains a space should be entered between quotes.
  For example to exclude a text use the command sequence "!-"
  mario !-invoice

 Advanced syntax for Filter search

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 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.

• !=
  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
  Empty cell.
  From Banana version 10.1.16 it select cell with empty values.
  !=
• !+
  Contains, the cell must contain exactly the specified text. Useful for accounts.
  It is equivalent to searching without the ! command, "mario" or "!+mario" are the same.
  But the "!+"allows to be used in combinations of other characters .
  !-+mario excludes rows that contains mario.
  !~+mario excludes rows that contains mario case sensitive.
• !<>
  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
• !_
  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 is used the xml value of the content.

• 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
• !<>
  Different than.
• !><
  Within a range of values , excluding the specified elements.
  It is equivalent of using two filter > and <
  A range and it need two elements separated by "|".
  !><99.99|200
• !>=<
  Within a range of values, including the specified elements.
  It is equivalent of using two filter >= and <=
  A range and it need two elements separated by "|".
  !>=<100|199.99

Columns specifier

It allows to specify the column to use as a search.

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

• !description!
  It includes all the columns header that match the entered text. You can use wildcard and separators
  • !description!mario
    Search "mario" on column header "description"
  • !description!=
    Search empty text 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 column name XML "description" .
  • !debit*!=1000
    Search in column with header starting with "debit" for the exact content "1000"

Show rows with color

They are used for searching rows with color.

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

Style colors:

• When assigning a text and background color to a row, the program assigns for a combination of color a progressive style number. The style number depends from when a color is assigned and is then reused when the same colors are applied.  
  The style number will therefore be different for each accounting file.  

No color and Automatic color:

• The rows with Text No color or background Automatic color have the style number 0.
• Rows that use the color black or white are considered as colored and have their own number even if they, depending on the display theme used, may look as no color. 
  If you don't want to view rows with black and white displayed, select the rows and apply the No Color and Automatic color.

Show rows with formatting

They are used for searching for formatting.
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

Show rows with errors

Used for searching rows that the software has marked with an error or a warning. 
To check again for errors use the Recalculate all command. 

• !_er! or !_error!
  Show all lines marked with an error or warning.
• !_er!e
  Show all lines marked as errors.
• !_er!w
  Show all marked as warning.

Combining different filters

Enter the filter text separated by the space. For example:

• 2022 !des*!mario !debit*!=1000
  Will show rows that contain "2022" in any column, "mario" in any column starting with "des" and with all column starting with "debit" that contains exactly "1000".
• |!PeriodTotal_?C:xml!<> |!PeriodEnd?C:xml!<>
  Displays the rows with an amount, in columns whose XML name starts with PeriodTotal and ends with _C or whose XML name starts with PeriodEnd_ and ends with _C.

Rationale for the filter syntax

The advanced search syntax looks 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.
• It is not specific to a language, so it only uses symbols.
• Does support all the possible searches, so that it can be used to express filters entered by the user in a way similar to Excel.
• It opens the possibility to include commands that are specific to Banana like color, errors, etc.

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 come up with creating a special syntax where the explanation mark "!" is used as a command starter. The "!" is normally used at the end of a phrase, so it is unlikely to interfere with the search.

All feedbacks and suggestions are welcome!

Banana Accounting Plus GPT command generator for Filter search

The GPT command creator for advanced search syntax (Filter) is only experimental: it might be subject to changes and improvements.

This Banana Accounting Plus GPT was created using ChatGPT and is designed to generate advanced search syntax for filtering rows in the Transactions table.

Requirements

To use this GPT, you must:

• Download and install the last version of Banana Accounting Plus.
• Have a ChatGPT account.
  • To use this GPT, you must have a ChatGPT Plus subscription. Without it, you can try the service for free for up to ten requests. After that, you’ll need to either upgrade to ChatGPT Plus or wait some hours before making additional requests.

How it works

• Open the Banana GPT from this link:
  • https://chatgpt.com/g/g-HTOEAHRVb-banana-accounting-filter-rows-command-creator-gpt.
• In+the Message field at the bottom, enter a sentence describing what you want to search for within the Transactions table. Generally, more specific requests will yield better results.
• GPT will process your request and return the corresponding result.
  Based on the input provided to the GPT, a text string is generated containing the command needed to filter the rows in the Transactions table.
• Copy the generated text string.
• Paste the copied string into the Filter of the Transactions table in Banana Accounting Plus.

Examples of messages to enter in GPT

The following are examples of messages to enter into the GPT chat, along with their respective generated results:

• "Find all transactions from the third quarter of 2024"
  • Result: "!date:xml!>=2024-07-01 !date:xml!<=2024-09-30"
• "Search the rows with an amount of 450.00"
  • Result: "!amount:xml!=450.00"
• "Search for rows with an amount greater than 200"
  • Result: "!amount:xml!>200"
• "Search for rows with the value 3001 in the Account Credit column"
  • Result: "!accountcredit:xml!=3001"
• "Give me rows with the value 1020 in the Account Credit column and an amount between 1500 and 4000"
  • Result: "!accountcredit:xml!=1020 !amount:xml!>=<1500|4000"