How to combine multiple customer records into a single record?

I think that the ongoing maintenance can be done natively.


Make a new table called Members. Make an email field and make it the key field.


Make a formula checkbox field with a formula of true. Call it [Email exists?]


Make a relationship to each of your other individual tables based on their email field and look up the field [Email exists?]


On the first table, say newsletter signup, make a reverse relationship to lookup the data up to the members table. To do that, make a summary Maximum field of the Maximum Record ID. Call it [Record ID of most recent newsletter sign-up]


Then make a reverse relationship where 1 Newsletter sign up has Many Members. Lookup the data fields from the newsletter sign ups up to members.


Repeat that for all tables.


If you get that far, you can test along the way by manually entering an email address into members and observe that the data populates.


So now the challenge is how to keep the members table updated with a unique set of email addresses that may come from any of the individual data tables.


To populate the members table initially, you can make a summary report of the email addresses on respectively each of the individual tables and then use the More button and then Copy these records to another table, to copy those email addresses into the members table.


So a low-tech solution is to just remember to do that about once a week and then your consolidated table will be pretty up to date.


But if you want to fully automated this then you can use Actions. This assumes that the data in the individual tables is being added one by one and not imported.


So make an Action by going to Settings for the newsletter signup.


So The Action will be that when a record is added in the newsletter sign-ups and subject to the conditions that after the record is saved at the [email exists?] checkbox field is unchecked, then add a record and populate that email field.


Repeat for all tables., so each table will create the centralized members email Record if it does not already exist.

That should work. Post back if you get stuck anywhere along the way.

This is amazing. Thank you for getting back to me so soon. I'm keen to start setting this up and getting it working as soon as I can.

Note that when you make the reverse relationship, be sure to use that newly created field

[Record ID of most recent newsletter sign-up]

on the right side of the relationship.

Nice response Mark! I'm just curious: Instead of needing actions to populate fields up in the parent record by making copies of what is in the child tables, why not have each bit of information from the child tables come up to the parent as a lookup field, and then have a formula field in the parent to look at each of those separate bits from each child source and choose one that's filled in and make that the value through the formula? That way, the data only lives in one place, but the weaving together can happen at the parent with formula fields working on the lookup fields.

Right. That is what I suggested. We just need to get the master members Record created if a new email address is discovered, and then those reverse relationships would populate the members record.

I initially started to do the answer where the action would actually populate the data in the master members record but then I realized it would be much simpler to get those fields created and to build all those actions if we just use a reverse look up to get the look up feels up to the master record.

Then all the action needs to do is to create for the master member record and populate a single email address.