Recently, I have been working with two clients that were planning to import data into their Microsoft Dynamics Customer Relationship Management systems.
During the design stage, it became apparent that the data source and how the clients planned on using the data would play a defining factor in how we created the CRM Entities and the data fields. The sources of the data being imported were different between the two clients, one was being exported from another application while the other involved several manually updated spreadsheets. Another difference that needed to be considered involved how the different clients planned on using the data after it was imported into CRM.
Before creating the entities and defining the fields which will be used to store the imported data, there are a couple of factors which need to be considered:
1) How will the data be used once it is in the CRM system?
- Will the users be updating the data once it is imported into CRM?
- Will the users be creating advanced finds using data filters?
2) What types of data are expected in the import file?
- Will the data have strict data validations?
- Will the data be variable and constantly changing?
Once these factors have been considered, there are 2 different approaches to importing data into CRM.
The Simple Approach
The easiest way to import data is to define all the CRM Entity fields as Text fields. This approach allows any data, regardless of format or content, to be easily imported without any problems; text, dates and numbers are all treated exactly the same. Even if the data changed format after several imports had already occurred, there would not be any import issues.
- Data validation is not required
- The import process does not fail due to data incompatibilities
- No need to analyse data elements to determine their data types and create matching CRM fields (all data are treated as text)
- Date formatting is not an issue
- Future changes in the source data format would not cause import problems
- If future editing in CRM is required, there are no validation controls
- There is no consistency in how the data is formatted
- No ability to perform math functions on numbers
- Advance Finds with data filtering is extremely limited
The Advanced Approach
The advanced method requires an analysis of the data to be imported and the creation of matching data field types in the CRM Entity. The imported data must match the CRM field types, or the import process fails. Imported date fields are stored in CRM date fields, fields with a defined number of options are stored in option sets, and imported numbers are stored in the appropriate numeric fields.
This approach requires more effort upfront but the benefits after the data has been imported are greatly extended. If users are allowed to edit these data fields in CRM, there is already some data validation functionality built-in. Additionally; Advanced Finds and data filtering options are enhanced.
- If future editing in CRM is required, the data fields are ready to guide the user’s inputs (e.g. date fields)
- There is consistency in how the data is formatted and displayed within CRM
- Numeric data fields can be used in math functions or calculations
- Advance Finds with data filtering is possible
- Strict data validation on the source file is required
- The import process fails when data incompatibilities are encountered
- Prior to creating the CRM entities and fields, there needs to be an analysis of the source data elements in order to predetermine their data types
- A standard date format must be adopted (i.e. yyyy.mm.dd)
- Future changes in the source data format will cause import problems
Tracking a Yes/No data field
- Importing a Yes/No value as a Text field.
- There is no validation required, the field can accept any value provided.
- If CRM users are allowed to update this field, there are no validation rules to assist in the data entry.
- If the format of the data is changed in the future, no changes to the import would be required.
- The use of any data filtering in an Advanced Find dialog would be difficult. The user would need to very carefully specify all the valid data possibilities. e.g. To filter records which indicated a “yes” value, numerous optional formatting would need to be considered, such as “YES”, “Yes”, “yes” and any other data options which may have been imported.
- Importing Yes/No values as an Option set containing the valid options.
- Additional upfront data analysis of the data to be imported is required to define the valid data options.
- Importing data values which are not in the Option Set will cause the import to fail.
- Further editing of this field’s value in CRM is controlled by the defined values in the Option Set.
- The use of Advanced Finds and data filtering are easier to create and offer better control over the data selection process.
Tracking a Date field
- Importing date values in to a Text field.
- There is no validation required; the date can use any format and it can even change format between records.
- There would be no validation or data formatting if CRM users were allowed to edit these data fields.
- The use of any data filtering in an Advanced Find dialog is virtually impossible. Without a controlled and constant date format, any filtering on this date field is extremely limited.
- Importing date values in to a Date field.
- A standard date format needs to be selected, and all imported dates need to adhere to this format.
- Further editing of these date fields would be fully validated and the user would have access to the pop-up calendar for easier date selection.
- Advanced Finds and data filtering is easier to create and offers superior control over the date selection process.
While neither of these two approaches is right or wrong, each method has its own advantages and disadvantages. The decision to select one method over the other will be driven by the data being imported and how it will be used once it has been imported into CRM.
Most CRM solutions involving data imports will involve a combination of both approaches. Key data fields would use the advanced approach by utilizing specific data type fields, while other non-critical data would be stored in generic Text fields.