It may be 2023 but you still can’t beat exporting things into an excel spreadsheet sometimes. A frequent ask I get is for the ability to export marketing form submissions in Dynamics 365 Marketing. Sounds simple but if you have tried it, you will know its not as easy as it seems, even with excessive spreadsheet massaging post export - it’s not too good!

This blog will show you how we can use a custom page, custom button and some power automate magic to be able to export marketing form submissions into a lovely excel spreadsheet. It will also lay some nice ground to be able to export event registrations (including custom field responses) in my next post. I am basing this on outbound marketing forms with plans to re-engineer for real-time goodness in the future!

NOTE: Dynamics 365 Marketing is changing, outbound marketing will still be available but this is a clear message from Microsoft that real-time marketing is the future. Existing customers of outbound marketing do not need to panic, it’s not going away any time soon. But it’s time to start planning the move to real-time functionality and ensure you understand the license cost implications of moving to Dynamics 365 Customer Insights – Journeys.

Convert form submissions into JSON

To be able to create an excel spreadsheet of form submissions, we need to convert each form submission into JSON. Everything you need to do this can be found here (everything before ‘Map the form & field submissions to your custom table’), here (choices/option sets) and here (lookups).

The end result is to transform the form submission into JSON which is stored in a new hidden field on the marketing form submission (Summary JSON). Doing this when the form submission is created (aka when the form is submitted), it ensures all the point in time information is captured and its quicker when it comes to exporting many responses at once.

PS. I will get around to doing a detailed blog post on this and to support event registrations eventually

Create a Custom Page

We will use a custom page as the modal pop up, which will also call the flow that kicks off the flow which exports the form submissions. This custom page will be loaded from the Marketing Page form, and usd to gather/export all the submissions for that particular form. Create the custom page, then add a flow from within the custom page, skip the template and select ‘+ Create from blank’.

Create a form export flow

Before you go any further, delete the godforsaken PowerApps trigger, it’s evil and breaks all the damn time.

Replace it with the ‘Flow Button for mobile’ trigger, I like this guy and it is way more powerful than a mobile flow button. Give your flow a lovely name too while you are here please.

Optional: Now is a good time to save an exit out of the squashed screen flow editor, reopen the flow from your solution in the full screen edit mode instead,.

Define the inputs for the excel table creation

Using the outputs captured in the ‘HeaderString’ we can define the inputs for ‘Columns names’ and ‘Table range’ in the excel table creation step. For the column names we are just removing the trailing comma. For the table range we are turning the number of columns into its alphabetical counterpart i.e. A=1, B=2 etc.

Respond with a file Link

At this point, the file exists and is being populated row by row. Ideally you would respond to the user with the file link only once it is complete but this may take some time if you have a lot of form submissions to export, and when you call a flow from the custom page, the default timeout is two minutes. So if you don’t respond within the two minutes the response cannot get back to your user on the custom page. Instead we will create a read only share link to pass back to the user, so they can access the file while its still being updated.

Happy path first. Assuming it all went well we can respond to the waiting custom page with the File Share Link. The flow run URL can be blank if everything went well. The result will be ‘Success’.

Populate form submission into the Excel table

Now we can append each of the JSON form summaries into the excel table we created earlier.

AppendJSONResponses - Apply to Each
Select an output from previous steps: @{outputs('GetMktFormSubmissions')?['body/value']}

AddTableRowPerFormSubmission - Excel Add a row into a table
Location: your SharePoint location
Document Library: your SharePoint Document Library
File: @outputs('CreateFileXLSX')?['body/Path']
Table: @outputs('CreateTable')?['body/name']
Row: @{json(items('AppendJSONResponses')?[your_summaryjson'])}

Run only when something goes wrong

Now we can configure the failed response to only run if anything prior to it goes wrong by modifying the ‘Configure Run After’ settings as seen in the screenshot. It should only run if the previous step fails, is skipped or times out.

Test your flow

Now is a good time to test out your flow before you get carried away with the fun custom page & button bits. Open the flow and provide test inputs to run it from the Flow details screen. You need to make sure the form submission JSON data has already been populated for this bit to work. You can run this flow and check the excel file comes out as expected using this testing method.

Launch the Custom Page as a Modal

The custom page is currently blank, but lets get the custom page loading in context of the Marketing Form record. This has been so wonderfully done already by Diana Birkelbach, skip on down to ‘Calling the Custom Page Dialog from the Ribbon (Commanding)’ in this blog. Youy want to create the button to show on the Main Form of the Marketing Form table

Button Visibility

Add the below condition to the Visible formula which will ensure the button only shows when the form is enabled to store form submissions, has at least one form submission captured and is of type ‘Landing Page’.

And(CountRows(Filter('Marketing form submissions','Source marketing form'.'Marketing form'=Self.Selected.Item.'Marketing form'))>0,Self.Selected.Item.Default,Self.Selected.Item.'Form type'=0)

Design the Custom Page

Finally we need to design the custom page to call the flow that generates the spreadsheet and to return the file URL (or the error message.).

OnStart

The main purpose for this modal is to kick off the export, prpovide clear indication to the user that something is happening, and a place to return the download link to. We don’t need the user to input any data so we will kick off the flow run in the OnStart property of the App. We also set some variables (varFlowInProg & varFlowComplete) that we can use to control the show/hide of elements later on. By wrapping the Flow run call into a a Set() for varFlowResponse we can capture the response values and use them in subsequent steps.

Runtime errors - Don’t Panic

If you close and reopen your custom page it might look a little scary/broken but don’t worry. As the flow runs ‘On Start’ in context of the form it can sometimes get a bit upset in studio edit mode. So long as the errors are ‘Runtime’ isues, don’t worry too much before testing from the modal screen with form context.

You can also slide in some hardcoded test variables by passing in a marketing for ID in the OnStart property so you can stay within the app maker studio to test, just dont forget to comment them out before you try testing in the model-driven app!

SUMMARY GIF AGAIN E2E

If you made it this far then kudos. Bonus outcome - even if it fails, it still looks really neat!

Coming soon…

Creating the JSON summary for Marketing Form landing page and eevnt registration submissions - including custom registration responses! Then, how to export event registrations too.