This is an interesting problem… because it can’t actually be encountered in Airtable due to a restriction in its forms.
The key blocking issue is you can’t create new linked field entries in a form. A straightforward implementation would obviate the security concern: users of the form can only select from what’s already there – Table 2 would just need to contain a billion entries, one for each possible SSN.
A Free Plan–friendly solution requires manual (or Zapier-automated) intervention, and would look something like this (I’ve labeled the field types for clarity):
Table 1
- [PRIMARY] Name
- [NUMBER] Submitted SSN
- [LINKED FIELD] Link to Table 2, displays Primary Field, {SSN}
…
Table 2
- [PRIMARY FIELD] SSN
- [LINKED FIELD] Link to Table 1, displays Primary Field, {Name}
…
Form (Table 1)
- [PRIMARY] Name
- [NUMBER] Submitted SSN
When a user fills out the form, instead of entering their SSN into the linked field, they’d enter it into a normal number field. You then copy and paste the value manually into {Link to Table 2}.* This has a side benefit of identifying duplicates ({Link to Table 1} will have more than one linked record).
*Or you can use Zapier to automate this step by first creating a view with the following filters:
- {Submitted SSN} is not empty, and
- {Link to Table 2} is empty
Then in Zapier, set a Zap to fire every time a record appears in this view.
As an aside, the diagram Danny gives for the Current Setup doesn’t paint a complete picture, since it’s missing one crucial field: the linked field on Table 2 that references Table 1.
Table 1
- [PRIMARY] Name
- [LINKED FIELD] Link to Table 2, displays Primary Field, {SSN}
…
Table 2
- [PRIMARY FIELD] SSN
-
[LINKED FIELD] Link to Table 1, displays Primary Field, {Name}
…
Table 2 records can only be linked via the Table 1 linked field – Table 2 cannot just “listen” to an arbitrary field (the two linked fields above are created simultaneously, and will remain even if the link is broken (just converted to a short text field)). Thus, the Table 1 Form must include {Link to Table 2} (for the setup in the original question as stated). Otherwise, no link between a Table 1 submission and a Table 2 record will be created, and (in theory) Table 2 will not be populated with new SSNs if the linked record does not already exist.
