Bonus Exercise 2: Adding calculations

Duration: 30 minutes

In this bonus exercise, you will learn how to use Excel like formulas to add cost calculations for a travel request. Our goal here is to use a per diem rate multiplied by number of days, added to the estimated airfare cost to get an estimated trip total cost.

Part 1: Creating our formulas

1. Navigate back to your App Home screen if you are not currently on it, then click the Travel request table to open table builder

   

2. Click the Forms button to get to form builder

3. Click Add a field in the table

   

4. In the pop-up, enter Travel days in Column label, and change Type to Integer. Leave the auto-populated field under Column name

   

5. Click Add

6. Click Add another one

   

7. Enter Estimated trip cost in Column label, and change Type to Decimal. Leave the auto-populated field under Column name

   

8. Click Add

9. Click Done

10. You should notice now on the left sidebar two form elements highlighted with a purple left border. These are your new fields you added

11. Drag and drop the Travel days and Estimated trip cost form elements onto your form

    

12. Click Save

13. Click the Travel days form element

14. On the rightside bar, you should see the options change

15. Click Formula

16. Click Add

    

17. In the pop-up Formula box, enter the following formula:

    TIMEDIFF(return_date,departure_date)

    

18. Click Submit

19. On the top right, click Save

20. Click Estimated trip cost

21. Click Formula, then Add

22. In the pop-up Formula box, enter the following formula:

    SUM(estimated_airfare,MULTIPLY(travel_days, 150))

    

    Our formula here takes into account that each travel day, the employee is given a $150 per diem

23. Click Submit

24. On the top right, click Save

Part 2: Testing our formulas

1. Go back to App Home

   

2. Click PREVIEW on the Travel request row

3. A new tab opens up to show the list of Travel requests

   

4. We did not format this list view back in Exercise 1, so let’s go ahead to do that quickly

5. Right-click anywhere on one of the column headers

6. Click List Layout under Configure

   

7. On the right Selected section, remove everything except Number and State. Do this by double-clicking on each line to be removed

   

8. Add Opened by, Reason for travel, Departure date, Estimated trip cost from the left Available section onto the right Selected section

   

9. Click Save

10. You should now return to the list layout (your records will be slightly different based on what you entered in the exercise 4)

    

11. Click New on the top right

12. Fill up the form, ensure that you enter information for Departure date, Return date and any numerical figure for Estimated airfare

    

13. Right-click on the form header, then click Save

    

14. Notice that the Travel days and Estimated trip cost fields are automatically populated, ensure that the values are correct

15. In the screenshot above, the following was calculated

    (4 * 150) + 1,390.50 = 1,990.50

Well done, you now understand how to build excel-like formulas into your application! Here are some other possible formulas that are currently supported, but more are on the way:

1. AND Performs a logical AND operation on the arguments.
2. AVERAGE Returns the average value of the arguments.
3. CONCATENATE Joins one or more input strings into a single string.
4. DIVIDE Returns the quotient value after dividing argument 2 by argument 1.
5. IF Executes the specified statements based on the Boolean output of the conditional expression.
6. ISBLANK Finds white spaces or blank values in the string and returns true if there are any.
7. LENGTH Returns the total number of characters in the input string.
8. LOWERCASE Converts the input string to all lowercase characters.
9. MAX Returns the highest value in the specified arguments.
10. MIN Returns the lowest value in the specified arguments.
11. MULTIPLY Returns the multiplied value of the arguments.
12. NOW Returns the current date and time of the instance in ISO format.
13. OR Performs logical OR operation on the arguments.
14. POWER Returns the result of the base value raised to the power of the exponent value.
15. REPLACE Replaces characters in the source string with the characters in the target string.
16. SUBTRACT Returns the result value after subtracting argument 2 from argument 1.
17. SUM Returns the sum of all the arguments.
18. TIMEDIFF Finds difference between 2 dates for Duration field.
19. TITLECASE Converts the input string to all title case characters.
20. UPPERCASE Converts the input string to all uppercase characters.