Welcome to the community, @Kent_Tompkins! :slightly_smiling_face:
Two different approaches to this.
Approach #1:
In that Zones table, if you break out every single zip code into its own individual record, you could create a linked record field in your service orders table that links to the zip code, and then automatically looks up the rate (in a lookup field) whenever a matching zip code is chosen.
Note that linked record fields never populate automatically, so you would need to manually populate the linked record field in some way. You could use Airtable’s automations (or Integromat’s automations, since you’re already using Integromat) to automate the process of copying & pasting the zip code from the text field into the linked record field.
If you use a native Airtable form or a MiniExtensions.com form or an On2Air + JotForm form, you could skip the automation by having the customer choose the zip code directly in a linked record field, by presenting them with a drop-down menu of all the zip codes in your system.
Approach #2:
If you want to keep all of your zip codes grouped together like that in your Zones table, then you would either need to use JavaScript or Integromat to search for the value within the zip code field, and then place the zip code into the linked record field.
Although, if you go down this route, you’d probably want to skip the linked record field altogether, because you could just search for the value within the zip code field, and then simply place the matching rate into the service orders table.
In other words, you wouldn’t need a linked record field at all, because you could just automate the process of typing in the rate.
In fact, this actually might be the BEST solution, because if you go with the “linked record” field & you change your rates over time, Airtable will go back and change the price of all your OLD historical service orders as well.
p.s. If you have a budget for this project and you’d like to hire an expert consultant to help you with your system, I am both a professional Airtable consultant and a Registered Integromat Partner. You can feel free to contact me through my website at scottworld.com.
