Maximizing Data Potential with FileMaker and Excel

Are you tired of spending hours wrangling Excel spreadsheets and wrestling with FileMaker databases to meet your business needs?

Regardless of industry your business likely has had the need to get data out of your existing FileMaker solution.  The reasons can be as simple as

• you want to share information with someone that does not have access to your FileMaker solution
• you need to supply data for someone to further analyze and manipulate
• you must meet strict standards for an external service or client to process

Efficient data export functionality is important for companies, especially when data needs to be shared with external stakeholders or outputted with specific requirements.

Let’s take a closer look at the need for custom reports for a particular business integration. 

Bridging the Gap with Mailchimp Integration

When pulling data from a FileMaker CRM application to import into a marketing tool like Mailchimp, you typically need to create an Excel report that contains relevant contact information and segmentation data. Here’s a basic outline of the key fields you might include in such a report:

Contact Information:

• First Name
• Last Name
• Email Address
• Company (if applicable)
• Job Title (if applicable)
• Phone Number

Segmentation Data:

• Tags or Labels: These could represent different customer segments or categories within your CRM system. For example, you might have tags for leads, customers, VIP customers, etc.
• Custom Fields: Any additional fields in your CRM that provide segmentation data, such as industry, location, purchase history, preferences, etc.

In addition to the data that the external service requires, we must also ensure that the data is formatted correctly for import into Mailchimp. For example, make sure email addresses are in a separate column and that each row represents a single contact.

Let’s take a closer look at a specific industry and the need for custom reports.

Streamlining Manufacturing Processes

Manufacturers often require custom reports to track various aspects of their operations, manage resources efficiently, analyze production performance, and make informed decisions. Here are some types of custom reports commonly needed in manufacturing:

• Production Reports: These reports provide insights into production activities, including output levels, machine utilization, downtime analysis, production line efficiency, and overall equipment effectiveness (OEE).
• Inventory Reports: Inventory reports help manufacturers monitor stock levels, track inventory movements, analyze inventory turnover rates, and identify potential stockouts or excess inventory situations.
• Quality Control Reports: Quality control reports detail information about product defects, rejections, scrap rates, and compliance with quality standards. These reports enable manufacturers to identify trends, implement corrective actions, and improve product quality.
• Work Order Reports: Work order reports offer visibility into work order status, progress, completion times, resource allocation, and associated costs. They help in tracking work order performance and optimizing production scheduling.
• Supply Chain Reports: Supply chain reports provide insights into supplier performance, lead times, delivery performance, inventory levels across the supply chain, and overall supply chain efficiency.
• Cost Analysis Reports: Cost analysis reports help manufacturers analyze production costs, including material costs, labor costs, overhead costs, and total manufacturing costs per unit. These reports support cost reduction initiatives and profitability analysis.
• Equipment Maintenance Reports: These reports track equipment maintenance schedules, service history, downtime due to maintenance, and predictive maintenance indicators. They help in optimizing equipment uptime and extending asset lifespan.

When we utilize FileMaker and Excel, we can efficiently export the required data in generating these reports and facilitating informed decision-making.

Let’s look at an example of one of these custom reports commonly needed in manufacturing.

Custom Inventory Report :

Scenario 1.

Holding Inc., a distribution center, manages inventory for several different companies in its warehouses.  A request has come in from one of their customers to produce an inventory report of just its’s “Blue Label” product line. 

Business Requirements:

• The report must look professional, including properly formatted numbers, including commas and no decimal places.
• Each stock keeping unit (SKU) will need to be summarized by geographical warehouse location and include a grand total.
• The grand total should included the location name and be formatted so that it can be easily identified on the report. 
• Each SKU should also include a percentage calculation of the total inventory by location.  Format the percentage to appear as 0.00%

Note* The Inventory Report examples below have been limited to show only SKU’s beginning with A to illustrate formatting.

Video: FileMaker Pro to Excel using native option Export Records

