Spreadsheets

As soon as someone finds that unordered notes or free-form documents aren’t able to provide enough structure or clarity to all the information they need to record, they’ll likely turn to a spreadsheet. A spreadsheet is a digital document with a grid of “cells” that can each hold some information, such as text or a number (in some cases it’s possible to put images or file attachments as well). Spreadsheets are familiar to anyone who’s ever worked in any sort of office environment, as they provide ways to create ad-hoc lists and tables, and record information in a neater layout, whilst still remaining free-form enough to not seem restrictive.

There are plenty of advantages to using spreadsheets. They’re reasonably intuitive and don’t require any formal training to get started with, at least not until you want to start summarising or performing calculations on the information in them. Simply click on a cell and start typing and apply some formatting if needed to make cells visually distinctive.

Spreadsheets are free-form, so there’s no need to structure information in a specific way (although you might want to do this regardless if you need to be compliant with some other system or if you adopt a convention that others have been using for consistency). They’re also portable, meaning that it’s easy to get information in and out of them (indeed, many Cloud services have an option for importing and exporting spreadsheet data).

Even though spreadsheets are easy to get started with, they have a lot of powerful functionality under the surface. Most prominently is that any cell can contain a “formula” which is simply something calculated from something else. This can be a total of a column of numbers, for example, but it can also be the combination of two cells of text, or other types of calculations with greater complexity.

As spreadsheets lend themselves well to tables of data, it’s also fairly easy to use them to generate a variety of charts. For example, if you had a spreadsheet listing a breakdown of costs for a particular shot, you could generate a pie chart to show the distribution of costs across the shot; likewise if you had a list of how many soundbites were recorded each day, you could produce a line graph showing their accumulation over time.

Though spreadsheets have been in use on desktop computers for decades, one of the common problems with them is that they’re not inherently collaborative. If two or more people want to make changes to a particular spreadsheet, they either have to coordinate with each other, sending copies of the spreadsheet back and forth, and even if there’s shared access to the spreadsheet itself (if it’s on a network drive for example), one person may have to close their copy to allow another to open it.

With the Cloud, though, that doesn’t matter. With Google Spreadsheets (docs.google.com), for example, all data are stored in the Cloud, so there are no files to manage (although they can be downloaded as files if needed, and individual spreadsheets can be organised alongside uploaded files on Google’s Cloud storage service). Using a Cloud-powered spreadsheet like this means that it can be easily shared with multiple people (each of which may be given permission to edit or just view all or certain parts of the spreadsheet) and that multiple people may access the spreadsheet simultaneously. This then allows for several people to add information to a particular spreadsheet, or have one person edit whilst others are viewing it, or allow people to structure the spreadsheet more interactively together. With all the data stored in the Cloud, it also means it’s possible to edit it via mobile devices.

Spreadsheets do have several drawbacks, though. First and foremost, trying to use them with any sort of relational information, such as connecting information about a specific television episode with information a particular season can be tricky. Furthermore, it can be difficult to search for things in a straightforward way, or cross-reference different types of information. Then there’s the inherent difficulty with trying to audit everything, to keep track of which changes to the information was made when and by whom.

There are ways to do these types of things with spreadsheets, but they tend to be complicated and not necessarily intuitive (Cloud-based spreadsheets seem to have an additional disadvantage compared to their desktop counterparts, which is that they can get rather unwieldy once they pass a certain number of cells of data).

As a rule of thumb, once you start spending a long time trying to do something in a spreadsheet that seems like it should be much simpler, it’s probably time to upgrade to a new approach.

Relational Databases

Because spreadsheets are free-form, you might have one spreadsheet with a list of people and which department they belong to and another with a list of tasks people need to do. But these two lists of information don’t “know” anything about each other, so it becomes difficult to, for example, group tasks by department without resorting to constructing formulae and reorganising the data beforehand.

With a relational database, information can be structured more intelligently. You can be specific about what different things are and how they’re related to each other. In a database, different types of things are represented as separate “tables”, and each table has its own set of “fields”, properties that are loosely analogous to columns on a typical spreadsheet (so “due date” might be a field, as might “name”). Each table also has a number of records, which can be thought of as the rows on a typical spreadsheet (so each person recorded in a database might exist as an individual record on a “people” table).

