The Great Debate: Claris FileMaker vs. Excel

In the world of Claris FileMaker development, Excel is not well-liked. It’s the elephant in the room full of majestic steeds, only securing an invitation to the party because the elephant knows horses in high places. Many Claris FileMaker developers consider spreadsheets to be outdated, slower, and less capable imitations of relational databases. Developers will provide an “Export to Excel” button begrudgingly, but many don’t even have a licensed version of Excel installed on their machines. One Claris FileMaker developer I worked with said there is no reason to ever use Excel if it can be done in FileMaker, scrunching his nose up at the idea that a spreadsheet could deliver any value which Claris FileMaker could not. Now, I might be biased because I only came to know the horses through the elephant, but I disagree. I think there is a time and place to use each tool, and knowing the circumstances which are best served by each of them can streamline your workload to maintain and deliver accurate data with the most efficiency.

Use Claris FileMaker… to mandate valid data entry and track changes to data over time

When you want to record data and then track what happens to it, do it in Claris FileMaker. The sentiment that Excel is an imposter data tracking solution without nearly as much capacity or functionality as Claris FileMaker is correct – when the goal is to store data and track its evolution over a period of time. Excel users usually start recording what they want to track in Excel, and then run into trouble when they need to record activity against that data. Imagine you have 5 pairs of pants in the stock room at your store, and you want to record that information. You enter the pant style number, color, size, quantity of 5, and today’s date. Then, a customer comes in and buys a pair. You can update that line in your spreadsheet to reflect a quantity of 4, which is how many pairs you have in stock now. However, it doesn’t tell the story of that sale. When you look back on the spreadsheet later, you might wonder: didn’t I order 5 pairs from the factory? Was one pair sold? Who bought it – and when?

It’s not impossible to do this in a spreadsheet. You can copy the information from the original inventory entry and paste it into a second line with a quantity of -1, then add a sum formula underneath it to show that the total pairs of pants in inventory is now 4.

This method works fine in a spreadsheet – until it doesn’t. When you have 100 other products in various colors and sizes to track, the spreadsheet begins to grow, and it gets easier to make a mistake. With every sale, you have to enter the negative quantities of purchased items with the product information. You have to maintain multiple lists for each kind of product so that you can see the total by each product iteration to determine whether there are any in stock. If you have employees and you ask them to maintain the spreadsheet, they may not know how to use Excel with your level of skill. They might accidentally type over a formula, leave out information about the sale or enter the information incorrectly. They might enter a positive number instead of a negative one when recording a sale, falsely increasing the inventory count for a given style. There are ways to prevent these errors from happening by creating an entry form in Excel or using macros, but these corners of Excel are not visited often and are not very intuitive to implement.

It’s All Relative: When Your Data is Entered Here, It’s Family

In Claris FileMaker, you can create separate tables to store particular kinds of information and then relate that information using unique IDs. Claris FileMaker offers native functionality to generate and set unique serial numbers whenever an entry is created. In the example above, you could create 4 (or more) tables to store the stock and sale information: Inventory, Products, Invoices, and Invoice Items. The Products and Inventory tables can have their own entry layouts for your employees to enter Products once they have been designed, ordered, produced by the manufacturer, and received at your store. When customers buy things, you can enter a new Invoice record for each sale, and then use a dropdown menu to create one Invoice Item and select the item being purchased. This dropdown menu would pull from the information entered into the Products table. Choosing from an existing list of values prevents inadvertent typos and inaccuracies. Alternatively, you can use the unique serial numbers created for each product to generate bar codes and adhere them to the items in your store. Then, you can scan the barcode of the items getting purchased, and Claris FileMaker will read the barcode, translate the unique ID represented by the barcode, and enter it into the Invoice Item. Because the Invoice Item is related to the Product by its unique ID, this ID can pull up all the style, color, size, and pricing information for the item. Moreover, the quantity of the item gets set as positive 1, and it doesn’t have to get re-entered anywhere as -1, like in the example where the sales was tracked in a spreadsheet. Because the Invoice Item is also related to the Inventory table using the unique ID, the Inventory record for this product can house a separate calculation field named Available Quantity, which takes the initial quantity of the product entered into the Inventory table, and subtracts from it the total quantity in all Invoice Item records created during checkout with that product ID.

