The Hidden Join

Two Fields, One Review Link

Sending patients to the right Google review link after a visit was one of the main reasons customers bought the platform.

For almost everyone, the routing was simple:

• one review link per provider, or
• one per location

A single connection decided where a patient was directed after completing the survey.

Enter: a multi-location dermatology group that needed both at once.

For this customer, the correct link depended on the provider and the location where the provider worked, combined, so the routing decision hinged on two fields rather than one, and the platform had no way to evaluate them as a pair.

Even if it was an edge case compared to our usual, the account size was substantial, so it became a pain point we needed to solve.

At the time, the customer was still importing contacts via CSV, with an integration scheduled for later that quarter.

That integration would automate the upload, but it would not help the routing problem.

The platform could not natively connect a Google Review link to the provider-and-location relationship unless it came pre-combined. The customer had no way to do this on their side.

How the Grouping Tool Normally Works

The platform's main lever for this was its grouping logic: it takes one field in and maps it to one operational value out.

A typical setup is direct:

1. Start with four locations: New York, Albany, Boston, and Cambridge.
2. Create a group called State.
3. Assign each location to its state.

The result is two states, each with two locations.

That covered almost every case I had built… until now.

What Made It Hard

The group ran dermatology across several locations, with providers working at more than one site.

The correct review URL depended on the exact provider-and-location pair: the same provider working in Boston sent reviews to a different page than when they were working at Cambridge.

I was brought in to consult on this once the implementation moved past standard configuration; edge cases from working with strange setups on other accounts gave me the best head start.

I partnered with the customer's business analyst and integration lead on the big decisions, while day-to-day execution ran through their operational point of contact.

Going Down a Layer

The grouping tool mapped a single field to a value.

I could route to one destination:

• every visit with a given provider
• or every visit to a given office

What I could not express was the relationship between the two fields.

Two obvious paths presented themselves:

• "The platform can't do this" wouldn’t work for a core feature on an enterprise account.
• "Let's build a new product feature" when the ask was too niche to justify platform-level work (and frankly, bad practice).

I needed another option.

Synthesizing the Key

Beneath the grouping tool sat a feature called calculated fields, an internal-only part of the platform that customers never saw and, in practice, only engineers really opened.

I had seen it once used a couple of years earlier, when another customer fused two fields into one for a different use case.

An internal reference page from that work covered the two things I now needed:

• how to fuse fields
• how the grouping tool read its inputs underneath

That page was my guide, alongside a lot of trial and error.

The platform couldn't express the relationship, so I compressed the two fields into a single value it could read: a calculated field, provider_location_key_c.

provider_c + "_" + location_c

That produced values like DrSmith_Boston and DrSmith_Cambridge.

I thought we were done.

Hitting the Wall

I went back to the grouping tool expecting the fused field to work like any other lookup key.

It did not: the tool only accepted imported fields as keys, and mine was calculated.

There was a further wrinkle.

The interface displayed the connection I had built below, so it looked configurable like standard calculated fields there.

It was not: any edit errored, with messages vague enough to explain almost nothing. It was a notoriously difficult product feature with no updates in scope.

You already had to know that the real logic lived one layer down.

Finishing in the Calculated Fields

The mapping had to live where the key was generated.

I moved the provider-location-to-URL lookup there too, written as direct JSON connections:

{
  "DrSmith_Boston": "https://g.page/r/...boston-smith",
  "DrSmith_Cambridge": "https://g.page/r/...cambridge-smith",
  "DrJones_Boston": "https://g.page/r/...boston-jones"
}

No automated way to generate or push this existed, so I built it in a spreadsheet and moved it in:

• one row per provider-and-location pair
• collapsed into the JSON the area expected
• placed in by hand, and maintained the same way

Every change meant going back in and editing the map directly.

From the interface, it still looked like an ordinary grouping rule, but the real logic now lived two layers down, in a system never meant to carry it.

Because that logic was invisible and the errors communicated so little, I documented it heavily in the account record so anyone diagnosing it later would know where to look.

That was a stopgap. We kept customers out of this area, which held the risk low, but an internal handoff without the note would have been hard.

What the Problem Really Was

The routing itself was the easy part.

The platform already had the right pattern for the job: lookup and mapping. What it couldn't do was combine two existing fields.

It also couldn't assume clean behavior upstream.

It had to survive:

• inconsistent naming
• human error
• non-technical operators
• fragile imports