Show and Tell

While answering a question on the forum about Checklists, I decided I would write a blog post about how to use Zapier to automatically create Checklists, assign them to a project and then to a collaborator… Creating Checklists with Airtable and Zapier and here’s a link to the base: I hope you find this useful!!

I’ve created a UK version of my base to calculate holidays as input to WORKDAY() or WORKDAY_DIFF(). (For detailed information, see the post referenced above.) The base takes as input the years for which you wish to calculate holidays and the applicable area (England, Wales, Scotland, Northern Ireland, or the Republic of Ireland). The output is a field called Holiday String containing a ISO-formatted list of governmental holidays for the specified locality, ready to be copy-and-pasted as an attribute to either workday function. (Specifying holidays enables the functions to take both weekend days and holidays into account in their calculations.) This base requires a much more complicated algorithm than the earlier one, given either Good Friday or Easter Monday is an official holiday in the U.K. To calculate either date, one must first arrive at a date for Easter itself — a computation that has remained challenging for hundreds of years. Having absolutely no idea how the calculation works

A silly little base that does one thing: It generates a string of ISO-formatted holiday dates as an input to WORKDAY() and WORKDAY_DIFF(). As published, it supports current U.S. federal holidays, with options to deselect three federal holidays (Martin Luther King’s Birthday, Columbus Day, and Veterans Day) some companies do not take off, as well as two non-holidays (Friday before Labor Day and Friday after Thanksgiving) many companies do. For holidays that fall on a specific day of the month (e.g., New Year’s Day, Veterans Day, Christmas), if that date should fall on a weekend, the base returns either the previous Friday or following Monday as appropriate. It’s been lightly QAed — by which I mean I know it works for 2018 through 2021. The base will return a string of ISO-formatted dates for periods from one to fifty years. (Caveat: There’s a good chance Airtable won’t accept fifty years’ worth of holidays at once, but I’ve not yet been interested enough to find out when it chokes.) Dat

I recently worked on a project that required loan calculations. This was easy in excel or google sheets but Airtable is missing these common financial functions. So, I brushed up on my math skills and with some help from the internet was able to reproduce those functions in Airtable. Thought I’d share with community: Airtable Financial/Loan Formulas: PMT, FV, PPMT, IPMV - Airtable Universe Excel and Google Sheets users will be accustomed to common financial functions used in loan calculations that are currently not available on Airtable. This... Formulas currently implemented: PMT, FV, PPMT, IPMV

Chinara James – 5 Nov 18 How to paginate records in Airtable when using the API | Chinara James Airtable’s API is great and so is their documentation. However, I came across a small stumbling block while using it to build a simple project. The problem, Airtable returns a maximum of 100 records…

Chinara James – 19 Nov 18 Getting Started with the Airtable API | Chinara James Airtable is an easy way to get a database up and running for a side project or to quickly prototype an idea for app. I have used it to manage track projects, expenses, client contacts and more.

Is there any trick to get the menu “Hide fields, Filter, Sort” in other languages like French ? Could we add something to the iframe code to replace these specific words? Unfortunately, there are submenus or criteria fields in English as well. The best solution would be a parameter that would make the app displaying these words in a chosen language… Benoît

