Testing Applications that Import Excel Files into Microsoft Dynamics CRM

Miguel Nepomuceno, 10 October 2016

The ability to import data from spreadsheets is a powerful and useful functionality in Microsoft Dynamics CRM. It allows business users to collect massive amounts of data all into Microsoft Dynamics CRM such that users are able to keep track of customer information all into one place. This is because in the CRM game, information is power and data provides powerful business intelligence.

A common functionality regarding Microsoft Dynamics 365 is importing CSV files into the client. The Microsoft stack offers Microsoft Excel, a popular spreadsheet application tool. It allows users to store data into spreadsheets, and provides various useful tools such as applying formulas, creating visual graphs and statistical analysis. Many users and businesses are familiar with Microsoft Excel and have Microsoft Excel as their ‘go-to’ spreadsheet application tool. Users having the familiarity of using Excel for data entry and data analysis while also using Excel in their everyday business. This means the ability to import Excel csv files into Microsoft Dynamics CRM is fundamental.

Third applications are used by businesses to gather information from its customers. Not all businesses will use the ‘Import Data’ tool that is provided out-of-the-box by Microsoft Dynamics 365.  Third party applications can provide a more seamless and integrated approach to gather data into Excel files and importing these into Dynamics CRM. In the context of this blog we store information into Excel files, such that third party application can import Excel files into Dynamics CRM. Seamless integration of data importing from these third party apps into Dynamics CRM is key. As testers, we must ensure that importing Excel files into Microsoft Dynamics CRM is well tested and functional.

The most important thing to check and test with importing Excel files is data mapping and integration. Because what is the point of using our data if it does not provide useful and contextual information to its user? To ensure data accuracy, quality and integrity, we must ensure that the import functionality is reliable and tested for data mapping into CRM.

When your data mappings get messy during imports – not exactly useful is it?

This means primarily checking if the correct entity records are created, and checking if the data is correctly mapped to the fields. The Advanced Find functionality in Dynamics CRM is useful to test for data mappings. Instead of individually navigating through each entity record and checking each record fields (which is painstakingly inefficient), we can use a custom view in Advanced Find to view the data in the columns. We can create a custom view, alter the column order identical to the column headers in our Excel files, and compare whether the correct data under a column gets mapped to the appropriate fields. This way we can view a large amount of records to eyeball and validate, thus easily compare selective records.

Using Advanced Find to easily eyeball and validate data mappings for CRM records with the imported Excel spreadsheet. We can easily compare by altering the columns in our view to mimic the columns in the imported Excel spreadsheet.

Consider the following real-world scenario to test: given a console application, it prompts the user to import data file pairs into Microsoft Dynamics CRM.

From the above example, we can draw up a few test cases that can hopefully give you some ways in how to test the import functionality of Excel files into Microsoft Dynamics CRM.

Again, as testers, we assume that the system user can make mistakes, especially when it comes to dealing with large amounts of data entry. Users can be overwhelmed by the massive stream of information and the mental load regarding this means that mistakes can happen. Consider the following to test for the Excel file to be exported:

Consider User Mistakes in Excel Data Entry:

-    blank data file – will it prompt the user if there are contents in the Excel file to import?
-    empty columns in the Excel spreadsheet
-    empty rows in the Excel spreadsheet – does an empty row still create a record when imported in CRM?

Make sure that an empty line in your Excel spreadsheet does not create a default record in CRM (unless it is intended functionality). Records have been blurred out for demonstration purposes.

Validation of File Format when Reading the File to Import:

-    validating the correct file names that identify a file to be imported
-    correct file format to import (csv file type)
-    missing or incorrect file names to identify a file to import
-    duplicate detection of files to be imported
-    consider access verification of the folder location of file to import

Field Mappings:

-    missing column header on a column with data
-    duplicate column headers in an Excel spreadsheet
-    Are the appropriate CRM entities created correctly in CRM?
-    Are the CRM fields mapped correctly in CRM with the associated column header in the Excel file?

Relationships:

-    Are Excel data linked with other data from another Excel file/ sheet?
-    Does an Excel file to be imported require another linked Excel file/ spreadsheet before executing the import?

Another important feature to test is whether we want to bring in user interaction and friendliness into testing. As testers we want to simulate what the user is experiencing when using the system. As such, it is important to assume users do not have familiarity with using the system application and to always give user feedback regarding required actions, import process actions, and processing status updates. We must consider the following test cases for the Excel file to be exported:

Required Actions from the System User:

-    Does it require the user to manually choose the files to be imported?
-    Does it require any validation from the user to execute the import process? E.g. “Do you want to import the following import files: Y/N?”
-    Will it validate if the required actions from the user is correct? E.g. “Do you want to import the following import files: Y/N?”, expecting a ‘Y’ or ‘N’ input but entering an invalid text or a number

User Friendliness:

-    Does the user get updated by the system on what is going on and what the system status is?  e.g. connected to CRM, import data located at …., currently processing the following csv file, import process completed
-    Is the language simple enough for the user to understand and interpret? Will there be any jargon that can be confusing to the user?
-    Are the system statuses given by the system informative and useful to the user?

A good system to test must also be able to handle what happens when things don’t go as expected. The ability to detect an error and execute error handlers is key in testing for a robust system. We also want to think about how an error is not only informative to the system user, but to the developers as well. This is to ensure that:

Error rollback/ error handling during import, information to the user and to the developers/ support team

-    Will the user be notified of any errors and what the reason is during the import process?
-    Test how an error during the import process is handled by the system and rollback processes. E.g. The import process already creates the records in CRM, then midway through the import process error has occurred. A rollback method could be to stop failed import process and delete the created records.
-    A log file generated can be useful for testing purposes to detect any implementation errors, error handling such that we can find more bugs, record all errors and so that developers can further improve the system

We take into account other business requirements, specifications or functionality that is specific to the system and the project we are testing. We also want to consider a stress test for dealing with imports of large data files. Here we can possibly analyse and test functionality, limitations and the speed of the importing process and if the system can handle or break. Through stress testing we can identify system stability, reliability and detect other modes of failure dealing with larger data files

Above are some of the ways I approached my testing regarding an import functionality into Dynamics 365. Testing a functionality that deals with streams of data and information needs to be considered with certain scenarios involving the system user and system integration. A well tested and well thought out test execution process is essential to creating an accurate and robust system to import data into Dynamics CRM. While every test execution is different for every project and has many different specifications, requirements and functionalities, hopefully you can draw a few ideas into how to build your test cases based on the experience of my own testing. Let’s get testing!