Linking table fields

[Expatica_Team]

Hi,

I can’t find an answer to these two questions on the forums, but maybe I’m not looking correctly. Hope you can help me anyway - or link me to the solutions!

My website functions the following way: it is split into 12 different micro-sites that gives similar information about 12 different countries. Each country site follows the same structure, with the same article topics covered throughout.

In my airtable base, I have 12 tables:

• Table 1, that lists the 200+ common article topics present on these 12 website. In the next field I have assigned each article the category it belongs to - I have 10 categories. In the next field, I list the sub-category (I’d say about 50 of them). And finally in the last field I attach the pictures I use (over and over again for each country) for that particular topic.
• 12 Country Tables that list all the articles that I have published about these topics in each country. I have easily managed to make a “topic” field that links to Table 1. It makes it really easy to see in Table 1 what topics have already been covered for a particular country and which ones still need to be written.

Here’s problem #1. I need to keep track of the category and sub-category of each article in these 12 Country Tables too (it’s the “view” I use the most, really - grouped by category and then sub). But I don’t know how to link these fields to the category and subcategory fields in Table 1. For now I’ve just created new fields in each table, but that is so counterproductive. If I ever wanted to change a topic’s category, I’d have to go through all 12 Country Tables and modify them manually. Any idea how to do this?

Problem #2: For the same reason (automation!), I’d really like it if, when I add a new topic to Table 1, a line gets automatically added to my 12 Country Tables (in the right category and subcategory!). Right now, when I think of a new topic, I have to manually add the line to all 12 countries.

It’s not a huge deal, you’ll say, but I am planning on expanding the site to 40 countries, so this is going to become insane very fast :slightly_smiling_face:

Let me know if you can help me figure this one out!

Thank you.

[IT_BeeTee]

Hi,

Answer #1: As in your 12 Country tables, you have already had the link to Table 1 through column Topic, you can define column Category and Sub-category as Look up and select Topic as the field to use as the link and Category & Sub-category respectively as the value you wish to obtain from Table 1. This way, whenever Category and Sub-category are updated in Table 1, the changes will be reflected in your Country tables.

Answer #2: You cannot do it with Airtable GUI, if you insist, you can try Zapier (automation tool that works with Airtable) or have to code it yourself via Airtable API.

Hope this helped,

Alex

[Andy_Lin]

You might consider changing your setup slightly to make the best use of the database structure in Airtable.

I’ve set up an example for you here:

Airtable

Website Article Tracking - Airtable

Explore the "Website Article Tracking" base on Airtable.

The strength of this approach is that whatever details or assets you’ve set up for one topic will be available across all countries.

The weakness of this approach is that it doesn’t tell you what gaps there are for articles in the view that you’re used to using (it’d be under Categories instead).

[Expatica_Team]

Thank you Alex, I found the look up field not long after posting this; it sucks for #2, but with the look up fields it won’t be that much work to add new topic in each country; I’ll just have to create the title field instead of going through every one on each line!

Many thanks.

[Expatica_Team]

Thanks Andy; unfortunately I need to have a table per country because it’s extremely important that we see at a glance what articles have been written for what country in each topic (there’s sometimes several articles per topic), so as you can see in your category table, the “Articles” field won’t give enough information. :frowning: Maybe I can figure something out with pivot tables! My head hurts. Hahaha

[W_Vann_Hall]

See if this gives you what you want. I stole @Andy_Lin’s base from a few replies ago and beat on it until it did what I asked. It requires you to step somewhat outside basic Airtable functionality at times — but only rarely after initial set-up, and even then only upon adding a country or topic to the base.

Regarding style: Upon looking back through my posting history, I realize [yet again] that I am one wordy #&%@! Typically, this is because, as Blaise Pascal would say, I rarely have time to write a short post. While I still don’t, I’m going to try to write as pithily as I can; should I throw comprehensibility out with the bath water, please don’t hesitate to ask for clarification…

---

Note: I take extensive advantage of the ability to generate records automatically by copy-and-pasting values into linked record fields. If you are unfamiliar with this nifty piece of underused Airtable functionality, I recommend you review the previous link before exploring the demo base.

Note again: The names of [Tables] are enclosed in square brackets, the names of {Fields} in curly braces, and the names of <Views> in angle brackets. A constructs such as {Titles::Article List} means the {Article List} field in the [Titles] table.

Airtable

📜 Website Article Tracking - Pala - Airtable

Explore the "📜 Website Article Tracking - Pala" base on Airtable.

Base Structure

In my reimagining, the base contains four tables:

