AX1681

ShowFilterWizardDialog function

Opens the Filter Wizard in advanced view so that users can create a filter. Users can double-click on the cell that contains the function to open the wizard. The filter is then saved to a designed cell.

This function is intended to be used within spreadsheet Axiom files, to launch the Filter Wizard and place a filter in a cell. The filter can then be referenced as needed by Axiom queries, sheet filters, and so on. It is up to the file designer to configure the file to use the filter in some way.

Syntax

ShowFilterWizardDialog("DisplayText", "TargetCell", "PrimaryTableName", "LimitColumn", "DialogTitle")

Parameter	Description
DisplayText	The text to display in the cell. The DisplayText displays as normal text in the cell. If you want the text to appear like a hyperlink on a web page, you must manually apply the font formatting to the cell.
TargetCell	The target cell in which to place the filter. You can enter any of the following: A standard cell reference, with or without the sheet name. For example: "B2" or "Variables!B2". A column reference, such as "H" or "AZ". The filter will be placed in that column, within the same row as the cell with the function. A relative column reference, using the format +N or -N. The filter will be placed N columns to the right or left, within the same row as the function. For example, "+1" places the filter one column to the right of the cell with the function, and "-1" places the filter one column to the left of the cell with the function. NOTE: Remember to place the target cell location in quotation marks, such as "C25". If you omit the quotation marks and use just C25, then the entry is interpreted as a cell reference within the function—meaning that Axiom Software will attempt to read the target cell location from the specified cell. If C25 is empty, the function will behave as if no target cell was specified.
PrimaryTableName	Optional. Complete this parameter as follows, depending on the objective: Create a filter using any table: Omit both the PrimaryTableName and the LimitColumn if you want to open the Filter Wizard dialog showing all available tables. The user can create a filter using any table. Create a filter based on a primary table: Specify a PrimaryTableName if you want to open the Filter Wizard dialog and only show tables that are valid to filter the primary table. The user can create a filter based on the primary table or on a lookup reference table. This is the recommended approach if the file is designed to apply the filter to a specific data query, so that you can be sure the filter will be valid in the context of the query. Create a limit query statement: Specify a PrimaryTableName and/or a LimitColumn name if you want to use the Filter Wizard to create a limit query statement for an Axiom query. See the description of the LimitColumn parameter for more information. In this context, the primary table determines the tables available to limit the data in the Axiom query, based on the specified limit column. The user will first select a table, and then define a filter based on the selected table.
LimitColumn	Optional. Complete this parameter if you want to use the Filter Wizard to create a limit query statement for an Axiom query. Otherwise, leave this parameter blank to create a standard filter criteria statement. This parameter specifies the column to limit in the Axiom query. The column listed in this parameter must be present in the Axiom query. You can use fully qualified Table.Column syntax or just a column name: If a Table.Column name is specified, then that table is the primary table for the wizard. This means that you do not need to complete the PrimaryTableName parameter. When the limit query statement is created, the full Table.Column name will be listed in the Limit parameter. If only a column name is specified, then you must complete the PrimaryTableName field. When the limit query statement is created, only the column name will be listed in the Limit parameter. For more information, see Limit the data in an Axiom query based on another query.
DialogTitle	Optional. Specifies custom title text for the Filter Wizard dialog. If omitted, the standard title of "Filter Wizard" is used.

All non-numeric entries must be placed in double quotation marks, unless you are using cell references to reference the text held in another cell. However, the Boolean values TRUE and FALSE do not need to be in quotation marks.

Remarks

• The function uses the advanced view of the Filter Wizard dialog. It is not possible for the user to switch to simple view (hierarchies) when using this function.

• The function uses the web version of the Filter Wizard dialog. For more information, see Using the Filter Wizard.

• When the user clicks OK in the Filter Wizard to complete the filter creation, the filter is placed in the designated target cell, overwriting any current contents. If the target cell already has contents when the Filter Wizard is opened by the function, these contents are displayed in the Filter box (regardless of whether the contents comprise a valid filter criteria statement).

• The Axiom Double-Click setting does not need to be enabled for the sheet in order to use the ShowFilterWizardDialog function.

• The ShowFilterWizardDialog function is a non-volatile function.

Examples (standard filter criteria statements)

=ShowFilterWizardDialog("Filter Data","A10")

This example allows the user to create a filter using any client table in the system. The filter will be placed in cell A10 on the current sheet.

=ShowFilterWizardDialog("Filter Data","H","GL2019")

This example allows the user to create a filter that is valid for use against the GL2019 primary table. The wizard only shows the GL2019 table as well as any lookup reference tables (such as Dept and Acct). The filter will be placed in column H, in the same row as the cell containing the function.

=ShowFilterWizardDialog("Filter Data","+2","GL2019")

This example is the same as the previous example, except this time the filter will be placed in the column 2 cells to the right of the cell containing the function, within the current row. If the function is in cell C15, the filter would be placed in cell E15.

Examples (limit query statements)

=ShowFilterWizardDialog("Limit Query","A10",,"Dept.Dept","Limit Query")

This example allows the user to create a limit query statement for an Axiom query, to limit the data in the Dept column based on another table. The user will first select a table to limit the query by. The user can select any table with a lookup to the limit column Dept.Dept. Note that the dialog title also uses the custom text "Limit Query", as defined in the last parameter.

Then the user can create a filter on the selected table, to further limit the query. When the user clicks apply, the limit query statement is placed in the Filter box. The user can further modify the statement if needed, and then click OK to place the statement into cell A10 (the target cell in this example).

The limit query statement can then be referenced by the Limit query data based on another table option of an Axiom query. This is the only valid place to use a limit query statement.

=ShowFilterWizardDialog("Limit Query","A10","GL2019","Dept")

In this example, the primary table and the limit column are listed separately. The limit column is GL2019.Dept, which looks up to Dept.Dept. When the limit query statement is created, only the column name is listed for the Limit parameter, instead of a full Table.Column name. You might use this approach if you need the limit column to be deliberately ambiguous.