Formula to retrieve most recent data

[Aaron_Rittmaste]

I’m sure that I’m making this more difficult than it needs to be, and I keep ALMOST finding what I’m looking for by searching this forum, but then not quite getting there. I’m hoping someone can help me work this out.

I have a base that I use to track commissions paid to sales agents.
Sales agent commission rates change over time - they can adjust up or down, based on a sales volume formula. The sales volume formula is complex enough that I’m not even worried about integrating that element. But the tricky part is that when a new sale is entered by the sales agent, I want the commission rate to be automatically generated at whatever their current rate is. So …

I have an Agents table, which includes a host of information about each agent, including their commission rate.

I set up another table, called Commission Rate History. It’s linked to the Agents table. Commission Rate History includes the following fields:
Agent
Commission Rate
Change Date

I want the Agents table to have a field called Current Commission Rate, which retrieves whatever Commission Rate value has the most recent Change Date in the Commission Rate History table

So far, I’m shooting blanks. I managed to create a formula to convert the Change Date into a Days Since Change integer, thinking that might be easier to work with than a date. But it’s still not helping me retrieve the current rate for the agent.

Ultimately, the goal is to be able to link the Agent table to the Bookings table, and when an agent enters a new Bookings record, to have the Commission Rate for that Bookings record be whatever the agent’s current rate is.

In the current version of my base, the ugly workaround is that I create an entirely new Agent record with the new Commission Rate, and then hide the old record by having the agent select themselves from a view that excludes the older record. Otherwise, if I edit the Commission Rate for an agent, it screws up all of the previous records that are correctly calculated using their previous rate. I’d like to have a more elegant solution for the updated base, but I feel like I’m banging my head against the wall here.

[Justin_Barrett]

Long story short, there’s no way to do what you want by linking to the agent record. That will always pull whatever rate is currently set/pulled for that agent, even for old bookings, which will mess up old booking rates. To make this work, link each booking to a rate from your [Commission Rate History] table.

I’m building something similar as I work on migrating my invoicing system from a third-party accounting tool into a custom Airtable solution. I need invoices tagged with old rates to stay unaffected if a given client’s rate changes, so I’m building a [Rates] table. In my case I’m using both {From} and {To} date fields to track when each rate starts and stops. I have a formula field named {Current} that shows a checkbox emoji if a rate record has a {From} date, but no {To} date, and made a “Current Rates” view that only shows rate records marked as current. When linking to a rate from this table, the link field is set to only show records in the “Current Rates” view.

To know at a glance if a linked rate is current or old, and also to ensure that the primary field contents are unique across all rate records, the primary field in [Rates] not only pulls the client name from a {Client} link field, but it also tacks on the from-to date combo on the end if the rate isn’t current.

You could so something similar in your situation with your [Commission Rate History] table. I prefer the From-To combo for tracking rate effective date ranges, but using only a {Change Date} field could still work if you renamed (and repurposed) it to {End Date}. Any agent rate record without an end date would be considered current, and could appear in a custom view for selection in your [Bookings] table. Each time an agent’s rate changes, you add an end date to the existing record, then make a new record with the new rate that links to the same agent. The old rate would remain linked to old bookings, and the new rate record would be available to select for new ones.