Power Platform – Creating Canvas App from Scratch with Excel Data

by Snehal Sabadra · Published June 22, 2020 · Updated June 23, 2020

Hi Everyone

We will going to create an scratch app with excel table and then add data from other resources. In this article we will create app with two screens in first screen we will show all records and in second will do crud operations on records.

Now for this we first create excel data in table format, give name to file (File name – Records1 and Tablename – Table1 ) and save it on cloud account like OneDrive.

Open black app follow the below steps:-

1. Go to https://make.powerapps.com.

2. In Make your own app select Canvas app from blank.

Fig.1 Power Platform – Power Apps –Create canvas app from blank

3. Specify a app name and select format “Phone” and click on “Create”.

We also build app for other devices but in this topic focusing on designing app for phone.

Fig.2 Power Platform – Power Apps – Creating blank app for Phone

4. Welcome message dialog box opens , select skip.

Connecting to data source follow the below steps :-

1. In the left navigation bar select “Data sources” and then click on “Connectors” then select “OneDrive for business” (because we stored our excel file on OneDrive cloud ) and select “+ Add a connection” .

Fig.3 Power Platform – Power Apps – Connecting to Data sources

We will also click on middle of the screen for data connection.

2. Then OneDrive for Business dialog box opens click on connect.

3. Now choose an excel file (Records1) by search bar put your file name and search.

Fig.4 Power Platform – Power Apps – Choosing an excel file

4. After this Choose a table page opens then select your table name and connect.

Create the view records Screen follow the below steps : –

On the home tab you can see “New Screen” select the down arrow and select “List” from drop down list.

Fig.5 Power Platform – Power Apps – Creating list screen

2. Now screen is added with several default controls such as search and gallery control which covers all screen under search box.

Fig.6 Power Platform – Power Apps – List screen with records

3. At top of the screen select label control ([Title] )and give “Records” name as shown in fig.6.

4. In left navigation bar select BrowseGallery1.

5. Then on your right – hand pane of BrowseGallery1 Properties tab select Data source as a “Table1” (table name) then select “Layout” and from drop down list select “title,subtitle and body”.

6. Now Select “Fields” from Properties tab and click on “Edit” we will select any columns to any three boxes.

Step 5 and 6 shown in below figure.

Fig.7 Power Platform – Power Apps – BrowseGallery1 Properties Page

7. Now on the formula bar select down arrow select formatting text in this bar “CustomGallerySample” replace with “Table1”, and replace both instances “SampleText” with “Name” as shown in below fig.

Fig.8 Power Platform – Power Apps – Changes in formula bar

User can sort or filter the gallery by name.

Create the change records screen follow the below steps :

On the home tab you can see “New Screen” select the down arrow and select Form.
Select “EditForm1” from left navigation pane.
On “Properties” tab of right hand side panel select Data source and select “Table1” from list for connection.
Now select “Edit fields” and click on “+Add field” and check all columns from drop down list.

Fig.9 Power Platform – Power Apps – Add field Page

We will also drag and drop fields to change there sequence as per your choice.

5. Set the Item property and type “BrowseGallery1.Selected” on formula bar and change the label of form.

Fig.10 Power Platform – Power Apps – Changing Formula bar property

6. We will delete and rename form by clicking on ellipsis(…) rename of screen2 to Records_view and screen3 to Edit_Records .

Fig.11 Power Platform – Power Apps – Delete and Rename Options

Configuring Icons on Records_View page follow the below steps :

On Record view Screen select circular-arrow icon set the OnSelect property to formula “Refresh(Table1)”. When user selects this icon, the data from Table1 is refreshed from the excel file.
Now select Plus “+” icon and set OnSelect property to formula “NewForm(EditForm1);Navigate(Records_Edit,ScreenTransition.None)”. When user click on plus sign “Edit Form” opens and we will add new records in excel data.

Fig.12 Power Platform – Power Apps – Adding new records

Select right side arrow of records in the gallery and set the OnSelect property by this formula “EditForm(EditForm1); Navigate(Records_Edit, ScreenTransition.None)”.When user click on arrow “Edit Form” open in which user edit, update and delete records as shown in fig.14.

Fig.13 Power Platform – Power Apps – Applying function on OnSelect property

Fig.14 Power Platform – Power Apps – Records edit form

Configuring Icons on Records_Edit page follow the below steps :

Select cross sign of the page near left side to the page title and set OnSelect Property by this formula ResetForm(EditForm1);Navigate(Records_view, ScreenTransition.None). When user click on cross sign its redirected to “Records_view” page as shown in fig.6.
Now select on checkmark sign right to title and set OnSelect property by this function “SubmitForm(EditForm1);Navigate(Records_view,ScreenTransition.None)”. When user click on sign the changes made by user is saved and redirected to records_view page.
Now for adding new icon on edit page select insert tab and click on Icons and select trash icon.
We will set the icon’s color property to white and you can also set many more property of icon.

Fig.15 Power Platform – Power Apps – Adding Icons

5. Now click on “Advance” tab next to icons properties and click on “More options” scroll down and set visible property to “EditForm1.Mode = FormMode.Edit” means form is opens when it is in edit mode not in new mode.

6. Now set the OnSelect property of trash icon to Remove(Table1, BrowseGallery1.Selected); Navigate(Records_view, ScreenTransition.None) this will remove record from data source and redirect to records page.

Step 5 and 6 show in fig.16 as below.