Commission math seems simple until a rate changes. Someone calculates a payout using this month's percentage, files it away, and three months later a manager bumps the commission rate up for the new quarter. If that percentage was ever calculated live off a shared rate field, every old payout silently recalculates too — and suddenly last quarter's report doesn't match what anyone actually got paid.

In this tutorial, we'll build a Salesperson Commission Report — a Sales table that calculates each commission at the moment a sale happens, and locks it in permanently, so it stays historically accurate no matter what the commission rate does afterward.

---

Table Columns

Here's what we're working with — a Salesperson table holding names, linked to a Sales table through a One-to-Many relationship, with Amount, Commission Rate, and Date fields on each sale.

• Salesperson — Name
• Sales — Salesperson (linked), Amount, Commission Rate, Date

---

Step 1: Add Your Salespeople and Sales

Add a couple of salespeople, then log several sales for each, entering the commission rate that applied at the time of each sale.

Salesperson table populated with Ravi Chandran and Elena Petrova

Sales table showing multiple sale records linked to each salesperson with amount and commission rate

For this walkthrough, assume the following sales, all made while the commission rate was 8%:

• Ravi Chandran — $5,000 sale, 8% rate, dated July 10, 2026
• Ravi Chandran — $3,200 sale, 8% rate, dated July 22, 2026
• Elena Petrova — $7,500 sale, 8% rate, dated August 5, 2026

Right now, we know the amount and the rate for each sale, but nothing has actually calculated what that translates to in dollars earned.

---

Step 2: Calculate and Snapshot the Commission

Since a plain column can't be converted into Custom Logic after the fact, create a brand new column and set its type to Custom Logic from the start.

Adding a new Custom Logic column for Commission Earned

Describe what you want — multiply Amount by Commission Rate to get Commission Earned, and lock this value in permanently at the moment the sale is created, so it never changes even if Commission Rate is edited later.

Describing the logic to calculate commission and freeze it at the time the sale record is created

Confirm the clarification screen, checking that it understood you specifically want this value frozen at creation, not recalculated every time you view it.

Clarification screen confirming the commission calculation will be locked in at record creation

Save it, and every existing sale calculates its commission once, permanently:

• Ravi Chandran's $5,000 sale → $400
• Ravi Chandran's $3,200 sale → $256
• Elena Petrova's $7,500 sale → $600

Sales table showing Commission Earned calculated for every sale record

---

Step 3: Watch a Rate Change Leave the Past Untouched

Here's the real test. Suppose the company raises the commission rate to 10% starting September 1, 2026. Add a new sale for Ravi Chandran under the new rate.

Adding a new sale for Ravi Chandran with a 10 percent commission rate dated September 2026

• Ravi Chandran — $4,000 sale, 10% rate, dated September 2, 2026 → Commission Earned calculates fresh at $400

New sale record showing Commission Earned calculated at the new 10 percent rate

Now go back and try editing the Commission Rate on one of his old July sales, just to see what happens.

Editing the Commission Rate field on Ravi Chandran's original July sale

Ravi Chandran's July 10 sale keeps its Commission Earned at $400, completely unchanged, even though the Commission Rate field itself now shows a different number.

Ravi Chandran's July sale showing Commission Earned still at 400 dollars despite the rate field being edited

---

Step 4: Build a Commission Report Chart

With every sale's commission locked in accurately, aggregate it per salesperson. On the Salesperson table, add a Rollup summing Commission Earned across each person's linked sales.

Adding a Rollup field summing Commission Earned across each salesperson's linked sales

Then build a bar chart using that rollup, comparing total commission earned per person.

Bar chart comparing total commission earned between Ravi Chandran and Elena Petrova

• Ravi Chandran → $1,056 total commission
• Elena Petrova → $600 total commission

---

Recap

1. A Salesperson table linked to a Sales table via One-to-Many
2. A Custom Logic field calculating commission and freezing it permanently at the moment of sale
3. A live demonstration proving a later rate change leaves historical payouts untouched
4. A Rollup aggregating locked-in commissions per salesperson
5. A bar chart turning that into a stable, audit-ready commission report

This same snapshot pattern applies well beyond commissions — exchange rates at time of purchase, discount percentages applied to a specific order, or tax rates in effect when an invoice was issued all need this same kind of historical protection.