The immediate benefit is that everything is structured in a consistent way. Each record in a table has the same fields as every other record in that table so aggregating or filtering all the information is straightforward. Furthermore, with relationships correctly defined, it’s similarly easy to group and find related data.

Lots of systems exist for creating databases, and most of them are designed to be collaborative without any reliance on the Cloud. The main draw to using a so-called Cloud database, such as Google’s Cloud SQL (cloud.google.com/sql) is that you don’t have to worry about all the setup and maintenance from an IT perspective. However, these provide only a “back-end” to a typical database application. This means they provide a way to structure and store data but no “front-end”, or easy way to allow for editing and displaying the data. This means that significant time and expense must be spent on creating these systems.

To counter this, some Cloud services that provide an accessible approach to creating and storing information in a custom database, whilst also providing an easily customisable interface for displaying and editing the underlying data are gaining popularity. Airtable (airtable.com), for example, provides browser- and mobile-based interfaces to create multiple databases, each of which can have different tables, and each of those can have a number of different types of fields created. Best of all, the interface is easy to get to grips with and allows for customisable form and grid views of all the data. Relationships between the different tables can be created in a very free-form way, simply by adding a special type of “link” field to data on other tables.

The problem that remains, though, is that designing and creating a database from scratch is a daunting task even without having to factor in the creation of the front-end. Depending on the specific database system used, this can be difficult from a technical standpoint, but it can also be difficult conceptually. Often a particular structure (or “data model”) that seems to make sense initially needs to be changed later on to accommodate different priorities or unexpected limitations. Furthermore, setting up relationships between different types of data can be difficult to get right. Every time a data model needs to be changed, it’s possible that the data within will need to be reconciled in some way, which can be time-consuming and complicated.

Box 10.1 Back to Spreadsheets

As useful and powerful as databases are, particularly when compared to spreadsheets, an interesting side-effect of using them is that it’s often useful to dump out data to spreadsheets in a number of situations.

Sorting through duplicated records is one such problem. When there’s duplication of data in a database, it can be difficult to reconcile the duplicates easily within the database itself. In this situation, where possible, it’s preferable to first export the data and open the data in a spreadsheet, then filter the duplicates and move or copy information between different rows, before finally updating the main database with the reconciled data.

In addition, it’s not a simple matter to just make a copy of a database to give to someone else in most cases. Even in those database systems that do offer a way to make a direct copy of the data, there will typically be lots of additional information (metadata) or structural information that is not relevant to the data you’re trying to send. In this instance, it’s often a lot easier for the sender and the recipient to just export a spreadsheet and send that.

Hybrid Approaches

As spreadsheets and databases actually have a lot in common with each other, some approaches have been to try and combine the relative strengths of each into a hybrid approach. Fieldbook (fieldbook.com), for example, presents a streamlined spreadsheet interface to users but leverages much of the power of relational databases by allowing links to be established between different “sheets”. So you could create a sheet for tasks that need to be done, another for projects, and then have a “project” field on the tasks sheet that is linked to the projects sheet. When you edit one of the cells in the project column, you choose between different projects that are listed on the projects sheet.

Behind the scenes, these systems are maintaining a relational database, but the end-user doesn’t have to understand the mechanics to use it effectively. As far as they’re concerned, they’re just using something like Google Sheets, but with a few extra features. The system even retains familiar features from other spreadsheet applications, such as creating formulae and filtering and sorting.

Whilst these approaches may be suitable for collating data together quickly and informally, they can be considered too generalised for more overarching scenarios. Spreadsheets, for example, are a great way to build a list of episodes in a television series, along with specific information like the air date or running time, and a database could be constructed quickly as a way to log footage for every episode; however, neither of these approaches are designed to have any “business logic”, that is, some understanding of what each piece of information is from a practical perspective or how they might all fit together to lead you to the successful completion of the production. For that, a more specialised approach is needed.

Industry-Standard Production Tracking Systems

As the Cloud matures, an increasing number of tracking systems are emerging, each of which may be tailored to meet specific business needs. Numerous such systems exist for the software development industry, and other industries such as construction are embracing the Cloud, with tracking solutions catering specifically to their best practices and methodologies. Fortunately, the film and video production industries also have a number of specialised tracking systems available.

