I’ve got a table of rates with “effectivity dates”. Such that when an order comes in, I need to find the “current” rate associated with that order, on the order’s date. My structure so far is as follows:
- Line Items (Link to Order Line Items)
- Order Date
Table Order Line Item
- Order (Link to Orders)
- Order Date (Lookup to Orders.Date)
- Line Item Total
- Line Item Vendor (link to Vendor)
- Effective Vendor Rate (?????HOW????)
- Rates (Link to Vendor Rates)
- Start Date
- End Date
- Rate (Percentage)
Hi @Eric_Falsken - unfortunately, you can’t do this exactly as you describe as you’re trying to link something explicit (the order date) with something implied (some date between the start date and the end date) - not something Airtable does. However, there are some solutions/workarounds which might work depending upon your situation.
use a 3rd party service like Zapier or some custom code accessing the AT API to find the vendor rate for the order date and set it in the order items table. I wrote a post about something completely different, but the principles are the same:
If you can base your Vendor Rate table on some other notion tied to dates - weeks of the year, months, quarters etc - then you can use this to help you add the link manually. Here’s how I would do this (I’m going to assume that your vendor rates can be based on calendar quarters to illustrate):
What I’m doing here is constructing a value that matches the ID field in the Vendor Rates table. You can then copy and paste the value from the Rate C&P field into the Link to Rates field, single or multiple cells at a time:
The link to the Rates table is made and the lookup value of the rate % can be pulled into the order items table.
As above, this method relies on your rates applying within some fixed calendar unit - week, month, quarter, year - that can be derived from the order date. It won’t work if your rate periods are arbitrary dates, unfortunately.
In addition to the approach @JonathanBowen outlined above, you might want to take a look at the pricing schedule mod of the Product Catalog template. It would serve your purpose best if your vendor price changes happen in sync (or if you can fudge a few change dates to block as many together at a time as possible); otherwise, you’ll have a large number of short-lived pricing schedules.
If you don’t use something like a pricing schedule, then you’ll have to use a copy/paste mechanism like the one Jonathan suggests; otherwise, whenever you change the price for, say, “3Q”, all earlier prices based on a 3Q pricing model will change as well.
Actually, I think I just came up with a way to do this with arbitrary date ranges; however, it still requires a copy/paste (manual or through Zapier et al.) to retrieve the value for that date. Let me beat on it a moment when I get a chance…