Using Formula Fields

Users can now perform basic math calculations on existing data and dates.

Table of Contents:

1. Overview

2. Some Common Problems Formula Fields Solve

3. Step by Step Guide: Creating a Formula Field 

4. Examples of Common Use Cases (with formulas) and Best Practices 

Overview

Formula Fields allow you to automate calculations and comparisons directly in Rose Rocket, enabling you to solve complex business problems like calculating sales commissions, driver pay, invoice reconciliation, and now, date-based calculations. With this feature, you can define custom formulas that reference object properties, use operators for calculations, and apply functions to manipulate data—just like in Excel or Google Sheets!

Some Common Problems Formula Fields Solve

Formula Fields solve for calculations like:

• Multiplying a commission rate by revenue.
• Combining factors to calculate driver pay.
• Comparing invoice totals with other financial documents.
• Calculating Deadhead Miles on an Order
• Converting Miles to Kilometers
• Tracking Detention Time
• Track on Time Deliveries
• Monitor Driver License Expiry 
• And more!

Step-by-Step Guide: Creating a Formula Field

1. Navigate to Object Builder

   • Go to the relevant Object (e.g., Order, Invoice, Driver) in Rose Rocket.
   • Select Add Field to create a new custom field.

2. Choose “Formula Field” as the Field Type

   • Select Formula Field from the list of available field types.
   • Provide a name for your new field (e.g., "Total Commission" or "Invoice Due Date").

3. Define Your Formula

   • In the Formula Input box, create your calculation using the three main components:
     • Properties: Reference existing fields within the object.
       • Example: Revenue, PO Num, or Invoice Amount.
     • Operators: Use symbols for calculations (e.g., +, -, *, /).
       • Example: Multiply a rate by revenue: Rate * Revenue.
     • Functions: Apply built-in functions to manipulate data.
       • Example: Use ROUND to round a number: ROUND(Total, 2).

4. Save the Field

   • Once your formula is complete, click Save. The Formula Field will now be available on the object and automatically calculate based on the defined logic.

Operators

Operators are the building blocks of your formulas. They allow you to perform calculations and comparisons. Common operators include:

• • + (Addition): Add values together using ‘+’ to get the total. Follows order of operations. 
    • Example: 2+3 OR 1+2+3+4+5
  • - (Subtraction): Subtract one value from another using ‘-’ to get the difference. Follows order of operations.
    • Example: 10-5 OR 100-21-19

• • * (Multiplication): Multiply values using ‘*’ to get the product. Follows order of operations.
    • Example: 4*6 OR 3*4*5*6

• • / (Division): Divide one value by another using ‘/’ to get the quotient. Follows order of operations.'
    • Example: 9/3 OR 6/5/2

• • () (Brackets): Use brackets ‘( )’ to set the order of operations manually for values.
    
    • Example: (2+3)*(1+4) OR 60/(5/2)

Functions

Functions are used to manipulate data and calculate strings and numbers. Examples include:

• SUM: Returns the sum of a specified list of values. Equivalent to using the + operator.
  Syntax: sum(value1, [value2,...])

• PRODUCT: Returns the product of a specified list of values. Equivalent to using the * operator.
  Syntax: product(factor1, [factor2,...])

• CEILING: A ceiling rounds a number up to the nearest whole number or to a specified number of decimal places. Example: 5.2 becomes 6 (nearest whole number) or 5.234 becomes 5.24 with 2 decimal places. 
  Syntax: ceiling(value, [factor]).

• FLOOR: A floor rounds a number down to the nearest whole number or to a specified number of decimal places.  

  Syntax: floor(value,[factor]).

• AVERAGE: Returns the average of a specified list of values.
  Syntax: average(value1, value2,...).
• MAXIMUM: Returns the largest value in a specified list of values.
  Syntax: maximum(value1, value2,...).
• MINIMUM: Returns the smallest value in a specified list of values.
  Syntax: minimum(value1, value2,...).
• COUNT: Returns the total number of values provided in the list.
  Syntax: count(value1, value2,...).

Date & Time Functions (NEW)

• Date Offset: dateOffset(date, number, interval) - Offsets a date by a specified duration.

• Date Difference: dateDiff(date1, date2, units) - Returns the difference between two dates.

• To Number: toNumber(value), toNumber(value, 'unitlabel') - Converts input to a number.

• To Money: toMoney(value, currencyUnit) - Converts input to a monetary value.

• To Percentage: toPercent(value) - Converts input to a percentage.

• To Duration: toDuration(value, timeUnit) - Converts input to a duration.

• Today: today() - Returns the current date.

• Now: now() - Returns the current date and time.

Callouts:

• NOW() and TODAY() return the current UTC day/time, not the local timezone. 

Examples of Common Use Cases

1. Calculate Sales Commission
   Formula: Margin * Commission Rate (ex: Margin * 0.10)

2. Deadhead Miles on an Order 
   Formula: maximum(0,[#manifest total miles] - [#total miles])

3. Carrier/Driver Cost Per Mile
   Formula: [$Total Cost]/[#Total Miles]

4. Calculate Driver Detention Time: dateDiff([#actual arrival time], [#actual departure time], 'hours')

5. Determine Days Until Invoice is Due: dateDiff(today(), [#invoice due date], 'days')

6. Calculate Length of a Trip Based on HOS: dateDiff([#trip start time], [#trip end time], 'hours')

7. Administer Due Dates (e.g., Container Return Date): dateOffset([#shipment arrival], 5, 'days')

Best Practices

• Test Your Formulas: Use sample data to ensure your formula works as intended.
• Simplify When Possible: Break down complex formulas into smaller parts for better readability.
• Use Descriptive Names: Name your formula fields clearly so others understand their purpose.

With the addition of date-based calculations, Formula Fields in Rose Rocket now provide even more automation and efficiency for logistics businesses, reducing manual work and improving operational accuracy.