Developing filters

Quick Tutorial Full Training

The query Filters tab allows you to narrow down the type and number of rows returned in a query.

Add Conditions button

If a query includes multiple sources, the Filters tab will display an Add Condition button, allowing users to specify additional conditions. The following conditions are available:

• And - Returns results that are "true" for all conditions specified. It is used to narrow down the results and retrieve records where all combined conditions are met.
• And Not - Refines results by combining conditions where the first part must be true and the second part must be false for a record to be included in the results.
• Or - Used to broaden the search to include rows that meet any of the specified conditions.
• Or Not - Used to combine conditions where you want to include rows that either do not meet a specific condition or meet another condition.

Use the up and down arrows to specify the order in which to apply a filter.

Multiple checkbox

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.

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"

In Search?

The In Search? drop-down allows you to add a search filter to the query, so that finding specific results in the query may be easier. 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.

The following results are available:

• No - Property is not added as a search filter.
• Optional - Property is added as a search filter but is optional and not required to execute query.
Click to view image



• Required - Property is added as a search filter and is required to have a value before query is executed.
Click to view image

Limit the number of results returned

The Limit the number of results returned checkbox is an advanced filter that will set a hard limit or default value for the number of returned rows. In the provided checkbox, enter a number for the maximum rows to display.

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

Value options

The Value drop-down sets the filter value for the selected property. The following options are available:

• Constant - Chosen property is set to a constant value.
• Property - Allows you to compare the filter property with a property selected 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.
• Dynamic - 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] BeginDateadded 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.

• URL param - 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 In Search? 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.