I have developed a very simple base of 3 tables:
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.
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.
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!