I put together a dummy prospect→customer base as a test. It doesn’t actually do anything differently than what has been discussed, but I think it may do so a little more gracefully.
The base contains two tables, [Prospects] and [Customers]. ([Prospects] is loaded with 250 fake records from that terrific prototyper’s tool, fakenamegenerator.com.) In the demo, the conversion of a prospect to customer status is signified by the selecting of the {Prospects::Customer} checkbox, which in turn leads to the {Customer Name} field being populated.
In my envisioned scenario, these newly flagged customers are used to generate new, linked [Customer] records at some point in the process. This might be done manually, either record-by-record or batched, or it could be done by Zapier, et al., on either a triggered or interval basis.
To do so manually takes only three keystrokes (and maybe a mouseclick):
- With the targeted
{Customer Name} selected, press Ctrl-C to copy the cell.
- Press
►, the Right Arrow key, to move to the adjacent {Customer Link} field.
- Press
Ctrl-V to paste the value into the {Customer Link}.
This will cause a new [Customers] record to be created with its primary field set to {Prospects::Customer Name}.
Over in the [Customers] table, the generation of a new, linked record results in the population of a Linkup field, {DataLU}, which retrieves from the [Prospects] table a portmanteau value consisting of a number of [Prospects] fields, tagged and concatenated. A series of Formula fields deconstructs this composite field into its component parts, breaking out the customer name, address, and so on.
If you look at any of these Formula fields, you will notice they all consist of various MID() and FIND() statements in which the source or searched string is in turn represented by an IF() statement:
IF(CustomerData,CustomerData,ARRAYJOIN(DataLU))
This preferentially selects the {CustomerData} Single-line text field over the Lookup text field, {DataLU}, and allows for the decoupling of customer and prospect data — for instance, to allow for a table of purchased prospect names to be deleted to make way for a new set of prospects.
The mechanism for doing so is another copy/paste three-step:
- Copy the
{DataLU} cell.
- Move to the adjacent
{CustomerData} cell.
- Paste the copied data.
Once the looked-up data is replicated as a local text field, the link between the records may be broken without corrupting [Customers] data.
I use Airtable’s Pro-level record coloring function to flag [Prospects] records where {Customer} has been checked but no corresponding linked record created, and [Customers] records where {CustomerData} has yet to be replicated from {DataLU}.
This base gives you the ability, then, to generate new customer records automatically when a prospect converts and, later, to decouple the generated record from the impermanent prospect data store. In both instances, the effort required to effect such changes is a 3-keystroke sequence; in both instances, said manual action can easily be replaced with middleware automation.
(Note this implementation assume — and requires — a unique identifier be used for the [Customers] table primary field; otherwise, the first copy/paste sequence above will simply create an additional link to an existing [Customers] record.)
