Mid-Month PowerApps Video Round Up!

 

While we are only 11 days into the month, we already have an amazing selection of PowerApps videos for your viewing pleasure!

If you haven’t already checked out the Community Gallery be sure and do so!

 


 

Creating Update-able Power BI Reports with PowerApps

 

 


Using PowerApps Visual in Power BI to interact with data

 

 

 


Creating a PowerApps Menu Components

 

 


PowerApps Deep Linking How To

 

 


PowerApps With Function

 

 


Data Validation Demystified – IsMatch()

 

 


Getting Started with the PowerApps Component Framework

 

 


PowerApps In-App Security Trimming – Easy SharePoint Method

 

 


PowerApps Draggable/Moveable Control

 

 


Adding a Signature Line to PowerApps and Saving

 

 


Walk through creating the internal Microsoft PowerApps Tool “Thrive” application with Pat Dunn

 

 

 


Share PowerApps With Guest / External Users

 

 


Microsoft PowerApps – Pass string array to SQL Stored Procedure from PowerApps using Flow

 

 


Shoutouts Template Configuration – Save data in SharePoint

 

 


Connect XrmToolBox To CDS

 

 


PowerApps CDS Security


 

Creating Updateable Power BI Reports with PowerApps by Ike Ellis

When asked what content our blog readers and video viewers would like to see more of, the most requested topic is: “Industry Best Practices”.

With that in mind we will be starting a series of Blog posts and Webinars by our MVPs highlighting work they have done.

The first in this series is by Ike Ellis (see bottom of post for more about Ike) and in this post he highlights how you can create a solution that enables a mobile sales force to update their own Power BI reports.

Webinar on this topic: September 12th 10AM PST

Where: https://aka.ms/IkeEllisWebinar

Combining PowerApps with Power BI to create powerful and compelling solutions

By Ike Ellis

One of my customers is a very large RV dealer with multiple locations. They allow sales people to set their own monthly goals. They wanted a Power BI report that would update when sales came into the database and when salespeople updated their particular goals for each month. Creating an updateable report is a very common request from customers.

Each RV dealership is humongous. RVs are individually large, so when you’re selling a lot of them, the lots have to be pretty large to accommodate them. It is very difficult to cover a lot that size in WIFI. Additionally, sales people often engage with customers away from the dealership. This made Microsoft PowerApps an ideal solution for updating their goals and sales. PowerApps for mobile devices can use the mobile network, in addition to WIFI. PowerApps can use the PowerApps gateway to query and update records in databases that are behind firewalls.

Combining PowerApps with Power BI, along with all the Azure services, really makes each individual product so much more powerful and compelling to the customer. If we’re comparing Power BI with its competitors, emphasizing how easy it is to integrate with these other products is a major competitive advantage. After we showed a demo of the solution to the customer, it was an easy sell. There really is no competition.

This blog post is a simplified version of the app I created for the customer. It will show how a Salesperson can update their goal for the month, while decision makers can see the Power BI report update as the data refreshes.

For our demo, we’ll be using three Microsoft products together: Power BI, PowerApps, and Azure SQL Database.

Directions:
Sign up for Power BI, Power Apps, and a free trial of Microsoft Azure

 

Create and seed a new Microsoft Azure SQL Database.


  • Search for “SQL Database” and click on the first result


  • On the welcome screen, click “Create”.
  • Choose your subscription and location. Try to pick a datacenter that’s close to you. I chose “West US”, since I live in San Diego, California.
  • Type “SalesDB” as the name of the database.
  • Create a new server or choose an existing server.
    • Choose a name for the server. It needs to be globally unique. When you connect to the database from PowerApps and Power BI, you’ll use the full name, which will include the unique name you just entered with a prefix of “.database.windows.net.”
  • Click “Configure database”. This is where we will choose how much money we will spend on our database. For a real production application, we would obviously try to predict our load and size the database accordingly. For our demonstration, we can just use the cheapest solution possible. I chose a Basic tier database. I slid the slider bar for the amount of space I need down to 100MB. At the time of this writing, this database will cost about $5 per month. Not bad. Click “Apply”.


My filled out form looks like this:


  • Click “Review + create” and then “Create”.
  • One of the things that amazes me most about Azure is how quickly resources come online and are available to use. As a developer, I’ve put in requests with IT departments for my entire career. When I ask them to create a SQL Server with a new database for me, it usually takes between two weeks and two months. In Azure, it’s done in less than five minutes!
  • When it says the deployment is complete, click “Go to resource”.


  • Azure SQL DB has a built-in firewall. Three things need access to your new Azure SQL Database: your workstation, PowerApps, and Power BI. Click on “Set Server Firewall” to configure the firewall.


  • Make sure you click “Add Client IP” and verify that the button for “Allow access to Azure Services” is selected. Then click “Save”.


  • In the next few steps, we’re going to use Azure Data Studio to create a login account and password, assign permissions, create two tables, and seed those tables so that we can use your new Azure SQL Database.
  • On the connections window, click “New Connection”


  • Under Connection Details, type in your unique servername that you choose earlier. Remember it ends with “.database.windows.net”.
  • Under authentication type, click “Azure Active Directory – Universal with MFA Support”
  • Under account, click “Add New Account”.
  • This screen will direct you to go to a URL and enter in a code while helping you login to your Azure Account. This will authorize your Azure Data Studio to connect to your Azure SQL Server.


  • Under Database, type “SalesDB”.
  • Your Connection Details should look like this with the correct account selected:


  • Now in the connection window, right-click on your new server and click “New Query”
  • Paste the following code in the window and run it.

drop
table
if
exists SalesPeople;

drop
table
if
exists goals;

drop
table
if
exists Sales;

go

create
table SalesPeople

(SysID int
not
null
identity(1,1) PRIMARY
KEY

, SalesPersonFullName varchar(100))

GO

insert
into SalesPeople

(SalesPersonFullName)

VALUES

(‘Sterling Sanchez’)

, (‘Rick Archer’)

, (‘Morty Figgis’)

, (‘Lana Smith’)

go

create
table Sales

