Your data is valuable and protecting it is vital in terms of legal obligations, your business reputation, competitive advantage, and more. The importance of test data in FileMaker is a discussion worth having when starting a new project or updating your solution.

One easily overlooked area of data protection is the security of your data in the development and test versions of your solution, whether a FileMaker solution or a web application. While your live production data may be carefully secured, the copy of the data made for testing, especially if residing on an outside server, may not be safe.

Depersonalizing the data in dev/test environments is one way to improve data security by removing personally identifiable data or key business data from the data set when it is hosted outside your live environment.

A Clarification

This article explores approaching your data safely and securely, but keep in mind that depersonalization is only one aspect of a robust data protection plan. Generally, this topic is geared toward the data you use in your testing environment, not the data in your live environment.

The Importance of Test Data in FileMaker

You might ask, why not simply delete all the ‘real’ data when moving it to the dev/test environment? Often, this solution is perfectly acceptable, but might lead to a testing situation that is inadequately representative of the real world. You might find it difficult to closely reproduce potential problems when testing, leading to inaccurate and inadequate results.

• Using test data helps ensure data integrity
• Using test data lends to cost-effective development

Ensuring Data Integrity

Test data is simulated to mimic real business operations data without risking actual sensitive information, thereby letting you perform various operations and changes in a controlled environment. When rolling out a new feature in your FileMaker application, test data allows you to experiment without fear of corrupting actual production data.

Cost-Effective Development

Incorporating test data in your development process can lead to more cost-effective application development. Identifying bugs and issues in the development stage can significantly reduce the costs associated with post-deployment fixes, which are often more complex and expensive to resolve once the application is in active use.

We have legal requirements with some clients, the HIPAA and FERPA regulations that some companies and educational institutions have to comply with, and the risks of having real data hacked or gotten out to the public are just too great. So masking or depersonalizing the data becomes a pretty important precaution.

John Newhoff, FileMaker DevCast Episode 6

Masking Identities: Incorporating Data Depersonalization in FileMaker Pro

In the sixth episode of the FileMaker DevCast, our team discusses the steps to anonymize sensitive data, ensuring robust privacy protection without losing the ability to analyze and utilize information effectively.

Top Reasons to Use Test Data in FileMaker

• Risk reduction
• Performance optimization
• Enhanced user training
• Regulatory compliance and data protection

Risk Reduction

Test data reduces the risk associated with software updates and new feature deployment. By thoroughly testing changes before they go live, you can ensure that your business operations remain stable and reliable, which is essential for maintaining trust with your clients and partners.

Performance Optimization

Test data also allows you to assess the performance of FileMaker solutions under various stress conditions and data volumes. This is crucial for anticipating potential system failures or bottlenecks and addressing them proactively.

Enhanced User Training

Learning the ropes of complex FileMaker solutions can be daunting for new employees.. Test data provides a safe learning environment, free from the pressures and risks of accidentally compromising real data. This method not only facilitates the training process, it also boosts the confidence of your team members as they learn to navigate and manipulate the database.

Regulatory Compliance and Data Protection

In today’s digital age, regulatory compliance and data protection are more critical than ever. Using test data ensures that your business is in compliance with standards like GDPR or HIPAA without exposing sensitive information during testing.

Creating Effective Test Data for FileMaker

Identifying the Data Needs

The first step is understanding what type of test data is most relevant to your business applications in FileMaker, and determining the scope and scale of data needed to test the system’s capabilities and the scenarios it will encounter.

Tools and Techniques for Generating Test Data

The next step is using the right tool. Several tools and techniques are available for generating relevant test data for FileMaker. From manual data entry to sophisticated software that automates the data creation process, choosing the right tool often depends on the complexity of your database and the specific testing requirements.

Historically, we have used a file of ‘fake’ name and address data to import over real data, but this only allows the depersonalization of one type of data. So we’ve experimented with a JavaScript library, Faker.js, that allows you to create fake customized data and data type requirements. With the advent of AI and large language models, however,  this process has become a lot easier!

Use ChatGPT to Generate Test Data

To generate a set of test data for a FileMaker database using ChatGPT, it is necessary to create a prompt that specifies the type of data, the format, and any particular constraints or characteristics relevant to your CRM.

Here’s a structured way to create a detailed and effective prompt:

• Data Fields Required
  • Specify the fields pertinent to your CRM, such as customer names, contact information, transaction history, interaction dates, notes, and status updates.
• Data Format
  • Define the format in which you need the data.
  • Such as CSV, JSON, SQL insert statements.
• Volume of Data
  • Decide how many records you need.
  • Specify the number of entries, such as 100 or 1,000.