Essentially, Claris FileMaker allows you to create human-readable, intuitive entry screens where your employees can enter data in a way that makes sense for that particular situation. When you receive 5 pairs of pants into inventory, the entry screen shows Quantity 5. When a customer buys one pair of pants, the entry screen shows Quantity 1. The logic of subtracting Quantity 1 sold from Quantity 5 in inventory can be added as a calculated field in a table, built into a script that runs when completing the sale, or built into a routine of recurring validation or calculation scripts which runs at scheduled intervals. So much of the complexity that would be unavoidably present and confusing when tracked in a spreadsheet can be folded into a background process or field in Claris FileMaker. Ultimately, this allows for cleaner and more intuitive user entry screens, which increases the likelihood that your employees will use the system to record data and maintain data integrity.

Use Excel… to analyze data from a specific point in time

Inevitably, you will come to work one day to find that your data no longer has integrity. Maybe this was your fault because you expected your data to behave honorably in Excel even though Excel is not great at setting boundaries or disciplining your data when it gets out of line. Maybe this wasn’t your fault at all. You raised your data right, sent it to FileMaker boarding school, and it was still corrupted because your computer crashed in the middle of recording a sale, and now your available inventory totals don’t reflect the units sold because that sale was never saved properly or completely before the crash. When your data isn’t telling you the truth, interrogate it in Excel. I am a developer and I still regularly export data to Excel to locate a discrepancy.

Can this analysis also be done in Claris FileMaker? Sure. But when you don’t know what you’re looking for, it can be difficult to pinpoint the portion of data to examine, the view in which to examine it, and the operation you need to do. Even if you know that your Available Quantity is correct for a product but the sale was only partially recorded, it can be tedious to comb through each sale in existing layouts, looking for discrepancies. Additionally, viewing the total quantity sold for each style would require creating and scripting a subsummary report or using subsummary fields and sorting by the product ID. Depending on how many records you have or how the relationship graph has been set up in your solution, this may not be as easy or speedy to summarize as it would be in Excel. Most of all, any kind of summing quantity sold by product would need to be scripted or calculated by adding one or more fields. Adding fields is not always possible in a live solution, because users are performing operations that would not allow interference with the file schema. Scripting a report can take more time than you’d like to spend addressing a discrepancy, especially if you aren’t the developer. 

Just Pivot: The Instant Report

Excel is great at slicing and analyzing data from a single point in time.  Why? Pivot tables! VLOOKUP! Static data! Exporting data to Excel and summarizing that data using a pivot table allows you the flexibility of viewing any column or row as a top-level field by which to filter the other data. It’s an instant report which you can modify in real time in order to sum, count, and compare the other field values through the filters (fields) selected in the rows and columns. It’s infinitely useful in many situations, and I export data to Excel all the time in order to find mistakes, duplicate entries, misspellings, or invalid data. Exporting records to Excel can be slow when exporting unstored calculations or fields from related tables. However, when exporting stored numbers and text from the table of the current layout, it is super speedy, even when exporting tens of thousands of records at a time. If I have to include information from related tables, I will move to a layout with the context of the related table, and export the primary key along with the data I need. Then, I look up the data related to the fields in the original exported spreadsheet using the foreign key.

Just Pivot: List Values to Find Typos

