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

September 14, 2020September 26, 2020 priyeshwagh777 Microsoft PowerApps addcolumns, canvas power apps, Power Platform, sharepoint

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 –

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

Which will look like this
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.
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

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.
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.
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

Thank you!

Implement real-time search in Gallery of CDS records in a Canvas Power App | Power Platform

September 9, 2020September 26, 2020 priyeshwagh777 Microsoft PowerApps canvas power apps, common data service, filter, power apps, Power Platform

One of the most common asks is to be able to search CDS records in a Gallery control in real-time using a Text-box.

Search Box & Gallery connected to CDS

Let’s design a simple gallery that is populated with CDS records from Account entity.

Take a Text Input control (Textbox) and make it look like a Search Box.

In order to have the Search watermark, write the text to appear as water-mark, enter it in Hint text property of the Text input
Take a Gallery control and select Data Source as Accounts from CDS.

The way the above shown Gallery control is populated is by selecting a CDS Data Source which is as below –

Filter records

Finally, there’s a 1-line formula that will do the trick for you

In the Items property of the CDS Gallery, I have to filter the records based on the Text of the Text Input on my form.

Marked by green arrow Filter() function is the one which handles filtering of the data set based on the parameters demanded by the method. Here’s Microsoft Docs on the same – https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Underlined by red Accounts is the CDS data source which I want to filter on.

StartsWith() is a method which is used to compare the start of the string in question. In this case, the text I enter in the SearchField textbox.

1. ‘Account Name’ is the field in the Accounts dataset to be considered on which the search parameter is dependent.
2. Underlined by purple SearchField.Text in which SearchField is the textbox which is used to query the Name of the Account and .Text is Text value of the string which will be considered.

So at all times, whenever there’s a change in the Textbox value, the Items of the Gallery control are supposed to be updated with the filtered records.

Working

Now, when you use the App, here’s what it will behave like. As you type, the records will filter with a certain delay (depending on how large your dataset is)

Hope this was useful!!

Here are some more Canvas Power Apps posts you might want to check –

Thanks!

Using triggerBody() / triggerOutput() to read CDS trigger metadata attributes in a Flow | Power Automate

September 6, 2020September 6, 2020 priyeshwagh777 Power Automate cds connector, Power Automate, triggerBody, triggerOutputs

Now, for all the newbies working with Common Data Service (Current Environment) connector face a little constraint in order to read certain fields which are NOT in the list of the Dynamic Content of the CDS connector itself.

Scenario

Let’s say you want to read this field from the Body / Outputs of the CDS trigger in a Flow in Power Automate

Attributes (or rather supporting metadata) won’t be accessible directly since it’s not from the context of the CDS connector itself like you see for other fields as below –

Flow

In this example, here’s how our Flow will look like.

We’ll declare 2 variables after the CDS connector with Create or Update trigger on Accounts entity and how we can work with the results.

Reading from triggerBody()

When to use triggerBody() ? – When you want to fetch attributes from the body of the trigger.

Here’s how you can use triggerBody() function to read ‘RunAsSystemUserId’ from the outputs of CDS connector.

Let’s say you have a variable declared to store your results. Let’s call it Trigger Body. Click on the fx sign to enter the formula
Now, in the formula, enter triggerBody()?[‘RunAsSystemUserId’]. triggerBody() is a method to you’ll find it in auto-complete
And the variable will look like this

Result of triggerBody() will be as below

Reading from triggerOutputs()

When to use triggerOutputs() ? – Whenever you need to access anything from within the body as well as some info from the header as well as shown in the 2nd screenshot in step #2 below.

Here’s how you can use triggerOutputs() funtion to read ‘RunAsSystemUserId’ from the outputs of CDS connector

Let’s say you have a variable declared to store your results. Let’s call it Trigger Output. Click on the fx sign to enter the formula
Now, in the formula, enter triggerOutputs()?[‘body/RunAsSystemUserId’]. triggerOutputs() is a method to you’ll find it in auto-complete.

The reason this we write “body/RunAsSystemUserId” is because in the JSON structure of the complete Outputs of the CDS Connector, you’ll see that RunAsSystemUserId attribute lies inside body in the JSON.
Whereas in terms of triggerBody() above, we are reading attributes from the “body” itself.

Result of triggerOutputs() will be as below

Hope this was useful!

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

Thank you!

Convert environments between Production and Sandbox | Power Platform Admin Center [Quick Tip]

September 5, 2020 priyeshwagh777 Power Platform Admin Center Power Platform Admin Center, production instance, production to sandbox conversion, sandbox instance, sandbox to production conversion

In most scenarios, you might need to either –

