MFILTER <Where=SQLite where> [<Reverse=Yes|No>] [<Join=None|or|and>]
The MFILTER (Macro Filter) command allows you to set filters on the fly without having to set them up and save them. Not all the functionality of the filter dialog is supported. If you can't generate the filter you want using this command then you will still need to set up a filter, save it, then use the FILTER command.
Note: The use of Expression= is deprecated and supported for backwards compatibility only.
Where = This must be a valid SQLite "Where" statement. Database variable names can be found here. However, when using SQLite the database variable names should have the "$d_" prefix removed. Do not enclose the Where= in double quotes (because of the nature of a Where expression you may already have double quotes as part of the expression)
The where clause supports variable substitution for all non database variables (database variables begin with $d_) . So you can use any native GSAK variable in the where clause and it will automatically get converted to the corresponding Sqlite Literal:
GSAK string: Output is single quoted with embedded single quotes replaced with two single quotes
GSAK date: Output is a single quoted string in the format 'YYYY-MM-DD'
GSAK Numeric: Output as is but ensure decimal separator is always a . (period)
GSAK Boolean: Output True as 1 and False as 0
So in macros you just allocate variables as you would normally. When you want to use those variables in Mfilter Where=, you just place them as is without any need for conversion or adding quotes etc.
However, there may be times when you want to dynamically build the *whole* where expression. Use the special system variable $_SQL to build your complete where expression. This system variable is the only string variable that will not have it's contents escaped with single quotes, allowing you to build the compete where statement. This does mean you are completely responsible for formatting your query to the correct SQlite syntax. You will need to make sure all dates, booleans, and string literals are correctly formatted to be compatible with Sqlite (numbers should be OK as is). It really is not that dissimilar to using the Sqlite() function and building your own Select statement that includes a "Where". When you use Sqlite() you already have to do this same conversion of any macro variables you want to use (functions like DateToSql(), BoolToStr(), and SqlQuote() help in this regard). For example:
$_sql = "name like '%hill%'" Mfilter where=$_sql |
If we use a normal variable here, then we don't get the automatic removal of the quoting as we do with $_Sql, then your where clause will throw an error (or produce unexpected results) because of the extra single quotes.
Reverse = Yes - To invert/reverse the filter (That is, give you all the waypoints that do not match your selection criteria). No - do not reverse. The default value is No
Join = Action to perform with the current filter. The default of "none" meaning the Filter command does not do any "joining" and behaves exactly the same as setting a new filter. Any other value will compare the current filter against this filter using the "join" type and produce a new subset/filter (which will then become the current filter and you can do another "join" and so forth). See join notes for a full explanation of Join.
Sample code:
# set filter on all found caches MFILTER Where=Found # set a filter on all caches found in the last 7 days MFILTER Where=LastFoundDate > date('now','localtime','-7 days') # set a filter on all caches that contain "hill" (ignore case) MFILTER Where=g_regex('hill',name) # set a filter on all caches that start with "The" (ignore case) MFILTER Where=g_regex('^the',name) # set a filter on all caches that are not found, and not archived, and not temp disabled (ie available) MFILTER Where= not found and status='A' # Set a filter for all caches that have "Boat" or "Canoe" in the long or short description mfilter Where=g_regex('boat|canoe',LongDescription) or g_regex('boat|canoe',ShortDescription) #Set a filter for all caches that have at least one child parking waypoint mfilter where=Code in (Select distinct cParent from waypoints where cType = 'Parking Area') # Set a filter for all caches that do *not* have a "Publish Listing" log mfilter where=Code not in (Select distinct lParent from logs where lType = 'Publish Listing') # Set filter for all caches that have at least 2 dnf's in the last 30 days mfilter where=Code in (select lparent from logs where date(ldate) > date('now','localtime','-30 day') and lType = 'Didn''t find it' group by lparent having count(lparent) >= 2) |
When you use the MFILTER command you will notice the left most status panel of the grid will show in red "Subset: Macro Filter". This will let you know the current sub set has been set by the MFILTER command. Note: These settings are at the macro level only and are not reflected in the Filter dialog (if you bring up the dialog there will be nothing set)
Note: If the filter does not return any waypoints it will automatically be canceled and your subset will contain all waypoints. You can test for this situation using the $_FilterCount system variable. For example:
MFILTER Where=UserFlag IF $_FilterCount > 0 ... do some action ELSE PAUSE Msg="No waypoints in filter, action canceled" ENDIF |
Related: CancelFilter Filter
Alpha List Category List