Exercise - Use collections and manipulate tables

15 minutes

In this exercise, you'll practice using tables. You'll create a collection, filter the collection, perform a lookup to the collection, edit a record in the collection, add a record to a collection, and remove a record from the collection.

Create the collection of data

Sign-in to Power Apps.
From the Home screen, select + Create from the left-side navigation panel. Then select Blank app.

Finally, select Create under Blank canvas app.
Name your app and select Create.

Select the Insert tab and add a button, and set its OnSelect property to this formula:

ClearCollect(CityPopulations, 
{City:"London", Country:"United Kingdom", Population:8615000}, 
{City:"Berlin", Country:"Germany", Population:3562000}, 
{City:"Madrid", Country:"Spain", Population:3165000}, 
{City:"Rome", Country:"Italy", Population:2874000}, 
{City:"Paris", Country:"France", Population:2273000}, 
{City:"Hamburg", Country:"Germany", Population:1760000}, 
{City:"Barcelona", Country:"Spain", Population:1602000}, 
{City:"Munich", Country:"Germany", Population:1494000}, 
{City:"Milan", Country:"Italy", Population:1344000})

Press and hold Alt Key, and select the Button control. (This will create your collection and store all the information.) Update the Text property of the button to "Collect".
Insert a Vertical gallery control and choose CityPopulations as the Data source (alternatively, you can update the Items property of the gallery to CityPopulations).
With the gallery selected, in the Properties panel on the right side of the screen change the layout to Title, subtitle, and body. Then select Fields and change the Body dropdown option to read Population. Refer to the image below.
With the gallery still selected, update the OnSelect property to the following:
```
 Set(varRecord,ThisItem)
```

Filter your collection

Insert a Text label and change the Text property to "Population". Then move it to the right of the gallery.
Next, insert a Slider control and put it under the label we created. You can find the control quickly by entering "slider" in the search field.
Select the slider control and change the Min property to:
```
Min(CityPopulations, Population)
```
Next, change the Max property to:
```
Max(CityPopulations, Population)
```
Now select the gallery and change the Items property to:
```
Filter(CityPopulations, Population>= Slider1.Value)
```
Now we'll only be seeing records that are greater than the selected slider value. Put the app in preview mode and move the slider around to see this in action. With the slider all the way to the left, you'll see all of the cities. As you move the slider to the right the list narrows so that only London remains in the gallery.

Use the LookUp and Patch functions

Insert a Text label and change the Text property to "City" and place it under the slider.
Next, insert a Drop down control and move it under the City label.
In the Items property of the drop-down, type in the following:
```
CityPopulations.City
```

In the OnChange property of the dropdown, input the following:

Set(varRecord, LookUp(CityPopulations, City=Self.SelectedText.City))

Insert another Text label and update the Text property to "Country".
Insert a Text Input control and move it below the "Country" label.
For the Default property of the Text Input, put varRecord.Country.
Change the name of this Text input control to txtCountry.
Repeat the previous 4 steps to add text labels and text input controls for City and Population, updating the Default properties of the text inputs to varRecord.City and varRecord.Population, respectively. When you're complete, your screen should resemble the image below.
Now, add a button to the screen and put it under the Population text input. Change the Text property to say "Submit".
Change the OnSelect property of the button to the following:
```
Patch(CityPopulations, varRecord, {Country: txtCountry.Text, City: txtCity.Text, Population: Value(txtPopulation.Text)})
```
Note

We have to wrap txtPopulation.Text with Value() because the text input returns a string by default, and our collection expects a number.
Place the app in Preview mode and select an item from the gallery. Change one of the values, press the "Submit" button and you'll see that the item has changed in your gallery.
Put the app back into Edit mode.

Add and remove records

Select our "Submit" button and copy it. Paste it three times.
Change the Text property of the three new buttons to Clear, New, and Remove.
Position the buttons in two rows of two buttons aligned on your original Submit button ("New" under "Submit", "Clear" to the right of "Submit", and "Remove" below "Clear").
Ctrl-Click the Clear and Remove buttons. Change the DisplayMode property to:
```
If(IsBlank(varRecord), DisplayMode.Disabled, DisplayMode.Edit)
```
Now these buttons will only be clickable if we have a record selected.
Ctrl-click the Remove button to deselect it. Update the OnSelect property of the Clear button to the following:
```
Set(varRecord, Blank())
```
You can now clear the selected record using this button. Preview the app and select your Clear button.

Notice how both the Clear and Remove buttons appear disabled.
With the app back in edit mode, select the Remove button. Update the OnSelect property to:
```
Remove(CityPopulations, varRecord)
```

Finally, select the New button and change the OnSelect property of the button to:

Collect(CityPopulations, {Country: txtCountry.Text, City: txtCity.Text, Population: Value(txtPopulation.Text)})

Place your app in Preview mode and try adding a new record and removing an existing record.

You now know how to work more extensively with tables in Power Apps. You could add additional functionality to the different properties of your buttons for a better user experience, but hopefully this exercise has given you an idea of what is possible.

Exercise - Use collections and manipulate tables

Create the collection of data

Filter your collection

Use the LookUp and Patch functions

Add and remove records

Feedback