Convert from Production to Sandbox or
Convert from Sandbox to Production

Converting from Production to Sandbox

Navigate to Power Platform Admin Center’s environments section – https://admin.powerplatform.microsoft.com/environments
Select the environment you want to convert (you can even navigate inside this environment)
You’ll be asked for confirmation. It states that any services will be unavailable during this conversion.
Process initiated. It takes a few minutes.
In Progress.
Since there’s no refresh button to show you it’s completed, you’ll have to check back in a few minutes in the same link as above – https://admin.powerplatform.microsoft.com/environments
You’ll find that the conversion must’ve been completed by now.

Similarly, the opposite holds true while converting from Sandbox to Production! Hope this quick tip helps!

Here are some more Admin/PowerPlatform posts you might want to check out –

Thank you!

Log Canvas Power App telemetry data in Azure Application Insights | Power Apps

August 31, 2020 priyeshwagh777 Microsoft Azure, Microsoft PowerApps application insights, canvas power apps, instrumentation key, Microsoft Azure, power apps

Here’s how you can register your Canvas Power App in your Azure’s Application Insights and log telemetry data into Azure.

Some basic info about what all you can see in Application Insights is –

Count of Users who used the app
Events logged, Sessions logged
Device info
Region info

It’s quite simple to set it up! Let’s take a look –

Registering in Application Insights in Azure

First, make sure you do have an Azure Subscription. Let’s look at how you can register an Application Insight record.

Look for Application Insights in Azure in the search bar
Then, among other records, you can register a new one which will identify with your Canvas Power App
Review all that you entered and move ahead
It’ll be deployed pretty quickly within a few minutes unlike some heavy Azure resources
Upon completion, you can navigate to the resource and see the details

Zoomed In

Add Instrumentation Key to Canvas Power App

Next step is to add the Instrumentation Key to the Canvas Power App

Look for the App itself in the Navigation tree
Once you select that, you can then look at it’s Properties on the right hand side. In Instrumentation Key, paste the Instrumentation Key you copied when you created the App in the Azure.
And to go with that, let’s say my Canvas Power App has some basic structure like below

Using Application Insights

As the users use the Canvas App, the following information is logged –

Navigate to the Usage section in the Application Insights you registered and if you scroll down on the main pane, you can see w
And then scroll down to reveal more Insight data
Or, if you see Events, you can see info like this –

I’m not completely sure why my users showed high when I tried with only 2 users. But perhaps, once I dive more deeper into using Application Insights and how each of the metrics are read, I’ll come back to update this post. 😊

And likewise, if you know how to read the Application Insights, you can customize this to give you what you need to see.

Here is some Microsoft Documentation on Application Insights to help you out –

https://docs.microsoft.com/en-us/azure/azure-monitor/app/usage-segmentation

Hope this was useful. Here are more Azure/Canvas Power App related posts you might want to check –

Thank you!

Find Created On date of solution components in Solution Layers | Dynamics 365 [Quick Tip]

August 28, 2020 priyeshwagh777 dynamics 365 administration, Dynamics 365 CE dynamics 365 crm, field created on, solution history, solution layers

If you’ve been using classic CRM since 2011 days until Solutions History came out (around 2018 timeline), we always wondered when was a certain field, view, form (component in general) was created.

It’s possible to see using Solution Layers

Example: When was a field created

Let’s say you want to find out when a field was created.

Select the field, check for Solution Layer as shown below
Now, go into the Active layer
In Active layer, scroll down to the details
You’ll be able to see the Created On field
If there was a component/field that is too old and was created before this feature was introduced, it’ll show a default 1900 DateTime value

Looking elsewhere

I tried to look for this info in XrmToolBox’s Metadata Browser but couldn’t find it –

Please let me know if any better suggestions as well! 🙂

Here are some more Dynamics 365 CE / CRM related posts you might want to check –

Hope this was useful

Call HTTP Request from a Canvas Power App using Flow and get back Response | Power Automate

August 25, 2020August 25, 2020 priyeshwagh777 Microsoft PowerApps, Power Automate canvas power apps, http request from canvas power app, http request in flow, Power Platform

Now, there are several questions about how can we make HTTP requests from a Canvas Power App. Well, as of today, only HTTP with Azure AD exists in native Canvas Power App Connectors, so in order to call HTTP requests only to external resources, you’ll need to go via a Flow and get response back.

Scenario

Call a Flow from within a Canvas Power App and capture the response back in the Canvas Power App.

In my example to keep things easy, I’m using a sample HTTP request provided by https://openweathermap.org/ (OpenWeatherMap) [I know there are native Weather connectors, but I’m just using as an example]

