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:

• Arithmetic Operators:

  • + (Addition): Combine two values. Example: Base Pay + Bonus.
  • - (Subtraction): Subtract one value from another. Example: Total Amount - Discount.
  • * (Multiplication): Multiply values. Example: Rate * Hours Worked.
  • / (Division): Divide values. Example: Total Cost / Number of Units.

• Comparison Operators:

  • = (Equal to): Check if two values are equal.
  • > (Greater than): Check if one value is larger than another.
  • < (Less than): Check if one value is smaller than another.

• Logical Operators:

  • AND: Combine multiple conditions. Example: Status = "Approved" AND Amount > 100.
  • OR: Specify alternative conditions. Example: Status = "Pending" OR Status = "Processing".

Functions

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

• ROUND(Number, Decimal Places): Rounds a number to the specified decimal places.
  Example: ROUND(Invoice Amount, 2).

• IF(Condition, Value if True, Value if False): Returns different values based on a condition.
  Example: IF(Total > 1000, "High", "Low").

• CONCAT(String1, String2, …): Combines multiple strings into one.
  Example: CONCAT(First Name, " ", Last Name).

Examples of Common Use Cases

1. Calculate Sales Commission
   Formula: Revenue * Commission Rate

2. Driver Pay Calculation
   Formula: Base Rate + (Miles Driven * Rate per Mile) + Bonus

3. Invoice Reconciliation
   Formula: Invoice Amount - Payment Received

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.