Lookup by date range

Just posted a reply to this, but it is awaiting moderation for some reason. Hopefully will be allowed through soon

JB

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

Set up your Vendor Rates table like this:

The ID field is a formula:

Vendor & '-' & {Quarter/Year}

So, instead of start date and end date, a rate for a vendor applies for a specific quarter of the year.

In your Line Items table, you have an order date and a vendor for the item (link to Vendors table):

From this, you can construct a “helper” field - Rate C&P - using the formula:

Vendor & '-' & 'Q' & DATETIME_FORMAT({Order Date}, 'Q-YYYY' )

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.

Hope this helps

JB

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…

Yes, this is a good point, I forgot about keeping the price of old order items fixed.

Thanks for all the great tips. I’ll take some time to digest it all and get back to you.