(SysID int
not
null
identity(1,1) PRIMARY
KEY

, CustomerName varchar(100)

, OrderDate datetime

, SalesPersonID int

, SaleAmount decimal(14,2))

go

create
table Goals

(SysID int
not
null
identity(1,1) PRIMARY
KEY

, SalesPersonID int

, GoalMonth int

, GoalYear int

, GoalAmount decimal(14,2))

go

insert
into Sales

(CustomerName, OrderDate, SalesPersonID, SaleAmount)

VALUES

(‘Arch Burgers’, ‘7/1/2019’, 1, ‘520.00’)

, (‘Bob”s Investigative Service’, ‘7/3/2019’, 1, ‘139.00’)

, (‘Arch Burgers’, ‘7/13/2019’, 1, ‘272.00’)

, (‘Southland Laundry’, ‘7/21/2019’, 1, ‘499.00’)

, (‘Smith Consulting’, ‘7/2/2019’, 2, ‘296.00’)

, (‘Reed Gardening Services’, ‘7/13/2019’, 2, ‘321.00’)

, (‘Fashion Design By Gilbert’, ‘7/25/2019’, 2, ‘673.00’)

, (‘Sterling Bar and Grille’, ‘7/30/2019’, 2, ‘152.00’)

, (‘Allington Investment Advisors’, ‘7/3/2019’, 3, ‘210.00’)

, (‘Rad Construction Supply’, ‘7/5/2019’, 3, ‘329.00’)

, (‘Sterling Bar and Grille’, ‘7/7/2019’, 3, ‘199.00’)

, (‘Allington Investment Advisors’, ‘7/17/2019’, 3, ‘195.00’)

, (‘Johnson Tool & Die’, ‘7/22/2019’, 3, ‘52.00’)

CREATE USER SalesUser WITH
PASSWORD
=
‘PowerAppsAreGreat!’

ALTER
ROLE db_datareader ADD MEMBER SalesUser;

ALTER
ROLE db_datawriter ADD MEMBER SalesUser;

  • Create a PowerApps application that allows SalesPeople to enter their goals for the month.


  • Name the app “Sales Goals” and select the phone layout. Click “Create”.


  • It will take a minute to create the new application.
  • You may need to skip a welcome screen.
  • We are going to connect this app to our Azure SQL Database. Click the “View” tab, then click “Data sources”. Type in “sql server” into the text box and click the red SQL Server icon.
  • Click “Add a Connection”.


  • Since our database is in Azure, we’ll click the radio button “Connect directly (cloud services)”.
    • Type in the SQL Server name from earlier that ends in “.database.windows.net”.
    • Type in SalesDB as the SQL database name.
    • Type SalesUser as the Username.
    • Type PowerAppsAreGreat! as the password.


  • For the table, we’ll select “Goals” and “Salespeople” and click “Connect”.
  • Our goal is to create to create a screen that looks like this:


  • We are going to create one data entry element at a time and then add the button. Then we’ll test our application.
  • Let’s start with the label at the top. On the Insert tab at the top bar, click “Label”. In the Text property, enter “ENTER GOAL AMOUNT FOR MONTH (0.00)”. Include the double quotes.


  • On the right side, you’ll see the PowerApps property window. You can use this window to control the behavior of an individual control in PowerApps.
  • Click on the Advanced tab on the window on the right.
  • In the search area, type “weight”.
  • Change the FontWeight to Bold


  • Change the Align to “center”.


  • We will be following that pattern with a lot of these properties without giving accompanying screenshots.
  • Now let’s add dropdown list for Goal Month.
    • Add a label to the control. Change the Text value to “Goal Month:”
    • Add a Drop Down control. On the left window. You’ll see a new dropdown titled DropDown1. Next to the name, you will see an icon with the ellipsis (…). Click on it and rename it to ddlGoalMonth. You will be renaming many of the controls. The completed Tree view on the left will look like this


Change the Items property to the following:

Table(
{
Text: “January”,
Val: 1
},
{
Text: “February”,
Val: 2
},
{
Text: “March”,
Val: 3
},
{
Text: “April”,
Val: 4
},
{
Text: “May”,
Val: 5
},
{
Text: “June”,
Val: 6
},
{
Text: “July”,
Val: 7
},
{
Text: “August”,
Val: 8
},
{
Text: “September”,
Val: 9
},
{
Text: “October”,
Val: 10
},
{
Text: “November”,
Val: 11
},
{
Text: “December”,
Val: 12
}
)

  • This makes the dropdown display the individual months in the correct order. It also assigns a value to the month so that it sorts correctly.
  • Now let’s create the dropdown for Goal Year.
    • Add a label and change the Text property to “Goal Year:”.
    • Add a drop down. Rename it to ddlGoalYear.
    • Change the Items property to [“2019”, “2018”]. In this case the value and the text are the same thing, so we don’t need to use a Table function in our code like we did for month.
  • Create the dropdown for Salesperson.
    • Add a label and change the Text property to “Salesperson:”.
    • Add a drop down. Rename it to ddlSalesperson.
    • Change the Items property to ‘[dbo].[SalesPeople]’.
    • Change the Value property to “SalesPersonFullName”.
  • Create the textbox for Goal Amount.
    • Add a label and change the Text property to “Goal Amount:”.
    • Add a text input control. Rename it to txtGoalAmount.
    • Clear out the Default property.
  • Add the Save button
    • Add a button control. Rename the control btnSave.
    • Change the Text property to “Save”.
    • Change the OnSelect property to the following code:

