Selecting a price based on a date

Hi @shren - you’re right that Airtable can’t “loop through” the records and make the correct associations/links. You have to make the links that Airtable needs manually. However, there is an efficient way to make these links without linking each record one by one.

To illustrate, I’m going to assume that your job rates change every year, but it could be every month or some other arbitrary time period. In your Rates table I would have the following:

So for each job type, you’ve got a rate and a time period that it applies for.

In the Completed Jobs table you might have this:

The work type and the date the job was completed are self explanatory. The Year is a formula on the date to get the year the job was completed:

DATETIME_FORMAT(Date, 'YYYY')

Rate Link Copy is a formula:

{Work Type} & '-' & Year

Rate Link Paste is a link field to the Rates table

Rate is a Lookup field using the Rate Link Paste field as the lookup source.

Now, when you add a new record (Work Type and Date) in the Completed jobs table, you’ll get this:

Then just copy Rate Link Copy field into the Rate Link Paste field.

You don’t have to do this record by record - select as many cells as you want and paste them in.

If you wanted to automate this, you could use Zapier or similar to do it for you, although copy and paste is so quick, I wouldn’t go that far in this scenario.

Hope this helps

JB

First, thank you. I was not expecting such a comprehensive response. You could not have done a better job of understanding the issue and laying out an easy-to-follow recipe for how to deal with it. Thank you for putting in the time.

Unfortunately, my prices are inconsistently set.

At our company we have the chops to code a custom web application. I was just trying to see how far we could go with Airtable to avoid having the expense and risk of another custom app at the company. When we started playing with Airtable, we were impressed with how fast we were able to make things happen. But seeing how we’d have to jump through the hoops you outlined just to accomplish that which would be trivial in a custom database app, I think Airtable is not a good fit for us at this time.

Again, thank you for your response. Your answer will no doubt be helpful to many other people.

Indeed, a gallant effort by @JonathanBowen and wonderfully articulated, and despite the [seeming] complexity, my near-70-year-old brain was able to follow it. :winking_face:

In every solution implementation, we are faced with the classic make or buy decision. But we don’t often realize that there are many shades of make or buy.

Airtable has a deep bench when it comes to creature-comfort. The user experience is darn near ideal for business workers. But we can all agree that user experience only carries a solution so far. If there are inherent ceilings in the solution, it doesn’t matter how polished the UI is - it’s not a good fit and it seems that’s what @shren has concluded.

As I study @shren’s requirements, there’s a simple formula that describes the solution. Unfortunately, (in my opinion) equations like this cannot be elegantly achieved [solely] through data-mechanics (i.e., linking, rollups, additional fields, copy/paste…). Don’t misread my indictment - data-mechanics do provide many elegant outcomes in Airtable - perhaps not this one.

There’s a simple algorithm at play here and the curveball is the term “inconsistently set”. The algorithm must factor in the likelihood that the pattern is not as rigid (or predictable) as one might expect.

unitPrice is a function of workType, workRate and completionDate

Addressing the inconsistency, or lack of a logical pattern is a job for data-mechanics. Attempting to craft all of this solution with only data-mechanics will result in more complexity or more human-effort/skill.

If Airtable meets all of the usability requirements for this solution – setting aside just one determinant value – why not use the API to connect the final dot either through Zapier or your own code since you have access to internal development skills?

Using the API, you could buy most of the solution (at a very reasonable cost) and make a very small sliver of the solution. This would require a very simple API process that fills in the empty field whenever it notices there is an empty unitPrice. API-bound, the business logic (algorithm) for determining unitPrice would have perfect knowledge of every transaction.

Really this shouldn’t matter - your prices could be by year, month, week or day (and different timeframes per job type) and the model would still work. I just did it by year for speed :winking_face:

JB