Autodesk’s Shotgun (shotgunsoftware.com), for example, is aimed squarely at the visual effects and animation industry. Because of this narrow focus, its customisable database comes predefined with fields, tables, and relationships already set up in a way that works well for those industries. This leads to less time spent trying to map out how the data should fit together and allows users to enter and find information right away. Furthermore, emerging industry trends are able to be anticipated and folded back into the system’s design, which can result in the system actually nudging users into a more streamlined approach in their day-to-day work.

For pre-production, systems like Production Minds PMP (productionminds.com) might be a good fit. Things like scenes and shooting locations are predefined in the system, which also has some understanding of how they relate to each other. This makes it easy to add information that’s relevant and provides structure for people who view the information later on. It also means that common processes like script breakdowns (and revisions later on) are factored in to the system, so there are tools that help manage those processes.

There’s something of a paradox in using systems that are designed for specific purposes, which is that they can be both less flexible and require more training to use. With well-established systems, this may be less of an issue; if a particular system is considered to be an “industry standard” then it is expected that many people within that industry are well-versed in how to use it, but with many of the emerging Cloud-based systems that won’t always be the case. The lack of flexibility can be made up for if the system offers integrations with other systems (or, ideally, an API, though this will likely require some technical expertise to make use of) or there’s at least a way to “round trip” (export, edit, and re-import) data somehow.

Capturing Information

Lots and lots of information is generated at all stages of every production. Some of it is relevant only for a certain amount of time (such as how many sheets of paper the art department needs on a particular day), and each bit of information varies in overall importance (how much of the budget is being used by a particular shot), but for an ideal system that can capture any information, there are two crucial questions: how easy is it to capture this information (what’s the cost of capture), and how much is this information worth (what’s the value of the capture)?

Figuring out the relative value of any information is inherently problematic. Often there’s no way to know whether a particular piece of information will prove useful at some point in the future. Something that might appear to be an unimportant detail at the time (such as the fabric used in a particular costume) might hold great value later on to someone else (such as a visual effects artist trying to animate a digital re-creation of that costume). Experience will help, of course, but it’s not always possible to anticipate every single thing that might prove useful, especially when a large number of people are involved and there’s the pressure of time.

The ideal tracking system, then, instead ensures that the cost of capture is always low enough that the value doesn’t really matter. Going back to the costume example, if the designs and associated notes were created in a digital format, then the process of capturing them merely involves copying them from one location to another, and possibly doing some format conversion. With a sufficiently streamlined Cloud-based system, even that might not be necessary. If the costume designer is storing his or her designs and notes on the same Cloud-based system as everyone else in the first place, no copying or conversion would be needed.

Indeed, productions that are fully committed to using the Cloud will likely have an easier time capturing absolutely everything. The Cloud loves to store information in its various forms, whether chat messages or digital images, and with the proper automation and integrations in place, even completely separate Cloud-based systems can automatically share information.

For processes that aren’t (or simply can’t be) Cloud-based, it’s still possible to capture useful information. As discussed in chapter 7, paper documents can be scanned to create digital counterparts. Optical Character Recognition (OCR) can be used to extract text from scanned documents. Recorded audio can be digitised to audio files that can be played as needed or even sent to be transcribed, again extracting text that can be easily copied and pasted elsewhere, or at least providing an additional, searchable source of information.

Files that begin their life in a digital format will likely be a mine of hidden information. Most notably, media files from digital cameras typically contain a great deal of “metadata”, or additional information, about the media. This metadata might be technical information such as the colour space of the file, or photographic information such as the shutter speed used, or it might even be information like the time and date and location the media was captured. Crucially, all of the data were created automatically without the photographer actively doing anything other than take a picture, and all of the data may be captured and put somewhere else, like a database or other system.

Box 10.2 Digital Clutter

With the convenience of being able to keep everything ever recorded in a digital form, particularly when it comes to the Cloud, comes the problem of clutter. Information may not occupy physical space, but it still has some form of representation. In a digital folder containing only a few files, it can be easier to sort through and find something specific, as well as “discover” items you didn’t necessarily know about that become of interest. As more files are added, both finding specific things and discovery of interesting items becomes much harder.

