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
Table of Contents
⚠️ You must have System Admin access to create an Advanced Search Formula.
Creating an Advanced Search Formula
3. Click the Application tab, then click the Advanced Search Formula subsection. You will see already existing formulas on this screen.
4. On the Advanced Search Formula screen, click the New button in the upper right to create a new Advanced Search Formula.
5. The New Advanced Search Formula box will pop up and is ready to be filled out.
6. In the Search List field, select the search list you would like the advanced search formula to appear to be used on your list.
5. 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 as this field will become available to others in the company.
6. 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.
7. Select if the calculated field value should have any decimal numbers after it. It will default to 0 decimals.
8. To start building your formula, you can select a value from the Formula dropdown.
A) 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.
B) Pick an operator (plus, minus, etc.), and it will be added just like the field of ActHrs.
C) 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. For more information on these Date Function fields, click here.
10. Keep choosing fields, operators, or functions until your formula is complete.
9. 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.
10. 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!
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).
⚠️ Make sure to refresh your screen before adding the new field if you have just created the Advanced Search Formula!
1. 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.
2. 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.
3. 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.
4. 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
1. To apply this change as a new list view, click the three dot menu in the upper right and then Save As.
2. Name the list and include the purpose of the new list. We'll use Onsite Time Percentage Review.
3. 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.
4. When finished, click Save.
You now can choose it from your search lists in that specific module!