Nov 23, 2020 09:35 AM
We’re needing some help with a formula (or proper linking) to auto-price our services based on the service area - in this case, by Zip Code. The idea is Service Type + ZipCode = Price. For example…
Currently, we have a form that takes customer info (Service Submissions). The Client/Customer selects the service and specifically enters the ZipCode in a required field. Through integromat, a record in Service Orders is created. This is the table our team works in. We’d like the price from ZipCode Pricing table above to be calculated and show up in the Service Orders and Invoices tables.
Ultimately, the pricing data will be sent to Quickbooks, but that’s a later step. Any help would be greatly appreciated. If there’s any way I can explain this better, please let me know.
Thank you in advance!
Nov 23, 2020 09:52 AM
Welcome to the community, @Kent_Tompkins! :slightly_smiling_face:
Two different approaches to this.
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.
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.
Dec 07, 2020 10:33 AM
We don’t have the budget to outsource right now. Is this the right track?
Dec 07, 2020 11:05 AM
You wouldn’t need any of those purple modules — you would primarily just use the Airtable modules:
Dec 16, 2020 09:23 AM
Is there perhaps an example, I could follow?