Re: Helpful Pre-Made Data Tables, Formulas, and Routines

Jeremy_Oglesby · ‎Nov 09, 2018

These are static tables I find myself using frequently when building bases for clients, so I keep a copy of them on their own in my account so I can easily copy-paste them into bases when needed. I’m creating this wiki to share them in case other people might want to use them, or contribute their own reusable pre-mades.

To make use of these, open the base through the links below, then use the “Copy base” button (top right corner) to add a copy of it to your own workspace. Then, you can copy (⌘/ctrl + a → ⌘/ctrl + c) and paste the table desired into other bases as needed.

US & CA States/Districts/Territories

Includes Country, Capital City, and Postal Abbreviation.

Airtable Keyboard Shortcuts

The same list you can access from the “Help” menu, but will be hit by global searches from the Search Block if included in a base, which is a bit more exposed and allows serendipitous discovery of shortcuts by users.

AlphaNumeric ID Generator

Produces the ID’s AA001 - ZZ999 in sequence, for a total of 675,324 unique ID’s. Thanks to @W_Vann_Hall for building this one.

Timezone single-select

Instructions here on how to create a single-select field containing supported timezone indicators for use with SET_TIMEZONE(). (Note: It appears Airtable has modified the TZ list since that reply was originally written: You’ll also want to delete the empty records from the resulting base before converting the single-line text field to single-select, and you’ll need to append a comma to the new[?] 'GMT' entry.)

U.S. Holiday Generator for WORKDAY() Functions

Select the years you want to generate holidays for, and optionally check/uncheck those ambiguous holidays, to generate a list of dates for each year, and a list for ALL years selected, which can easily be copied and pasted into WORKDAY() functions. Thanks to @W_Vann_Hall for building this excellent tool.

U.K. Holiday Generator for WORKDAY() Functions

Select the years for which you want holidays and the U.K. holiday type — England, Wales, Scotland, Northern Ireland, Republic of Ireland — and the base will generate an ISO-formatted list of holiday dates to paste into WORKDAY() or WORKDAY_DIFF(). Includes code to calculate the date of Easter for any year 1900 through 2033.

URL [and General Text] Cleaning and Validation Routines

Link to a number of handy canned formulas for doing such things as stripping punctuation from text fields, cleaning and standardizing URLs, removing query strings, and the like. Thanks to @willinspire for providing these!

World/US State Database

Another great @willinspire creation, a base that, to quote,

[contains] every country name, ISO alpha2 code, ISO alpha3 code, national flag, currency name, currency ticker, national language, time zone, international dialing code, national capital, GPS grid location, and alternative and native language spelling of every country in the world.

It also includes every US state, state flag, state seal, state map, image of the states largest urban areas, image of every state’s rural area, capital city of every state, and a list of the largest largest cities in the US. … among other things.

==> WorldDB

Government/Corporate/NGO Database

One more from @willinspire and ResoNova:

DataDB is a public collection of Open Source Intelligence (OSINT) data that is designed to fortify and expedite research activities for a wide range of interested parties.

==> DataDB

W_Vann_Hall · ‎Nov 09, 2018

These are great! I especially like the idea of including the keyboard shortcuts as a table that might felicitously appear in a search.

Might I suggest others append similarly useful links to tables or community posts, so this might become a one-stop shop for oft-needed data sets? (Alternatively, I suppose you could wiki-fy it — although I’m not sure in what form, exactly, such a thing would take.) I have a few entries I’d like to contribute — such as a single-select timezone field or the base that generates a list of (U.S.) holidays for use in WORKDAY*() functions — and I’m positive dozens more are out there, as well.

Jeremy_Oglesby · ‎Nov 09, 2018

I already did “wiki-fy” it. You should be able to edit/add to it… I think…

Jeremy_Oglesby · ‎Dec 06, 2018

Bump – Added @W_Vann_Hall’s U.S. Holiday for WORKDAY() Functions base.

W_Vann_Hall · ‎Mar 31, 2019

Bump — added a collection of URL/text processing routines and two databases from @willinspire.

Edit: As it’s nearing that time of year, I’d earlier added my U.K. Holiday Generator for WORKDAY() Functions — which also includes code to calculate the date of Easter for years 1900 through 2033.

willinspire · ‎May 26, 2019

I’ve improved some of the functions that I previously built etc.

[ URL-To-Protocol ]

