Skip to content
Franco Mussopower pages
Window with purple light
Photograph · Manos Gkikas / Unsplash
Guide No.017
TutorialMultimedia · Portals Web API

How to retrieve File columns using Power Pages’ Portal Web API

Relatively recently, Dataverse introduced the File column type.

Level
03CustomiseStage 3 of 4
Read
4 min
Includes
3 snippets
Written
Feb 2022

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:

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

Retrieve file column and display PDF in iframeJavaScript
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

Retrieve file column to HTML5 audio playerJavaScript
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:

Check whether File column contains dataLiquid
{% 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 %}
Go deeper · Power Pages Mastery

This guide is one technique. The course is the method.

The course covers this in Level 1 · Foundations: “Document management approaches”, alongside “Data modelling for external users”. A structured programme distilled from dozens of real portal launches — from messy stakeholder requirements to successful go-live.

  • 6 weeks
  • 3 levels
  • Live training + video lessons
Where this fits
  1. Level 1Foundations
    • Document management approaches
    • Data modelling for external users
  2. Level 2Extending the platform
  3. Level 3Advanced customisation
Franco’s engaging teaching style made training a pivotal contribution to my professional growth. The walkthrough of a Power Pages site resembling Spotify left an indelible mark.
Bhargav Reddy — Solution Architect — TCS

Comments

No comments yet. Spotted a gotcha I missed? Say so below.

Leave a comment

Your email address won’t be published. Comments are moderated.

Consulting

Need a Power Pages build that editors love and brands survive?

I help teams design and build Power Pages sites with bespoke UX. Tell me about yours.

Search

Book a discovery call

30 minutes. Bring your trickiest Power Pages problem — I’ll tell you honestly whether I’m the right fit.