Advanced Search Formula Screen
  • 08 Sep 2022
  • PDF

Advanced Search Formula Screen

  • PDF

As a system administrator, The Advanced Search Formula screen enables you to define or edit the definitions of formula fields. Formula fields that you create using this screen are made available in the search list specified by the Formula Type field. The formula field is defined by performing mathematical operations using other existing numeric fields or date fields that exist in the search list for which the formula field is being created. Calculated values resulting from the formula must be numeric – they can not be a date or string values.

The Advanced Search Formula screen is accessed from the Advance Search Formulas Search List screen by clicking the New Advanced Search Formula button or by clicking an existing item in the search list.

Each of the screen elements on the Advanced Search Formula screen is described in the following table.

Screen ElementDescription
Search ListRequired. Enables you to choose from a list of the Aspire search lists to determine the search list for which the new formula field will be available. The selected search list also determines what fields are available for calculating the value of the newly created formula field.
Formula NameRequired. Determines the name of the field in the target search list.
Display FormatSpecify a display format as described in the section, Display Formats.
Formula Areaa

Required. This area helps you with entering a mathematical formula describing how to calculate the formula field value based on other numeric fields in the target search list. The drop-down field allows you to select fields available for the calculation. When you choose an item from the drop-down, the field will be inserted into the text boxb at the point of the cursor. If you click one of the symbol buttonsc, the symbol will be inserted into the calculation at the point of the cursor. Parenthesis can be used to specify the order in which the arithmetic operations should be performed to calculate the value.

The values available in the drop-down list are specific to the formula type.

Date FunctionsSee below.

Date Functions

Aspire provides the ability to perform date calculations on date fields when creating custom search list columns. Three date calculation functions are provided as outlined in the following table. As will be explained further below, Aspire guides you to provide the proper values for the parameters for their selected function. The guide for each date function is also depicted in the table.

Date FunctionDescriptionParameters
DATEADDEnables you to add a numeric value to a date to return a new date. You can specify a date part that determines the unit of time to be added (i.e. days, weeks, months, etc.)

Date Part – Value that specifies how the numeric time value will be interpreted. Can be any of the following: year, quarter, month, week, day.

Time Value – Numeric value representing the time period based on the specified date part.

Date – Date to which the time value is added to return a new date.

DATEDIFFReturns a numeric value representing the time difference between two dates. You can specify a date part that determines the unit of time such (i.e. days, weeks or months, etc.). 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 Part – Value that specifies the units to be counted between the start and end date. Can be any of the following: year, quarter, month, week, day.

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

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

DATEPARTReturns a numeric value representing a certain part of the date (i.e. month, day, etc.). For example, this would enable you to return the numeric value representing the quarter of the year so that you could group the records in a search list by quarter.

Date Part – Value that specifies the units separated out into a numeric value for display. Can be any of the following:

  • 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 – Start date of the period whose duration will be returned by the function.

The controls for adding these functions are available at the bottom of the Advanced Search Custom Formula screen and are displayed when you select the desired function.

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 clicks the Insert Function icond.