One--and only one!--child record

posted Feb 21, 2011, 7:43 AM by Ezra Kenigsberg   [ updated Mar 26, 2011, 3:06 PM ]

#DataTipOfTheDay 2011-02-21

Q: "How do I enforce a one-to-one relationship between a Parent record and Child record?"

A: Depends on which edition you're using.

If you're using Contact Manager, Group, or Professional Edition, this approach works for Master-Detail relationships:*
  1. Create a Rollup Summary Field on the Parent that counts the number of Child records.
  2. Create a Validation Rule on the Child record that prevents the Child from being associated to a Parent if the Child count is greater than zero.
If you're using Developer, Enterprise, or Unlimited Edition, this approach works for both Master-Detail and Lookup relationships:
  1. Create a new text field on the Child and name it "Unique Parent". Make it a 15-character text field and case-sensitive unique. It doesn't need to be visible to any users or shown on any Page Layouts.
  2. Create a Workflow Rule that (a) is fired whenever a record is created or updated, and (b) has a trivial criterion (like "ID is not blank"). Associate a Field Update with the Rule that writes the value from the Parent relationship field to the "Unique Parent" text field.
For extra credit in either example: create a Lookup Filter on the Child's relationship field that suppresses the display of any Parent with a Child count of one.
    * Be careful with this approach. As Manpower's Andy Ognenoff and X-Squared On Demand's David Schach pointed out, Rollup Summary Fields are asynchronous. While this approach probably works OK in the UI, data loads through the API could definitely defeat it by loading records before the Rollup Summary Field has time to catch up. Many thanks to Dorset Consulting's Sandra Schanzer for the original tip!