Formula Field Examples

Listed below are three examples of how formula fields are most commonly used: combining names, marking up a cost, and adding a number of days to a work order's Date Available. for PM work orders.

Combine Names

We'll call this example Person. This formula combines a person's last name and first name, with a comma separating the two names, so that the end result is a person's "last name, first name" in one column.

For this example, use a data source in the Work Ordersmodule.

1. Open the data source in which to use this formula by going to Data Sources > Query.

2. Once you open the necessary data source, click the Data Source Editor tab.

3. On the Designer sub tab, expand Related Tables > Assignments.

4. Drag the Resource Last Name and Resource First Name to the Column Area.

5. Hide both of these fields so that they will not display when the data source is generated.

6. Click Add Formula Field.

7. Select String Functions in the Functions and then STRINGPLUS.

8. A field titled Formula_1 appears in the column area. Click its Properties button.

9. In the Caption field, enter Person.

10. For String 1, click the toggle button next to the field and select Resource Last Name from the drop-down menu.

11. For String 2, enter a comma and a space (, ).

12. For String 3, the toggle button next to the field and select Resource First Name from the drop-down menu.

13. Click the Close button (the gray X in the upper-right corner of the Properties panel).

    In the data preview, your formula field will display information like this: Smith, Carl.

    Conversely, if you wanted to put the first name before the last name, follow steps 10 through 12 below.

14. For String 1, click the toggle button next to the field and select Resource First Name from the drop-down menu.

15. For String 2, enter a space.

16. For String 3, the toggle button next to the field and select Resource Last Name from the drop-down menu.

    This would display the person's name as Carl Smith.

Mark Up a Cost

We'll call the second formula example Charge Cost. In this formula, a work order's total cost is marked up by a percentage to be charged to a customer.

For this example, use a data source in theWork Orders module.

1. Open the data source in which to use this formula by going to Data Sources > Query.

2. Once you open the necessary data source, click the Data Source Editor tab.

3. On the Designer sub tab, expand Summary Data.

4. Drag Total WO Cost to the Column Area.

5. Hide the field so that it won't display when the data source is generated.

6. Click Add Formula Field.

7. Select Mathematical Functions and then MULTIPLIED.

8. A field titled Formula_1 appears in the column area. Click its Properties button.

9. In the Caption field, enter Charge Cost.

10. Select a Summary Function if necessary.

11. Select a Mask for the data if necessary.

12. For Number 1, click the toggle button next to the field and select TotalWOCost from the drop-down menu.

13. For Number 2,enter the multiplication factor. For example, if you want mark up the cost by 25%,  enter ".25".

14. Leave Number 3 blank.

15. Click the Close button (the gray X in the upper-right corner of the Properties panel).

Add on Days

We'll call the last formula example Due Date. In this formula, the Date Available of a work order has a number of days added to for PM work orders.

For this example, use a data source in theWork Orders module.

1. Open the data source in which to use this formula by going to Data Sources > Query.

2. Once you open the necessary data source, click the Data Source Editor tab.

3. On the Designer sub tab, expand Common Fields > Schedule.

4. Drag Date Available to the Column Area.

5. Hide the field so that it won't display when the data source is generated.

6. Click Add Formula Field.

7. Select Date Functions and then DATEADD.

8. A field titled Formula_1 appears in the column area. Click its Properties button.

9. In the Caption field, enter Due Date.

10. The Date Part field is where you select what you want to change: the day, week, month In this formula, we want to add 14 days to the date, so we will select Day from the drop-down menu.

11. In Value to Add, enter the number of days, months to add. We enter "14".

12. Click the Close button (the gray X in the upper-right corner of the Properties panel).