The illustration below is the output using FileMaker’s Export Records. This approach requires the user to modify the Excel output to meet the client’s request.  Here is a list of steps that need to be taken each time this request is made by the client.

1. Column headers must be manually updated from FileMaker field names to be user friendly and match client’s request.
2. The Units on Hand column needs to be properly formatted with commas and no decimals. 
3. A total row needs to be inserted for every change in site and a calculated field will need to sum all of the units on hand for that location.
4. The total row will need a label and identifiable formatting must also be applied.
5. A new column for Percent must be added manually and a formula must added for each SKU to determine its percentage of inventory by location. 
6. The percent data requires that formatting must also be applied showing two decimal places with % suffix.

FileMaker to Excel – Export Records

Now let’s take a look at how we can take on this same task using FileMaker Pro with the use of some advanced features.

Video: FileMaker Pro to Excel using advanced features.

This illustration below shows one of the output examples using advanced features in FileMaker Pro to achieve the client’s results.  Notice that total rows have been inserted at each change in location and formulas exist in the respective cells.  A percent column also exists and the report is properly formatted to meet the client’s request.  Best part (besides looking good) is that there is no need for manual user intervention to achieve this result! 

FileMaker to Excel – Advanced Option 1

The illustration below shows another output example using advanced features in FileMaker Pro.  In this scenario, the client asked for the same report but this time it should only include Green Label products.  The formatting was also adjusted to reflect the client’s wishes.  Again, there is no need for manual user intervention to achieve this result and the report was easily customized using a few lines of code in FileMaker’s Script Workspace. 

The business saves money (labor), allows team members to do other tasks, eliminates potential mistakes (human error), and delivers what the customer wants quickly (think scheduled automation). WIN!

FileMaker to Excel – Advanced Option 2

Now that you know what’s possible, let’s look at some additional real-world scenarios where companies would need to export their data from FileMaker to Excel.

Business Scenarios:

Scenario 2:

Rocky’s Electronics, a fulfillment center, processes orders and collects payments from its customers. Each night, the Accounts Receivable team is responsible for generating a report of all ACH payments that the bank will process. The team is required to export the data to a CSV file that then can be uploaded to the bank. 

Business Requirements:

• The external bank requires that the export must be in a specific format with designated columns for the upload to be processed.
• Accounting Manager would like to eliminate the manual steps the Account Receivable team must take each business day to process the file for the bank. Some of the manual steps include modifying column names, changing certain cells from text to number format and renaming each workbook and worksheet tab to identify with a predefined reference id.

Scenario 3:

Bennie’s Bistro, a local coffee shop looking to expand to two more locations, needs to analyze its operations to determine the feasibility of its plans for growth.                                                                    

Business Requirements:

• The report should include a separate worksheet (tab) for every month over the past year. Every month’s tab should be identical in format and include its week’s sales, cost, and labor hours.
• Bennie liked this report so much that he has also asked for it by week. He would like a tab for each week of the current year, requesting that the report is sent to him every Monday morning, with the prior week added to the beginning of the workbook, keeping all prior months for reference.          

Scenario 4:

Silo Construction, a building contractor, needs to share information with its project managers.  Some project managers would like to be able to run scenarios with the exported data.

Business Requirements:

• Every project should have its own export and be emailed to the correct project manager upon request.
• The project managers must be able modify the data with their projected numbers, so the totals cannot be hardcoded. The cells for ‘totals’ in the worksheet must utilize formulas.

No matter the business scenario, creating custom exports to meet your needs is possible to accomplish with a partnership between FileMaker and Excel.

Leverage Export Capabilities

The benefits of FileMaker to Excel export features range from improved data analysis and communication to streamlined manufacturing processes and effective marketing campaigns.

If you are not already taking advantage of this relationship, I encourage you to invest in solutions that enable seamless data export capabilities to meet diverse needs and enhance operational efficiency. Explore and leverage advanced features and integrations offered by FileMaker and other tools to optimize your data management and communication workflows.