Advanced Field Settings Examples

Hyperlink to Open a ConnectWise Record

Use the Advanced Field Settings URL field to make any report cell open a ConnectWise record directly.

Generic URL pattern

/v4_6_release/services/system_io/router/openrecord.rails?locale=[locale]&companyName=[mycompany]&recordType=[recordtype]&recid={[FieldNumber]}

Build the URL

1. Paste the generic URL into a text editor.

2. Replace [locale] with your locale code. See Locale examples below.

3. Replace [mycompany] with the company name you use on your ConnectWise login screen.

   Note: Replace every instance of [companyName] in the URL — not just the first one.

4. Replace [recordType] with the appropriate record type. See Available record types below.

5. On the Fields tab of the report, add the recid field for the record type you want to link (e.g., company_recid for companies). This field can be hidden in Advanced Field Settings.

6. On the field you want to hyperlink (e.g., the company name field), select Advanced Field Settings.

7. In the URL field, paste your URL and replace [FieldNumber] with the field’s position number. Count only fields where the VG checkbox is not selected.

   Important: The URL is case sensitive. Leave the field Format blank — any additional formatting (such as a comma separator) will break the URL.

Available record types

• ActivityFV
• AgreementFV
• ServiceFV
• ContactFV
• CompanyFV
• OpportunityFV
• ProjectHeaderFV
• ConfigFV
• ScheduleRecordFV
• TimeEntryFV
• ExpenseFV

Required recid fields by record type

Record type	Field to add
Company	company_recid
Contact	contact_recid
Activity	so_activity_recid
Opportunity	opportunity_recid
Ticket	sr_service_recid or ticketnbr
Configuration	config_recid (or id in v_rpt_config)
Schedule Record	schedule_recid
Time Entry	time_recid
Expense Entry	expense_recid

URL examples

Company

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=CompanyFV&recid={2}

Contact

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=ContactFV&recid={2}

Activity

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=ActivityFV&recid={2}

Agreement

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=AgreementFV&recid={2}

Opportunity

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=OpportunityFV&recid={2}

Ticket

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=ServiceFV&recid={2}

Project

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=ProjectHeaderFV&recid={2}

Configuration

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=ConfigFV&recid={2}

Schedule Entry

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=ScheduleRecordFV&recid={2}

Time Entry

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=TimeEntryFV&recid={2}

Expense

/v4_6_release/services/system_io/router/openrecord.rails?locale=en_US&companyName=mycompany&recordType=ExpenseFV&recid={2}

Locale examples

Locale	Code
English / United States	en_US
English / United Kingdom	en_UK
English / Australia	en_AU
English / New Zealand	en_NZ
English / South Africa	en_ZA

Note: If you use these URLs in email notifications, replace the field number with the email token [itemrecid] or equivalent. Only licensed ConnectWise member accounts can open records using these links.

Hyperlink to Open Email

Make a field open a new email when selected.

1. On the Fields tab of the report, add the field containing the email address.
2. Select Advanced Field Settings.
3. In the URL field, enter: javascript:window.location.href='mailto:{0}'
4. Replace 0 with the field number of the email address field. Count only fields where the VG checkbox is not selected.

Text Highlight

Highlight cell text color based on a numeric range.

1. On the Fields tab, select Advanced Field Settings on the field you want to highlight.

2. In the Text Highlight field, enter your criteria using this syntax:

   # to #:COLOR; # to #:COLOR

   Example: 0 to 100:Red;101 to 150:Green

• Spaces do not affect results.
• Hex color codes work in place of color names.

Cell Highlight

Highlight cell background shading based on a numeric range.

1. On the Fields tab, select Advanced Field Settings on the field you want to highlight.

2. In the Cell Highlight field, enter your criteria using this syntax:

   # to #:COLOR; # to #:COLOR

   Example: 0 to 100:Red;101 to 150:Green

• Spaces do not affect results.
• Hex color codes work in place of color names.

Value Ranges

Return a defined label based on a numeric range. Useful for bucketing data — for example, grouping invoice aging into bands.

Syntax: value to value:Label; value to value:Label

Example — invoice aging buckets:

0 to 30:Up To 30;31 to 60:31 to 60;61 to 90:61 to 90;100 to 9999:100+

Separate the value range and label with a colon. Separate each range section with a semicolon.

Expressions

Expressions let you compute new values from existing fields. Build them in Advanced Field Settings for the target field.

Tip: Always wrap field names in brackets for consistency: [field_name]. While not always required, it prevents issues in edge cases.

When using an expression, a field must be selected in the Field drop-down. Select one of the fields used in the expression, then update the Description to the label you want displayed.

Static values

Return a fixed string by wrapping it in single quotes:

'Abc'

Arithmetic

Remaining hours:

