Usecase: Automatically Calculate and Update Pre-Travel Reimbursement Approval in Jira

This use case demonstrates how to use an Excel-like table integrated with Jira Field Mapping to automatically calculate and update travel reimbursement details for pre-travel approval. When a new Pre-Travel Reimbursement Approval issue is created in Jira, a pre-configured table loads with formulas that compute key values—such as travel days, city-based hotel rates, and total estimated cost—which are then synced with corresponding Jira fields.

Scenario Overview

A Human Resources administrator needs to streamline the pre-travel reimbursement process by automatically calculating travel-related costs. The team has set up a custom Pre-Travel Reimbursement Approval issue type in Jira. When a new issue of this type is created, an Excel-like table loads automatically with formulas that:

• Compute the total travel days based on the Departure Date and Return Date.

• Calculate individual cost components, such as flight, hotel, and food expenses.

• Use a separate dataset with a list of cities to lookup estimated hotel rates.

• Sum these values to produce a Total Estimated Cost.

These calculated values are then pushed to Jira custom fields (e.g., Travel Days and Estimated Cost) for immediate review and approval.

Step-by-Step Workflow

Assumptions

• Your Jira project is already set up.

• You have the necessary permissions and configurations in place.

Step 1: Create an Excel-Like Jira Template

• Action: Create a template specifically for your issue or request type.

• Reference: Excel-Like Jira Template Setup

Step 2: Build Your Template Structure

• Action: Design the templates that will appear in the issue (See: link)

  • Calculation Sheet: This sheet will host your dynamic formulas and mapped fields.

  • Dataset Sheet: Include historical city data for lookups (e.g., hotel and food rates per city).

Step 3: Map Your Table to Jira Fields

• Action: Configure Jira Field Mapping to link your table cells with Jira fields.

• Reference: Jira Field Mapping Documentation

Step 4: Save it

• Action: Save your template

Table Setup and Formula Examples

Predefined Calculation Table (Calculation Sheet)

In our Excel-like table (Calculation Sheet), the following key columns are set up to capture travel details and perform calculations:

• Trip Purpose: Descriptive text about the travel purpose.

• Departure Date: The start date of the trip.

• Return Date: The end date of the trip.

• Total Travel Days: A formula that calculates the difference between the Return Date and Departure Date.

• Destination: The travel destination (used as a lookup key).

• Flight Cost: The cost of the flight.

• Hotel Required?: An indicator (e.g., a checkbox) for whether a hotel is needed.

• Hotel Cost (per day): The estimated daily hotel cost for the destination (looked up from the Dataset Sheet).

• Food Required?: An indicator for whether food expenses apply.

• Food Cost (per day): The estimated daily food expense.

• Total Estimated Cost: The final cost computed by adding the Flight Cost to the product of daily expenses (hotel and food) multiplied by Total Travel Days.

Predefined Dataset Table (Dataset Sheet)

A second sheet in the same workbook contains historical data with a list of cities and their corresponding hotel and food rates. For example, this sheet might include:

This dataset is used by the Calculation Sheet to automatically look up and apply the correct daily costs based on the selected Destination.

Example Formulas

• Total Travel Days (Cell B7):

  =B5 - B4

  • B5: Return Date (Jira Field To Cell Value)

  • B4: Departure Date (Jira Field To Cell Value)

• Total Estimated Cost (Cell C10):

  = FlightCost + (VLOOKUP(Destination, DatasetTable, 2, FALSE) * TotalTravelDays) + (VLOOKUP(Destination, DatasetTable, 3, FALSE) * TotalTravelDays)

  • FlightCost: The flight cost input.

  • VLOOKUP(Destination, DatasetTable, 2, FALSE): Retrieves the Hotel Rate per day from the Dataset Sheet.

  • VLOOKUP(Destination, DatasetTable, 3, FALSE): Retrieves the Food Rate per day from the Dataset Sheet.

  • TotalTravelDays: The calculated number of travel days.

Note: Adjust cell references and table names as per your actual configuration.

Jira Field Mapping

Field to Cell (Read Mapping)

• Trip Purpose, Departure Date, Return Date, Destination:
  These fields are mapped from Jira into corresponding cells in the Calculation Sheet. When the issue is created, the table automatically displays these values pulled from Jira.

Cell to Field (Write Mapping)

• Total Travel Days:
  The calculated value in the Total Travel Days cell is pushed back to the Jira custom field Travel Days.

• Total Estimated Cost:
  The computed total cost is automatically written back to the Jira field Estimated Cost.

This two-way mapping ensures that any updates to the table are immediately reflected in Jira, maintaining real-time accuracy.

Additional Notes and Best Practices

• Validate Formula Complexity:
  Ensure that your table supports the advanced functions required for your calculations.

• Conditional Formatting:
  Consider applying visual cues if certain expense thresholds are exceeded.

• Data Validation:
  Implement rules to prevent errors, such as negative values or unrealistic percentages.

• Testing:
  Always test your formulas and mappings in a staging environment to handle various scenarios.