Advanced Search Formula Screen
  • 16 Feb 2024
  • PDF

Advanced Search Formula Screen

  • PDF

Article Summary

As a system administrator, you can use the Advanced Search Formula screen to define or edit formula fields. The formula fields you create here are accessible in thesearch list via theFormula Type field. These fields are defined by conducting mathematical operations on existing numeric or date fields within the relevant search list. 

Note:
The outcomes of these formulas are strictly numeric. They cannot be dates or strings.


To access the Advanced Search Formula screen, go to the Advance Search Formulas Search List screen and select the New Advanced Search Formula button or select an existing item in the search list.

The following table describes the screen elements on the Advanced Search Formula window.

Screen ElementDescription
Search ListRequired. Select from various Aspire search lists to identify where your new formula field will appear. Your selection also determines which fields you can use to compute the value of this new formula field. 
Formula NameRequired. Specify the display name of your formula field in the selected search list. 
Display FormatFrom the drop-down list, select a display format following the guidelines in the Display Formats topic.
Formula Areaa

Required. Use this area to create a mathematical formula. This formula area describes how to calculate your formula field value using other numeric fields within your selected search list. From the Formula dropdown list, select a field available for calculation or click a symbol buttonc to add it to your formula. Aspire inserts your selections into the text boxb at your cursor's location. Use parentheses to clarify the order of operations in your calculation. 

Note:
The Formula dropdownlist values vary depending on the Search List formula type.
Date FunctionsSee the following section.

Date Functions

Aspire enables date calculations on date fields for custom search list column creation. Three date calculation functions are available, and Aspire guides you to input proper values for your selected function's parameters. The following table describes each function and its parameters.

Date FunctionDescriptionParameters
DATEADDEnables you to add a specific unit of time, such as days, weeks, or months, to a given date to calculate the resulting numerical difference or interval. 

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.

DATEDIFFReturns a numeric value representing the time difference between two dates. You can specify a date part that determines the unit of time, such as days, weeks, or months. For example, this enables you to create a field showing the number of days between the opportunity start date and the opportunity end date as a column in the opportunity search list.

Date PartA 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.

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

End Date – The end date of the period whose duration will be returned by the function.

DATEPARTReturns 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.

The controls for these functions are located at the bottom of the Advanced Search Custom Formula screen. Select a function to configure the parameters.

To add the function to the formula areaa, you select the desired functionb, specify the appropriate parametersc for what you are seeking to accomplish, and click the Insert Function icond.


Was this article helpful?

Changing your password will log you out immediately. Use the new password to log back in.
First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.