An introduction to Airtable: data management for your FYP

An introduction to Airtable: data management for your FYP

My Final Year Project was a valuable learning experience for me. One of the things I learnt was how to use various tools and software to facilitate the research and writing process better so that I could focus on the project and not get bogged down by time-wasting procedures, e.g. data management, cleaning up citations, etc. I had been planning to blog about the tools I used for my FYP, so here I am with my first post.

Quick question for you: while in the midst of your research project, how do you consolidate your data, quantitative or otherwise? For many of you, it boils down to needing a program that imitates a table so you can fill in rows and columns. In many cases, people end up using Excel for this. Even until university, Excel appeared excel-lent (heh) for recording data. Many working professionals still use Excel at their jobs for databases. But at the preliminary stage of my FYP data collection, Excel began to frustrate.* I later learnt that Excel, while suited for basic databases, isn’t really purpose-built to be a database; it’s primarily an accounting software with some basic data analysis and reporting built in.

My FYP data collection, briefly speaking, was a literature review. I was reading academic papers, and trying to tag them with issue-based categories pertinent to my project framework, e.g. socioeconomic issue, conservation issue, wildlife trafficking issue, etc.

Here were the main issues I faced:

  1. When I wanted to ‘tag’ a paper (e.g. under a ‘Benefits’ column’, I basically had to type out a category. If I misspelled the category or decided to change the phrasing for the same tag on a subsequent paper, I had to edit the previous one manually (Ctrl-F and Replace, et cetera). I ended up with multiple versions of the same tag, which I would struggle to sort out when I wanted to come up with a descriptive statistics summary.
  2. My research database was initially paper-based (i.e. every row was a record for a paper, and I would fill in relevant information about everything else in the columns). I later realised that I also wanted my data to be species-based, i.e. I needed another table with every row being a record for a species, and relevant data filled up in the columns, including the papers which mention that species.

Not sure what I was talking about? Here’s a capybara.

A capybara.
A capybara.

A capybara (Hydrochoerus hydrochaeris) is one of the wild species that was being bred in commercial captivity, so it was included in my project. The capybara was being farmed in four countries and was documented in three papers. The papers were providing different assessments on the conservation impacts of farming the capybara. Do I sort this info according to species, papers, or country? Or should I sort all three categories in three different tables and keep all the relevant information consistent between them as I read more papers? The latter is the best idea but also the hardest to manage manually.

Problem (1) is something that can be averted in Excel if one is extra careful and meticulous. It’s just that Excel is prone to such errors, and data entry requires much more verification and double-checking than it should have. Problem (2) was something that neither Excel nor Google Spreadsheets seem able to do automatically. Something in the back of my mind nagged me that there was probably something out there that could do what I desired, but for months I basically filled up the Papers Table, then manually updated the Species Table with the same information. I didn’t even bother making a Countries table. It was prone to error, and I constantly wished for a better way.

Welcome to my nightmare: my original Papers table in Google Spreadsheets, where I listed species, countries, and other info.
My Species table, where I manually created a relation to the Papers table under the column ‘Relevant Papers’. You can see how changes made to either of the tables made keeping them consistent a pain.

What I didn’t know then was that I had independently ran into problems that data management professionals had been also frustrated with since the 1970s, and had already addressed with the creation of relational database systems. I later found out about SQL and Microsoft Access, but I couldn’t quickly figure out how to use them. It wasn’t intuitive straight after installation, and I didn’t have the time to devote myself to learning a database platform which I wasn’t able to pick up right off the bat.

Enter Airtable. I stumbled across it while hunting for database alternatives, and unlike the rest, I was able to use its basic functions almost immediately.

Airtable is free and accessed through the Internet, which was a big plus for me, since it was hardware-independent and I didn’t have to pay any money to give it a try. In Airtable, you create ‘bases’, which are the equivalent of an Excel file that can contain multiple sheets. These bases, like Google Docs, can be shared with others with varying access privileges e.g. owner, creator, edit only, read only. This opens up the base to collaborative use.

Airtable markets itself as a cross between a spreadsheet and a database, and anyone can see why. On the surface, it resembles Excel and other spreadsheet apps more than database apps like Access; it had rows, columns, and multiple tables in one base. The interface looked even simpler than Excel, and manipulating the cells just as easy;  there was no meta view where you had to define the relationships and field types like in Access. But underneath the friendly appearance and ease of editing was a surprisingly versatile relational database.

Fullscreen capture 13102016 24307 PM.jpg
My FYP database, which I took a few days to transfer to Airtable. Multiple categories are clearly delineated within one column, and records can be filtered and sorted based on these categories. This picture also shows a file upload column and a checkbox column. The other tables are relational to the primary table and changes are automatically updated through linked records across the base.

Unlike Excel, the first row and column are frozen and locked by default, and you can’t change it. That is because the first row is used to define the data types for the respective columns, and the first column is used to identify the record for each row. Familiar to seasoned database managers is the ability to assign different data types to the various columns e.g. short text, numerical values, dates, etc. Some of the more unconventional but useful data types available include checkboxes and file attachments, the latter which can be immensely useful for sharing files between members of a base.

But the feature which got me the most excited was a data type called Linked Records. When selected, it converts words separated by commas into categories, and lists them on another table as the first column. This is the ‘relational’ part of Airtable. If you update the column with a linked record, it also changes the other table automatically. This was the part which solved both problems number 1 and 2 for me. Linking records or creating relationships between tables is supposedly something that you can also do in Microsoft Access and SQL. However, I spent hours trying to figure it out in Access and I still couldn’t replicate this functionality. Also, SQL would require me to learn their language before I could begin using it. Call me stupid, but doing the same thing in Airtable just required a few clicks through a spreadsheet-like interface I was already familiar with. If you need something that does the job without requiring a database management training course to use, Airtable is ridiculously simple and easy to pick up, yet incredibly functional, especially for a project with the scope of an FYP.

I filled the Papers Table and created a Linked Record to a table for Species.
Here is the Species Table. The Papers under the Papers column are linked to the Species Table and filled up by themselves! Then I made the Countries column a Linked Record as well.
Here’s the Countries Table with the Species automatically updated.

Airtable also features a Formula field, which allows you to manipulate data in a column in the same way you would in Excel. It has most of the same functions that Excel users will be familiar with. But unlike Excel, you write the formula in the header row to dictate all the results in the records, rather than copy-pasting/drag-copying the same formula down the column in Excel. There are plenty of functions available and creative ways to construct your database so that it best fulfills your needs; it’s definitely worth your time delving into how to use the formula field and the rollup field once you’ve figured out the basics. For advanced Excel users, these slightly more technical aspects of Airtable allow you to unlock a higher level of potential.

Another useful feature which apes an existing Google Docs function is the ability to create forms that can be shared like a survey to collect data. In this way, you can populate your database directly from online survey questions, instead of transferring the results of handwritten survey forms to a database one-by-one.

A few caveats: Airtable can’t really do data visualisation, but you can export the data as a CSV, then import it into a data visualisation software like Tableau or Excel. There also isn’t really conditional formatting like in Excel (although this function can be emulated somewhat). That being said, Airtable is a relatively new product that is still being improved, so a few of these features may come along at some point. Also, Airtable does do one thing worse than Excel: it’s not really meant for 2-dimensional calculations. That’s what a spreadsheet program like Excel is for.

I hope this basic introduction gave you a quick look at a tool which I found extremely invaluable during my FYP. Without Airtable, my data management and interpretation would have been more protracted and torturous than it should have been. I’m glad Airtable came out before I had to do my FYP, and I hope you also find out how it’s just a much better option for data management than Excel ever was.

If you’re interested in trying out Airtable for your FYP (or any other project), I’ll like to invite you to try it at this referral link, and let me know how it goes!

*Or in my case, Google Spreadsheets, which is pretty much the same thing, except reliably cloud-based. I wanted something I could access anywhere.

Food Review for Si Chuan Dou Hua

Food Review for Si Chuan Dou Hua

My friend and food blogger Justin Daniel Pereira recently gave me an opportunity to contribute a food review to his blog, GourmetEstorie. For that review, I wrote about a 100-dish ala carte buffet for Sichuan cuisine at Si Chuan Dou Hua. You can read the review here.

The Precious University Summer, or: 10 Better Things To Do Instead of Rag

The Precious University Summer, or: 10 Better Things To Do Instead of Rag

I woke up this morning to this announcement in the Facebook group for my faculty:

“Hey what are you doing in summer?”

Have you been asked this question lately? Wondering what to do for summer? Why not join USP Rag 2016! Just come down and have fun this summer, working together with freshies as well in Design, Engine, Costumes and Dance (yes dance, seniors can dance too ^.^) No experience required! This year, the overall theme of RAG and Flag 2016 is “Go Beyond”; the theme for RAG is going to be “Big World, Bigger Dreams”. So go beyond your bed and join us in a big world to fulfill bigger dreams!

This message left me both bemused and exasperated. Talk about a misnomer for a theme; Rag is the epitome of living in the small sheltered world of NUS and settling for something else other than actual dreams. Perhaps I should first explain what Rag is for those who are unfamiliar with it.

In the National University of Singapore (NUS), we have a unique schoolwide tradition: come May, students from every faculty, of which a majority are freshmen, will begin preparing for Rag: a one-day event akin to a dance performance with a giant float as a backdrop that happens at the beginning of August, near National Day. The preparation encompasses dance practice, constructing set pieces from ‘recycled’ materials, and costume design. Before the preparation phase, people from the Rag committee will usually ask students to donate their trash such as used plastic bottles, cans, and cardboard for use in the float construction.

Since my freshman year, I have been ideologically opposed to Rag for environmental reasons. The long and exhausting float construction effort culminates in a one-day event after which the float is dismantled and trashed, and is effectively made unrecyclable as mixed materials are glued together. To the credit of NUS, they have been trying to make Rag more sustainable over the years.

Additional issues other than environmental were also examined in a 2011 documentary called ‘Rags to Riches’. The documentary found that inter-faculty competition and rivalry overshadowed its original intention of giving back to the community through fundraising. Some of these issues were discussed further in an article written by Kwan Jin Yao for The Ridge (NUS Student Union’s official publication). I’ve also heard first-hand from ex-raggers how Rag can take an emotional toll — a former rag director I’ve interviewed described his experience as ‘very, very depressing‘.

The primary focus of this article addresses the biggest issue I have with Rag: frankly, participating in Rag is a waste of time. Rag demands the commitment of a ‘ragger’ for most of the summer, for activities that are unlikely to be of either extrinsic value (for your CV) or intrinsic value (for your self-fulfillment). And as a soon-to-be-graduate with the privilege of retrospection, summers are not to be wasted. A typical 4-year university graduate has only 3 summers, and there are many potential activities one could embark on to improve oneself.

In typical millennial fashion, I will list the better alternative summer activities to Rag that a freshman or pre-university student should do, in the form of a listicle:

1. Do an Internship

It’s never too late to get some work experience. This is where the irony of the 2016 Rag theme comes in. What could be more fitting than learning how the ‘big world’ beyond NUS works by actually getting some working experience in the working world?

2. Start a Business

This isn’t for everyone, but for the more entrepreneurial among us, a start-up could be a good project to commit to in the summer months that can provide invaluable life lessons to being your own boss. Not that I would know; I’ve never done this. If anything, it’s definitely a ‘bigger dream’ than building a float that is meant to be taken down after show day.

3. Travel

Travelling is a huge eye-opener, especially if you go off the beaten track. You can learn about other cultures through their food, architecture, and locals, or enjoy nature if you decide to go hiking in the forest or up a mountain instead. If you don’t have a lot of money to go far, even Southeast Asia has plenty to see. The benefits of travel have been covered ad nauseum by others; I don’t really need to say any more.

4. Learn New Skills

As I’m about to graduate, I’ve learnt a lot, but there are still a lot of skills I wish I had acquired that would make me more employable and useful. I haven’t learnt MySQL, for database management. I can’t code to save my life (beyond a year 1 class in C which I’ve forgotten). I’ve only reached level 2 in Thai, barely enough to converse at a primary school level. I’m still not confident with my statistical analysis skills. I’m only a novice in R. I don’t know GIS.

As a young freshman, I didn’t even think about what skills I should be learning in order to prepare myself for the working world. I didn’t even know that some of these skills were even a thing. The tragedy is, some of these skills are things that you can easily self-learn, given enough motivation and time.

Talk to seniors or acquaintances in the industry you want to get into, and ask them what skills have become useful in their line of work that you should pick up early. Chances are, they’re not going to recommend that you take part in Rag.

5. Play Sports

There are many sports clubs in NUS, and specific ones for your faculty or hall, for all kinds of sports. Canoe polo, fencing, martial arts, floorball. IFG season starts in late August, so why not train in the representative sport you’re interested in and also make friends in the process? Alternatively, join a varsity sports club and aim for even loftier achievements. Rag crunch also often occurs around the time IFG trainings start, and could prevent you from being drafted for a team if you didn’t attend enough trainings.

6. Join Camps

Beyond faculties, many clubs in NUS also organise camps for freshmen, e.g. windsurfing, Qrientation, Sports Camp, among many others.

7. Read Books

Socialising not an important goal for you? Enrich your mind and soul in the comfort of your home!

8. Netflix*

Or watch Unbreakable Kimmy Schmidt, Fargo, Game of Thrones…you’ll probably derive more enjoyment and excitement from the adventures of Jon Snow.

*Chilling optional.

9. Write

If you’re the literary type, pen out a script for a play. Write poetry daily for a month. Write a story. Blog.

10. Volunteer

How could I forget this one? Volunteer for a cause you care about: the environment, the disabled, the poor, the elderly…anything for the community. Rag & Flag is ostensibly about the community, but you could be doing so much more for society as a long-term volunteer in a local organisation.

If you really enjoy Rag, go ahead and do it. It’s your life. But if you’re uncertain about how to spend your summer, don’t let friends or seniors coerce you into doing something you’re not sure will be enjoyable for you or enrich your life. University summers are few and precious, and after graduation, it’s unlikely that such long blocks of time will continue to be available. Use them well.

22 Feb 2016: An update

I haven’t updated this blog in months, but here’s an update of what’s been going on in my life recently.

I’m in my final semester of university, finishing up what remaining modules I have left as well as working on my FYP on wildlife farming. I’m also trying to look around for jobs and internships related to my field that I can embark on since nothing is confirmed yet. The future is uncertain, but frankly quite exciting.

I also revamped this blog to look a lot neater and minimal, and also transform it into a general professional profile page for public representation. The tone of this blog will also be a lot more professional but still casual and friendly. I’m hoping to be more semi-regular with updates and blog about things I’m learning about and interesting in – basically a form of public documentation that can articulate my thought processes from week to week. If I feel like writing a story, that might still come up, but it’s not currently my primary focus right now.

Happy Lunar New Year to everyone, and I hope you’ll read more words from me in the year to come.

Farewell, Lee Kuan Yew

Farewell, Lee Kuan Yew

This is not meant to be an eloquent eulogy, just a reflection of an important milestone in a history that I am a part of.

It is pouring outside as I type this. Thousands are standing by the side of the roads where Lee Kuan Yew’s cortege will make its way across the island. I did not queue up like many of my peers for hours to pay respects during the public wake. Neither am I now standing in the rain, waving a flag while waiting for him to pass.

Admittedly it is because I am a somewhat practical person, too practical perhaps. I have homework due tomorrow that I need to finish, and I can’t afford to stand two to three hours in the rain jostling with others for a three-second glimpse of the procession for a dead man that I am not related to.

Lee Kuan Yew’s death does not fascinate me as much as Lee Kuan Yew’s life. In the aftermath of his passing, I have been reading about the things he said and did when he was in power. He makes quotes that are sometimes evidently sensible, and a few times clearly dated and unprogressive statements. Yet it cannot be denied that he made his mark profoundly on our country, and even on other world leaders, daring the world to think differently about what a successful society and nation could look like. At the same time, he definitely made mistakes, some that seem ludicrously obvious in retrospect. And he wasn’t always hardheaded about them. If he realised he was wrong, he would admit them. He knew he wasn’t a perfect man.

“Let me give you a Chinese proverb “do not judge a man until you’ve closed his coffin.  Do not judge a man.”  Close the coffin, then decide.  Then you assess him.  I may still do something foolish before the lid is closed on me.” – Lee Kuan Yew

In the years to pass, people will study him, to sift out the best decisions he made for this country, and discarding the parts of outmoded political wisdom that may not work in our present time and place. I will remember him in my own way, by reading his autobiographies and accounts of him, to get a clearer sense of his legacy. His character intrigues me. He was one of the most special men of our time. There is much to learn from him.

Seth Mydans: “The public view of you is as a very strict, cerebral, unsentimental. Catherine Lim, “an authoritarian, no-nonsense manner that has little use for sentiment”.”

Lee Kuan Yew: “She’s a novelist, therefore, she simplifies a person’s character, make graphic caricature of me. But is anybody that simple or simplistic?”

Goodbye, sir.

Habits, or: Resolutions for A Better 2015

Habits, or: Resolutions for A Better 2015

As everyone knows, habits are important. They make you or undo you.

The past few years has seen huge shifts in my personal habits. Closer introspection seems to reveal that the neglect of certain good habits and the allowance of poor ones to take root have in aggregate led to a poorer version of myself that I would like to be.

Here are my list of habits:

Good Habits I’ve Lost That I Need To Get Back

1. Reading: before bed, for recreation and during breaks. Mainly fiction, or Pocket

As you can see below, I’m definitely reading less books than I used to. The majority of reading nowadays is school-based material, which is often dry and rarely has a long-term impact on my personal growth and life-based education. However, with the secession of USP modules which often pile such readings on you, my hope is that I’ll be reading more fiction in 2015. Also, I’ve been adding more articles to Pocket without following up on them. I still read a lot of online articles through feedly though, and I’ve read the most Reddit of my life last year. I think a good balance between all my recreational reading is necessary.

Fullscreen capture 26122014 90353 AM

2. Writing: stories, blogging, journaling

Writing used to be so important to me, and defined who I am. The fact that I’ve stopped writing for personal fulfillment is really sad. Stories and blogging has given way to essays, and ephemeral Facebook brain farts. You can probably tell how rusty I am from the dry boring tone of this blog post. I’m hoping to blog at least once a week in 2015 just for writing and public reflection, and hopefully (though this has always proved to be difficult to maintain) journaling daily with Evernote. Also, I hope that I’ll find time to pen some short stories, just for fun. Writing a full-length novel at this point might be unrealistic so we’ll start with more manageable but still challenging goals.

3. Spiritual: Prayer, Bible reading, Attending Church

My parents would be happy to read this. Last year saw the complete secession of prayer and bible-reading. Church attendance was sporadic. Despite my still strong feelings that the Church as an institution is inadequate and often disappointing as a spiritual community, I think it is still important that this continues to be a part of life, imperfect as we all are. Praying daily before sleep would be a good start. I used to not be able to find a reason to pray – I felt that God had given me more than I could ask for or that he wouldn’t give me things that I wanted anyway. But maybe I should pray more for communion and thanksgiving, since I’ve noticed that He doesn’t entertain requests in the light of some bigger plan. I’ve given up on believing that my vision of my best life is what will be happening, but even then, maybe acceptance of my destiny doesn’t mean I should reject Him.

Bible reading is important, if only to keep in touch with my faith. I might also want to try reading the Quran on my shelf as well. I’ll probably start to attend church regularly next year, although extra commitments like joining a cell group remain on the table.

Waking Up Early

My sleeping time for the past four years has seen a gradual shift from 11pm to later than 1am and now to a general stabilisation of 1-2am. Waking up time has moved from 7am to 9-10am when I have no classes. This is very bad because it also messes up with the habit that I once created to exercise after waking up early. Waking up late takes exercise away from me as well. Sleeping early may result in the neglect of social activities, but I’ve noticed that my increased involvement in late-night socialising has not led to an appreciably stronger social network. I realise now that sleep is rarely worth sacrificing. Tiredness is the first enemy of productivity. If I get this in order, everything else should fall into place much better.

Eating Less Meat

This might actually be the easiest habit to get rid of. Ever since a 21-day attempt at vegetarianism in and a largely no-beef diet in 2013, I realised that I do not have to eat meat most of the time. I still like meat, but to eat it at every meal nowadays feels too excessive. For mainly environmental reasons, I think it would be a good idea to reduce my meat consumption significantly. But it would be hard to eliminate entire meat types from my diet as I’ve found that my upbringing has predisposed me to enjoy all sorts of food. I think I could try to attempt several quantifiable ways of reducing meat intake, such as Meatless Mondays or Meatless Breakfasts or something else of that sort.


Always on the list. But at least I exercised enough last year to pass IPPT. It’s just that I let myself go quite badly after I passed. I’m hoping to find more physical activity with recreational sports such as frisbee or kayaking like I used to. The 7-minute workout also needs to make a comeback. I can still do 8 pull-ups without difficulty though. That’s a good thing.

Bad Habits To Get Rid Of

Surprisingly few bad habits come to mind, but the few I have can be damaging to time management, mainly: playing Dota, compulsively scrolling through Facebook, and procrastinating. The latter is more of a bad impulse more than a habit to get rid of, which can be gamed successfully. Facebook is becoming less important to me and so perhaps that won’t be too much of a problem. But Dota is the difficult one. However, I’m hoping it’ll be easier to be less addicted to it once I let myself play other computer games for recreation. Also don’t forget: books!

I was going to list bad habits I successfully got rid of and good habits to maintain, but they were either too few or perhaps so ingrained I do not even realise I have them or have got rid of them. In any case, this is a good start to figuring out how to make a better me in 2015.

Movie Review: Short Term 12

Movie Review: Short Term 12

I wrote a movie review of Short Term 12 starring Brie Larson. Really moving film.

The Cinnamon Roll

By Benjamin Ho

It is quite fitting that the Perspectives Film Festival’s opening film is one that emphasises learning to see a person’s life through their own eyes. Short Term 12 depicts a temporary home for troubled teenagers, exploring life in their shoes as well as life in the shoes of the twentysomething staff who run the home. Director Destin Daniel Cretton’s second feature-length film after I Am Not A Hipster, Short Term 12 was inspired by his experiences in a group facility for teenagers.

The film chooses to use a cold open, leaving you guessing as to the exact nature of the home for a while. Cretton’s modus operandi for your discovery of the world of Short Term 12 is through mostly showing and a bit of telling – snippets of conversation between kids and the staff, and around-the-door camera angles that seem to put you in the eyes of a new worker…

View original post 398 more words