Discussions

 View Only
  • 1.  Referencing Multiple Records a Once

    Posted 11-12-2019 14:53
    So, we have a table full of client information and we want to reference it in another table. However, there are duplicates of certain clients in the first table but some of the info is varied in the duplicates.
    For example, John Smith has two records in Table 1, both records include his basic info such as DOB, address, etc. However, in Record 1 he has not included a phone number but has included an email. In Record 2, he has included an email but not a phone number. In Table 2, we want a way to get all info related to John Smith without creating multiple reference fields, that way we get both his email and his phone number populated into Table 2's record. Is there any way to achieve this? Sorry if this is confusing, I'll try to clarify if needed.

    ------------------------------
    Anaya Steward
    ------------------------------


  • 2.  RE: Referencing Multiple Records a Once

    Posted 11-12-2019 15:08
    Edited by Justin Lapier 11-12-2019 15:08

    Technically, if the Table 1 is the parent table to Table 2, you can only relate to one record per relationship (natively).

    Other than going through and updating all data to one Client record, and referencing that one.

    Another way to natively achieve this would be to create the fields on Table 2 that are open for each of these values:

    DOB, Email, Phone, etc.

    Then you could create an automation that when a record is added to Table 2 it performs an action.

    Action 1: queries Table 1 for records that match the "Contact Name" or some other identifier, and contain an Email Address, and it updates the newly created records Email field with that value. 

    Action 2: queries Table 1 for records that match the "Contact Name" or some other identifier, and contain a DOB, and it updates the newly created records DOB field with that value.

    Action 3: Same as above, different field.. and so on.

    This probably isn't the most ideal, but it would be one way to solve it natively.



    ------------------------------
    Justin Lapier
    Synctivate Inc.
    ------------------------------



  • 3.  RE: Referencing Multiple Records a Once

    Posted 11-12-2019 15:13
    Thank you!

    ------------------------------
    Anaya Steward
    ------------------------------



  • 4.  RE: Referencing Multiple Records a Once

    Posted 11-12-2019 15:13
    Edited by Austin K 11-12-2019 15:13
    You are going to want to start with your data being correct in the first place. If you have contacts that are spread around multiple Record ID's then that is not a great place to be.

    Is there anything else contained in the Clients table that is a required field that can be used to make sure the client is unique? Or are you only working with the default Record ID?


  • 5.  RE: Referencing Multiple Records a Once

    Posted 11-12-2019 19:31
    Anaya,

    Austin is right.   Cleaning up Table 1 so that you have nice, clean normalized data will pay you dividends later.  That kind of DB work is tedious but you will not regret it later.

    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------