Data Validation
If you’ve been tasked with integrating data, then you know there are a wide variety of challenges that you must overcome. Every time I’m asked to programmatically import large data sets from a flat file like a CSV or Excel sheet, I think about how Forest Gump would react. If he was a developer, I know he would say “Flat files are like a box of chocolates … You never know what you’re going to get.” There’s a lot of truth to that statement. When data is stored in a flat file, there are no rules or constraints on what is delivered.
If you are faced with this situation, it’s a best practice to fully validate these files before you import a single row of data. A validation process should confirm that the structure is acceptable to import, and alert the client if it is not. This process should provide a log of issues for the client to review, so they can fix then resubmit their data.
How do I validate a flat file with Microsoft Dynamics 365? It’s easy to do if you can programmatically communicate with the Dynamics 365 metadata service. The metadata provides you detailed information about the structure of your Dynamics system, including entities, attributes, relationships, and option sets.
Here’s my top three items to validate, along with the related classes available from the metadata service. You can view full code samples on Microsoft’s site here: https://msdn.microsoft.com/en-us/library/gg509035.aspx#BKMK_RetrieveAttribute
Verify entities and fields exist
Each file should be named after a specific entity, and each column should represent an attribute. You can validate the entities using RetrieveAttributeRequest.EntityLogicalName and the fields using AttributeMetadata.SchemaName.
Verify required fields contain data
Every required field in the file should contain data. These are most likely your unique identifiers, and cannot be blank. You can find the required fields using AttributeMetadata.RequiredLevel.
Verify the data matches the field constraints
Every field should contain the correct type of data that matches the Dynamics 365 schema. For example, if the field is a decimal, you should verify the data is a number. You can find the type by using AttributeMetadata.AttributeType. Data should also respect the minimum and maximum length and values by using AttributeMetadata.MaxLength, AttributeMetadata.MaxValue and AttributeMetadata.MinValue.
Beringer is a gold certified Microsoft partner, and has deep experience migrating and integrating data. Can we help integrate your data with Dynamics 365? Please reach out to us today.
Beringer Technology Group, a leading Microsoft Gold Certified Partner specializing in Microsoft Dynamics 365 and CRM for Distribution. We also provide expert Managed IT Services, Backup and Disaster Recovery, Cloud Based Computing and Unified Communication Systems.
