Find Latest value less than a transaction date
I have 2 tables, a transaction table and a rate table. The Transaction Table has the following fields: Transaction ID, Transaction Date, Equipment, Transaction Rate.
The rate table has: Rate ID, Rate Entered Date, Equipment, and Daily Rate.
Tables are linked by equipment and sorted by Rate Entered Date. I am able to use the latest rate entered, but I am trying to use the latest rate that is less than or equal to the Transaction Date.
I tried a calculation and I tried a script with variables to perform when the equipment field was modified, but neither were giving the correct results.