As soon as you have a spreadsheet to use as a data source, you can analyze it with a pivot table in a variety of ways. For example, if I wanted to make sure that a spreadsheet with a list of products to import didn’t have any misspellings, I could summarize the sheet with a pivot table. The first row in each column of source data is automatically used as the field name in the list of pivot table fields. I’d select the Product Name field data to be displayed in a row, which would display a list of unique values on the left side of the report, sorted in alphabetical order. Without choosing any other fields to display, I can look at the sorted list of Product Names and determine whether there are two names in the list with almost identical spellings. If so, one of those product names is misspelled. Because field data displayed in a pivot table row shows only unique values, the appearance of two identical product names in the list means that there is an invisible extra character in one of the product name entries which is causing it to show up as its own distinct name. There might be a space after the name of the product, or two spaces between two words instead of one space. This is a great way to identify inconsistencies in your data, which can lead to inaccurate and misleading reports if it isn’t corrected.

Just Pivot: List Values to Find Duplicates

Another way to double check data integrity with a pivot table is to display the Product Name field as a row and then choose any other populated field to serve as the value to be counted. This will appear to the right of each Product Name in the column titled “Count of” and the name of the field selected. The number of entries which have that Product Name will be displayed in this “Count of” column. If there are any duplicate product names in the source data, the field value count would show a number greater than 1, indicating that there are multiple lines of data containing the same product name. This is helpful when you’re looking at data that is intended to be a list of unique product names. If there are two or more entries with identical product names, then you can double-click on this number, which will show all entries from the source data with that product name. This will allow you to compare the entries and determine whether they are duplicates (and should get deleted) or legitimately unique products which should get updated with unique names.

Exporting data to Excel also offers the benefit of stripping it of any live calculations, formatting, and relationship graph dependency slowness. Hopefully, your Claris FileMaker solution has a relationship graph with table occurrences related in a way that is optimal for your most frequent operations. Even so, that structure may not be the most performant for trying to analyze your existing data in order to identify a data anomaly. When reviewing multiple sales, with many invoice items within in each sale, adding up the total quantity sold by product ID across multiple orders may take a long time to process, especially when comparing it to the available inventory figure for each product. The fields being compared may be unstored calculations with references to other fields or dependencies on other calculations, which get recalculated for every record in the found set. What is usually a quick and painless process when recording one sale at a time may be significantly slower when analyzing multiple transactions simultaneously.

Just Pivot: Compare Totals to Find Discrepancies

Exporting data to Excel strips it of any contextual baggage to process in memory, capturing just the textual or numeric value of your data. This makes filtering and aggregating data in a pivot table faster and more responsive than it might be in Claris FileMaker (without additional development) – even with a 100,000 rows in your spreadsheet! You could export your Inventory records into one spreadsheet, export your Invoice Items sold for the year into another sheet, and put a pivot table on the Invoice Items data to display the sum of items sold for each product. Then, you could use the VLOOKUP function to return the Received Quantity and Available Quantity stored in the Inventory table for each product ID. Pivot tables allow you to add calculated fields directly to the pivot table itself, or you can create a new column in your source data, enter a formula, and perform the calculation as a new column. Where the total of items sold for a product does not equal the expected quantity sold (Received Quantity minus Available Quantity), there is a problem with the data. In our example where the computer crashed in the middle of entering a sale, the products with discrepancies in the total quantity sold will allow you to drill down and identify whether an order needs to get reentered completely, or if only some of the products in the order need to get added so that the sales history matches the stock level.

Using a pivot table to filter and slice your data in different ways is one of the fastest and easiest ways to find discrepancies, missing values, and inconsistencies without having to spend time or effort on additional development.

TL;DR

So, Claris FileMaker is best at managing data in the long term, and Excel shines brightest when analyzing snapshots of data for summary and review. Are there any other factors to consider in this Great Debate of when to use Claris FileMaker and when to use Excel? The key lies in understanding the practical applications of each tool. In the next blog post, we will dive into real-world scenarios and use cases, which demonstrate the strengths of both Claris FileMaker and Excel. Stay tuned to learn how to leverage these powerful tools to their fullest potential!