Tip #1458: How to copy files between Dataverse tables

No names in this story have been changed to protect the innocent—or was it to protect the guilty? Or to expose the innocent? Anyway, read on.

When Megan V. Walker (her real name) asks a question, others listen because it’s often a non-trivial challenge.

I have two tables, both with a file column. I want to use Power Automate to move the file from TABLE A to the corresponding field on TABLE B.

(Easy, right? Not so fast! – g.d.)

I’ve used the action of ‘Download a file or an image’ from TABLE A. Then used the action of ‘Upload a file or an image’ to TABLE B. The Content used is – @{body('Get_Document')} and the Content name used is @{outputs('Get_Document')?['body/hr_antibriberycorruptionpolicydocument_name']}.

I can only get it to show the name of Untitled.txt regardless of the real file name or file type. Any ideas what I am doing wrong?

Megan V. Walker

Some things are not what they seem or expected, and this is one of those cases. Turns out, we can do it in nocode-locode-hicode or in 3-2-1 steps!

3. No code

As Megan discovered herself, and Chris Piasecki (his real name) confirmed, file name and file content are like fire and water, water and electricity, sushi and chocolate, alcohol and medication, sleep and caffeine, superheros and capes – they don’t go well together. You need two actions to get them both and then one action to write it to the destination.

This image displays a three-step process in a Power Automate flow, intended to copy a file from one database table to another. Get a row by ID: The first step involves fetching a row from a table named 'Contacts' by a specified Row ID. Selected columns to retrieve are 'fullname', 'lab_registration', and 'lab_registration_name'. Download a file or an image: The second step shows the action to download a file or an image. It uses the same Table name 'Contacts' and Row ID as the first step. The 'Column name' specified for the file or image to be downloaded is 'Registration'. Upload a file or an image: The final step involves uploading a file or an image to a different table named 'Accounts'. It uses a different Row ID and specifies the 'Column name' as 'Registration'. The 'Content' to be uploaded is fetched from the body of the output of the first step, specifically the 'lab_registration_name'. The flow visually indicates that data is retrieved from a row in the 'Contacts' table, a file associated with that row is downloaded, and then a file is uploaded to a different 'Accounts' table, preserving the original file's or image's registration name.

(George, why are you still using old designer? Uhm, because I can get everything in one screenshot?!)

2. Locode

Fire and water, put together, produce a lot of smoke. All we need is a bit of mirror to accomplish all in two steps. The mirror is HTTP with Microsoft Entra ID (preauthorized) – Connectors | Microsoft Learn connector that allows us to call Web API directly. The call https://thisisnotadrill.crm.dynamics.com/api/data/v9.2/tablename(record-guid)/file-column-name/$value will return the file name in a header unimaginatively called x-ms-file-name (and thanks jod for that).

Screenshot of a simplified two-step Power Automate flow. The first step, 'Invoke an HTTP request', sends a GET request to retrieve information from a Dynamics CRM API, with details like URL and headers provided. The second step, 'Upload a file or an image', inputs data into the 'Accounts' table in a 'Registration' column, with the content dynamically populated from the previous HTTP request's response.

1. Hicode

The challenge would not be complete without a seasoned developer like Guido Preite (yep, real name too) stepping up and creating nothing less than a Dataverse custom action that accomplishes the challenge in one step.

Screenshot displaying a Power Automate flow step titled 'Perform an unbound action'. It shows the action 'fca_CopyFileColumnToFileColumn' with advanced parameters configured. Parameters include 'Item/SourceTable' set to 'account', 'Item/SourceColumn' set to 'new_filecolumna', 'Item/TargetTable' set to 'contact', 'Item/TargetColumn' set to 'new_filecolumnb', 'Item/SourceId' and 'Item/TargetId' filled with GUIDs, and an empty 'Item/NewFilename' field. On the right, a visual flow diagram highlights this action as the second step, following a 'Manually trigger a flow' step.

The parameter NewFilename allows you to rename the file; if you leave it empty it will copy the source filename.There is an output parameter called Success that is set to false if anything goes wrong. In this scenario, additional output parameter Error will have the juicy details.

Source code and managed solution are available from https://github.com/GuidoPreite/FileCustomAPIs.