Selecting a price based on a date

[shren]

I have developed a very simple base of 3 tables:

1. Work_Type: this table has fields that describe types of work that can be done. Most importantly, the Name field in this table holds a unique name for the type of work, e.g., Painting, Gardening, Snow Removal, etc.

2. Work_Rates: this table has a) a link to the Work_Type table, b) a unit price for the work type selected, and c) the date the unit price is applicable. Example records would be Gardening $5 7/1/2014; Gardening $8 7/1/2018; Painting $12 7/1/2014.

3. Completed_Jobs: this table has a) a link to Work_Type field to indicate the type of work that was done, b) quantity, indicating the amount of work that was done, and c) completion date.

I would like to add a field to the Completed_Jobs table indicating the relevant unit price for the work done based on the date the job was completed. For example, if the completion date for a Gardening job was 4/15/2017, the field would show $5 for the unit price. If the completion date for another Gardening job was 5/15/2019, the field would show $8 for the unit price.

Conceptually, on each Completed_Job record I would need the ability to loop through the Work_Rates for the relevant Work_Type and compare those values to the completion date in order to pick the correct unit price. From my research, I believe that this cannot be accomplished using Airtable.

Before I abandon using Airtable for this project, I want to make sure I am correct. Any thoughts from the community are most appreciated. Thank you!

[JonathanBowen]

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

[shren]

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.

[Bill_French]

shren:

…such a comprehensive response.

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.

shren:

Unfortunately, my prices are inconsistently set.

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.

shren:

At our company we have the chops to code a custom web application.

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.

[JonathanBowen]

shren:

Unfortunately, my prices are inconsistently set.

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