Maintaining Audit History of Files Uploaded to Common Data Service File Attribute
If you want to maintain the audit history of the files that were uploaded to the file attribute, the standard audit history won’t work. The Audit view will only provide you the GUID of these files. You won’t be able to keep the record of all the files that are uploaded to the file attribute.
Below is my file attribute along with the audit history that is available out of box.
As you can see the audit history only shows the GUID of the file record. If you click on the record, on the detail page you see the file name but there is no option to download this file.
In this post I am suggesting a workaround that you can use to create an audit trail of the files you upload that contains the actual file, not just the file name.
High Level Steps
When the file is uploaded to the Common Data Service File Attribute:
- trigger the flow when the file is uploaded
- get the file content
- get the file name
- create a note
- attach that file to this note
- capture the name of the details of the user that uploaded the file along with the date
The file attribute that I have on my form is named Proposal
I clicked on it, attached the file,
A note gets created.
Note that this note contains the GUID of the person that uploaded the file along with the timestamp.
In this post first we will see how we can create this note and then we will convert this timestamp to a readable date/time format and replace the Guid with the name and email of the person that uploaded the file.
Note on Uploaded On and Uploaded By
If you are running the flow as the triggering user, you don’t need to fetch the user details. The note that you create will have the same user.
Also on timeline, it will give you the time the note was created/modified.
Hence you don’t need to put the Uploaded By and Uploaded On fields in the note description.
Trigger the flow when the file is uploaded
Get the file content
The easiest way to get the file contents is to use the below REST API.
GET /api/data/v9.1/<entity-type(id)>/<file-attribute-name>/$value
Here is how the below URL looks in my case:
You can call the REST API in the flow using the invoke HTTP with Azure connector.
Click on the three dots on the top right corner and make sure that your Azure AD connection is valid.
Azure AD connection is required because if you invoke the API without it, it will not work.
Click on the invalid connection and specify your Dynamics 365 URL as Base URL and Azure Resource AD URI.
Click sign and sign with the account you are using with your flow.
Another alternative to fetching the file content is this connector.
Get the file name
Getting the file name is tricky. If you check the field created corresponding to the file attribute in the CDS view available on PowerApp Studio (make.powerapp.com), the field will show you the GUID of the uploaded file.
The best way I found to identify the column containing the file name was to use Power BI Desktop. When you connect Common Data Service with PowerBI Desktop, it shows you all the internal columns available in every CDS table.
Click Get Data and connect to Common Data Service.
In the next screen select the Opportunity table from the list of tables. The Opportunity table will start appearing on the right side in the column titled field. Right click the Opportunity and then select edit query
Now select the column and select all the columns that have a proposal in their name as the file attribute that we have created is named ‘proposal’. When you filter the results you will find that proposal_name contains the file name.
The reason why I am suggesting this is because this column is an internal column that is currently not available in CDS view.
Create a note
Select Common Data Service ‘Create new record’ action.
Specify
Mime Type: text/xml
Object Type: opportunity
Note: for object type don’t select the Opportunity option from drop down as currently it is not working. Select custom and then type opportunity in small letters.
Also note that in regarding (Opportunities), I have put /opportunities/Opportunity. If you don’t do it, you will get an error as this action expects this format (it is OData format).
Attach that file to this note
Set note Is Document to yes.
In the file name specify the file name column as shown below:
Easiest way to do it:
- select dynamic content and then select expression
- type string
- click dynamic content and randomly select any available column of your entity.
- replace the column name with your file name column
In my case it is string(outputs(‘getOpportunity’)?[‘body/***_proposal_name’])
Your file body will be the Body field of ‘Invoke an HTTP request action’
- click on expression, type base64
- click on Dynamic Content
- select the Body field of ‘Invoke an HTTP request’ action
- the final expression will look like this: base64(outputs(‘Invoke_an_HTTP_request’)?[‘body’])
Fixing the user details
Fetch the user details using the common data service get record action
Fixing the date format
In case of datetime formatting there are two things that need to be done. First the time is in GMT, this needs to be converted to your time zone, second is the format that needs to be fixed.
Since I am based in Melbourne (Australia), I will use that timezone. Here is how the expression looks like:
formatDateTime(convertTimeZone(outputs(‘getOpportunity’)?[‘body/modifiedon’], ‘UTC’, ‘AUS Eastern Standard Time’), ‘dd/M/yyyy h:mm tt’)
Once this is done. Now when I upload the file, the note contains my details along with the timestamp.