Flow

Here’s how Flow looks –

Let’s say I create an Automated Flow which is triggered from a Power App. Just select that, there’s nothing to add to it.
Next Step, I’ll make an HTTP request to a Service (In my example, I want to retrieve Humidity. You would of course have your use case)
I’ve used the same example in one of my posts where I talked about making HTTP requests using Flow. Check this post – Make HTTP request from Flow in Power Automate
To breakdown my received HTTP response into distinct pieces of info, I’ll parse it.
Final step would be to send back the parsed info to the Power App itself so that I can use the info internally in the Canvas Power App from which this Flow was called.
Now, I’m choosing to send back the Humidity value. So, I’ll choose a type of Output to be sent my response into
And since I already parsed the response in the step above, it’s easy to select what piece of info I want to send back to my Canvas Power App.
I’ve created a variable called as humidityValue and in that I’m passing humidity from the parsed JSON step.
That’s it. My Flow overall looks like this

Canvas Power App

In my Canvas Power App, I only have a button which will populate the data into a label –

Let’s say, I have a button called as Get Humidity. And below it, is a Label having “Humidity is” already written
Now, the next step is to add the Flow in the Canvas Power App. Navigate to Action in the menu bar in Canvas Power App Studio and look for Power Automate
On clicking it, you’ll be able to select the Flow you want to add in the Canvas Power App
Once you select that, it’ll appear on the OnSelect formula bar of the button. Ideally, this should be added to whatever event you want the Flow to Run from (In my case, click of Get Humidity button)
It’ll populate with a default formula like below. But we need some modification for it to be populated to the Label.
Now, you can write the below formula to add the value retrieved from the variable which you created dynamically called as ‘humidity‘ and upon adding dot to the Run(), you’ll be able to select the variable you passed to the Canvas App from Flow.
So, the completed Formula looks like this.
Here, I’m using Set() function to set a dynamically declared variable in called as humidity and populate it with the value of humidityValue which we’ll get when we run Flow and the Flow will make an HTTP call to get that value.
In case you also need to understand how variables work, you can refer this post of mine – Variables in Canvas Power Apps | Global and Context
Now, we need to set it to the Label. So, we’ll write a formula on the Label’s Text event. So that, the Label is displaying Humidity is <valueOfHumidity> variable
And you’re set. Hope this explanation was easy. Now, let’s Run the App and click Get Humidity button

Working

When I click a button, I’ll get the value into the Label field which I created.

When I click the button, it’ll be greyed out for a moment while the Canvas Power App calls the Flow and the Flow runs the HTTP request and sends back response to Canvas Power App.

Finally, once the variable is set, the Label control will display the value as below which came via HTTP using Flow

You can also see the results going in the Flow Run as well

Hope this was useful. Here are some more Canvas Power App & Power Automate posts you might want to look at –

Thank you!

Change Booking Status colors on Schedule Board for Field Service/PSA [Quick Tip]

August 19, 2020August 19, 2020 priyeshwagh777 Dynamics 365 CE, dynamics 365 field service, Dynamics 365 PSA dynamics 365 field service, Dynamics 365 PSA, project service automation, schedule board

Here’s a Quick Tip for everyone using Field Service and Project Service Automation. In case you are wondering how can you change the colors of the Booking Statuses on the Schedule Board for Field Service and PSA, here’s what you need to do.

Default Colors

This what your default Schedule Board looks like out-of-the-box.

Booking Statuses

Booking Status is an entity within which you can set this up.
You can navigate to Booking Statuses entity in your Field Service like below

There are these records you can modify. Also, for PSA, you have the same entity with different records for Booking Statuses
But, let’s look at Field Service’s example –

Now in each record, you can see in Common tab

And you can change by selecting this color picker control

And save the new selected color.

And let’s change for Completed as well, just another example – changed for Completed also to a greenish shade

Updated Colors

Now let’s look at the updated Schedule Board of how these colors look.

Hope this quick tip helps!

Here are some more PSA/Field Service posts you might want to look at –

Thanks!

Send a Power App Push Notification using Flow to open a record in Canvas App | Power Automate

August 16, 2020 priyeshwagh777 Microsoft PowerApps, Power Automate canvas power apps, power apps, Power Automate, Power Platform Admin Center, push notification

Let’s say most of your users are using custom Canvas Power Apps to follow their business tasks and rely on Canvas Apps for their actions inside Dynamics 365.

There could be scenarios where you want to notify them conditionally of important items that need their attention.

Scenario

Let’s say you have a few users whom you want to tell them that an Opportunity was Won. It could be a team or a single User.

