Referencing Multiple Records a Once

Question

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
------------------------------

austink · Answer

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?

donlarson · Answer

Anaya,Austin is right.&nbsp; &nbsp;Cleaning up Table 1 so that you have nice, clean normalized data will pay you dividends later.&nbsp; That kind of DB work is tedious but you will not regret it later.------------------------------Don LarsonPaasporterWestlake OH------------------------------

justinlapier · Answer

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.&nbsp;
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 LapierSynctivate Inc.------------------------------

anayasteward · Answer

Thank you!------------------------------Anaya Steward------------------------------

Forum Discussion

Referencing Multiple Records a Once

Related Content

Using Grid Edit to Edit Multiple Records at Once

Edit Multiple Records at Once with Grid Edit

Modify multiple rows within the same group using Single Record ?

Custom Record Titles

Managing record access for multiple data levels