Creating a Custom Field for Your Lists with Advanced Search Formulas

Purpose

One of the great things about Aspire is how flexible search lists can be! Even with the many fields that you can filter, display, sort, and group with, you even have the ability to calculate fields together, creating a whole new custom field! That new field can then be used in certain Aspire search lists!

This can be useful in all sorts of cases including:

Estimated Material Cost vs. Actual Material Cost
Days Since Opened Issue
Over/Under at the Opportunity Level
Total Cost of Inventory

Requirements

⚠️ You must have System Admin access to create an Advanced Search Formula.

Creating an Advanced Search Formula

To get to User Roles , click on your profile icon in the bottom left corner.
Click Administration.
Click the Application tab, then click the Advanced Search Formula subsection. You will see already existing formulas on this screen.
On the Advanced Search Formula screen, click the New button in the upper right to create a new Advanced Search Formula. The New Advanced Search Formula box will pop up and is ready to be filled out.
In the Search List field, select the search list you would like the advanced search formula to appear to be used on your list.
Create a name for the new field in the Formula Name field. This is what will show in the search list if displayed, grouped, or filtered.
Note
It's important to be specific with your advanced search formula because this field will become available to others in the company.
Select the Display Format type. This can be a number, currency ($), or percentage (%). For this example, we want to look at the percentage of onsite time our crews have when looking at actual hours minus their drive time.
Select if the calculated field value should have any decimal numbers after it. It will default to 0 decimals.
To start building your formula, you can select a value from the Formula dropdown.
1. Once you select the field from the Formula dropdown, it will appear in the text box below. You'll have the ability to type and use your cursor in the Formula text box as well.
2. Pick an operator (plus, minus, etc.), and it will be added just like the field of ActHrs.
3. You can also add a date function to your formula from below the textbox. The three functions are DATEADD, DATEDIFF, DATEPART and will show additional fields to fill out. Refer to the Date Functions section below to learn the specifics of each one.
Keep choosing fields, operators, or functions until your formula is complete.
Review your formula. Especially if it is your first time, make sure you are following the order of operations. If you need to add or edit, click in the text box to do so.

With the formula reviewed, select Save. Afterwards, refresh your screen. Your Advanced Search Formula is now available to use as a new field in your selected list!

Date Functions

If you want to use one of the Date functions in your advanced filter, there are some more details you need to know. The following table describes each function and its parameters.

Date Function	Description	Parameters
DATEADD	Allows you to add a numeric value to a date, which returns a new date. You can set which Date Part that helps determine the unit of time to be added (examples: days, weeks, months, etc.) Note: Custom formulas must return numeric values, so this function cannot be used alone in a custom formula. It must be used on combination with other available functions that produce numeric results.	Date Part – A value that specifies the unit of time for the calculation. For example, year, quarter, month, week, or day. This selection dictates how the system interprets the added time value. Time Value – A number indicating how many units of the specified Date Part you wish to add to your start Date. This value is integral to calculating the numeric outcome. Date – The start date to which the Time Value is added. The resulting calculation does not produce a new calendar date but a numeric value representing the interval based on your inputs.
DATEDIFF	Returns a numeric value representing the difference between two dates based on the unit of time you select. DATEDIFF is useful when creating custom fields that show durations, such as the number of days between start and end dates. How the function works: DATEDIFF measures boundary crossings, not the number of fully elapsed units. For example, the difference between January 31 and February 1 is 1 month when using DATEDIFF(month) because a month boundary is crossed, even though only one day has passed. Common uses: Days between opportunity start and end dates Days between ticket creation and completion Months between contract start and renewal dates Example: `DATEDIFF(day, [StartDate], [EndDate])` To insert in a formula: Select the function, choose the Date Part, select the Start Date and End Date fields, then select Insert Function. Important: DATEDIFF always returns a numeric value, not a new date or text.	Date Part – Specifies the unit of time for the calculation. Options include year, quarter, month, week, day, hour, minute, or second. The Date Part determines how Aspire interprets the difference between the two dates. Start Date – The beginning date for the calculation. End Date – The end date for the calculation.
DATEPART	Returns a numeric value that represents a certain part of a date, such as a month or day. For example, this function enables you to return a numeric value representing a quarter of the year so that you can group the records in a search list as such.	Date Part – A value that specifies the units separated into a numeric value for display. Choose from: year – Return a numeric value representing the year of the date. quarter – Return a numeric value representing the quarter of the year represented by the date. month – Return a numeric value representing the month of the date. week – Return a numeric value representing the week of the year represented by the date. day – Return a numeric value representing the day of the date. dayofyear – Return a numeric value representing the day of the year represented by the date. Start Date – The start date of the period whose duration will be returned by the function.

