Forum Discussion
QuickBaseCoachD
7 years agoQrew Captain
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.
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.