Linked field-dependent formula - help

[Expatica_Team]

Hello!

I hope an Airtable wizard out there can help.

I have 12 tables - one named “Topics”, with a list of topics, and 11 tables of “Articles” (one per country I write about, which isn’t very handy but would be solved if Airtable finally did nested tables!), with a list of articles, which all have a field linked to “Topics”.

Here’s my problem. All topics have not been written out as articles in all countries. I need to see that from the “Articles” tables. At this moment, the only solution I’ve found is to create an article with a blank title ("-") and assign it to that topic in the linked field.

But I have hundreds of topics, and thousands of non-written articles, so it’s extremely time consuming to do it that way (and I am expanding to 40 countries, so it will soon be an impossible task).

I was wondering if there was a formula I could use in the title field (which is the primary field in “Articles”) that would automatically show an empty title (or “-”, whatever!) if there is no article for a particular topic from the Topic table.

Any solution outside the box is welcome too!

Thank you in advance for your help.

[David_Skinner]

This might not solve the problem you are trying to solve, but this is what I came up with when I thought about how I would go about structuring your base. I would have 3 tables (Topics, Countries, Articles) and then use the Topics or Countries tables to keep track of unwritten articles. This requires a formula field in the Articles table for every topic and/or country. The formula is just:

IF({Country}=“UK”,1,BLANK())

or IF({Topic}=“Sport”,1,BLANK())

You then need a Roll Up field for every topic in the Countries table (and/or a Roll Up field for every country in the Topics table. This just sums the values in the relevant column in the Articles table to tell you how many articles of that country-topic combination have been written. You can then use configured Views to quickly see articles that have not been written yet.

I appreciate that this will take some time to set up with the number of countries/topics you are dealing with. I have made an example base. I have configured example views in the Topics and Countries tables (Unwritten UK Articles and Unwritten Sports Articles).

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[Julian_Kirkness]

I completed agree with @David_Skinner about the data structure (3 tables) - using views to show each country either as a separate view per country or grouped by country.

There may be a different approach to working out which topics are not covered in particular countries…

You could have an Integromat Scenario (their name for a process) which would go through all Countries and then loop through each topic (for each country) and look for articles that match - if not found, it could add the topic to a list of topics NOT covered in the country.

This may take a while to run so I would suggest running it daily - or manually when you need to update the list.

[Marie_Peze]

@Julian_Kirkness @David_Skinner Thank you for your suggestions! Unfortunately, for several reasons, until Airtable makes nested tables, I cannot put all countries in the same table. At this stage I can already see from the Topics table which countries don’t have an article (I have lookup fields from each country for each topic, so if there’s nothing there, then I know it’s not been written), but I’m trying to make our sales team’s job easier so they ONLY have to look at the Articles tables to determine what topics are available (ie. not already sponsored, which is a field in the Articles tables, or not yet written.)
I guess there’s no easy solution to this, so for now I’ll just refer sales to both tables, and see what happens in the future of Airtable!
Thank you again for your help :slightly_smiling_face:

[W_Vann_Hall]

Didn’t we go through this about a year ago? I thought we had come up with a solution that did away with the need to keep a separate ‘articles’ table for each country — a design that can only lead to tears. (I don’t remember the specifics; I only remember my brain hurt, but in a good way, once I was finished with your base.) If there was a problem with that design, please let me know, and I’ll try to fix it; I just fear your current design will grow increasingly unwieldy as you add countries and articles…

[W_Vann_Hall]

I’ve also been thinking about your term ‘nested table’, and as I understand the term, Airtable already supports nested tables in the form of linked records.

As best I can tell, nested tables were an Oracle solution in search of a problem. One early article introducing readers to the concept stated

a nested table is nothing more than a normal table that has a column whose datatype is another table.

It also went on to say

we are […] creating a relationship within a table that could easily be done with two distinct and separate tables.

Possibly for this reason, it doesn’t appear nested tables ever became a commonly used feature of Oracle or of the various flavors of SQL that support them.

But if you return to that first definition — a field within a table whose datatype is also ‘table’ — it would appear an Airtable linked record fits the bill in that it provides access, by reference, to one or more records from [usually] a different table. I can’t think of any way to implement a nested table that would be any different or offer any additional functionality.

Having said that, there are some things that can be done more easily with an Oracle nested table — but not necessarily because of the data type. Instead, they appear to suffer more because of Airtable’s current lack of support for looping and array manipulations.

---

P.S. I found my earlier reply discussing a possible design for your base: It’s here, with a possibly related modification here.