Date Function

Description

Parameters

DATEADD

Allows you to add a numeric value to a date, which returns a new date. You can set which Date Part that helps determine the unit of time to be added (examples: days, weeks, months, etc.)

Note: Custom formulas must return numeric values, so this function cannot be used alone in a custom formula. It must be used on combination with other available functions that produce numeric results.

Date Part – A value that specifies the unit of time for the calculation. For example, year, quarter, month, week, or day. This selection dictates how the system interprets the added time value.

Time Value – A number indicating how many units of the specified Date Part you wish to add to your start Date. This value is integral to calculating the numeric outcome.

Date – The start date to which the Time Value is added. The resulting calculation does not produce a new calendar date but a numeric value representing the interval based on your inputs.

DATEDIFF

Returns a numeric value representing the difference between two dates based on the unit of time you select. DATEDIFF is useful when creating custom fields that show durations, such as the number of days between start and end dates.

How the function works:
DATEDIFF measures boundary crossings, not the number of fully elapsed units. For example, the difference between January 31 and February 1 is 1 month when using DATEDIFF(month) because a month boundary is crossed, even though only one day has passed.

Common uses:

Days between opportunity start and end dates
Days between ticket creation and completion
Months between contract start and renewal dates

Example:
DATEDIFF(day, [StartDate], [EndDate])

To insert in a formula: Select the function, choose the Date Part, select the Start Date and End Date fields, then select Insert Function.

Important:

DATEDIFF always returns a numeric value, not a new date or text.

Date Part – Specifies the unit of time for the calculation. Options include year, quarter, month, week, day, hour, minute, or second. The Date Part determines how Aspire interprets the difference between the two dates.
Start Date – The beginning date for the calculation.
End Date – The end date for the calculation.

DATEPART

Returns a numeric value that represents a certain part of a date, such as a month or day. For example, this function enables you to return a numeric value representing a quarter of the year so that you can group the records in a search list as such.

Date Part – A value that specifies the units separated into a numeric value for display. Choose from:

year – Return a numeric value representing the year of the date.
quarter – Return a numeric value representing the quarter of the year represented by the date.
month – Return a numeric value representing the month of the date.
week – Return a numeric value representing the week of the year represented by the date.
day – Return a numeric value representing the day of the date.
dayofyear – Return a numeric value representing the day of the year represented by the date.

Start Date – The start date of the period whose duration will be returned by the function.

Adding the Advanced Search Formula to Your List

Now that we've created our Advanced Search Formula, we need to add the field to our list (and save it if we like it).

Note
Make sure to refresh your screen before adding the new field if you have just created the Advanced Search Formula!

Go to the list where the new custom field was created. This would have been from the search list you chose when building your Advanced Search Formula. In this example, we'll be navigating to the Work Tickets module.
Select the search function where the new field should be applied. In this example, we'll be adding our Onsite Time percentage field to the Display function.

Add any other additional formatting and click Apply. The field will be displayed in the last column. You can move it by dragging the custom field's column header and dropping it where need.
Add or edit any other filter, display, sort, or group to get the most value out of your list. Since this custom field looks at actual time and drive time, we might filter to only completed work tickets. We'll also remove any 0% values.

We'll also sort by lowest percentages first, as these work tickets have the highest priority to be reviewed. To do that, we can just click on the column header. An arrow pointing up will show the list now sorted from least to greatest.

Saving Your New List

To apply this change as a new list view, click the three dot menu in the upper right and then Save As.

Name the list and include the purpose of the new list. We'll use Onsite Time Percentage Review.
You can make this your default view when in the module by checking the My Default box. You can also share this list with other roles or users, but only you will be able to make changes to this specific search list.
When finished, click Save.

You now can choose it from your search lists in that specific module!