Dealing with Duplicates ACROSS TWO tables

I guess that you have been busy in the last 2 1/2 months  :)
Just thinking out loud here.  I'm sure that is not the only way but there will be several ways.

What if you you set up a new table called Client ID's.  Set the Key field to be Client ID.  Initialize that table by copying the records from each respective table into the Client ID table.  Now you have a list of the unique combined list of client ID's which are in use. 

Make a pair of Relationships between the Client ID table and each of the two Client tables let the system create a field for related client, but then make that a formula field equal to the client ID.  That way the data entry for Client ID will continue to be Free form data entry and not from a drop down list.

Make a field in the Client ID table as a formula checkbox field called [Client ID exists?] with the formula of true.  Look that up down to each Client table.

Maintain the Client ID table at going forward with the "belt and suspenders"  of two Automations to write out a new Client ID when a record is entered in either of the two tables and the lookup of [Client ID exists?] is not true.

Then just as an added safety net “suspenders”  in case Automations completely fail, is to have an Automation run two saved table to table imports  to copy all records from each Client table where the Client ID does not exists into the Client ID table.

Lastly, make a pair summary fields of the # of children up to the Client ID table.  Total them up there.  They should all total just 1 child.  Then look that up down to the Clients table.  Then have a safety net daily subscription report to catch any client ID's with 2 or more children and also a big red warning Rich text field on each of the two client forms, to say when the duplicate exists to stop and fix the problem.  That warning might also be able to be show in Add Mode since if you in add mode then the lookup of the Client ID exists must be false.  It it's true then the Client ID they are hand entering is a duplicate of a Client ID that already exists.  So one warning in add mode (and try Abort the save if that lookup is true and a field named [Record ID# mirror] (formula = [Record ID#]) is blank and a different warning  one in view mode.  View is allowed to have 1 child as it's the child you are on, but in add mode the client ID should not yet exists.



​

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------