You need a relationship based on SSN* between the active table and the inactive table. You might want a self-relationship with the active table also, to prevent duplicate SSNs there. I can think of a few ways to do this.
1. Script Trigger on SSN field, OnObjectSave. Script Commits, then tests for Count ( relationship::SSN ) > 1 (duplicate).**
2. Field validation, similar tests. But awkward, as the validation kicks in before the Commit. I would use #1 above.
The advantage of a Script Trigger in this regard is that it could tell you where the duplicate exists. It could even tell you who (check that the name is the same, to verify your typing), with an option to make the person active again (if inactive).
Field Validation is good for some things. But if there can be more than one thing wrong, it has a hard time telling you which, as the dialog is one of the only places in FileMaker which does not allow you to use a calculation.
Script Triggers require that you attach them to the particular instance of the field, which is awkward if it pre-exists on a lot of layouts. But an enterable SSN should only be on a few layouts.
* I would create a stripped-down calculation field, with result number, to test. In case someone entered the dashes wrong, or left a space at the end. Or have the SSN field auto-enter by calculation, with [ ] Do not replace contents unchecked, to strip then reformat. Basic SSN as digits-only calculation:
Filter ( SSN; "0123456789" )
** Actually I would use what I call a "not_me" relationship. Which is the same relationship, but adds a criteria to exclude the current record from the match. If you have a unique primary ID, "PersonID", then it would be:
SSN (calculation) = Inactive::SSN (calculation)
PersonID ≠ Inactive::PersonID
Assuming you push the same ID to the Inactive table, the test is then: not IsEmpty ( Inactive~SSN_not_me::SSN )
I set the relationship as
SSN = SSN
ID does not = ID
Is that right?
I'm Not sure of the steps for the script trigger.