In this article, I am going to perform CRUD operations on an Excel sheet stored on One Drive and I am also going to show how to use Collections and perform Validations.
First, we will learn how to create a database connection. Click on PowerApps as shown below.
Then, click on Connections.
I have created a connection with One Drive for Business. I will create a connection to an Excel document that I have stored in One Drive.
Once you click New, you can select the templates as shown below.
Select from the connections, as shown below.
I am selecting EmployeeInfo Excel as my data source. Given below is the structure of my Excel sheet.
One thing to remember is that we have to define the name for the table in this Excel sheet so that we can select it as a data table.
As soon as we create an app, three screens appear - Browse, Details, and Edit.
Note: The Edit screen performs both New and Edit operations based on the command.
Below is the Browse screen.
In the above screen, we can see all the data which is in the Excel sheet.
Below, you can see all three properties.
Body1, Subtitle1, Title1 are the labels. These labels are connected to the Excel columns. In the below screenshot, you can see the search formula for items on the screen.
Search formula - SortByColumns(Search(EmployeInfo, TextSearchBox1.Text, "Email","First_x0020_name","Last_x0020_name"), "Email", If(SortDescending1, Descending, Ascending))
It clearly states that it is going to search Email, First_x0020_name, Last_x0020_name.
In the below screen, we can see how sorting is done.
The OnSelect event of IconSortUpDown1 icon has the below formula.
- UpdateContext({SortDescending1: !SortDescending1})
IconRefresh1 button OnSelect event has formulae Refresh(EmployeInfo) – It will refresh the EmployeeInfo data table.
On IconNewItem1 icon, the OnSelect event has the formula NewForm(EditForm1);Navigate(EditScreen1, ScreenTransition.None). It will navigate to EditScreen1 and then will set the Editform1 as NewForm.
With the help of NextArrow1, we can navigate to the detail screen.
Below is the screenshot for Details screen.
Below, you can see when we select Detailform1, the Item property shows which item is selected.
In the below screen, we are going to see how we can delete an item.
How we can edit the item is shown below.
Finally, we have the Edit screen which is shown below.
How to use Collection
I am going to show how we can use Collection.
- Suppose I want to show a table with a display name column. So, I decided to concatenate, First name and Last name.
- I have created a new screen and I have added a Data table with EmployeeInfo_1 as DataSource.
- I have also added a button which will create an extra column in MyCollection (name of the collection).
On Add column button, I have added a function to add a display name column and then store data in Mycollection.
- ClearCollect(Mycollection,AddColumns(EmployeInfo_1, "Display name",
- Concatenate(First_x0020_name," ",Last_x0020_name)
- ))
We can also create a Collection on OnStart event of the first screen like below.
Now, on screen 7, I am displaying the data from Mycollection.
Validations
In this article, we are going to check validation based on email -- is it text string or not, is it text numeric or not, or if the text is blank.
We are going to see how we can prevent the form from submitting if validations are not valid. I have created an Edit screen based on the data from the Excel sheet.
Below is the function to check email.
- If(!IsMatch(DataCardValue1.Text,Match.Email),true,false)
On the Visible property of ErrorMessage label, I have applied the above function. If Email is not valid, then it is going to show the above error message.
Below is the validation for the first name.
- If(IsNumeric(DataCardValue2.Text) || IsBlank(DataCardValue2.Text),true,false)
Below is the validation for last name.
- If(IsNumeric(DataCardValue3.Text) || IsBlank(DataCardValue3.Text),true,false)
Below is the validation for Phone number.
- If(!IsNumeric(DataCardValue4.Text),true,false)
In order to stop the form from entering the invalid information, below is the formula that I have applied on DisplayMode property of Button.
- If(Or(IsNumeric(DataCardValue2.Text) || IsBlank(DataCardValue2.Text),!IsMatch(DataCardValue1.Text,Match.Email),IsNumeric(DataCardValue3.Text) || IsBlank(DataCardValue3.Text),!IsNumeric(DataCardValue4.Text)),Disabled,Edit)