Using Formula Fields to do Basic Math

Users can now perform basic math calculations on existing data.

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, or invoice reconciliation. With this feature, you can define custom formulas that reference object properties, use operators for calculations, and apply functions to manipulate data - just like you might 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
• 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 Difference").

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,...).

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]

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 Formula Fields, you can automate repetitive calculations, reduce errors, and streamline your workflows—all without needing external tools.