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:
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.