This can, of course, be rectified by several things, like more sophisticated search processes that infer context better (such as how Google is able to—in theory at least—present the most relevant pages out of potentially millions), but also more considered, hierarchical organisation of files, making it easier to zero in on specific files. As Cloud storage and document management systems mature, we’ll likely see greater development in this area, along with benefits like having things automatically categorised by their properties (for example, documents related to a specific scene might be recognised as such and filed away accordingly, becoming visible only when browsing or searching for documents related to that scene).

For now, though, the best approach is likely to be a well-defined, hierarchical folder structure, coupled with practices such as moving data and files into digital archives as needed, so there’s always direct access to potentially relevant information only.

Organising Information

Though there are several ways to organise digital information on most systems, whether they be files on a computer, documents in Cloud storage, or even mobile apps on a mobile device, the most readily available is the “folder” metaphor. In the simplest terms, a digital folder acts like its real-world counterpart, as a container for things that can be individually labelled, and whose primary purpose is to reduce chaos by collecting similar files together. Folders are a very intuitive and straightforward way to allow users to organise files, both conceptually and visually.

Where the folder paradigm really shines is when there’s the ability to put folders within other folders. With this seemingly innocuous improvement comes the ability to create “hierarchical” systems of data. A hierarchical system can be thought of as a tree, with each “branch” leading to further branches. Just as a book might contain chapters, with each of those chapters containing paragraphs, then sentences, and so on, a hierarchical structure allows for very broad categorisation to present information in a way that “funnels” complexity, and gradually becomes more specific. For example, the highest level of a hierarchical structure might represent the entire production of a television show; this then has branches (or “sublevels”) for each season, and then again for each episode. Then each episode might have its own hierarchy, with sequences, then scenes, then shots, and so on.

Hierarchical approaches to organisation of data have a great number of benefits. They’re almost universally supported to some degree across different systems, and they’re easy to establish. Furthermore, people seeing a hierarchical structure for the first time will usually be able to find what they need simply by browsing through the structure, assuming they have enough prior knowledge on the subject matter and that the structure matches their expectations.

Box 10.3 Master-Detail Representations

There are a few approaches to presenting data, whether a collection of documents, or simply information stored in a database. Perhaps the most recognisable approach is the “master-detail” model. Most recognisable across mobile devices, where screen real estate is limited, the master-detail view presents either a “master” view of a list of items (such as a list of camera lenses), each of which may be individually accessed to display a screenful of “detail”.

With this type of approach, it’s possible to reduce the amount of information on a display at any given time, which can be critical for smaller displays. However, such an approach has other benefits, as it reduces the amount of information the viewer needs to process at one time, in much the same way as organising files into folders does. As the Cloud matures, there will likely be a shift from helping people get information into to the Cloud, to making processes that require using or finding that information more efficient. A reduction in the amount of information presented to people at any time will be a critical part of this, but there will need to be a careful balance struck between reducing the information presented and making people work too hard to get the information they need.

Hierarchical data structures do have some drawbacks, however. First and foremost is that an item can only really exist at one point in the hierarchy. Any data that are effectively shared or split between two different things need to be duplicated in order to be correctly categorised. Secondly, hierarchical structures should be rigidly defined. Shots belong to scenes, and so on. But for certain types of information, such well-defined categorisation isn’t suitable. When defining concepts for a new production, for example, the information produced cannot be so clearly classified. Should a particular piece of concept art be tied to a specific scene or a character? Having to try to fit ideas into a predetermined framework in this way can break the flow of the creative process.

The popularity of systems such as Trello (trello.com), that offer looser, less formal data structures (with Trello, users simply create individual “cards” that can be freely moved between “lists”) are a testament to the need to use a different approach on occasion. But other “taxonomies” (systems of classification) also exist. “Tagging” is one such example. With tagging, one or more labels (“tags”) are applied to a piece of data. For example, a document relating to the safety considerations of the shoot of a particular scene might be tagged with things like: “safety”, “scene 45”, “stage B”, “production management”, “location”, and so on.

With a system based on tags, it becomes possible to retrieve related information based on a particular topic. Looking at everything tagged with a particular character might yield a number of different documents or data points in one go that might otherwise have been split across multiple branches in a hierarchical system. Furthermore, it would be possible to zero in on more specific information by considering multiple tags, for example, looking at everything that contained the tags “set decoration”, “opening sequence”, and “finance”.

