Linked field-dependent formula - help

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).

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.

@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:

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…

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.