IQA reference sheet

Use the SQL Expression field on the IQA Display tab to style various query properties.

Before you begin: Troubleshooting

Before beginning, review the following troubleshooting tips.

Specifying the source to avoid ambiguity

In many cases, there is more than one business object defined in a query. If the business objects have properties with the same name and those properties are included in the SQL expression, you must specify which business object (source) you are referring to by using the following syntax: [source].[property name].

Review the SQL output on the Summary tab of the IQA to find the exact source name. In this example, the CsEvent and CsRegistration business objects are used in the query. The source names are vBoCsEvent and vBoCsRegistration. If I needed to reference the Event Code from the CsEvent buisness object, I would need to specify the source, since both business objects contain an Event Code property: vBoCsEvent.EventCode

Date and time

Display dates and times using any of the following styling formats.

Tip! For a list of all available date and time formats, see the Microsoft Documentation.

Returning only the year

Update a date property to only return the year using this syntax: YEAR(PROPERTYNAME)

• Example Input: YEAR(BIRTHDATE)
• Example Output: 1989

Note: To return the fiscal year, see Reporting using the fiscal year.

Changing the date display

Update the way date properties are displayed using this syntax: FORMAT(PROPERTYNAME, 'DesiredDateFormat')

Example Input	Example Output
FORMAT(BIRTHDATE, 'MMMM dd, yyyy')	December 04, 2024
FORMAT(BIRTHDATE, 'dddd, MMM dd, yyyy')	Wednesday, December 04, 2024
FORMAT(BIRTHDATE, 'yyyy-MM-dd')	2024-12-04

Changing the time display

Update a property that contains a time using this syntax: FORMAT(PROPERTYNAME, 'DesiredTimeFormat')

Example Input	Example Output
FORMAT(UPDATEDON, 'hh:mm tt')	04:13 PM
FORMAT(UPDATEDON, 'MMM dd, yyy, hh:mm:ss tt')	Sep 10, 2024, 04:13:19 PM
FORMAT(UPDATEDON, 'HH:mm')	16:13

Changing the date and time display

Update a property that contains a date and time using this syntax: FORMAT(PROPERTYNAME, 'DesiredDateFormat, DesiredTimeFormat')

Example Input	Example Output
FORMAT(UPDATEDON, 'yyyy-MM-dd, HH:mm')	2015-04-13, 16:28
FORMAT(UPDATEDON, 'dddd, MMM dd, yyyy, hh:mm tt')	Monday, Apr 13, 2015, 04:28 PM
FORMAT(UPDATEDON, 'yyyy-MM-dd, HH:mm:ss')	2015-04-13, 16:28:53

Text

Update any IQA property using these text formatting options.

Type	Syntax	Example Input	Example Output
Bold	CONCAT('<b>', PROPERTYNAME, '</b>')	CONCAT('<b>', FULLNAME, '</b>')	Mr. Bernard H. Bufton
Italics	CONCAT('<i>', PROPERTYNAME, '</i>')	CONCAT('<i>', BIRTHDATE, '</i>')	Jan 30 1965 12:00AM
Underline	CONCAT('<u>', PROPERTYNAME, '</u>')	CONCAT('<u>', ID, '</u>')	22683
Color	CONCAT('<p style="color: color name;">', PROPERTYNAME, '</p>')	CONCAT('<p style="color: red;">', CHAPTER, '</p>')	NORTH
Color (using Hex codes)	CONCAT('<p style="color: #HexCode;">', PROPERTYNAME, '</p>')	CONCAT('<p style="color: #00A9E0 ;">', COUNTRY, '</p>')	United States
Color (using RGB codes)	CONCAT('<p style="color: rgb(#, #, #);">', PROPERTYNAME, '</p>')	CONCAT('<p style="color: rgb(0, 255, 0);">', CITY, '</p>')	Johnstown
Highlight	CONCAT('<mark>', PROPERTYNAME, '</mark>')	CONCAT('<mark>', FULLADDRESS, '</mark>')	PO Box 90481 Johnstown, PA UNITED STATES
Strikethrough	CONCAT('<s>', PROPERTYNAME, '</s>')	CONCAT('<s>', EMAIL, '</s>')	bernardbufton@mailinator.com

Hyperlinks

The Hyperlink field located on the Display tab of an IQA query supports adding links. For details on adding basic links using the Hyperlink field, see the following:

• Adding links to IQA results
• Adding filters and links

An alternative option for adding links is through the SQL Expression field, also located on the IQA Display tab.

The following examples demonstrate how to create hyperlinks using a shortcut URL, ContentCode, NavigationCode, or an ASPX web page.

Opening a link in a new tab

The default behavior for any link is to open the link in the same tab. Create a link that opens in a new tab using the following syntax: CONCAT('<a href="hyperlink"> target="_blank">Link text</a>')

• Example Input: CONCAT('<a href="/Party.aspx?ID=', ID, '" target="_blank">Profile </a>')

Note: NetContactData is the only source in this query.

• Example Output: Profile



• Example Input: CONCAT('<a href="/EventDetail?EventKey=', vBoCsEvent.EventCode, '" target="_blank">Event Link</a>')

Note: The CsEvent and CsRegistration business objects are both used as data sources in this query, and each contains an EventCode property. To avoid ambiguity, you must explicitly specify which source to use when referencing the EventCode property (e.g., vBoCsEvent.EventCode in the example above). This rule also applies to any other business objects that share the same property name.

• Example Output: Event Link

Creating an email link (email address is displayed)

To create an email link where the email address is both displayed and functional, use the following syntax: CONCAT('<a href="mailto:', Email, '">Text before email address: ', Email, '</a>')

• Example Input: CONCAT('<a href="mailto:', Email, '">Email: ', Email, '</a>')
• Example Output: Email: example@example.com

Creating an email link (email address is not displayed)

To create an email link where the email address is functional but not displayed, use the following syntax: CONCAT('<a href="mailto:', Email, '">Text before email address: ', Text shown, '</a>')

• Example Input: CONCAT('<a href="mailto:', Email, '">Email</a>')
• Example Output: Email

Creating a call link (number is displayed)

Phone numbers can be transformed into clickable links. On mobile devices, tapping these links prompts the user to confirm if they would like to call the number. To create a telephone link where the phone number is both displayed and functional, use the following syntax: CONCAT('<a href="tel:', PhoneProperty, '">Text before phone number: ', PhoneProperty, '</a>')

• Example Input: CONCAT('<a href="tel:', MobilePhone, '">Mobile Phone: ', MobilePhone, '</a>')
• Example Output: Mobile Phone: (512)555-1234

Creating a call link (number is not displayed)

To create a telephone link where the phone number is functional but not displayed, use the following syntax: CONCAT('<a href="tel:', PhoneProperty, '">Text shown</a>')

• Example Input: CONCAT('<a href="tel:', MobilePhone, '">Mobile Phone</a>')
• Example Output: Mobile Phone

Changing a link to a button

A button link is a hyperlink styled to look like a button. To create one, apply a CSS class that changes the link's appearance to match a button. To review the out-of-the-box button styles, go to RiSE > Style Guide.

To change a link into a button, use the following syntax: CONCAT('<a href="hyperlink"> class="CSS Class Here">Link text</a>')

Note: If you are using the Hyperlink field, use the <span> tag.

• Example Input: CONCAT('<a href="/Party.aspx?ID=', ID, '" target="_blank" class="TextButton PrimaryButton">Profile</a>')
• Example Output: Profile

Addresses

Display addresses using any of the following styling formats.

Note: The following examples use the CsAddress and NetContactData business objects, joined on the ID property.

city and state/province

To return only the city and state/province, separated by a comma, use the following syntax:

CONCAT( 
    vBoCsAddress.City, 
    CASE  
        WHEN vBoCsAddress.City IS NOT NULL 
             AND vBoCsAddress.StateProvince IS NOT NULL 
             AND vBoCsAddress.City <> '' 
             AND vBoCsAddress.StateProvince <> '' 
        THEN ', '  
        ELSE ''  
    END, 
    vBoCsAddress.StateProvince 
)

formatting an address on multiple lines

To format a member’s address to display on multiple lines, use the following syntax:

CONCAT(
    vBoCsAddress.Address1, 
    '<br>', 
    vBoCsAddress.City, 
    ', ', 
    vBoCsAddress.StateProvince, 
    ' ', 
    vBoCsAddress.Zip, 
    '<br>', 
    vBoCsAddress.Country
)

Images

Review the following information for help with various image outputs in IQA queries:

• Displaying profile images
• Displaying images uploaded through panels
• Adding image thumbnails to query results