Undoubtedly the biggest drawback to tags is that they aren’t readily available, and they aren’t very portable. Modern desktop operating systems have only recently started to provide the means to use tagging with documents, and in the Cloud there are perhaps even fewer options. Overall, the concept is not as familiar to people as with file and folder structures, and it will likely be many years before tagging of files becomes a common practice.

When transferring data between systems, it’s likely that there will be some way to preserve a hierarchical structure, particularly with folders which are well supported, but there’s little chance that the same can be said for tags. Being free-form, it can also be difficult to determine in advance which tags might be suitable or even necessary for a particular piece of data, and so the incorporation of a widespread tagging system can mean that previously tagged information has to be updated to include new tags periodically, to ensure it stays relevant.

Box 10.4 Reclassifying Data

As tempting as it might be to switch between different approaches to organising data, there’s a point where doing so should be treated with caution. If you already have hundreds or thousands of files or records stored in a hierarchical system, it can be an extremely daunting task to attempt to introduce a tagging system without backtracking through all the previous data and painstakingly applying the appropriate tags to each.

Even where it’s possible to devise a system of translation between systems, such as having files tagged based on their location within the hierarchical structure (so documents within an “approved scripts” folder might be converted to contain the tags “approved” and “script”), this will likely paint an incomplete picture later on and not provide the same benefits had the tagging system been used from the outset.

Due to their limitations, the best use for tags is within a closed system, where there’s no concern that vital tagging information will be lost when importing or exporting data. This means that tracking systems and comprehensive production databases become good candidates for using tagging systems, as places where tags can be defined alongside other data being created, as well as being the place where people might make use of tags to quickly find particular things.

With the collaborative nature of the Cloud, tagging becomes a much more viable and attractive proposition. Rather than relying on someone to add applicable tags themselves at the point of creation, tagging information in the Cloud can be done in a more iterative way. Initial tags are added and then as other people edit and access particular things, they can add more tags. Such collaborative tagging can enrich the tracking process, whilst spreading the workload amongst a high enough number of people as to not make it be a chore. Similar approaches have been demonstrated across a number of social media platforms, for example, where a person is able to tag other people’s images.

As the Cloud grows and is used to store an increasing amount of information, it might become apparent that there’s no single approach that will work well in every situation. Approaches that combine both tagging and hierarchical structures might prove superior to more traditional folder-like structures, as well as things like allowing a file to exist in multiple folders simultaneously. Tags might be used to help with searching for things, so searching for “costume” should find items with the corresponding tag applied, but search algorithms could be made to be more interpretive, so that a search for “costumes” would yield items tagged with “costume”, as would “wardrobe”, without anyone specifically adding those tags. One thing’s for sure, though, the more data get uploaded to the Cloud, the more opportunities there are to analyse and summarise what’s there.

Box 10.5 Smart Folders, Smarter Tags

In addition to allowing users to create their own folder structure and copy or move files between them, some systems also allow for “smart” folders. These are special folders that don’t directly store files, but instead have some rules or logic defined, and then display the files that match these rules. For example, a smart folder might be set up to show files created during the past week, that is an image or movie, and that contains “draft” in the file-name. Any new movie or image files that are created with the word “draft” would then show up in the folder and stay there for one week.

Smart folders can be an effective way to get around some of the limitations of traditional folder structures, and although the concept has not yet gained much traction with Cloud storage providers, it’s possible that it’s functionality that will start to appear in the near future.

Likewise, it should be possible to apply specific tags to things based on other properties, or triggered through specific events. Files that originate from a specific location could have predetermined tags applied, or tags could be applied based on the rules concerning the contents of the file, or if, for example, a specific person had accessed it.

Analysing Information

Once there’s enough information available about something (or collection of things), it may become useful to perform some sort of analysis on it, whether to get summaries of different aspects of it, such as how many takes were recorded in a given week; perform comparisons, such as how this week’s shooting compares to the previous week; and more complex analysis like identifying trends or making predictions, such as how much longer the shoot is likely to need.

