Archive
[Step by Step] Canvas App | Dataverse | Filter, Patch, For All, Lookup
In this article, lets understand the Filter, Patch, For All and more functions and formulas using following ‘Car and Customer’ scenario. I will be using Dataverse formerly CDS as Data Source.
- Create a ‘Manufacturer’ table to store Car manufactures.
- Use OOB ‘Contact’ table to store Customers.
- Create a ‘Cars’ table to store Cars.
- Configure ‘Contact’ and ‘Manufacturer’ look ups in ‘Cars’ table.
Lets proceed first by understanding ‘Data Model’ and then with creating ‘Canvas’ App.
Dataverse Data Model:
- ‘Cars’ form with ‘Manufacturer’ and ‘Customers’ look up.
- ‘Cars’ view.
Configuring Canvas App
Lets build the Canvas App, with following features:
- Add “Manufacturers”, “Cars” and “Contact” (i.e., Customer) Data Sources.
- Add ‘combManufacture’ Combo box to show ‘Active’ Manufacturers.
- Select ‘Data source’ as ‘Manufacturers’.
- Select ‘Active Manufacturers’ from the ‘Views’.
- Add ‘combCustomers‘ Combo box to show ‘Active’ Customers (i.e., Contacts).
- Select ‘Data source’ as ‘Contacts’.
- Select ‘Active Contacts’ from the ‘Views’.
- Filter the ‘Cars’ by selected ‘Manufacturer’ and set to ‘collCars’ collection variable.
- Formula : ClearCollect(collCars,Filter(Cars,Manufacturer.Manufacturer=combManufacture.Selected.Manufacturer));
- Add ‘galCars’ Gallery control and select the ‘Data Source’ as the collection variable from previous step.
- In the Gallery, add a ‘Checkbox’ control to enable selection and a Label to show the ‘Customer’.
- On check of ‘Checkbox’ add the selected ‘Car’ to ‘selCars’ collection variable, using ‘OnCheck’ event.
- Formula : Collect(selCars, ThisItem);
- On uncheck of ‘Checkbox’ remove the selected ‘Car’ from ‘selCars’ collection variable, using ‘OnUncheck’ event.
- Formula: Remove(selCars, ThisItem);
- Add ‘Set Owner’ button to map the selected Cars and Customer.
- First move the selected ‘Cars’ from ‘selCars’ collection to ‘tempCollection‘ by renaming ‘Primary’ column to “ID” using RenameColumns function.
- Use ForAll function to loop through ‘tempCollection‘ (i.e., Selected Cars) and update the ‘Contact’ lookup with ‘combCustomers‘ selected record using Patch function.
- Reset the Cars Gallery(i.e., Reset(galCars)) to refresh.
- Display the message using Notify function.
- Clear the collection variables.
- Formula: ClearCollect(tempCollection,RenameColumns(selCars,”crbb8_carid”,”ID”));ForAll(tempCollection,Patch(Cars,LookUp(Cars,Car=ID),{Contact:combCustomers.Selected}));Reset(galCars);Notify(“Update Successful..”,NotificationType.Success);Clear(tempCollection);Clear(selCars);
- Add ‘Clear Owner’ button to unmap the selected Cars and Customer.
- To unmap the Customer with Car, clear ‘Contact’ look up by setting ‘Blank()’ function (i.e., {Contact:Blank()}).
- Use ‘For All’ and ‘Patch’ functions as above to update the record.
- Formula : ClearCollect(tempCollection,RenameColumns(selCars,”crbb8_carid”,”ID”));ForAll(tempCollection,Patch(Cars,LookUp(Cars,Car=ID),{Contact:Blank()}));Reset(galCars);Notify(“Update Successful..”,NotificationType.Success);
- Now run the App and you should see screen as below.
Refer this article for PowerApps functions and formulas.
🙂
Canvas App -Working with Bing Maps connector
In this article lets understand the basics of ‘Bing Maps‘ connector to locate the addresses in Canvas App.
Prerequisites:
- Subscribe to Dynamics 30 days trial and get Office 365 account.
- Connect to Power Apps maker portal using Office 365 account to build the Canvas App.
- Bing Map API Key
How to get Bing Map API Key:
- Connect to Bing Maps Portal
- ‘Sign In’ using either ‘Microsoft Account’ or ‘Enterprise Azure Active Directory account’.
- Go to ‘My account -> My Keys’ and create a new key. (Steps to create new key)
- Copy the Key which we gonna use in next steps.
Adding ‘Bing Maps’ connector to Canvas App:
- Create a new Canvas App.
- Add a ‘Bing Maps’ connector.
- Provide the ‘API Key’ captured previously.
Locate address using ‘Bings Maps’ connector:
- For better understanding, I added below controls to my Canvas app’s screen.
- 3 Text box controls to capture address
- An Image control which loads the map
- A Button to load the map.
- On Button ‘OnSelect’, Declare a global variable ‘varAddress‘ and use BingMaps.GetLocationByAddress API to convert the address to coordinates (i.e., Latitude and Longitude).
-
Set(
varAddress,
BingMaps.GetLocationByAddress(
{
addressLine: txtAddress1.Text,
locality: txtAddress2.Text,
postalCode: txtZip.Text
}
)
);
-
- Now use the coordinates from ‘varAddress‘ variable and load the map in Image control.
- Set ‘Image’ property to BingMaps.GetMap() by setting coordinates from ‘varAddress’.
-
BingMaps.GetMap(
“AerialWithLabels”,
15,
varAddress.point.coordinates.latitude,
varAddress.point.coordinates.longitude)
-
- Run the App and you should see the address located in Map.
- You can also show a ‘Push Pin’ using ‘pushpin’ property. You need to pass coordinates as below.
-
BingMaps.GetMap(
“AerialWithLabels”,
15,
varAddress.point.coordinates.latitude,
varAddress.point.coordinates.longitude,{pushpin:varAddress.point.coordinates.latitude&”,”&varAddress.point.coordinates.longitude}
)
-
🙂
Power Apps – ‘Environment Variables’ and their usage in Canvas Apps
Lets take a scenario, where you would want to pass a 3rd party API URL to your plug-in which changes every quarter. Couple of options to pass the URL to plug-in are either pass the URL as ‘Secured/Unsecured parameters’ or Create an entity and store the API URL and fetch from plug-in using Query Expression.
Using ‘Environment variables’, which is Power Apps preview feature currently, we can configure the input parameters allow management of data separately compared to hard-coding values.
Up on creating ‘Environment variable’, a new record gets added to both OOB ‘Environment Variable Definition’ entity and ‘Environment Variable Values’ entities.
‘Environment Variable Values’ is child to the ‘Environment Variable Definition’ entity.
We can refer records from these OOB entities in plug-ins or Canvas Apps or Power Automate just like any other entities.
In this article, lets see how to create ‘Environment Variables’ and use them in Canvas App.
Prerequisites:
- Subscribe to 30 days trial and get Office 365 account.
- Connect to Power Apps maker portal using Office 365 account.
Create a new ‘Environment variable’:
- From Power Apps maker portal go to ‘Solutions’ tab.
- Either create a new solution/Open Existing solution.
- From ribbon, click on New -> Environment variable
- In the ‘New environment variable’ pane, provide ‘Display Name’ and choose ‘Data Type’.
- Provide either ‘Default Value’ or ‘Current Value’
- Save. I’ve created a ‘Text’ variable with an environment URL as ‘Current Value’.
- I’ve configured a Model driven app with ‘Environment Variable Definition’ entity added to the ‘Site Map’ to check the created ‘Environment variable’ record.
- Open the Model driven app and go to ‘Environment Variable Definition’ and you will see the record.
Using ‘Environment variable’ in Canvas app:
As the ‘Environment variable’ stores in ‘Environment Variable Definition’ and ‘Environment Variable Values’ entities, we can add these entities from ‘Data sources’.
- Create a new Canvas App.
- Add ‘Environment Variable Definitions’ and ‘Environment Variable Values’ entities as Data sources.
- Add a Label to display the ‘Environment variable’ value.
- Use ‘LookUp’ function to get the specific ‘Environment variable’ filtered by ‘Display Name’.
- LookUp(‘Environment Variable Values’,’Environment Variable Definition’.’Display Name’=”AppURL”).Value
- In the above formula, ‘AppURL’ is my ‘Environment Variable’ display name.
- The value would be fetched and set to the Label text.
Managing ‘Environment variables’:
- Environment variables can be created and managed through the modern solution interface or by using code.
- A separate JSON file is created within your solution package for the values, which can also be managed in source control and modified in a build pipeline.
- When you export the solution, a json file ‘environment_variable_values’ will be available.
- Open the file and the ‘Environment Variables’ render as json records.
- Export to and import from Excel is also supported.
🙂
Power Platform – Pass external API collection from Power Automate to Canvas App
In this article, lets see how to pass an external API’s json collection from Power Automate(Formerly ‘Microsoft Flow’) to a Canvas application.
For this example, I am going to use ESRIÂ mapping service API, in my Power Automate Flow. Refer here to know more about ESRI. You may also use other APIs as per your convenience.
ESRI API is url based and returns the ‘Address Suggestions’ in json format.
Before we start lets make sure to meet all the prerequisites.
Prerequisites:
- Subscribe to 30 days trial and get Office 365 account.
- Connect to Power Automate portal using Office 365 account to build Power Automate flow.
- Connect to Power Apps maker portal using Office 365 account to build the Canvas App.
- ESRI api URL – Refer my previous article on the usage of ESRI or you can simply use this url
Once all prerequisites are met, here is the high level design:
- Create a new Power Automate flow and call ESRI API to fetch Address Suggestions.
- You can take any open API which returns json collection.
- Parse the ESRI API Response to json collection.
- Create a new Canvas app
- Trigger Power automate flow from Canvas App and read the collection.
Lets get started.
Steps to configure the Power Automate Flow:
- Connect to Power Automate portal
- Create a new ‘Instant flow’ by selecting the right Environment.
- Provide a name ‘GetAddressSuggestions’ and select trigger as ‘PowerApps’
- To call ESRI api, add a new ‘HTTP’ action.
- Choose Method as ‘GET’ and in URI paste the ESRI url as mentioned in prerequisite section.
- Next, we need to parse the response from ESRI api. As the ESRI results would be in json format, add ‘Parse JSON’ action.
- In ‘Parse JSON’ action,
- set Content as ‘Body’ from HTTP action.
- Next we need to provide the json schema of ESRI response. To do that, click on ‘Generate from sample’.
- Now copy the response from ESRI API (Copy the browser’s output using ctrl+a+copy)
- Paste in the ‘Insert a sample JSON Payload’ window and click ‘Done’.
- If no warnings, your ‘Parse JSON’ pane should look as below.
- As we completed the ESRI response capture and parsing to json, now we need to pass the captured json response to Power App.
- To pass the json response to Power App, add a new ‘Response’ action.
- In the ‘Response’ pane,
- Set the ‘Body’ to ‘Body’ from ‘Parse JSON’.
- Expand ‘Show advanced options’ and click on ‘Generate from sample’.
- Copy the response from ESRI API and paste in the ‘Insert a sample JSON Payload’ window and click ‘Done’. (Same step like we did in ‘Parse JSON’ pane).
- ‘Response’ pane should look as below with no warnings.
- Run the flow and make sure it ran successfully.
Steps to configure the Canvas App:
As we completed the Power Auto Flow, now its time to consume the Power Automate flow response from Canvas App by following the steps below
- Connect to Power Apps maker portal using the same Office 365 account.
- Create a new Canvas App.
- Note: Make sure the same Environment used for Power Automate is selected.
- Add a new button.
- Select the button, from the ribbon menu, click on ‘Action -> Power Automate’.
- From the pane select the ‘GetAddressSuggestions’ Power app flow.
- After few seconds, you would see the Run() command auto populated as below.
- As we going to get json collection from flow, lets capture the json collection to a collection variable ‘collAddress’ using ‘ClearCollect()’. Refer article to know more about ‘ClearCollect()’.
- With the ClearCollect() and Run() functions, the final ‘OnSelect’ statement should look as below.
- Lets run the App and click on the button. Application takes couple of minutes to complete the run.
- Post run, check the collection returned from flow by going to ‘File -> Collections’ tab.
- You can also add a ‘Data table’ control and display the results returned from flow as below
🙂
Canvas App using Dynamics On-premise data
Last week there was a question on my blog to check for the options to build a Canvas App which consumes CRM on-premise data.
To answer this, Power Apps has connectors (CDS and Dynamics 365) to communicate with Dynamics online but there is no built-in Dynamics on-premise connector.
However there are options to connect Canvas app with Dynamics on-premise, which are listed below.
Using ‘Gateway’ to connect to SQL on-premise DB:
- This option is about connecting to Dynamics SQL DB using on-premises data gateway.
- The on-premises data gateway acts as a bridge to provide quick and secure data transfer between on-premises data PowerApps.
- Refer my blog on steps to use gateway and build a Canvas App.
- Limitation:
- With ‘Gateway’ option we can only view Dynamics data in PowerApps.
- There is no way to add or modify Dynamics data using the current PowerApps gateways.
Using ‘KingswaySoft’ connector:
- KingswaySoft SSIS Integration Toolkit for Dynamics 365 includes components to integrate with CDS.
- Using the ‘CDS Connection Manager’ we can easily connect and bring data into and out of CDS.
- Once data is in CDS, we will be able to leverage this data with PowerApps.
- Any updated data being generated with PowerApps will be reflected in CDS which can then be retrieved and synced with your Dynamics on-premise instance.
Integrate on-premise environment with the CDS:
- Its the same approach, ‘KingswaySoft’ connector leverages but this option is manual integration.
- Integrate the on premise environment with the CDS–in effect setting up a hybrid environment, where you have a copy of your configuration in the cloud as well as your on premise CRM, and create a bi-directional integration to synchronize data changes between the two environments.
- Limitation:
- There will be potential delay for record changes to synchronize between the On-premise and CDS.
Refer the article for more details.
🙂
[Step by Step] Canvas App using on-premise SQL server as Data Source
In this post, lets see how to build a Canvas App with data from on-premise SQL Server.
Using Gateways ,data can be transferred quickly and securely between a canvas app and an on-premises data source, such as an on-premises SQL Server database or an on-premises SharePoint site.
Lets get started to configure a Gateway and read the on-premises SQL Data which acts as a Canvas App’s ‘Data Source’.
Prerequisites:
- SQL Server with a Database and a simple table.
- Dynamics 365 subscription. Subscribe to 30 days trail here.
Configure a ‘Gateway’ to on-premise SQL Server:
We need to install ‘On-Premises Data Gateway’ service on the machine with ‘SQL Server’.
- Connect to PowerApps maker portal using your Dynamics 365 credentials.
- Go to Data -> Gateways and click on ‘+New gateway’ button.
- You will be redirected to downloads page and click on ‘Download’ under ‘On-Premises Data Gateway’ section.
- Download and click on the ‘GatewayInstall’ application.
- Provide your Dynamics 365 email (i.e., User ID) and click ‘Sign in’ to connect to Power platform.
- Provide your desired gateway name,recovery key and click ‘Configure’
- You will end up with below success screen.
- We are done with Gateway configuration.
- If you go to the ‘Services’ on your machine, you should see ‘On-premises data gateway service’ up and running.
- Go back to PowerApps portal and refresh the ‘Gateways’ and you should see the configure gateway.
Create a Canvas App:
As we completed ‘Gateway’ configuration, the next step to create a Canvas app and connect to on-premises SQL server using the ‘Gateway’.
- Click on ‘+Create’ and choose ‘Canvas app from blank’
- Provide the ‘App name’ and click on ‘Create’
- Now we need to establish the connectivity between Canvas App and SQL Server.
- Click on ‘Connect to data’ and select ‘SQL Server’ from the list.
- Choose ‘Connect using on-premises data gateway’ option and provide the SQL server, Gateway details and click ‘Create’.
- Select the table (i.e.,Contact) and click ‘Connect’ which creates a new ‘Data source’.
- Now add a Gallery component and map the ‘Data source’.
Additional Details:
- To install the ‘Gateway’, machine should have minimum configuration mentioned here.
- Gateway installs as ‘On-premises data gateway service’ on the server. Make sure this service is running all the time.
- Along with service, a desktop application by name ‘On-premises data gateway’ gets installed. You can enable logging and manage settings using this application.
🙂
Rajeev Pentyala - Microsoft Power Platform 