This formula takes URL-like strings with a prepended transfer protocol and outputs the isolated transfer protocol. The input strings may include unlimited page reference depth, query strings, and browser-specific “protocols” without disrupting the output result.

Examples of input ==> output

http://example.com/some-page?q=some+query&more ==> http
chrome://settings/ ==> chrome
ftp://ftp.nasdaqtrader.com/ ==> ftp

Key Assumptions:

The column “URL” exists in the current table
Cells in the “URL” field contain a URL string
- If the reference cell is empty, the output defaults to being empty

Formula Source:

IF(SEARCH("://",{URL})="","",LEFT({URL},(SEARCH("://",{URL})-1)))

[ URL-To-Domain ]

This formula takes URL-like strings and outputs the isolated domain (and any sub-domains, if present). The input strings may include unlimited page reference depth, query strings, and browser-specific “protocols” without disrupting the output result. This formula is an optimized variation of the previous ‘url-to-domain’ formula that has been shared in here (specifically, this version is exactly 88% smaller in character length than the older one).

Examples of input ==> output

http://example.com/some-page?q=some+query&more ==> example.com
chrome://settings/ ==> settings
ftp://ftp.nasdaqtrader.com/ ==> ftp.nasdaqtrader.com

Key Assumptions:

The column “URL” exists in the current table
Cells in the “URL” field contain a URL string
- If the reference cell is empty, the output defaults to being empty

Formula Source:

IF(SEARCH("/", RIGHT(URL, (LEN(URL)-SEARCH("://", URL)-2)))="", RIGHT(URL, (LEN(URL)-SEARCH("://", URL)-2)),LEFT(RIGHT(URL, (LEN(URL)-SEARCH("://", URL)-2)), (SEARCH("/", RIGHT(URL, (LEN(URL)-SEARCH("://", URL)-2)))-1)))

[ URL-to-TLD ]

By using a decoy substring (\\/\\/) I’ve managed to simulate a sort of “parse-by-delimiter” effect that unbinds recursive parsing from the limitations of the user needing to know EXACTLY how long or how deep they need to parse. So now we can effectively “continue doing X until the end of Y” even if the actual length of “Y” is completely unknown. As a result, I’ve cut down my initial TLD parsing function by about 97.5% while simultaneously uncapping what it can handle. See the function size comparison below:

Examples of input ==> output

http://example.com/some-page?q=some+query&more ==> com
https://airtable.github.io/something-very-cool/777 ==> io
http://xn--c1acbl2abdlkab1og.xn--p1ai/ ==> xn--p1ai

Key Assumptions:

The column “URL” exists in the current table
Cells in the “URL” field contain a URL string
- If the reference cell is empty, the output defaults to being empty
- If the reference cell is an IP address, the output will be empty
- If the reference cell has a TLD that is less than 2 characters in length, the output will be empty

Formula Source:

IF(IF(SUBSTITUTE(IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1)))),".","")>=0,"",IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1)))))="","",RIGHT(IF(SUBSTITUTE(IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1)))),".","")>=0,"",IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1))))),(LEN(IF(SUBSTITUTE(IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1)))),".","")>=0,"",IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1))))))-SEARCH("\\/\\/",SUBSTITUTE(IF(SUBSTITUTE(IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1)))),".","")>=0,"",IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1))))),".","\\/\\/",(((LEN(SUBSTITUTE(IF(SUBSTITUTE(IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1)))),".","")>=0,"",IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1))))),"."," "))-LEN(SUBSTITUTE(SUBSTITUTE(IF(SUBSTITUTE(IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1)))),".","")>=0,"",IF(SEARCH("://",{URL})="","",IF(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))="",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),LEFT(RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)),(SEARCH("/",RIGHT({URL},(LEN({URL})-SEARCH("://",{URL})-2)))-1))))),"."," ")," ","")))/LEN(" "))+0))))))

Expert_Credit_S · ‎Aug 02, 2019

Are you able to share how you created a “copy base” button at the top right of the screen please?

Jeremy_Oglesby · ‎Aug 13, 2019

Airtable does that automatically when a user opens up a shared base that they do not own and the owner has allowed to be copied. it’s not something I created, it’s a feature of Airtable’s base-sharing functionality.

Expert_Credit_S · ‎Aug 13, 2019

so that means that I can take mine out of the universe and just used shared base links?

Jeremy_Oglesby · ‎Aug 14, 2019

Yes, if you are wanting to share your base “in-house” only.