Some of these things will be easier than others, but one thing is for sure: having information collected together in one place makes it easier to do. As more focused industry-standard tracking systems mature, there will likely be a greater possibility for more useful analysis to be available directly via those systems, without needing to dedicate significant resources based on specific requirements. Either way, it will be much easier to, for example, analyse costs for different things when there’s a single place from which to export a spreadsheet of all relevant financial data.

Google Sheets (docs.google.com)

Google Sheets has been around for quite some time, and as such represents a mature system for working collaboratively on spreadsheets. Changes are saved automatically, with each revision logged and able to be restored to become the current version. There is a robust set of options for sharing spreadsheets with others, and it’s possible for multiple people to edit a spreadsheet simultaneously. Google Sheets is also unique compared to other Cloud-based spreadsheets in that it offers an API.

Apple iCloud Numbers (icloud.com)

Apple’s offering for Cloud-based spreadsheets has roughly the same set of functionality as Google Sheets, although it’s perhaps not as popular. Native desktop and mobile versions are also available for running on Apple devices.

Microsoft Excel Online (office.com)

Although Microsoft’s prominent spreadsheet application isn’t free like the Google and Apple equivalents, it does come bundled with other applications in the “Office” suite, as well as 1 TB of Cloud storage and native mobile and desktop versions. In all other respects it is mostly equivalent to other online spreadsheet systems but does have the edge in terms of its formula and charting capabilities.

Google Cloud Datastore (cloud.google.com/datastore)

Google Cloud Datastore provides a means to store and retrieve any type of data in a scalable, non-relational way, although it can be accessed only via the API. It can be used for free up to certain limits, at which point it is charged out per usage.

Amazon DynamoDB (aws.amazon.com/dynamodb)

Functionally identical to Google Cloud Datastore but with a slightly different pricing model. There’s also a free version that can be used up to a certain limit.

Google Cloud SQL (cloud.google.com/sql)

Google Cloud SQL provides a scalable relational database, with price based on usage and hardware configuration required. Using it means you don’t need to worry about maintaining the hardware or software environment; however, there is no front-end, and as such requires technical resources to make the best use of it.

Microsoft Azure SQL Database (azure.microsoft.com)

Microsoft’s managed database is probably the most complicated to get started with and has all the same caveats as Google Cloud SQL in that it requires a front-end to be built specifically for whatever you need to do with it.

Amazon RDS (aws.amazon.com/rds)

Amazon’s managed relational database offering is unique in that it provides a number of different database technologies to choose from across a number of different hardware configurations.

Fieldbook (fieldbook.com)

At first glance, Fieldbook is similar to Google Sheets but with fewer features (there’s no charting or revision history currently). However, the important distinction is that it allows for “linking” data between separate sheets, providing an easy way to create relationships between different things. There’s also the ability to easily group and filter different things in an intuitive way. The only difference between the free and paid version is priority support and access permissions.

Airtable (airtable.com)

Airtable provides the ability to create relational databases quickly and easily. There are a number of features for power users, such as calculated fields, and it’s possible to generate forms for others to fill in. The main downside is that there are caps to the number of “rows” (records) each database can contain, which might be an issue if you’ve got a lot of data to work with.

Fusioo (fusioo.com)

Like Airtable, Fusioo enables users to build relational databases with ease. Though lacking in some of the features Airtable has, such as an API or mobile version, it allows database designs (referred to as “apps”) to be shared amongst the community to be used as starting points. While there are no published limits on the number of records allowed, there’s a limit of 30 fields per database, which is probably insufficient for many cases.

Production Minds PMP (productionminds.com)

Targeted specifically to the pre-production phase, PMP aims to manage everything from script breakdown to casting and scheduling for shoots. However, it is lacking in advanced features like an API or any integrations, and as such is heavily reliant on import and export to spreadsheets.

Shotgun (shotgunsoftware.com)

Primarily targeted to the visual effects industry, Shotgun has been gaining adoption amongst film and video production in recent months. It boasts a good number of customisation options, as well as integrations with several desktop applications. Limited functionality is also available via the “Shotgun Review” mobile application.

Ftrack (ftrack.com)

As with Shotgun, Ftrack is aimed primarily at the visual effects industry but is starting to target more of the production process. It also features an API and integrations, as well as automation through the use of “actions”, customisable modules to perform certain tasks, and built-in support for time tracking.