--- schema_version: "1" id: c2950315b33148f75d45b61491ebe3cd5894455f0748f76cebe99d0dda4a4d51 title: Use Lookup Tables and Dynamic Logic in Salesforce Spiff collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/sales/264-0-0/spiff_dynamic_logic.md canonical_url: https://help.salesforce.com/s/articleView?id=sales.spiff_dynamic_logic.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: f2e611bc4e2a75ecd210295bf59831272f3b09a1b25baf717ef5b8965432e721 description: Hard-coded values and long chains of if() statements are difficult to maintain and create risk when business rules change. Lookup tables, worksheet calculations, and statement periods give you flexible, dynamic commission logic that adapts to changing inputs without requiring changes to your plan configuration. products: - Sales Cloud - Sales Enablement release: "264" --- # Use Lookup Tables and Dynamic Logic in Salesforce Spiff Hard-coded values and long chains of `if()` statements are difficult to maintain and create risk when business rules change. Lookup tables, worksheet calculations, and statement periods give you flexible, dynamic commission logic that adapts to changing inputs without requiring changes to your plan configuration. ### Required Editions
Available in: both Salesforce Classic (not available in all orgs) and Lightning Experience
Available in: Enterprise, Unlimited, and Developer Editions
Available for an additional cost in: Professional Edition with Web Services API Enabled
## Use Lookup Tables Instead of Nested IF Statements When commission logic requires matching an input to a rate or tier—for example, a quota attainment percentage that maps to an accelerator rate—a common first approach is to write a series of `if()` statements. This approach works for a small number of tiers. However, it becomes unwieldy as the number of conditions grows, and every change to a tier requires editing the formula directly. Lookup tables solve this problem by storing the mapping as data. The calculation references the table, looks up the applicable row based on the input value, and returns the associated output. Adding, removing, or changing a tier means updating the table, not the formula. Use lookup tables in these situations. - Your commission logic requires multiple conditional branches. - Rates or thresholds are driven by dates, territories, product families, or other attributes that change between periods. - Multiple plans share the same rate structure and a single source of truth. ## Store Variable Amounts in Worksheet Fields Hard-coded numbers in commission formulas create hidden dependencies. When a rate, threshold, or quota amount changes, the engine must find and update every formula that contains the value. Missed occurrences cause incorrect payouts. Store variable amounts in worksheet fields and reference those fields in your formulas. When a value changes, update the worksheet field in one place. All formulas that reference it recalculate automatically at the next statement run. Worksheet fields are appropriate for these types of values. - Flat commission rates or multipliers that apply across a plan. - Threshold amounts, such as minimum deal sizes or quota targets. - Short lists of values, such as eligible product families or region codes. ![Tip](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-sales-sales_performance_management-264-0-0-production-enus/b1f2feb4-26b8-444f-97f7-be2349869d93/images/icon_tip.png) Tip Give worksheet fields clear, descriptive names that communicate their purpose. A field named `Q1MinDealSizeThreshold` is easier to find and audit than a field named `Threshold1`. ## Use Statement Periods for Key Dates Commission calculations often reference dates such as the beginning of a quarter, the end of a fiscal year, or a specific cutover date for a new plan structure. If you hard-code these dates, every period requires manual formula updates before the calculation run. Use statement period dates and worksheet calculations to make date logic dynamic. - Store key dates — such as beginning of quarter, end of quarter, beginning and end of first half, and beginning and end of second half — in a single dedicated worksheet. Reference those fields from any calculation that needs the date. - Avoid creating individual worksheet calculations for each monthly value in a series. For example, creating separate `JanARR`, `FebARR`, and `MarARR` fields isn't recommended. Return those values dynamically from a data filter and use date range conditions instead. - Use the built-in statement period variables (`BeginningOfPeriod`, `EndOfPeriod`) as the basis for date calculations wherever possible, so logic automatically adapts when statements are generated for different periods. ## Choose the Right Level for Your Calculations Consider whether a calculation belongs at the worksheet level or the datasheet level before you build it. Each level serves a different purpose in your commission plan. - **Worksheet calculations.** Best for values that apply to the statement as a whole — flat rates, totals across deals, period-level quotas, and other values that aren't associated with individual records. Worksheet calculations produce a single output for the statement context. - **Datasheet calculations.** Best for values that require record-by-record computation — deal-level commission amounts, per-record eligibility flags, or any calculation where the output varies across individual records. When a final payout is a sum of record-level amounts, perform the calculation in the datasheet and aggregate the result in the worksheet. ## Use Helper Functions for Simpler Syntax Spiff provides helper functions that replace complex, multi-argument standard functions with simpler, more readable syntax. Helper functions are interchangeable with their standard equivalents and produce the same output, but they reduce the chance of syntax errors and make formulas easier to read during implementation and auditing. When you encounter a complex function that requires many arguments or nested calls, check whether a helper function is available for that use case. Helper functions are especially valuable during an initial build when speed and readability matter most.