Here’s what my scenario is – A PowerApp Notification is sent to designated User(s) using Flow. When an Opportunity is Won

A Power App Notification is received

And when user clicks he Notification, Canvas Power will open and show that record.

But of course, your implementation/applications for this can be limitless!! This is just my example!

Flow

Here’s what the Flow looks like –

I’m triggering the Flow on Update of the Opportunity. You can use Trigger Conditions to make sure your Flow is triggered only on the update of the Opportunity Win
[Optional, according to my scenario] I want to send this to the Owner of the Opportutnity for now. Of course, it makes sense to send it to others. But let’s keep it simple for now. 🙂
So, I’m capturing Email address here to be used further down
[Optional, according to my scenario] Further, I’m only checking if the Status was Won. Status Reason = 3 meaning Opportunity was Won
Next, once your condition is satisfied, you can search for this Connector and Action in your Steps in the Flow.
For now, you only have this one Action which you’ll need.
This is how it looks –
Recipient Item – 1 holds the Email address of the User to whom the notification will be sent to. More can be added by using the + Add new item button.
Message holds what should be displayed when the notification is received to the end user.
Open App – Yes/No. Boolean to set if the Power App is supposed to be Opened upon selecting the Notification or not.
Parameters – You can pass parameters to the Canvas App and use it inside Canvas App. Example: To open the record directly if the App is designed in that way.
First thing you need to do is to create a specific connection for this so that you can use it to open the specific App in Power App.
You can give your Connection a name and then the ID of the App must be entered. Once done, click create.
In case you’re wondering where you’ll get the App ID. You can find the Canvas App ID in the Details section of your Canvas Power App, you’ll only need that to be copied
And you can enter the below options –
In my example, I’m sending an alert to the email I captured in #2 above.. In your case, you can set this dynamically and add more as well by clicking on “+ Add new item“

In Message, I’ll enter what the notification should read.
Open App is set to Yes. Means if I click the notification, it’ll open the Canvas Power App whose ID I used above to create the Connection.

Parameters, this is optional. If you want to open the specific record, you can pass the GUID like this and in the next section, we’ll see how we can open the record using that.

Canvas Power App

In the previous step, remember we sent the Guid as parameter to the Canvas Power App, here’s what you can do to read the record and use it to open the specific record.
Here’s how you can read Parameters passed to the Canvas Power App.
Param(“<ParameterName>”)

I’m using it to Lookup the Opportunity that I passed from the Notification to the Canvas Power App.

In my application, I’m using Lookup to fetch the record and set it to the Item property of the Edit Form control

Some references to use if you’re looking to Capture Parameters and Lookup/Filter data based on your GUID is that’s your implementation.

Pass Parameters to Canvas Power App – https://sachinbansal.blog/2018/06/17/powerapps-canvas-app-how-to-pass-parameter-in-app-url-display-data-based-on-parameter-passed/
Lookup/Filter Records – http://linnzawwin.blogspot.com/2019/12/power-apps-using-common-data-services.html

Working

Let’s say an Opportunity was Won in Dynamics 365.

And the user will receive a notification like this.

Clicking on which, they’ll be taken to the Canvas Power App record which I set in my Canvas Power App.

Hope this was helpful!

Here are some more Canvas Power App posts you might want to look at –

Thank you!

Add multiple Opportunity Products at once in Dynamics 365 Sales | Enhanced Experience [Preview]

August 12, 2020August 12, 2020 priyeshwagh777 d365 for sales, Dynamics 365 CE add multiple products, d365 Administration, dynamics 365 for sales

Very easy tweak but this will save loads of your time. One of the most important asks by Salespeople is perhaps this – “Add multiple Products on Opportunity at once!“

Here’s how you can do this –

Classic Experience

In current/classic experience, if you open Opportunity Lines and go on to add a Product as below –

It’ll either open in a New form.

This isn’t intuitive. You definitely need better experience.

Enhanced Experience

In System Settings, under Sales, you’ll need to enable the Adding Products to Yes. This will enable the enhanced experience.

Now, when you click on Add Products in the Opportunity’s Product Line Items tab, you’ll see a Quick Create Form like form on which you can Add Multiple Products in one go.

And then go to + Add products
Now, a Quick Create form will appear on which you can select multiple products
Now, when you click on any + sign in blue, you can directly enter what quantity you want to add.
Also, if you go to the Selected section which indicated how many unique products you’ve added, you’ll be able to remove the added Products in case you don’t want them

and then Delete the same if needed.
Now, let’s say I have this finally and when I click on Add to Opportunity, they get added as Opportunity Lines

And they appear as below

Hope this helps!!