RemoveIf(
‘[dbo].[Goals]’,
SalesPersonID = ddlSalesperson.SelectedText.SysID,
GoalMonth = ddlGoalMonth.SelectedText.Val,
GoalYear = Value(ddlGoalYear.SelectedText.Value)
);
Collect(
‘[dbo].[Goals]’,
{
SalesPersonID: ddlSalesperson.SelectedText.SysID,
GoalMonth: ddlGoalMonth.SelectedText.Val,
GoalYear: Value(ddlGoalYear.SelectedText.Value),
GoalAmount: Value(txtGoalAmount.Text)
}
)

    • Each salesperson can only have a single goal for the same month and year. The REMOVEIF function finds a matching record on those three criteria and deletes the record. It then adds a new record based on the inputs
  • Let’s add a few goals and then check the database to see if the goals have updated.
    • Hit F5 to launch the application in your browser.
    • Add goals for each of the sales people for July 2019.
    • Make sure the goals are at least over $500, but below $3,000.
    • Click the save button.
    • Go back to Azure Data Studio. Open a New Query window for the SalesDB database. Enter “SELECT * FROM GOALS”. Execute that code. You should see records for goals for each of your sales reps.
  • There are a few improvements that we would make to this application before releasing it. I didn’t add these to this post because it would detract from the main solution.
    • When the user logs on to PowerApps, PowerApps knows the email address that the user used. This is the same email address that was configured when we shared the application to the user. Using this email address, we know who the user is and wouldn’t need them to select their own name in the dropdown.
    • Give the user the ability to see and search through all of their old goals.
    • Create an approval process where managers see the goal and approve it or deny it.
    • Create an audit log where we can see who entered what goal and when for accountability.
    • Create a status bar that provides feedback to the user so they know a record was successfully inserted.
  • Now let’s make the Power BI report that the decision makers will see.
    • Open Power BI Desktop
    • Let’s import all the data we need
      • Click “Get Data”
      • Choose “SQL Server”
      • For the server name, put the same server name you entered into PowerApps.
      • Enter “SalesDB” as the database.


      • Click OK.
      • On the next screen, click the “Database” tab on the left. Enter your username and password: SalesUser and PowerAppsAreGreat!
      • Click OK.


  • Choose the Sales, Salespeople, and Goals tables. Click OK.


  • Click OK. This will import all of your data from SalesDB into a Power BI dataset.
  • We need to do a little data preparation before we can create our visuals.
  • Let’s add two columns so we can join the Sales table and the Goals table.
    • On the navigation pane on the left, click the “Data” tab. This is the tab in the middle.


  • On the right, next to the Goals table, click the Elipsis button (…). Click “New Column”.


  • In the DAX editor, enter the following DAX:

JoinKey = FORMAT(Month(Sales[OrderDate]), “#”) & “-” & YEAR(Sales[OrderDate]) & “-” & Sales[SalesPersonID]


  • The table should now look like this:


  • The JOIN column is month, year, and salespersonID concatenated. We’ll do the same thing for the Sales Table.
  • Click the ellipsis button for the Sales Table. Add a New Column. Enter the following DAX:

JoinKey = FORMAT(Month(Sales[OrderDate]), “#”) & “-” & YEAR(Sales[OrderDate]) & “-” & Sales[SalesPersonID]

  • Now let’s change the data model a little bit. Click Data Model tab, the bottom tab, on the left. Your screen should look like this:


  • Double-click on the relationship between the Goals table and the Sales table.
  • Make the screen look like the following screenshot. Choose the JOINKEY column for both tables. Verify that the cardinality is Many to One. Make sure the Cross filter direction is set to Both:


  • Change the relationship between the Sales and Salespeople table.
  • Make sure the relationship is Many to One. Make sure the Cross filter direction is set to Single.


  • Finally, we can add the visuals. We’ll create two simple visuals on the visual pane.
  • Click the Visual button on the left.


  • Add a slicer visual to the report
  • From the Sales table, drag OrderDate to the Field property.


  • Add a clustered bar chart to the report.
  • Add SalesPersonFullName (from the Salespeople table) to the Axis property. Add SaleAmount (from Sales table) and GoalAmount(from Goals table) to the Value property.


  • Your report should look like this:


  • You can clearly how the team is doing in relationship to their goal. As Salespeople make sales or enter goals into PowerApps, this dashboard will update. You can experiment with this by either inserting sales for Lana in Azure Data Studio and adding a goal for her in PowerApps for July, 2019. Come back to Power BI Desktop and click “Refresh” and see the new data in the report.

We have a hidden MVP in this solution in Azure Active Directory. Azure Active Directory users are the sharing mechanism in both PowerApps and Power BI. We can also grant AAD permissions to Azure SQL Database. This allows us to have a single sign-on between these products and easily know what users have access to which resources. This might seem trival and simple to people familiar with the Microsoft ecosystem, but it is very difficult to accomplish in competitive products.

And that’s it! We just created a great tool with PowerApps and Power BI. You can see that integration between these products is frictionless and powerful!

Ike

 

About the Author Ike Ellis

With over 18 years of experience in databases and a current Microsoft MVP, Ike has been Microsoft certified since the beginning, currently holding an MCDBA, MCSE, MCSD, and MCT.

Ike is the General Manager of Data & AI for Solliance. We have a full team of cloud data engineers and data scientists.   We specialize in building highly scalable data solutions for any size of organization.

Ike is a partner in Crafting Bytes, a San Diego software studio and Data Engineering group. We build software and BI solutions for companies all around the country.

In 2010, Ike founded the San Diego Tech Immersion Group (SDTIG). It has grown to be the largest user group in San Diego with over 125 active members including three other Microsoft MVPs. It is a technical book club that reads a book on a significant technical topic. Recent topics include Linux on Microsoft Azure, Angular 2/TypeScript, Data on Azure, Python for Data Scientists, and Docker/DevOps.   In July 2018, SDTIG started a track on Docker/Kubernetes. We will start a new track on Databricks/Spark in November 2018. You can join virtually and watch the youtube live stream: http://www.sdtig.com

Ike leads the San Diego Power BI and PowerApps user group that meets monthly. Find out more at https://www.pbiusergroup.com/communities/community-home?CommunityKey=a08275c5-90cb-4ba8-905b-646462aa1044

For more information, see http://www.ikeellis.com

 

 

Walk through creating the internal Microsoft PowerApps Tool “Thrive” application with Pat Dunn

 

PowerApps Guru’s Mehdi Slaoui Andaloussi, Venu Gopal Gaddamedi, and PowerApps celebrity Pat Dunn will walk through the PowerApps “Thrive” application.

 The PowerApps built, internal Microsoft tool called, Thrive, and is how Microsoft employees keeps up with company news, gets important alerts, see tasks at a glance, or see employee profiles, find and launch other Thrive apps.  In addition to being one of the widest used PowerApps at Microsoft it also has the distinction of having one of one of the highest NPS ratings!   In a recent Microsoft internal conferences 95% of the 4,000 attendees used this application for their entire conference experience of scheduling, evaluations and logistics. 

 In this webinar the team walk through their architecture enabling high performance and flexibility letting the team change setting of things like icons, descriptions, state, URLs etc. using app level configuration and user settings.

When 9/4/2019 8:00AM PST

Where: https://powerusers.microsoft.com/t5/Video-Gallery/Walk-through-creating-the-internal-Microsoft-PowerApps-Tool/td-p/351332

 

As a teaser wanted to include some screen shots of the Thrive Application:

News Feed

Application Launcher

Time Away

When 9/4/2019 8:00AM PST

Where: https://aka.ms/ThriveWebinar

About our presenter: Pat Dunn

 

 

Pat Dunn was one of the original authors of the PowerApps Canvas App Coding Standards and Guidelines whitepaper:  https://powerapps.microsoft.com/en-us/blog/powerapps-canvas-app-coding-standards-and-guidelines/

In his own words: Technical Program Manager for the Thrive suite of PowerApps within Microsoft. I design, build, and
support our employee experience and line of business PowerApps for over 120K employees.

Building PowerApps at scale, my team is a proxy to the enterprise customer and we’ve driven over
300 features and bugs into the Power platform while blogging on the experience, consulting with
customers, and authoring standards white papers.

Good by Live Writer HELLO Word Blog Templates

 

Lately I have been posting a lot of blog posts for my marketing brothers and sisters and unfortunately, they often (try to) give them to me in normal word or email format.

…Of course, pasting that into WordPress makes a mess of the formatting and loses all the images. The answer is really straight forward: the Word Blog Template.

Getting started is as easy as File > New and search for the word blog!

At this point word will take care of the image uploading and does a good job of preserving the formatting

 

This then gives you a striped down version of word that knows how to upload images, format to html etc etc

 

 

PowerApps User Group in Seattle Sept 18th

 

Join us for our 3rd PowerApps user group meeting here in the Seattle area!

These meetings are designed to help those new to the tool as well as those who have been with the tool since day one.

We look to leverage the meetings to engage with each other and the Microsoft team to help everyone along their learning journey.

We encourage you to come with questions and come along on this journey with us.

This month’s Agenda:

  1. How to get started with PowerApps Portals by Dileep Singh
  2. PowerApps & Flow Tips for Admins by our very own UG Leader Mandar Zope
  3. Microsoft Flow best practices with Maricel Medina
  4. Time providing PowerApps MVP, Ted Pattison, to show how to create PowerApps PCF controls

 

When:

September 18th, 2019 4-7pm PST

Where:

Microsoft Advanta C – Conf Room ADVANTA-C/4051
3003 160th Ave SE
Bellevue, WA 98007

Please RSVP as space limited to the first 24 people!!!!!!!!!!!!

https://www.powerplatformug.com/meetings/event-description?CalendarEventKey=65195c26-c408-4643-a2a1-6a35441d90f3&CommunityKey=c06415ce-273d-4ac6-81c2-e82d12587a9e&Home=%2fcommunities%2fcommunity-home%2frecent-community-events

 


Adv Hands On Lab 4: Embedded PowerApps canvas app into a model driven application

Scenario

Contoso has built a model-driven app to challenge their employees to innovate.  The way the app works is Contoso management can submit challenges and then employees can submit ideas. For example, a challenge might be “Reduce factory emissions by 10%.” Employees can submit ideas for challenges and get funded once they get enough votes. 

Contoso would like to announce new challenges in Microsoft Teams by posting some information about the challenge. Contoso’s users would like to be able to create this post from the model-driven Innovation Challenge app they use to manage the challenges.

You will be building an embedded canvas app that will let users start discussions without leaving the Innovation Challenge application.

Environment

The lab requires a PowerApps environment with a Common Data Service database created and the sample apps installed.

 

Requirements

The following have been identified as requirements you must implement to complete the project.

  • R0 – User must be able to start new Teams discussion from the existing Innovation Challenge application.
  • R1 – User must be able to select the Team in which they would like to start the discussion.
  • R2 – User must be able to select the Channel in which they would like to start the discussion.
  • R3 – The Challenge name must be included in the chat message.
  • R4 – User should be able to edit the message before posting it.
  • R5 – The embedded canvas should not be visible if a discussion was already started.

 

Exercise 1 – Canvas App in Model-Driven App

Task 1: Connect to Teams

In this task, you will add a Microsoft Teams trial to your tenant (if needed) and create a couple Teams and channels.

  1. Navigate to https://teams.microsoft.com/
  2. Login with your admin user.
  3. Click Use the Web App Instead.

  1. Click Start and wait for Teams to start. Note: if you already have a Teams licensed user, this step may not be needed.

  1. Select Teams and click Create Team.

  1. Click Build Team from Scratch.

  1. Select Public.
  2. Enter Team One for Team name and click Create.

  1. Click Skip when asked to add members to the team.
  2. Right click on Team One and select Add Channel.

  1. Enter Channel One for Channel Name and click Add.

  1. Right click Team One again and select Add Channel.
  2. Enter Channel Two for Channel Name and click Add.

  1. On the bottom left, click Manage Teams.

  1. Click Create a Team.

  1. Select Build a Team from Scrach.
  2. Select Public.
  3. Enter Team Two for Team Name and click Create.
  4. Click Skip.
  5. Right click on Team Two and select Add Channel.
  6. Enter Channel One for Channel Name and click Add.
  7. Right click on Team Two again and select Add Channel.
  8. Enter Channel Two for Channel Name and click Add.
  9. You should now have two teams with three channels each.

 

Task 2: Create Solution

In this task, you will create a publisher, create a new solution, add an existing entity to the solution, and add new field.

  1. Navigate to https://make.powerapps.com/ and make sure you are in the correct environment.
  2. Select Solutions and click New Solution.

  1. Enter Contoso Embedded for Display Name, click on the Publisher dropdown and click + Publisher.

  1. Enter Contoso for Display Name, contoso for Prefix and click Save and Close.

  1. Select Contoso for Publisher, enter 1.0.0.0 for Version and click Create.

  1. Click to open the solution you just created.

  1. Click Add Existing and select Entity.

  1. Select the Challenge entity and click Next. If you have difficulty locating the Challenge entity, use the search box to find and select it.

  1. Click Select Components.

  1. Select the Forms tab.
  2. Select all three forms and click Add. Note: Ideally we would just select the Main Form that we are going to edit, however due to a current bug that type of form is not showing and all three forms are named Information.

  1. Click Add again.
  2. Click to open the Challenge entity.

  1. Select the Fields tab and click Add Field.

  1. Enter Started Date for Display Name select Date Only for Data Type and click Done.

    Note: Currently Embeded Canvas Apps can only be created as a control for a field or a subgrid, you can’t just put a canvas app unbound to any data.

  1. Click Save Entity.

 

Task 3: Add Field to the Form

In this task, you will add the new field to the main form.

  1. Select the Forms tab.
  2. Hover over the Main form, click on the … More Commands and select Edit Form (Preview).

  1. Select the Contributed Ideas section.

  1. Click Add Control and select 1-Column Section.

  1. Go to the Properties pane, change the Section Label to Start Team Chat, change the Name to start_team_chat_section.

  1. Click Add Field.

  1. Select Started Date.

  1. Click Save.
  2. Click Switch to Classic. Note: Currently, you can only create the embeded canvas app and change the controls for a field from the classic form editor. In the future you will be able to do this without changing to classic.

Do not navigate away from this page

 

Task 4: Add Canvas App

In this task, you will create the canvas application. Since you are working in a solution, the canvas app you create here will also be included in the solution.

  1. Double click the Started Date field. In some browsers you may need to select the item and look for form properties in the header instead.

  1. Uncheck Display Label.

  1. Select the Controls tab and click Add Control.

  1. Select Canvas App and click Add.

  1. Select Web.

  1. Click Customize to create your canvas app in the PowerApps Studio. This will launch an app designer browser tab.

  1. Click File and select Save.
  2. Enter Start Group Discussion for Name and click Save.

  1. Click on Back button in the PowerApps console (not the browser back button).

  1. Right click on Form1 and select Delete.

  1. Rename the Screen teamsScreen.

  1. Select the View tab and click Data Sources.

  1. Go to the Data pane and click Add
    Data Source.

  1. Select Add New Connection.

  1. Search for Teams and select Microsoft Teams.

  1. Click Create.

  1. Click Add Data Source again.

  1. Search for Common Data Service and select it.

  1. Click Create.
  2. Select the Challenges entity and click Connect.

  1. Close the Data pane.
  2. Select the Insert tab, click Controls and select Drop Down.

  1. Right click on Dropdown1 and select Rename.

  1. Rename it teamsDropdown.

  1. Set the Items property to the formula below. If you have difficulty with copy/paste then type the formula manually. This formula will list the available teams in the dropbox

MicrosoftTeams.GetAllTeams().value

  1. Go to the Properties pane and click Advanced.

  1. Scroll down to the Data section and select displayName for Value.

  1. Click Controls and select Dropdown again.
  2. Rename the dropdown channelsDropdown.
  3. Set the Items property to the formula below. This formula will show the channels of the selected team in the second dropdown.

If(!IsBlank(teamsDropdown.Selected),MicrosoftTeams.GetChannelsForGroup(teamsDropdown.Selected.id).value)

  1. Go to the Properties pane and select displayName for Value.

  1. Select the teamsScreen.
  2. Select the Insert tab, click Text and select Rich Text Editor.

  1. Rename the RichTextEditor1 to messageText.
  2. Arrange the controls on the screen as shown below.

  1. Select the messageText control and set the Default value to the formula below. This formula will build the chat message.

Concatenate(“New Challenge available <strong>”, ModelDrivenFormIntegration.Item.sample_name , “</strong>. Go create your new ideas.”)

  1. Select the teamsScreen and click Button.

  1. Set OnSelect to the formula below. This formula will post the message to the selected channel.

MicrosoftTeams.PostMessageToChannel(teamsDropdown.Selected.id,channelsDropdown.Selected.id,{body:{content: messageText.HtmlText,contentType:1}})

  1. Change the Text value to Send.

  1. Rename the button Start Discussion.
  2. Move your Send button and place it below the text box. Resize it to be the same width as the textbox.
  3. Your canvas application will now look like the image below.

  1. Click File and select Save.
  2. Click Publish.

  1. Click Publish this Version.
  2. Click Close.
  3. Close the app designer browser tab.
  4. Click OK.

  1. Click Save.

  1. Click Publish.
  2. Click Save and Close.
  3. Close the form designer browser tab.
  4. Click Done.

  1. Select Solutions and click Publish All Customizations.

Task 5: Test the Application

In this task, you will test the application.

  1. Go to https://web.powerapps.com
  2. Select Apps.
  3. Click to open the Innovation Challenge application.

  1. Select Challenges.

  1. Double click to open one of the challenges.

  1. The Canvas app should load in the Start Group Discussion section and the challenge name will be included in the text.

  1. Start a new browser tab and navigate to https://teams.microsoft.com/
  2. Login if prompted.
  3. Go back to the Innovation Challenge app and select a Team from the first dropdown.
  4. Select Channel from the second dropdown.
  5. Click Send. You may edit the message.

  1. Go back to Teams. A new chat should get started in the team and channel you selected.

 

Bonus: Edit your form to look more like the image below

Exercise 2 – Update and Refresh

In this exercise, you will update the challenge record from the canvas application and show the updated record in the model-driven application.

Task 1: Add Field to the Form

In this task, you will add the Started Date field to the Challenge main form.

  1. Navigate to https://web.powerapps.com/ and make sure you are in the correct environment.
  2. Select Solutions and click to open the solution you created.

  1. Click to open the Challenge entity.

  1. Select Forms and click to open the Main form.

  1. Uncheck the Show Only Unused Fields checkbox.

  1. Select the Start Team Chat section, search for Started Date and click on it.

  1. Drag the new field and place it above the field you added in Exercise 1.

  1. Click Save.
  2. Close the form editor browser tab.
  3. Click Done.
  4. Select Solutions and click Publish All Customizations.

 

Task 2: Update Field and Refresh Form

In this task, you will update the Started Date value and refresh the form from the Canvas application.

  1. Navigate to https://web.powerapps.com/ and make sure you are in the correct environment.
  2. Select Solutions and open the solution you created.
  3. Select Apps, select the Start Group Discussion application, and click Edit.

  1. Select the Start Discussion button.

  1. Select the OnSelect property, place your cursor at the beginning of the existing function and paste the formula below. There are two functions in this formula, the Patch function will update the Started Date of the Challenge records and the ModelDrivenFormIntegration.RefreshForm will refresh the form.

Patch(Challenges, First( Filter(Challenges, Challenge = ModelDrivenFormIntegration.Item.sample_challengeid)), {‘Started Date’: Now()});ModelDrivenFormIntegration.RefreshForm(false);

  1. Click File and Save.
  2. Click Publish.
  3. Click Publish this Version.
  4. Click the app designer browser tab.
  5. Click Done.
  6. Select Apps and click to open the Innovation Challenge application.

  1. Select the Challenges and open one of the records.

  1. Locate the embedded canvas application and make sure the Started Date field is empty.

  1. Click Send.

  1. The Started Date field should now have today’s date.

 

 

Bonus – Hide Embedded Canvas

In this exercise, you will create a business rule that will hide the embedded canvas application if started date field has a value.

Task 1: Create Business Rule

In this task, you will create the business rule.

  1. Navigate to https://web.powerapps.com/ and make sure you are in the correct environment.
  2. Select Solutions and open the solution you created.
  3. Click to open the Challenge entity.
  4. Select the Business Rules tab and click Add Business Rule.

  1. Select the Condition, go to the Properties pane, and enter Check Started Date for Display Name.

  1. Go to Rule 1, select Entity for Source, select Started Date for Field, Contains Data for Operator, and click Apply.

  1. Click Add and select Set Visibility.

  1. Add it to the true side of the condition.

  1. Select the New Action, go to the Properties pane, enter Hide Canvas for Display Name, select Started Date for Field, select No for Visible and click Apply.

  1. Click Add and select Set Visibility again.
  2. Add it to the False side of the condition.
  3. Select the New Action, go to then Properties pane, provide the value shown in the image below and click Apply.

  1. Your Business Rule will look like the image below. Click Save.

  1. Click Activate.

  1. Click Activate again.
  2. Close the Business Rule editor browser tab.
  3. Click Done.

 

Task 2: Test Business Rule

In this task, you will test the business rule you created.

  1. Navigate to https://make.powerapps.com/ and make sure you are in the correct environment.
  2. Select Apps and click to open the Innovation Challenge application.

  1. Select Challenges and open one of the records. Select a record different from the one you used to test the canvas app.

  1. Go to the embedded canvas application and make sure it is visible on the form and the started date is empty.

  1. Click Send.

  1. The form should refresh, and the embedded canvas application will no longer be visible on the form.

 

Bonus – Open Quick Create Form

In this exercise, you will open a quick create form from the canvas application.

Task 1: Open Quick Create Form

In this task, you will add a button to the canvas application, this button will open a quick create form.

  1. Navigate to https://web.powerapps.com/ and make sure you are in the correct environment.
  2. Select Solutions and open the solution you created.
  3. Click on the … More Commands button of the Start Group Discussion application and select Edit.

  1. Select the teamScreen.
  2. Select the Insert tab and click Button.

  1. Rename the button QuickCreateButton.

  1. Place the button below the Send button and resize it.

  1. Change the button text Create Team Project.

  1. Select the OnSelect property of the button and paste the formula below. This formula will open the team project quick create form.

ModelDrivenFormIntegration.OpenQuickCreateForm(“sample_teamproject”)

  1. Click File and Save.
  2. Click Publish.
  3. Click Publish this Version.
  4. Click Close.
  5. Close the app designer browser tab.
  6. Click Done.

 

Task 2: Test Quick Create

In this task, you will test the embedded canvas application and see that the quick create form opens.

  1. Navigate to https://web.powerapps.com/ and make sure you are in the correct.
  2. Select Apps and open the Innovation Challenge application.
  3. Select Challenges and open a record you haven’t used to for testing before.

  1. Go to the embedded canvas application and click on the Create Team Project button.

  1. Quick Create form should open, provide the information below and click Save and Close.

  1. Select Team Projects. The Test Project should be in the list of records.

Adv Hands On Lab 2: Creating PowerApps Reusable Components

You are building a conference management application and need to display registered attendees and be able to cycle through them.  Each conference has different information that is important to see about the attendee.  For example, virtual conferences show different data than in-person conferences.  Therefore, you want to use a template associated with each conference to display the information.

You’ve decided to build a reusable component that will allow viewing of the records in a carousel that can cycle through the records.  The user will be able to manually cycle through the records or enable an automated play cycle. The content will be displayed using a dynamic HTML template that will be used to customize the display using an HTML text control.  By building a reusable component you believe other app makers will be able to use it for other similar scenarios.

To help ensure the control will work for multiple scenarios, you have chosen to build out the component using a test app that uses Accounts and Contacts and displays a simple business card for each contact.

Things you will learn

  • How to build a reusable component
  • How to define input/output properties
  • How to use simulate behavior properties on a component
  • How to dynamically tailor data using substitute
  • How to use the timer control to animate the carousel 

 

 

Exercise 1 – Create Canvas Application

In this exercise, you will create a canvas application and setup the carousel component. The hosting app itself will just be used as a test harness to support development of the reusable component.

Task 1: Create Canvas Application

In this task, you will create a canvas application and enable it for components. The app will have a gallery listing all the accounts from CDS and on the right side will have the component showing the related contacts. The component however could be used with any list of data and by design will not have any dependencies on a specific entity.

  1. Navigate to https://make.powerapps.com and make sure you are in the correct environment.
  2. Select Apps, click Create an App, and select Canvas.

  1. Select Tablet Blank App.

  1. Rename the screen MainScreen.

  1. Select the View tab and click Data Sources.

  1. Click Add Data Source.


  1. Select Common Data Service.

  1. Select Accounts and click Connect.


  1. Close the Data pane.
  2. Select the Insert tab, click Gallery and select Vertical.

  1. Rename the gallery accountsList.

  1. Go to the Properties pane and select Accounts for Items.

  1. Change the Layout to Title and Subtitle.

  1. Go to the Data pane, select Main Phone for Subtitle2 and select Account Name for Title2.


  1. Close the Data pane.
  2. Resize and reposition the accountsList as shown below.

  1. Click File and select Advanced Settings.


  1. Scroll down and turn on Components. Components are currently in preview and must be enabled prior to use in your applications.


  1. Select the App Name + icon tab and name the application Carousel Component.

  1. Select the Save tab and click Save.
  2. Click on the back button.

  1. You should be back to the app designer. Do not navigate away from this page.

 

 

Task 2: Setup the Carousel Component

In this task, you will create a component and set its properties. If you aren’t familiar with carousel type controls, they basically have a display area showing details and allow you to navigate through a collection of items. The display area for our carousel we are going to use the HTML text area for the visual display.

  • In the Tree view select the Components tab and click New Component. Note: if you don’t see Components here check your Advanced Settings to ensure the feature is enabled.

  • Rename the component Carousel.


  • Next you are going to add some custom properties. Properties define how your component will communicate with the hosting app. You can define input properties that allow the hosting app screen to give the component data and output properties that allow the component to give the hosting app screen data. Think of properties as defining a contract for how your component and app will communicate. Because your component might be used by multiple apps you should try to not make breaking changes to your input and output properties that would impact apps using your component.

     

    The following are the properties you will add:

Property Name

Description

Data

This is a table of data the component will visualize

Template

This is the HTML template – this will define the format for the HTML text control

RefreshData

This is a Boolean (True/False) used for the app to communicate to the component to refresh data

Selected

This is an output property that allows the hosting app to know which record is currently selected by the component

 

  • Go to the Properties pane and click New Custom Property.

  • Enter the following information and click Create:
    • Display name:    Data
    • Name: Data
    • Description: Description of your choice
    • Property type: Input
    • Data type: Table


  • Click New Custom Property.
  • Provide the following information and click Create:
    • Display name:    Template
    • Name: Template
    • Description: Description of your choice
    • Property type: Input
    • Data type: Text

  • Add another custom property with the following information and click Create.
    • Display name:    Refresh Data
    • Name: RefreshData
    • Description: Description of your choice
    • Property type: Input
    • Data type: Boolean

  • Add one more custom property with the following values and click Create. Make sure this property is an output property type.
    • Display name:    Selected
    • Name: Selected
    • Description: Description of your choice
    • Property type: Output
    • Data type: Record

  • Your component should now have 4 custom properties.


  • Select the Data property of the Carousel and replace the value with formula below. This sets a default value for the Data property and will allow you to build the component using these column names.

Table({ Record: “Contact”, Values:Table({Col:1,Value:”Full Name”}, {Col:2, Value:”Address 1: Street 1″}) })

  • Click File and Save.
  • Go back to the app designer by clicking on the back button. Do not navigate away from the app designer.

Exercise 2 – Build and Consume Component

In this exercise, you will build and use the component.

 

Task 1: Add Controls to Component

In this task, you will add controls to the component.

  1. Make sure you still have the Carousel component selected.


  1. Select the Insert tab, click Text and select HTML text.


  1. Rename the HTML Text control htmlText.

In the next few steps you will ensure that the controls are properly positioned, are automatically resized to take up the available parent’s space and have some margins for readability.

  1. Select the htmlText control, go to the Home tab, select the Height property and set it to the formula below.

Parent.Height -100

  1. Select the Width property and set it to the formula below.

Parent.Width -100

  1. Select the X property and set it to 50.
  2. The htmlText control layout should now look like the image below.

  1. Select the Insert tab, click Icons and select Left. We are adding left and right icons to allow the user of the app to manually change the item displayed.


  1. Rename the left icon previousIcon.
  2. Select the previousIcon, select the Height property and set it to the formula below.

htmlText.Height

  1. Select the Width property and set it to 50.
  2. Select the X property and set it to 0.
  3. Select the Y property and set it to the formula below.

htmlText.Y

  1. Select the OnSelect property and set it to the value shown below. This will cause the prior record to be shown if there is one.

If(CurrentIndex > 1,Set(CurrentIndex,CurrentIndex-1))


  1. Select the Insert tab, click Icons and select Right.


  1. Rename the right icon nextIcon.
  2. Select the nextIcon, select the Height property to formula below.

htmlText.Height

  1. Select the Width property and set it to 50.
  2. Select the X property and set it to the formula below.

htmlText.Width + 50

  1. Select the Y property and set it to the formula below.

htmlText.Y

  1. Select the OnSelect property and set it to the formula below. This will advance to the next record as long as we don’t exceed the number of records in the Data property.

If(CurrentIndex < CountRows(Parent.Data),Set(CurrentIndex,CurrentIndex+1))

  1. Your component should now look like the image below.

  1. Select the Insert tab, click Controls, and select Toggle.
    You are adding this control to simulate behavior properties, we need a way to run a formula when the data changes. The data property doesn’t have an OnChange property so we are asking the hosting app to change the RefreshData property when there is new data. We will then hookup the RefreshData to the toggle. And finally, our formula will run OnChange of the toggle. You will hide it later because the user never needs to see it, we are just using it to allow running our formula on-demand.

  1. Move the button to the bottom of the control.
  2. Set the OnChange property of the toggle button to the formula below. When the hosting app sets the RefeshData property to indicate new data has been set this sets the index variable back to 1

Set(CurrentIndex,1)

  1. Set the Default property of the toggle button to the formula below. This hooks up the RefreshData property to the hidden toggle control. When the hosting app changes this property value it will cause the OnChange formula above to execute. We are using the toggle control simply to allow the hosting app to run a formula on demand because components don’t have a way to directly have a hosting app run a component formula

Parent.RefreshData

  1. Click Controls again and select Slider. You are adding this control to simulate behavior properties. You will hide it later.

  1. Move the slider control to bottom.
  2. Set the slider Default property to the current index. This sets the value of the slider, when the index changes the slider OnChange behavior will run causing the formula to update the HTML Text control with a new record.

CurrentIndex

  1. Set the slider Minimum property to 1.
  2. Set the slider Maximum property to the formula below.

CountRows(Parent.Data)

  1. Set the slider OnChange property to the formula below. This runs when the CurrentIndex variable is changed.

If(CountRows(Parent.Data) > 0,ClearCollect(

TemplateData,

{

Name: “HtmlTemplate”,

Template: Parent.Template

}

);Set(CurrentRecord,Last(FirstN(Parent.Data, CurrentIndex)));

ForAll(

CurrentRecord.Values,

Patch(

TemplateData,

LookUp(

TemplateData,

Name = “HtmlTemplate”

),

{Template: Substitute(LookUp(TemplateData,Name = “HtmlTemplate”).Template,”{“& Col &”}”,Value)}

)

);Set(OutputHtml,LookUp(TemplateData,Name = “HtmlTemplate”).Template))

The above formula does the following (Each bullet below represents one of the forumlas above):

  • Creates a copy of the HTML template so we can customize it for this record.
  • Sets CurrentRecord varaiable based on the CurrentIndex. CurrentIndex represents the record number in the set we want to display. You can’t explictly get a record in a Table, so we are using Last and FirstN to help get the specific record. For example if CurrentIndex is 7 and we had 10 records, FirstN of 7 would return us the first 7 records. Last on that would give us just the 7th record. Using this technique you can access a specific record in a set by an index value.
  • Loops through all the values that we want to replace in the template, and for each calls Subsitute to replace the value in the template.
  • Sets the OutputHtml property with the results and later we will use this to populate the Html Text control.

  1. Select the htmlText control.
  2. Set the HtmlText property of the htmlText control to the OutputHtml.

OutputHtml

  1. Your component will now look like the image below.

  1. Click File and save your changes.
  2. Go back to the app designer by clicking on the back button.

 

Task 2: Consume the Component

In this task, you will consume the component in the hosting app.

  1. Select the Screens tab.


  1. Select the MainScreen, go to the Insert tab, click Components, and select the Carousel component you created.

  1. Set the Template property to the formula below. This is the HTML that will be used to format each record. {1} and {2} are replaceable with data from the record being visualized. This is a very simple template, it could include more styling and layout as needed.

“<H1>{1}</H1><H2>{2}</H2>”

  1. Set the Data property to selected contacts.

SelectedContacts

  1. Select accountsList and set the OnSelect property to the formula below.

Set(RefreshContacts, false );Clear(SelectedContacts);ForAll(accountsList.Selected.Contacts,Collect(SelectedContacts,{Record:contactid,Values:Table({Col:1,Value:fullname},{Col:2,Value:jobtitle})}));Set(RefreshContacts, true);

 

This is preparing the data for the component formatting the contact data into the format expected.

  1. Place the carousel component on the screen like the image below.

  1. Click Play.

  1. Select an account with more than one contact. Alpine Ski House has two related contacts. The Carousel should load the first contact.

  1. Click on the next button.

  1. The second contact should load.
  2. Close the preview.

 

 

 

Task 3: Add Auto Play

In this task, you will add auto play and hide the toggle and slider controls.

  1. Select the Components tab and select the Carousel component.


  1. Go to the Insert tab, click Controls, and select Timer.

  1. Change the timer Duration to 5 seconds. The unit of measure is milliseconds, so to set to 5 seconds, the value you set is 5000.

  1. Set the timer OnTimerEnd property to the formula below. We are using the timer control to automate rotating the current record being shown by the carousel. Every 5 seconds (based on how you configure the timer) the formula you configure for OnTimerEnd will run. We are using that to increment the index if it is less than the # of records in the data collection and if it reaches the end we are setting it back to the beginning.

If (CurrentIndex < CountRows(Parent.Data),Set(CurrentIndex,CurrentIndex+1),Set(CurrentIndex,1))

  1. Set the Repeat property of the timer to true.

  1. Set the Text property of the timer to “Auto Rotate”.

  1. Set the X property of the timer to the formula below. Next, we are going to adjust the timer play button so if the hosting app changes the size of our component the size of the button will adjust.

(Parent.Width / 2) – (Timer1.Width/2)

  1. Set the Y property of the timer to the formula below.

Parent.Height – (Timer1.Height + 10)

  1. Set the Visible property of the toggle and slider controls to false. Since we are using both these controls simply to call our formulas, we can hide them from the user.
  2. Your component should now look like the image below.

  1. Click File and save your changes.
  2. Click on the back arrow to go back to the app designer.
  3. Select the Screens tab.
  4. Resize Carousel_1 as shown in the image below.

  1. Click Play.
  2. Select Alpine Ski House again.
  3. Click Auto Rotate.

  1. The contacts should rotate based on the 5 seconds duration you selected.
  2. Close the preview.

 

Task 4: Add Selected Record

In this task, you will set the selected record of the component to current record and display information from the component on the consuming screen. This is useful because otherwise the hosting app doesn’t know what record is selected. This also shows how a component can make data available to the hosting app using output properties.

  1. Select the Components tab and select the Carousel component.
  2. Go to the Properties pane and select the Advanced tab.

  1. Scroll down and click More Options.

  1. Replace the text in Selected (Output) with CurrentRecord.

CurrentRecord

  1. Click File and save your changes.
  2. Go back to the app designer.
  3. Select the Screens tab, select the MainScreen, go to the Insert tab, and click Label.

  1. Set the Text property of the label to formula below.

First(Carousel_1.Selected.Values).Value

  1. Resize and reposition the label as shown in the image below.

  1. Click Play.
  2. Select Alpine Ski House. The label should display the selected contact.
  3. Click Auto Rotate.
  4. The label should rotate with the selected item in the component.