Possibly of interest to some, the following is an implementation of Soundex pseudo-homophone encoding. As there are variations of the algorithm, care should be taken if using its output to search externally encoded databases. (I think this conforms to U.S. Census encoding, but I’ve found contradicting statements.) To use, copy and paste the following into the formula configuration window of a formula field. It expects to use an existing field in the table named {Word} as input, so adjust field names accordingly. (There are two instances of the field name {Word} in the formula.) Yes, it’s far from a perfect algorithm; yes, it’s rooted somewhat intractably in American English; yes, there are alternative encodings that may be more appropriate for many uses: Feel free to modify, enhance, and improve as you see fit — preferably releasing your improvements to the community afterwards. LEFT( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUT

This may be obvious to many, but it eluded me until today… It is a HELL OF A LOT easier to code (and debug) formulas in Textedit (or the text editor of your choice), inserting returns between segments (B). When you paste your code into Airtable, it’ll smartly remove those returns and create the code block we’re used to seeing (A). A IF({Auto-Status} = “Awaiting Review”, “ :mag: ”,IF({Auto-Status} = “Complete”, “ :checkered_flag: ”,IF({Auto-Status} = “Design”, “ :art: ”,IF({Auto-Status} = “Released to Supplier”, “ :rocket: ”,IF({Auto-Status} = “On Our Radar”, “ :satellite: ”,IF({Auto-Status} = “Production”, “ :triangular_ruler: ”,IF({Auto-Status} = “Cancelled”, “ :skull: ”,IF({Auto-Status} = “On Hold”, “ :zzz: ”,IF({Auto-Status} = “Design Queue”, “ :art: :soon: ”,IF({Auto-Status} = “Content Needed”, “ :question: ”,IF({Auto-Status} = “Unassigned”, “ :busts_in_silhouette: ”,IF({Auto-Status} = “Design Route”, “ :art: :arrows_clockwise: ”,IF({Auto-Status} = “Copy”, “ :pencil2: ️”,IF({Aut

I could not figure out why when doing an alphabetical sort, some records were coming in “at the bottom.” For example I’d have A to Z for records 1 to 100 followed by a second A to Z of records 101 to 200. I could see no extra spaces, or anything to indicate why this was happening. At long last I discovered that occasionally CSV imports (mine were exported from Zoho) have a glitch where hidden tabs can tag along. The fix is a wee bit tedious, as you can’t just do a global replace (I did try that!), but it sure beats the alternative of retyping things (which was what I did for a while before getting determined enough to find the answer). Steps: Open exported CSV file in your spreadsheet program. For each column, insert a blank column next to it on the right. In that column, add the formula =CLEAN(A1) [or whatever cell is your first entry]. Use the copy / drag function (copy, format down) to fill the entire second column. That column now has clean data with no extemporaneous tabs. It

So, I have been trying to find a way to make weekly recurring tasks, and have figured something that works for me. Basically, just a complicated formula that updates the due date field each week to the same day of the week and time of day. Make a table that will act as a template for new tasks. This Post got me started. Make a date field, with the time, that will set the time you want to be your due date each week. Use this formula below, to make a separate field that will update to that day and time, each week, automatically. (You will need to edit the time zone to match where you are.) DATETIME_FORMAT(SET_TIMEZONE(DATETIME_PARSE(CONCATENATE(DATETIME_FORMAT(NOW(),‘ww’)," “, DATETIME_FORMAT({due time of week}, ‘ddd’),” ", DATETIME_FORMAT({due time of week}, ‘LT’)),‘ww,ddd,LT’),‘Asia/Bangkok’),‘D/M/YYYY h:mma’) Then have Zapier trigger each week, and use that new date field as the date when creating the new records. Optionally, you could have the zap find and reset the due date of

Without using Zapier or another app, would love to see empty, example bases of how to handle subtasks with Airtable. Thanks in advance.

I’ve created an Airtable to share with a large global research community working on fake news (https://airtable.com/shrhv3Vlezp7oOZIZ) and wondering if there are any tips out there for how to make this: More easy to navigate / accessible for people who are new to Airtable? Foster collaboration? would love to have other people add records to this as well, but a little worried about making it a completely open/editable database. Thanks! :slightly_smiling_face:

Is Airtable capable of creating a table-view is this? Basically, I’m trying to track 8 episodes through various stages of my editorial process. I want to record when each of its milestones happens (rough cut, notes received, fine cut, etc etc). But I also want to see the status of every episode at the same time. So basically each point within the table would be its own database record B7, B9, C7, C9 etc… Kindly let me know if a followup explanation is needed. Thank you in advance,

need help setting up an inventory database. Anyone interested in the job??

An intro : A few days ago, working on one of my bases (dedicated to my pets), I requested some help to know if there was a way to track down, within Airtable, the Opening and Closing times of stores (or whatever opens and closes at some point), as the {Date} fields and {Duration} fields actually don’t allow an easy solution :thinking: . Well, fortunately, W_Vann_Hall gave me a nice perfectly working solution for which I’m still very thankful :slightly_smiling_face: :thumbs_up: , put everything in a demo base Airtable - :clock8: Business Hours demo so I could see how it looked and gave me all the explanations so I could understand how it works. I was well served! :winking_face: The only tiny problem was that I needed to track down mid-day closing and opening times too :face_without_mouth: . (From where I am, there are lots of stores that close at mid-day) It took me quite a while, a lot of thinking :thinking: and testing and a lot of coffee :coffee: , but today, I finally

I recorded this video of a system I built using Airtable, Lob, and Zapier for automating & managing physical mail campaigns. I hope it is useful to anyone who is interested in using Airtable to manage this type of business workflow!

I love Airtable, but its not perfect. I also love Zapier. I wrote a blog post about how to combine the 2 to overcome a shortcoming of airtable: Medium – 30 Mar 18 Airtable Dynamic URL to Attachment Using Zapier – Dan (Danny) Fellars – Medium Today’s zap includes just 1 solution: Airtable. Airtable is quickly becoming my new best friend. However, its not perfect. I wanted to… Reading time: 3 min read Enjoy!

Hi, I’m learning to use the new Maps block and would like to be able to add a track from a GPX file to my Map block. Is this possible? Thanks!

In a recent reply to a post in Support, I set out a step-by-step process to create a table intended to drive an Airtable Chart Block bargraph, based upon the multi-record calculation routines I described earlier. In this case, the routines are not used to access values across multiple records; instead, they drive the population of a third table, allowing for the date and value from the previous X number of entries to be charted. At each new record from the [Main] table is added, the Xth value is dropped, the remaining values are moved up one position each, and the current day and value displayed as the most recent. Aside from linking each newly added [Main] record to [Calc] — a task that adds two mouseclicks to the record creation process and that may easily be automated using Zapier or equivalent middleware — once implemented, the routine should require no additional maintenance for nearly twenty years. Again, the reply describing the steps needed to implement the chart is here; the i

I work for a consulting company called Optimi. In our work, we’ve seen plenty of folks (especially small businesses) who don’t have the financial information they need when they’re making critical decisions. Here’s how we used Airtable to solve this problem: Medium – 26 Mar 18 Showcase: how we use Airtable to enable financial decision making Hi! Rupert here from Optimi, writing again about our work with Enspiral Dev Academy (see previous blog post here). Enspiral Dev Academy is… Reading time: 5 min read Would love to hear any comments or feedback on this story :slightly_smiling_face:

Hi! A little while ago, I pulled information from a baker’s dozen rankings of episodes from the first four seasons of Black Mirror, Charlie Brooker’s dystopian look at technology and society, produced originally by Channel 4 and later by Netflix. I had been amused by there being general critical consensus as to the series’ high quality but absolutely no agreement as to the merit of individual episodes. I thought by gathering the data into one base and subjecting them to various analyses I might be able to identify trends or commonalities not apparent at first glance. Nope: They don’t exist — or, if they do, they are still too subtle for my ham-handed processes to identify. Accordingly, I followed the time-honored tradition of less-than-scrupulous researchers everywhere: If the data are inconclusive, change the goals. I had been looking for a project with which to test Airtables new Blocks feature, still in beta test at the time. If I couldn’t use Airtable to identify previously hidden

The link below is to a base I developed for tracking medical expenses. It can track expenses for any number of patients, medical services, and providers. The base allows the recording of charges, insurance amounts (disallowed and allowed), deductibles, co-insurance and co-pay amounts. It also can track amounts paid by insurance to the provider and to the insured, as well as payments made in settlement of bills from providers. There is a main Medical Expenses table and additional Patient, Service, and Provider tables. Each field has a description of what it is for, and there are several different views of the main Medical Expenses table. Of course, the sample data in the base is just that and is intended to show the various types of entries that can be necessary in recording expenses, insurance rulings, and personal payments. Copying the base is enabled on this read-only link. I hope that some will find it useful. Medical Expense Tracking Van Zeck

I need to change the ‘description’ associated with my account userid, but haven’t been able to find where to do it. Where might I look?

Yup, you just need to create a calendar view, share the calendar, copy the ical and then suscribe to this calendar with mac calendar, outlook calendar or google calendar. On the local calendar you can add “1 minute before” reminders. Working amazing for me and i can do “time blocking” with that :grinning_face_with_big_eyes: REMEMBER! You need to add start and end time to every task. I fucking loooooove airtable, can anybody send me some stickers here? i need to share the love (and take out the trello)