• Data Characteristics
  • Realism: The data should mimic real-world entries while maintaining privacy and compliance with data protection laws. Use fictional but plausible data.
  • Variety: Ensure the data covers various cases, including edge cases, to thoroughly test the database’s handling capabilities.

Suggestion for structuring  the prompt:

“Generate a set of test data for a CRM system. Include the following fields for each record: first name, last name, email address, phone number, last contact date, customer status (new, active, inactive), and latest purchase details. Format the data as a CSV with 200 entries. Ensure the data reflects a variety of customer types, including high-value customers and those who haven’t made a purchase recently. All data should be fictional and suitable for a realistic testing environment.”

Such a prompt guides ChatGPT to produce data tailored to typical CRM requirements, effectively testing the functionality and robustness of your CRM software.

Upon entering that prompt into ChatGPT, you will likely receive a result similiar to this one.

Simply click “Copy Code” and copy the CSV file to your clipboard. Open Excel, create a new blank document, and paste. Then use that Excel file to import it into FileMaker.

Use Our Free Tool – FMDeepFake

Underscoring the rapid speed of technological advances, our developer, Jacob, created an awesome tool a few months ago to help easily randomize data – FMDeepFake. It uses the faker.js JavaScript library integrated into a FileMaker database to generate the test data.

Then at the time of release, ChatGPT came on the scene and quickly outpaced the app. However, it was still a great challenge to work through the building logic, and for anyone who prefers a less AI-integrated method, FMDeepFake may be ideal. Please enjoy the download. We welcome your feedback on its implementation or improvement.  

If your table has a considerable amount of fields you can match them all and then match the record count in your production data. If you want to test the capability of 100,000 records for the schema you just built, this tool meets that demand perfectly. You’re generating that exact request without having to go any other place. You want something that’s catered to the fields you’ve already built in your system.

Jacob Carr, FileMaker DevCast Episode 6

Other Sources for Pre-Defined Lists

• Random User Generator: “A free, open-source API for generating random user data. Like Lorem Ipsum, but for people.”
• Mockaroo: Allows you to generate up to 1,000 rows of realistic test data in CSV, JSON, SQL, and Excel formats. It’s also available as a docker image that you can deploy in your own private cloud.
• Faker Libraries: Libraries like Faker for Python can generate fake data in various formats.

By using these predefined lists and integrating them into your depersonalization process, you can effectively anonymize your FileMaker data set while maintaining consistency and data integrity. The importance of test data in FileMaker is not something to take lightly.

What Might the Fake Data Look Like?

• Consider using names and datasets from public domain lists like the U.S. Census Bureau or other demographic sources.
  • John Doe, Jane Smith, Alex Johnson

• Ensure email addresses follow the standard email format to maintain data integrity.
  • example@example.com, user@domain.com, test@domain.com

• Phone numbers should follow standard formats for your country but with non-identifiable numbers.
  • 123-456-7890, 555-555-5555, 000-000-0000
  • Consider using prefixes that indicate dummy data, such as (555) for U.S. numbers

• Address components should maintain consistency with correct formatting.
  • 123 Main Street, Anytown, USA, 456 Elm Street, Somecity, USA

• Use placeholder dates or randomize within a reasonable range. 
  • 01/01/2000, 12/31/1999, etc.

• Use non-identifiable but properly formatted Social Security numbers.
  • 123-45-6789, 000-00-0000

• Use non-real but valid credit card formats (using the Luhn algorithm). There are also tools to generate valid dummy credit card numbers.
  • 4111 1111 1111 1111, 5500 0000 0000 0004, etc.

Best Practices in Test Data Management

It is crucial to keep your test data updated and aligned with evolving business processes. It can become a real issue if your test environment doesn’t reasonably reflect your production environment. Regular reviews and updates of your approach will ensure that your environment accurately reflects real operational data, providing ongoing usefulness in testing and training scenarios.

The Strategic Importance of Test Data in FileMaker

The strategic importance of test data in FileMaker is more than a technical necessity; it is a business imperative that underpins operational integrity, enhances staff training, and ensures performance efficiency. Neglecting this crucial aspect can lead to significant financial and operational risks.

At Portage Bay, we specialize in optimizing FileMaker applications for small to medium-sized businesses. Our expertise in creating and managing effective test data environments can safeguard your business against unnecessary risks while enhancing your system’s capabilities. Please contact us today to learn how we can tailor FileMaker solutions to meet your unique business needs and ensure your continued success.

FAQ

About the Author

Kate Waldhauser joined Portage Bay Solutions in 2006.  As lead Claris FileMaker developer, she plays a key role in our custom development projects for a wide variety of clients. Kate’s communication skills, friendliness, and technical expertise make for an unusual combination: a high-caliber developer who can communicate effectively with our clients.