[hours_budget] - [hours_actual]

Gross margin:

([unit_price] - [unit_cost]) / [unit_price]

Difference between two dates:

(CONVERT(INT, [date_closed] - [date_entered]))

Case statements

Case statements evaluate a list of conditions and return a result for the first match.

Example — billing code labels:

(CASE
  WHEN [billing_code] = 'F' THEN 'Fixed Fee'
  WHEN [billing_code] = 'A' THEN 'Actual'
  WHEN [billing_code] = 'O' THEN 'Override Rate'
  ELSE 'Unknown'
END)

Field type rules for comparisons:

• Text fields: use single quotes — [billing_code] = 'F'
• Numeric fields: no quotes — [invoice_amount] > 1000

Convert boolean (0/1) to Yes/No:

(CASE WHEN [Closed_Flag] = 1 THEN 'Yes' ELSE 'No' END)

Return zero when budget is empty:

(CASE WHEN [hours_budget] > 0 THEN ([hours_budget] - [hours_actual]) ELSE '0' END)

Using a function inside a case statement — wrap the entire expression in parentheses:

(CASE WHEN (ROUND([AGE],0)) > 7 THEN '>7 Days Old' ELSE '' END)

Reporting on date custom fields

To report on a date stored in a custom field:

CAST([field_name] AS DATE)

Example — a custom field named Custom_Date:

CAST([Custom_Date] AS DATE)

Select Date or DateTime from the Expression type drop-down in Advanced Field Settings to ensure proper formatting.

Tip: To use a calculated date field as a filter, select [field_name](calc) from the Filter Field on the Filters tab.

Supported functions

Function	What it does	Example
CHARINDEX	Returns the starting position of one string within another.	CHARINDEX('5354',[Item_ID],1)
COUNT	Returns a count of records.	COUNT([TicketNbr])
DATEADD	Adds an interval to a date.	DATEADD(dd,45,[Date_Entered])
DATEDIFF	Returns the count of date parts between two dates.	DATEDIFF(dd,[Date_Entered],[Date_Closed])
DATENAME	Returns a string for a date part.	DATENAME(Month,[Date_Entered])
DATEPART	Returns an integer for a date part. Supports: YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, HOUR, MINUTE, SECOND, and abbreviations.	DATEPART(yy,[date_closed])
DAY	Returns the day of the month as an integer.	DAY([Date_Closed])
GETDATE( )	Returns the current database system timestamp. Note the space inside the parentheses.	GETDATE( )
GetLocalTimeFromUTC	Converts UTC to local time. Mode 1 = company time zone, Mode 2 = named time zone, Mode 3 = member time zone.	Mode 1: udf_GetLocalTimeFromUTC(getdate(),1,'') Mode 2: udf_GetLocalTimeFromUTC(getdate(),2,'Pacific Standard Time') Mode 3: udf_GetLocalTimeFromUTC(getdate(),3,'CBosse')
formatGlobalPhone	Formats a phone number by country. Available: United Kingdom, UK, England, Ireland, New Zealand, Great Britain, US.	udf_formatGlobalPhone([PhoneNbr],'CountryName')
ISNULL	Returns a fallback value when the field is null.	ISNULL(Userfield_1,'No Data')
LEFT	Returns the leftmost N characters of a string.	LEFT([detail_description],23)
LEN	Returns the length of a string.	LEN([company_name])
LTRIM	Removes leading spaces.	LTRIM([userfield_1])
MAX	Returns the maximum value.	MAX([hours_actual])
MIN	Returns the minimum value.	MIN([hours_actual])
MONTH	Returns the month as an integer.	MONTH(date_entered)
REPLACE	Replaces all occurrences of a string.	REPLACE([Company_Address1],'Suite','Ste')
RIGHT	Returns the rightmost N characters of a string.	RIGHT([Serial_Number],4)
ROUND	Rounds to a specified number of decimal places.	ROUND([hours_actual],1)
RTRIM	Removes trailing spaces.	RTRIM([userfield_1])
SUBSTRING	Returns a portion of a string.	SUBSTRING([Urgency],1,4)
SUM	Sums all values.	SUM([hours_actual]+[billable_hrs]) or (SUM([hours_actual])+SUM([billable_hrs]))
YEAR	Returns the year as an integer.	YEAR([Date_Entered])

Subtotals

1. Go to System > Report Writer and select a report.
2. Select the Open in designer icon.
3. Go to the Fields tab and verify your fields are configured correctly.
4. Select the Add Subtotals checkbox to enable the subtotals function.
5. Select Advanced Field Settings next to the field you want the subtotal applied to. The Advanced Properties dialog appears.
6. Select the Subtotal Function for that field. Report Writer selects a function automatically based on field type, but you can override it.
7. Select OK.