Developing filters

When you define more than one filter, by default the join uses AND, which narrows the results. However, if you add multiple filters on the same object Property (such as StateProvince), by default the filters join with an OR connective, which broadens the results. Therefore, to use the same object Property in an AND join, create those filters separately. For example, to define a filter to obtain customer records for all customers except those in TX and VA, construct a filter set with two filters using the AND connector:

[StateProvince] <> 'TX' AND [vBoCsContact].[StateProvince] <> 'VA'

• A Multiple checkbox is visible when the filter Property contains a discrete drop-down list of values. Selecting this checkbox changes the Value selector from a simple drop-down list that enables you to select only one specific value to a multi-select control that enables you to select multiple values. The selected values are used as an Or comparison. See Defining a filter property with several values for more information.

Note: Even if the Multiple checkbox is not visible, you can still manually specify multiple values for any filter property by entering a comma-delimited list of quoted values. For example, you could specify the following multiple values for a City property: "Toronto","Montreal","Calgary"

• Require the user to provide at least one valid value: User must enter a valid filter value before the query can be run.

In Advanced mode, you can create a parameterized query, which allows (or requires) users to specify a value (such as "state = FL") to narrow their search. This lets other staff members run a standard query, changing a runtime parameter value each time to get the results they need. Such queries are useful for organizations where limited number of users might create the actual queries.

You can set an advanced filter on a query to set a hard limit or a default value for the number of returned rows.

Do the following to limit the number of rows returned by a query:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. (Filters tab) Select Advanced mode.
3. Select the Limit the number of results returned option.
4. Enter the Value for the maximum number of rows to display.

Note: To create a top n records query result, specify at least one property on the Sorting tab.

When a filter Property contains a drop-down list of values, you can select a single value from the list or you can select more than one. The selected values are used as an Or comparison.

Do the following to define multiple values for a filter property:

1. Go to RiSE > Intelligent Query Architect > New > Query.
2. Enter a Name and add Sources.
3. (Filters tab) Select Advanced mode. A Multiple column is displayed between the Comparison and Value columns. For properties that have a discrete list of values, a checkbox displays in the Multiple column.
4. Select a property that has a discrete drop-down list of values, such as State Province.
5. Select the Multiple option.
6. A default Value displays. Browse to select another value.
7. From the Available Values list, select the values that you want to use in the filter. You can multi-select values by holding Shift or Ctrl while selecting values with your mouse.
8. Click OK. The selected values appear in the Value column. 
9. Click + to add the filter. You can add additional filters to your query. Additional filters can be reordered using the up/down arrows.

Note: You cannot move filters between filter sets.

Intelligent Query Architect enables filters for comparing properties from the same source.

Depending on the Comparison operator you selected, you can compare properties from the same database source in the Value column. Select the Property value in the drop-down. You can then compare that property against additional properties with a compatible data type from the same database source. Select your additional properties.

You can dynamically filter your query results. The dynamic filters are available in the Value column.

If the property supports it, you can select Dynamic from the drop-down in the Value column. The following options are available for contact related properties:

• LoggedIn User Key - Dynamically filters the query to display results specifically for the logged in user. Uses the UserKey field from tables such as ContactMain. Replaces the old "@Me" filter.
• Selected Contact - Dynamically filters the query to display results specifically for the selected contact, which is identified by an ID in the URL. If there is no ID, then the selected contact is the On Behalf Of contact. If there is no On Behalf Of contact, then the selected contact is the logged-in user.
• Selected ID - Dynamically filters the query to display results specifically for the selected ID, which are logged in users. Uses the ID from tables such as Name.

The following options are available for date related properties:

• Current Date - Dynamically filters the query to only display results for the current date.
• Current Date Time - Dynamically filters the query to only display results for the current date and time.

Events with a Begin and End time are not picked up using the Current Date or Current Date Time dynamic values.

Example: CsEvent.BeginDate is a DateTime property. An event’s Begin Date is 04/01/2023 at 9:00 a.m., and a query is created on the same day that has CsEvent.BeginDate added as a filter equal to Current Date. This query does not return any results, because iMIS is searching for an event Begin Date equal to 04/01/2023 at 12:00 a.m. Instead, the query filter should be updated to Within Days = 0. This will not affect events that do not have a defined start time.

For example, the event dashboard page includes a parameter in the URL called EventCode. To build a query that uses this EventCode as a filter, add a filter to the query that sets CsEvent.EventCode equal to EventCode. If the property supports it, you can select URL params from the drop-down in the Value column. You can then enter the URL parameter in the text field.

Tip! It is not necessary to prepend "@url:" for this filter. You can enter a parameter, for example, EventCode in the URL params field, and iMIS will parse this filter correctly.

Notes

• If you are prepending "@url:" to your URL parameter, you must enter the quotation marks where indicated when using the URL parameter.
• If you are building a query for an alert, make sure the Prompt is set to No. This is required in order to avoid the filter being removed from automated alerts.
• When creating filters using date properties, such as Join Date or Date Added, do not use URL params. Instead, use Dynamic values.