So here is how I would approach this, bearing in mind your app is creating invoices, and generally you would want the rates (and totals etc) on historical inspections and invoices to remain static over time, and not get updated whenever there is an annual price increase of your inspection rates.
Create your ZIP RATE object - with a zipcode and a zip rate.
Create your INSPECTION object with a connection to ZIP RATE, and an Invoice rate field, like this:
When you create a new inspection record (or edit an active inspection), use a record rule to pull the zip rate from the ZIP RATE object into the Invoice Rate field.
if you are ALWAYS importing inspections using a CSV file, then instead of using the record rule, you would need to create a TASK in the builder for the INSPECTION object that basically does the same thing, something like below, and it run it after every import.
Create your INVOICE object with a connection to inspection, and a rate field - in this case I’ve added an HOURS field and an equation to calculate rate x hours … but you may not have a variable rate?
Now the invoice part is where it can get tricky. The problem is you need an invoice to exist in order to pull through connected values to it. If you have a 1:1 relationship between inspections and invoices, then I would just keep the invoice fields in the INSPECTION record - you already have the rate in there.
But lets assume you need Invoice to be separate from Inspection for some reason - maybe is a 1:N relationship.
Then, I would create the Invoice header record first (using a simple form with a reference field and all the details you usually capture in an invoice header - invoice number, customer details, etc), and then I would Submit that information, and on submit run a record rule to pull through the rate from the INSPECTION record, and then use a submit rule to open a sub-page which lets you modify the “line item” information - hours spent, special costs, etc. i.e. the Invoice is a 2 step thing.
