A question that’s popped up a few times on the community forums is around a solution that would allow PowerApps Portals users to upload an Excel or CSV file and have records created in the CDS/Dynamics database. Others have suggested some answers, so I’d thought I’d summarize, plus offer another possible solution.

The Ask

Here are a couple of threads with people asking how to allow users to upload a CSV or Excel file to a portal that will cause records to be created in the database:

Import data in CRM from the Custom Portal
dynamics 365 portal data import

While the Entity List does have the out-of-the-box ability to download records, nothing exists to allow users to import records via a Portal.

Some Solutions

Arpit Shrivastava (http://arpitmscrmhunt.blogspot.com/) proposed a solution that involved these high level steps:

  1. Create an Import Data entity, ensuring that you’ve enabled Notes on it.
  2. Create an Entity Form for the Import Data entity, and give the user permissions to create a record of this type using Entity Permissions.
  3. Leverage the Attach File functionality in Entity Form to allow the user to upload a document to CDS/Dynamics when the record is created.
  4. Use a plugin/custom workflow activity that runs on the creation of the note that parses the file and performs the necessary operations to import the data.

The original poster on that thread then offered a twist on that approach – instead of using a plugin or workflow, they planned on using KingswaySoft/SSIS to perform the data import.

Another Possible Solution

When I saw the most recent question about this, another possible solution came to mind: what about using SharePoint integration, as well as Microsoft Flow? This way we could avoid having to write any custom plugins or workflow activities to parse a CSV. Since I’m always looking for an opportunity to learn more about Microsoft Flow, I figured I’d see if I could make it work.

Here was my approach:

  1. Enable Document Management (SharePoint integration) in your Dynamics instance and in the PowerApps Portals Admin Center.
  2. Create an Import Data entity, ensuring that you’ve enabled it for Document Management.
  3. Create an Entity Form that allows the user to create an Import Data record, and another Entity Form that allows the user to edit the record. The Edit Entity Form should have the Document Locations subgrid on it, which will allow the user to upload documents to SharePoint.
  4. Create a Microsoft Flow that:
    1. Is triggered when a file is created in the SharePoint Library associated with the Import Data record (using the SharePoint When a file is created (properties only) trigger).
    2. Gets the content of the file using the SharePoint Get file content action – use the Identifier field from the previous step as the File Identifier parameter..
    3. Uses the Plumsail Documents Parse CSV action to parse the file.
    4. Uses Apply to each on the output of CSV parsing, and uses the Create a new record action from the Common Data Service Connector to create the record in the database.

And voilà, when a user uploads a document via the Portal to SharePoint, Microsoft Flow automatically processes it and creates the records.

One drawback of this approach is that In order to use the SharePoint integration, you need to create the record first – the SharePoint integration is not available when creating a record, only when editing. There are of course a number of different ways to architect it to remove the requirement that the user creates a record first (for example, you could automatically create the record for the user), but to keep things simple, I haven’t done that here.

Plus, there are obviously lots of way to break this in its current form (error checking is required) – but I think it’s an approach that is viable in some circumstances.

The post PowerApps Portals: Data Upload Using SharePoint and Flow appeared first on Engineered Code.