1. [Topics]. This [roughly] corresponds with your list of ±200 topics. It contains data common to a given topic across multiple country sites: Topic title, category, subcategory, and so forth; it also contains a link to each country-specific article dealing with that topic, along with a couple fields used to create such article records automatically. Where this differs from your topic list is that each, hmm, uber-article is represented by its own record, as opposed to your system, in which there can sometimes be several articles per topic.

   (Note: I have not provided any way to identify articles by topic, assuming such classification would either be obvious or supported by naming conventions [“Indigenous Crafts of Manhattan: Counterfeit Birkin Bags, Instagrams”]. If such granularity is required, it could be provided through a third single-select (in addition to {Category} and {Subcategory} or a single-line text field.)

2. [Articles]. This represents the bulk of the base. Essentially, it consists of one record per topic per country. It takes the place of your individual country tables, instead gathering all article-level data into a single table; grouped and filtered views provide per-country visibility.

   One significant feature is an [Article] record exists for every planned topic, completed or not. This allows immediate comprehension of which articles do and do not currently exist. I’ve chosen to use a checkbox to indicate which articles are {Complete}; alternatively, this could be determined from the presence or absence of, say, an attached copy of or link to the article, or a range of statuses ('Assigned', 'Draft', 'Edited', and so forth) could support more-detailed project management.

   An optional link to the [Countries] table simplifies generation of country-level statistics.

3. [Countries]. Following Andy’s lead, I chose to implement it as a set of countries, with a subset indicated as being active. (For instance, the full list might include all forty planned countries, with only the current twelve flagged as active.) The ‘active’ indicator doubles as a link to a record in the fourth table, [Summary]. As published, [Countries] includes the optional {Articles} linked record and a rollup field, {Number Complete}, as an example of country-level summarization.

4. [Summary]. This is one of my omnipresent, single-record tables; its primary purpose is to roll up a list of active countries for use by the [Titles] table.

Implementation

As this approach depends so heavily on linked tables, it’s probably a little more difficult to incorporate its functionality into an existing base than that of most of my example bases. Towards this end, the following section describes the steps required to implement the base, followed by the processes to add a new country or title. (On the assumption this will be retrofitted into an existing base, I’ll only discuss fields that need to be added or modified. If one wishes to start from scratch and build on top of this base, my recommendation would be to copy it twice to one’s workspace. Leave one copy as-is for reference; delete all records from the second, and follow the instructions below using the appropriate data.)

[Countries]

Start with the [Countries] table.

1. Add a new single-line text field called {Link to Summary}.
2. For every country meant to be active, copy and past the following character into {Link to Summary}: ✅ (White Heavy Check Mark emoji, U+2705).
3. Right-click on {Link to Summary} and select 'Customize field type'.
4. Change the field type from ‘single-line text’ to ‘link to another record’. Select '+ Create a new table', and name the table [Summary]. Toggle off 'Allow linking to multiple records.'
5. Airtable creates a new [Summary] table with a single record, ✅, which is linked to all active countries.

[Summary]

1. Add a rollup field that follows the {Countries} field to roll up {Name}, using the aggregation formula

   ARRAYJOIN(values,': [A], ')&': [A]'
   

[Titles]

1. Create or modify a table so there is one record for every ‘uber-article’ to be included in a country site. For the most part, this will represent a 1:1 mapping of topics to titles except in those cases where multiple articles are written on a single topic; in such cases, each article should be represented by a unique record. (The base as published assumes comparable articles for different countries will have the same title; should this not be the case, an additional field will need to be added to the [Articles] table.)
2. Add a new single-line text field, {Link to Summary}. Copy and paste ✅ into {Link to Summary} for every record in the table.
3. Right-click on {Link to Summary} and select 'Customize field type'.
4. Change the field type from ‘single-line text’ to ‘link to another record’. Select the [Summary] table and toggle off 'Allow linking to multiple records.'
5. Airtable links all [Titles] records to the single [Summary] record.
6. Create a new rollup field, {Article List}, following the {Link to Summary} field to roll up {Article Key} using the aggregation formula
   .
   SUBSTITUTE(values&'','[A]',Title)
   .
7. Create a new linked records field, {Articles}, pointing at the [Articles] table. Leave 'Allow linking to multiple records' toggled on.
8. Select {Article List} for Row 1, scroll to the bottom of the table and while holding down the Shift key, select {Article List} for the bottom-most row of the table.
9. Press Ctrl-C to copy all data in the column.
10. Select {Articles} in Row 1 and press Ctrl-V to paste the previously copied data.
11. Airtable creates a new [Articles] record for every topic for every active country, linking them back to [Titles].

[Articles]

1. Create a new lookup field, {Category}, that follows the {Titles} link to look up {Titles::Category}.
2. Create a new lookup field, {Subcategory}, that follows the {Titles} link to look up {Titles::Subcategory}.
3. Create a new formula field, {Country}, with the formula
   .
   LEFT(Name,FIND(':',Name)-1)
   .
4. Create a new linked records field, {Link to Countries}; select [Countries] as the table to link to.
5. Select {Country} for Row 1, scroll to the bottom of the table and while holding down the Shift key, select {Country} for the bottom-most row of the table.
6. Press Ctrl-C to copy all data in the column.
7. Select {Link to Countries} in Row 1 and press Ctrl-V to paste the previously copied data.
8. Airtable links each {Article} to its appropriate country.

At this point, the base is up and running for data entry.

Note that any changes make to a [Titles] record — for instance, changing a title’s {Category} — are immediately propagated throughout the base, changing all [Articles] records based on that [Title].

The [Articles] table contains views configured at one, two, and three levels of grouping, grouped by country, country and category, and country, category, and subcategory, respectively.

Adding/Activating a Country

Additional, unactivated countries can be added to the [Countries] table with no extra manipulation or processing required.

To activate a country

1. From the [Countries] table, copy and paste ✅ into {Link to Summary} for the country you wish to activate. This creates a link from [Countries] to [Summary], updating {Summary::Article Key}, which in turn updates `{Titles::Article List},
2. From the [Titles] table, select {Article List} for Row 1, scroll to the bottom of the table and while holding down the Shift key, select {Article List} for the bottom-most row of the table.
3. Press Ctrl-C to copy all data in the column.
4. Select {Articles} in Row 1 and press Ctrl-V to paste the previously copied data.

This has the effect of creating new [Articles] records — one for every record defined in [Titles] — for the newly added country. I say ‘has the effect’ because it actually destroys the existing links to [Articles] and then creates them anew. This should not be an issue with the base as published, as the regenerated links will point to the same [Articles] records they did before; however, this means the primary field cannot contain any elements (such as a date/timestamp) that would change over time.

To come full circle, access the [Articles] table and select an ungrouped, unfiltered, unsorted view. Typically, the newly added records will be found at the bottom of the table. Mark and copy the {Country} field for the new country and paste the values into the {Link to Countries} field. This will create links between [Articles] and [Countries].

Adding a Title

To add a new title to the base and cause it to replicate across all active countries

1. Add a new record to the [Titles] table, specifying {Category} and {Sub-Category}.
2. Copy and paste ✅ into {Link to Summary}. This will update {Summary::Article Key}, which in turn updates {Titles::Article List}.
3. Select {Titles::Article List}, press Ctrl-C to copy the value, select {Articles}, and press Ctrl-V to paste it. This will create a new [Articles] record reflecting the added [Title] for each country currently marked as active.
4. From the [Articles] table, scroll to the bottom, where the newly created records can be found. Mark-and-copy the {Country] fields for the new records, and paste them into the corresponding {Link to Countries} fields.

[Expatica_Team]

Holy Guacamole, that is the kindest post I’ve ever seen on any forum, ever! It must have taken so much time for you to do all this, it is just so so cool. I will look at it over the weekend.
I like my current set-up, which I can’t share because it’s too full of proprietary and confidential client information, but there are many things about it that are similar to yours. I use linked and lookup fields all over the place, which are really nifty (did not know about the lookups just yesterday!) and I am sure I will find many great tips in what you gave me here.

What I like about having a table per country is that the topics table gives me this immediate view of what’s available:

and thanks to lookup fields, all I’ll have to do when I create a new topic is type a dash in every column - which is not HORRIBLE. Also, Airtable support told me that they’re going to implement that automatic field creation thing some time in the near future… yay…

Thank you again; I will let you know when I’ve gone over your post in detail - it’s too late now, I need Zzzz.

xx

[Jeremy_Oglesby]

I haven’t read all of @W_Vann_Hall’s post, but he is the smartest and nicest guy I’ve met on the forums, so I’m certain his post is sufficient for what you need. Perhaps what I say here is merely a piggy back off of ideas he has already expressed…

but, I think you can definitely still create a “Topics” dashboard just like you have there, while still having only a single table for all “Pieces” (or whatever you want to call them) and have “Country” be a Single-Select (dropdown) in that table. You could still create a field for each “Country” in the “Topics” table that parses a string carried over from the “Pieces” table for the country name and only displays when country name for that field is present. Just a thought.

But definitely read @W_Vann_Hall’s suggestions, because I’m sure his overall system is excellent.

[Joe_Ambrose]

Hello,

This is an interesting exchange, but I’m not sure how it was resolved.

I’m trying to write a function across two tables and multiple fields, but the naming convention is tripping me up.

I need to identify the most recent meeting with a contact. The results will be stored in the [Contacts] table, which is linked to the [Activities] table via the {Contact & Account} field.

My use case is to pull the most recent date on the [Contact] table from the [Activity] table when the {Completed} field is populated (w/ a date), the {Mode} = Meeting and the {Contact & Account} name are equal

This string is getting an Invalid Formula error:

MAX([Activity]!{Completed}, [Activity]!{Mode}="Meeting",{Contact & Account}"="[Activity]!{Contact & Account},””)

Any suggestions or could you please confirm the syntax for tables and fields?