AddColumns() function to dynamically add columns to a Data table in Canvas Power App | SharePoint List

At times, you want to do a quick calculation but the Data Source doesn’t have that column present.

Example, you’re populating some data from a SharePoint List which is a Scoreboard. But it doesn’t tell the difference between the current Score and Top Score.

So, to find how many points does the Score trails by the Top Score. To do that, we’ll dynamically add a Column to our Data Table in a Canvas Power App by using AddColumn() function.

SharePoint List & Canvas App

First part is the list itself which you already saw above, I have the below Canvas App –

  1. The only thing I have in this Canvas App is a Data table which I named as ScoreBoard

    Which will look like this

  2. I’m setting the Data Source of this Data Table to be DynamicList and not the actual SharePoint List. Because, in order to create a dynamic column, I should be using Collections in this demo and I can’t use the SharePoint List as Data Source directly.

  3. And to write my function, I’ll also add a Button so that on click of which, I can populate my internal Data Source in this case which is Collection called as DynamicList to be populated with data. You can use any other way as suitable.

AddColumns() function

  1. Since I want to generate my dynamic column, I’ll first use ClearCollect() method to add the SharePoint List with the dynamic column to my Collection which I’ll call as DynamicList.
    All this OnSelect of the Button which I added.

  2. The formula goes as below
    ClearCollect(<NameOfCollection>, AddColumns(<ActualDataSource>,”ColumnName”,<Expression>))

    In the above formula,
    ClearCollect(DynamicList) is being created to create a Collection called DynamicList and Clear if there’s any pre-existing data in it before populating fresh data.
    AddColumns(Scoreboard,”Trial By”, ‘Top Score’ – Score) – Scoreboard is the actual SharePoint List I’m using which doesn’t have the column, Trial By is the dynamic column I’m creating. And my expression is Subtract Score from Top Score so that I get the difference.

  3. Make sure you select the columns. Perhaps once you run in in your browser, and try to click the button, the column will be available for your selection in the below Edit Fields section. [I had to do a little back and forth, perhaps you might have a smarter way 😊]



    Here’s Microsoft Docs on the function – https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping ?WT.mc_id=DX-MVP-5003911

Run

Now, when I run the app, and click on Load Scoreboard button, I see the below data populated with by dynamic column Trail By.


Hope this was useful.

Here are some more Power Automate / Flow posts you might want to check

  1. Call HTTP Request from a Canvas Power App using Flow and get back Response | Power Automate
  2. Send a Power App Push Notification using Flow to open a record in Canvas App | Power Automate
  3. Accept HTTP Requests in a Flow and send Response back | Power Automate
  4. Terminate a Flow with Failed/Cancelled status | Power Automate
  5. Adaptive Cards for Teams to collect data from users using Power Automate | SharePoint Lists
  6. ChildFlowUnsupportedForInvokerConnections error while using Child Flows [SOLVED] | Power Automate
  7. BPF Flow Step as a Trigger in CDS (Current Environment) connector | Power Automate
  8. Pause a Flow using Delay and Delay Until | Power Automate
  9. Generate Dynamics 365 record link in a Flow using CDS connector | Power Automate
  10. Text Functions in a Flow | Power Automate
  11. Loop through array of objects in a Flow & Create records in CDS | Power Automate
  12. Get Count of records retrieved in CDS connector in a Flow | Power Automate

Thank you!

Save generated PDFs to SharePoint directly – 2020 Wave 1 | Early Access Feature

With Wave 1 of 2020, Early Access lets you use fantastic features in Dynamics 365 CE. For Sales customers, this little feature will definitely be a delight!

Disclaimer: Before we proceed, please note that this is a part of the Wave 1 2020 Early Access. We can’t be sure if this will make it to the final release. Also, you can enable Early Access into your Sandbox instance only and test since it’s not recommended for Production at this point.

Let’s start! Till now post Wave 2 2019, you were able to generate PDF Documents from the Document Templates you created for entity records. You can now take it a step further by having to choose between just downloading the file or Saving it to SharePoint directly behind the record.

Pre-Requisites

  1. SharePoint Integration enabled in D365
  2. Generate PDF option enabled for the entity record you want this feature on.
    If you are looking to enable this feature, check this post – Create & Send PDFs from Word Templates for Quotes in D365 CE Wave 2 Updates
  3. Wave 1 2020 Enabled for your Sandbox Organization.

You don’t need to perform additional settings for enable/disable this feature.

Creating PDF

  1. Just as you used to create a PDF from Wave 2019 updates, you need to do the same.
    createQuote
  2. And you’ll be prompted if you want to also save it to SharePoint.
    saveRec
  3. Once successful, you’ll see that it was saved to SharePoint.
    savedToSP

 

Saved to SharePoint

You can check the Documents grid, the Document will be attached there –
DocView
onSP
In this scenario, it won’t do both -Download and Save to SharePoint.

Hope you find this feature helpful!

Get SharePoint Document Location link on field on create in D365

I’ve been working on getting the SharePoint Document’s link to get back into D365 but looks like it can’t come back and get tagged to the record it was created from.

I tried to fire off a plugin on SharePoint Document record create, but in vain. Flow didn’t do justice since SharePoint Document entity doesn’t support Change Tracking.

So, I retrieved the Absolute URL of the “SharePoint Document Location” entity on it’s create itself so that I have the link once the record is created. And click that link to open the folder in SharePoint.

Workflow to wait and then fetch

So if you immediately make a call to retrieve the Absolute URL in the plugin code you’ve written on Create won’t work because Absolute URL gets created moments after the actual Location record is created unlike being passed in the execution context.

My approach is to fire the workflow on the create of the SharePoint Document Location record and Wait for 1 minute.

wfBody

After 1 minute, set off the flag to call the plugin and fetch the Absolute URL as shown below –

updateFlag

Retrieve Absolute URL in Plugin

You can use RetrieveAbsoluteAndSiteCollectionUrlRequest message to retrieve the SiteCollection URL and the Absolute URL of the document folder of the record

retrieveAbsolute

 

Working

  1. When you open the Document sub-grid on the record for the first time, a Document Location record is created –
    openForFirst
  2. The workflow designed above will run and fire off the plugin after 1 minute. The plugin will then make the message request go through to get you the Absolute URL as below –
    link2
  3. And probably you can use that link to open up the folder by clicking on the field that has this URL or send it across in email to internal users (whatever suits your requirements)
    linkInField
  4. It will open up the SP folder for that record and will have all the documents you attach in the Document sub-grid on the record.
    spFolder

Hope this helps!