Price by ZIP code or service area
Charge a trip fee by ZIP code, and tell a customer outside your area that you do not serve them yet, with a lookup table and two formulas.
One table and two formulas
Add a text question for the ZIP code, a lookup table of the codes you serve with the fee for each, and two calculated fields: one for the fee that the customer does not see, and the price the customer sees.
zone_fee: LOOKUP(service_zones, LEFT(TRIM(zip), 5), -1)
price: IF(zone_fee < 0, "Sorry, we do not serve that ZIP code yet.", sqft * 3 + zone_fee)The table service_zones lists each ZIP code you serve with its fee, for example 10001 at 0, 10002 at 25 and 07030 at 40. A code that is not in the table gives -1, and the price shows your sentence instead of a number. Write that sentence in your own words.
US ZIP codes: the first five characters
LEFT(TRIM(zip), 5) drops spaces around the code and keeps the first five characters, so " 10002 " and the ZIP+4 "07030-1234" both find their row. Without it, a customer who types a space or a ZIP+4 is told you do not serve them.
The prices below are for 500 square feet.
| Typed | Price shown |
|---|---|
| 10001 | $1,500.00 |
| " 10002 " | $1,525.00 |
| 07030-1234 | $1,540.00 |
| 90210 | Sorry, we do not serve that ZIP code yet. |
Other postal codes: match the whole code
The five-character rule is for US ZIP codes only. A postal code elsewhere, for example a seven-digit Israeli code, must be matched whole, or two different areas would share a row and a customer would get the wrong fee.
zone_fee: LOOKUP(service_zones, TRIM(code), -1)
price: IF(zone_fee < 0, "Sorry, we do not serve that postal code yet.", rooms * 400 + zone_fee)Ask for it in ChatGPT
Say which codes you serve and the fee for each, for example: "Add a ZIP code question. I serve 10001 with no trip fee, 10002 for $25 and 07030 for $40. Anyone else should see: Sorry, we do not serve that ZIP code yet."