Forum Discussion

MelissaFreel1's avatar
MelissaFreel1
Qrew Cadet
6 years ago

Dealing with Duplicates ACROSS TWO tables

Hi all:

I have two tables that will be collecting a identification number of the clients in each table.  The tables are related but the ids, obviously, are unique.  Within the tables, I have put all the controls to ensure that there are no duplicates.  But how do I test "between" the two tables? 

For example, Client A's id is 1234 and she is entered in Table 1.
Client B's id is also 1234 and he is entered into Table 2.  

Which table the clients are entered into is based on other issues I don't need to describe here but I am positive I need the two tables, so making one table is not a solution.

So, how do I monitor when I have two clients across my two tables with the same ID of 1234 and that is not allowed?

Native solutions work best for me...not a coder but can learn.

Thanks!

Melissa

------------------------------
Melissa Freel
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    ... what is the Key field of each table?  Record ID#?  Or will we get lucky and it will be ID?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------
    • MelissaFreel1's avatar
      MelissaFreel1
      Qrew Cadet
      6 years ago
      Sorry Mark for the delay.  If you see this response, they both have their own unique record id but the value that I am concerned about is in separate field in the two tables.   I don't like messing around with changing the primary key in QuickBase, should I?

      ------------------------------
      Melissa Freel
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        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
        ------------------------------