Help to filter to know where something isn't licensed

[Mary_Sivertsen]

My base keeps track of where our films are licensed. I can filter and get a list of titles that are available in a specific territory. For example if I need a list of available titles for Italy I tell it to show we me things that don’t have Italy marked. What I want to do now is filter in such a way that I can tell where a film hasn’t been licensed yet in the whole world, to see where the holes are that we can still offer it. Any ideas how I might do this?

Thank you!

[Justin_Barrett]

Airtable (like most databases) is great for collecting data on what a certain data set does contain. It’s tricker to say, “Show me what’s missing.” For that, you need to start with the full set of whatever you want (countries in this case), then remove the references to specific items from that full list. Unfortunately Airtable’s built-in formulas aren’t capable of doing that type of processing. For that, you’ll need to use a script, either internally inside of Airtable (Scripting block and/or automation), or via the REST API, with the script populating a separate field with the “missing” countries for each film.

In terms of setup, I would suggest (if you aren’t doing this already) using a separate [Countries] table to store the countries where a film could be licensed, with each film record linking to appropriate country records in that table. When the script is run, it could collect a list of all countries from that table, find out which ones each film is linked to, and populate another field with the list of what’s left.

If this is put into a Scripting block, the script would have to be triggered manually, either by running the script on all records periodically, or perhaps running via a button field to refresh a single film’s record. If you want a more automated approach, a trigger system could be designed so that as you add or remove links for a given record, the script would automatically run to keep the list of non-licensed countries up to date.

If you’d like help setting this up, just holler!

[Mary_Sivertsen]

Hi Justin,

Thank you for responding! We currently are not on the Pro plan and therefore do not have access to the Blocks. Is there anyway to do this without the Scripting block that you mentioned? Also, if I’m reading your instructions correctly, are you saying that I would have to go in and link each contract to the new countries table? It’s already in a formula and I’m very concerned about messing that up. Everything is working well for us except filter the places where we have not already licensed a film.

I just need someone smarter than me to help figure this out! (smile)

[Justin_Barrett]

I suppose this could be done in a formula field, but it’s going to be a large formula because you have to look for each country one-by-one. Airtable formulas only have a few limited array processing options at this time, and they won’t help in this situation.

Here’s the basic setup for such a formula. You haven’t mentioned how you’re connecting films to licensed countries, so I’m going to assume for now that it’s a multiple-select.

Start the formula by searching for a single country, and outputting that country’s name if it’s not found in your {Countries} field (assuming the name for now):

IF(NOT(FIND("Spain", Countries)), "Spain")

For each subsequent country, do the same thing, concatenating it with the one before it using the & operator. Also prefix the next country’s name with several spaces. The number isn’t important as long as it’s not just a single space, and as long as you’re consistent with using the same number of spaces each time (you’ll see why later on). For this example, I’ll use five spaces.

Here’s that first country check plus one more.

IF(NOT(FIND("Spain", Countries)), "Spain")
& IF(NOT(FIND("Germany", Countries)), "     Germany")

From there you can copy/paste that second line to add more countries:

IF(NOT(FIND("Spain", Countries)), "Spain")
& IF(NOT(FIND("Germany", Countries)), "     Germany")
& IF(NOT(FIND("Belgium", Countries)), "     Belgium")
& IF(NOT(FIND("New Zealand", Countries)), "     New Zealand")
& IF(NOT(FIND("Austria", Countries)), "     Austria")

Keep on adding as many of these lines as you need. I’ll stick with these few for the rest of this demo.

Let’s say that it doesn’t find Germany and New Zealand, but the rest are there. That will output this:

     Germany     New Zealand

To take care of the possible leading spaces before the first country, wrap the entire formula in a TRIM() function:

TRIM(
IF(NOT(FIND("Spain", Countries)), "Spain")
& IF(NOT(FIND("Germany", Countries)), "     Germany")
& IF(NOT(FIND("Belgium", Countries)), "     Belgium")
& IF(NOT(FIND("New Zealand", Countries)), "     New Zealand")
& IF(NOT(FIND("Austria", Countries)), "     Austria")
)

That will output:

Germany     New Zealand

Finally, to change the space blocks to something more reader-friendly, wrap the entire formula again in a SUBSTITUTE() function to change those blocks into a comma-space combo. However many spaces you added before each country’s name should be the number of spaces you replace using this function:

SUBSTITUTE(
TRIM(
IF(NOT(FIND("Spain", Countries)), "Spain")
& IF(NOT(FIND("Germany", Countries)), "     Germany")
& IF(NOT(FIND("Belgium", Countries)), "     Belgium")
& IF(NOT(FIND("New Zealand", Countries)), "     New Zealand")
& IF(NOT(FIND("Austria", Countries)), "     Austria")
)
, "     ", ", ")

That will give you this:

Germany, New Zealand

Because we used multiple spaces to prefix the country names, the SUBSTITUTE() function won’t replace the space in “New Zealand”.

The bulk of your work will be copying and pasting lines to add more countries, but this should do the trick.

[Mary_Sivertsen]

Wow! You are awesome!

I’m not sure how to begin this process. Here’s what I’ve done so far.

My tables across the top are : Contracts, then Movies, (and others that don’t need to come into play here)

When I enter a new contract, I go to the Movies table, in that table there is a Contracts field that is linked to the Contracts table. It allows for multiple records. That brings up the pop up box to add a new record in Contracts. In that new record from contracts, (you are correct in that) the countries are multiple select. The countries are listed in the Regions field on the Contracts table.

I began this new process by creating a new table called Countries and listed them, one per line. Should I have made them multiple select as well on the Countries table?

[Justin_Barrett]

Mary_Sivertsen:

Should I have made them multiple select as well on the Countries table?

No. Each country on that table should be its own record, which is what it sounds like you did, so you’re good there.

Back on the [Contracts] table, choosing between a multiple-select vs a link to another table comes down to whether or not you want to track/examine any specific data related to the countries you’re choosing for your contracts. If all you care about is tying a contract to one or more countries, and other data related to that relationship doesn’t hold any interest/value for you, then a multiple-select is fine. However, if you feel that there’s a chance you might want to look more deeply at the contract-country relationship—e.g. find all of the contracts tied to a specific country—then using a link field to a [Countries] table is the way to go.

For the specific question that started this thread, though, it doesn’t matter which way you go. I had originally suggested adding a [Countries] table as a means of making it easier for a script to pull certain data. However, if you don’t want to use the Scripting block (which is free until September), then we’re left with the formula setup that I described above.

Thanks for sharing more about your setup. To get the countries for a given film over on the [Movies] table, you’ll need to add a rollup/lookup using your contract link(s) to pull that data over. That rollup/lookup could be named {Countries} and you’ll be all set, though you might need to tweak the formula. If you use a rollup field, you could use the rollup aggregation formula:

ARRAYJOIN(values)

…which would compact all country names into a single string, with commas separating the names. That would allow you to use the above formula setup as-is. If you go with a lookup field, that will bring in an array of country names. The FIND() function in the formula operates on a string, and won’t recognize names in an array, so you’d have to change every reference to {Countries} in the formula to:

Countries & ""

Concatenating an array with an empty string turns the array into a string, and FIND() can then read it. In the end, I suggest going with a rollup because it would just simplify the whole process.

Does that make sense?

[Mary_Sivertsen]

Hi Justin,

I am so sorry… but I am LOST! Is there anyway we could chat? Either on-line or on the phone?

[Justin_Barrett]

Absolutely! I’ll send you a PM and we can discuss options for connecting.