I track media hits through a Base with 3 tabs/tables: Reporter Info, Media Campaigns and Articles.
I’ve been trying to link these 3 tables so when I enter an article’s URL in the Article table, which also has columns for Media Campaign and Reporter Name, it will automatically fill the article URL and Reporter Name in the Media Campaigns table...and the article URL and media campaign name will appear in the Reporter Info table under the Reporter’s Name.
I have managed to link the Articles table to the Media Campaigns, so the article’s URL will auto-fill but the corresponding Reporter field won’t automatically appear for that Campaign.
And If I’m on the Reporters page, and select the reporter’s name, I have to enter the Media Campaign name, which then does auto-fill the article URL tied to that media campaign and reporter.
But I want all 3 fields to automatically fill in the Reporter & Media Campaign tables, when I enter those fields in the Article Table. Is that possible?
Best answer by DisraeliGears01
Yep, this can be solved either through Lookups or Automation…
Lookups: Probably the cleaner solution, instead of directly linking all three tables together, Articles acts as the join table. You enter an Article URL, and link it to the appropriate Reporter and Campaign record. Then in the Reporter Info table, simply add a Lookup of the Articles Master List table for the Media Campaign field. Then in the Media Campaign table, add a lookup of Articles Master List table for the Reporter Info field.
If you need more specific info passed through the tables, you can lookup lookups to your hearts content. So if there’s info in the Media Campaign table that you want in Reporter Info, add a lookup of that field in the article table, and then add a lookup in the Reporter Info table.
Automations: If for some reason or another you do need all three tables to link directly, it’s easy enough to make an automation that fires from the Article table, when a record has fields Reporter Info and Campaign are not empty. Then Update the Record in Reporter Info to link to the Campaign record in the Article table entry. One important thing to note is for that last bit you want to have the Campaign record have both the updated entry and the prior list of entries separated by a comma (otherwise it'll overwrite and basically enforce a 1-1 relationship rather than multi). It’s a bit more room for error and I’d recommend the Lookup version, but it’s definitely doable.
Yep, this can be solved either through Lookups or Automation…
Lookups: Probably the cleaner solution, instead of directly linking all three tables together, Articles acts as the join table. You enter an Article URL, and link it to the appropriate Reporter and Campaign record. Then in the Reporter Info table, simply add a Lookup of the Articles Master List table for the Media Campaign field. Then in the Media Campaign table, add a lookup of Articles Master List table for the Reporter Info field.
If you need more specific info passed through the tables, you can lookup lookups to your hearts content. So if there’s info in the Media Campaign table that you want in Reporter Info, add a lookup of that field in the article table, and then add a lookup in the Reporter Info table.
Automations: If for some reason or another you do need all three tables to link directly, it’s easy enough to make an automation that fires from the Article table, when a record has fields Reporter Info and Campaign are not empty. Then Update the Record in Reporter Info to link to the Campaign record in the Article table entry. One important thing to note is for that last bit you want to have the Campaign record have both the updated entry and the prior list of entries separated by a comma (otherwise it'll overwrite and basically enforce a 1-1 relationship rather than multi). It’s a bit more room for error and I’d recommend the Lookup version, but it’s definitely doable.
Does this look right? If so I’ve set it up here for you to check out
We first add a lookup field in Reporter Info to grab all the Media Campaigns the Reporter is associated with. Then we have an automation that triggers whenever the lookup field gets updated, and its action is to paste the lookup field value into the linked field in ‘Reporter Info’ to the ‘Media Campaigns’ table
Setting up the Media Campaigns link is a weird one, we first have to select the lookup, then the Linked Record, and then the ID:
---
Echoing the previous posts, would doing this via lookups or rollups work fine? They’d be less work to maintain long term and you wouldn’t have to worry about the links being outdated or something
Makes it more tedious to work with the data as you’d need to create lookups of lookups, but in my experience that one off pain is much better than having to clean up the data later on when something mesed up, you know what I mean?
I recommend using an automation instead of a lookup to populate the remaining fields. Set the trigger to run on record creation or update; the automation can then pull the corresponding data from the linked tables.
