Relatively recently, Dataverse introduced the File column type. The catch was that these weren’t first class citizens of the portal and their contents couldn’t be accessed with either FetchXML or the Portal Web API. With the recent release of Read operations in the Portal Web API, all this changes 😎

Note – in case you missed it, here’s my post on how to retrieve Image columns using the Portal Web API

Why read on? Unlocking structured attachment capabilities in your portal makes managing files and chasing missing files a breeze, enhancing your business processes and making them ripe for automation… Oh and you’ll get some new multimedia superpowers too!

Previously, I used the Notes table as a workaround for file attachments; to upload and retrieve due diligence and evidence documentation in my case management portals, communicate report results, etc. and to retrieve audio files to play in my Spotify clone project. This got the job done but was clunky and came with drawbacks. I always hoped for (and my clients always demanded) a better way. Cue File columns in Dataverse…

Benefits of the File column type include:

• The ability to apply field-level security in the portal web API, limiting access to only specific image columns within a table e.g. okay to see a compliance certificate, not okay to see the client’s ID documents. Okay to upload ID documents, not okay to upload file reviews.
• Structured data: ease of management in the back-end e.g. check programmatically  / at-a-glance / with advanced find which documents have been received – no need to trawl through endless notes with meaningless filenames. Use Power Automate to chase only what’s missing
• Data storage benefits: out-of-the-box integration with Azure blob storage. Files uploaded to the File column type are stored in Blob storage rather than Dataverse relational storage – Unless you’re using Customer Managed Keys

How to retrieve file columns using the Portal Web API

Retrieving File columns with the Portal Web API is much the same process as retrieving Image columns. What will differ is the way we output the contents.

Example 1: Retrieve a PDF and display in an iframe

webapi.safeAjax({
    type: "GET",
    url: "/_api/msevtmgt_events({{ request.params.id }})/musdyn_brochure",
    contentType:"application/json",
    success: function(res) {
        console.log(res);
        $('#main').append('<iframe src="data:application/pdf;base64,' + res.value + '" frameborder="0" height="100%" width="100%"></iframe>');
    },
    error: function(xhr, status, error){
        var errorMessage = xhr.status + ': ' + xhr.statusText;
        console.log(errorMessage);
    }
});

Example 2: Retrieve an MP3 file and display an audio player

webapi.safeAjax({
    type: "GET",
    url: "/_api/musdyn_tracks({{ request.params.id }})/musdyn_audiotrack",
    contentType:"application/json",
    success: function(res) {
        console.log(res);
        $('#main').append('<audio controls><source src="data:audio/mp3;base64,' + res.value + '" type="audio/mpeg">Your browser does not support the audio element.</audio>');
    },
    error: function(xhr, status, error){
        var errorMessage = xhr.status + ': ' + xhr.statusText;
        console.log(errorMessage);
    }
});

Checking for the presence of files

We can’t return file columns in FetchXML or liquid so how can we check that a file is present? Luckily we have a supporting column – the name of the file. The naming convention is the database / logical name _name. For example, my musdyn_audiotrack image column would have a supporting column called musdyn_audiotrack_name
In the following example, I first use the entities object to retrieve the record then check for the presence of a file using liquid. If my liquid condition returns true, only then do I retrieve the file:

{% assign track_record = entities['musdyn_track'][request.params.id] %}
{% if track_record.musdyn_audiotrack_name %}
    <script>
            webapi.safeAjax({
                type: "GET",
                url: "/_api/musdyn_tracks({{ request.params.id }})/musdyn_audiotrack",
                contentType:"application/json",
                success: function(res) {
                    console.log(res);
                    $('#main').append('<audio controls><source src="data:audio/mp3;base64,' + res.value + '" type="audio/mpeg">Your browser does not support the audio element.</audio>')
                },
                error: function(xhr, status, error){
                    var errorMessage = xhr.status + ': ' + xhr.statusText;
                    console.log(errorMessage);
                }
            });
    </script>
{% endif %}