Developing a Power Apps Review Editing Application Based on Sharepoint Linked Lists

In this work, we will create an application with a nested list and, using its example, consider the functionality and capabilities of Power Apps in the presence of relationships between tables with data. For the convenience of users, data from two lists will be simultaneously displayed on one screen.

As a task to be solved, an application for editing reviews left by ReviewSP clients for our AccountSP specialists will be implemented.

Two linked lists were created in SharePoint to act as raw data

AccountSP (main list):

Several columns have been added to this list with a description of the account.

ReviewSP (linked – auxiliary list):

The second list refers to the first list. The connection between the two lists is implemented through the AccountSP field.

To start creating our application, open https://make.powerapps.com and go to the “+ Create” section and select “Canvas Application from Scratch”.

After that we will see a blank screen Screen1 on which we can place our visual elements. So that the user understands which screen of the application he is on, add a title at the top of the application.

And further, to make the appearance look more harmonious, we will set the background color in the panel on the left, where we can set the key properties of the controls.

Now let’s add a data display from the first AccountSP list. To do this, add the “Vertical” collection to the first screen. This component is actually a “scrolling” list with displayed items from our data source.

We connect the SharePoint list as data, but before that we need to add this data source to Power Apps

As initial data, we have our two SharePoint lists – AccountSP and ReviewSP, which we connect in the “Data” section

We connect the AccoutSP list to our Gallery component as a data source. As we can see the items of the Sharepoint list are immediately displayed on the screen.

To simultaneously display values from two columns from our Sharepoint list, we write a formula for displaying the name (the lines are combined through the & icon). The ThisItem keyword is bound to the current item in the list. The Predictor and Title are the column names from the Sharepoint list that we want to list.

Here we see how the list is displayed on screen in Power Apps

Here we can see the original content of the list in Sharepoint

Let’s change the display layout of the list to remove the picture, since we will not use it in this list

In addition, we will change the background and font color of our list.

Now let’s add a linked list to our first list. To do this, add an empty collection and place it on the right side of the screen.

Let’s connect data from our second Sharepoint list – ReviewSP

As a result, after placing the components, we will get the following interface after placing the components

Let’s set up the relationship between the elements of the two lists, as well as set up sorting.

To show only the ReviewSP list items associated with the selected AccountSP list item, you must use the Filter function.

The Filter function takes our original ReviewSP list as its first parameter. As the second parameter, we set the condition for displaying the list items.

The AccoupSP.Id = GalleryAccounts.Selected.ID condition allows you to display only those items that are associated with the selected items in the selected list on the left.

To sort the resulting filtering list, we use the Sort function, which takes the return value of the Filter function as its first argument. The second and third arguments are the field by which the sorting is performed and the sorting order – ascending (Ascending) or descending (Descending).

Suppose that the list on the left in the future can have many elements and for the convenience of the user we can add a search function. To do this, add a search text field above the main list and set a hint inside this field “Search for accounts …” using the “Hint text” property

Next, let’s change the display of the AccountSP list using the Search function. The Search function takes as the first parameter the source list to search through. The second parameter is the value of the text field in which the user sets the search string. The last parameters are used to set the columns by which you want to search.

To increase usability, and the user always understood which item is currently selected from the main list, we will customize the highlighting of the selected item by changing the background color of the selected color.

To implement this functionality, set up the TemplateFill property and write the condition for selecting this item through the ThisItem.IsSelected construction. When you select a list item, its background will turn dark blue (DarkBlue)

At the output, we get the appearance of the application, shown in the figure. This application allows you to render list items. Let’s configure the ability to add new list items and edit the current

In order to enable editing of elements, create a new screen and, by analogy, set its background color as on the previous screen.

In order to provide the ability to modify Sharepoint list items in connected data sources, you must use a form component and add a “change form”

By analogy with the first screen, let’s insert a title at the top of the form. To do this, copy the title and set it to dynamically change depending on the selected item in the GalleryReview list.

We connect data from our ReviewSP list to the created form

After binding to the list, text fields will be automatically created on the form. Since text fields do not suit us everywhere, we replace some of the text fields with the components we need, taking into account their data type and the most suitable visualization

Alternatively, we can still change the DisplayMode field in order to block editing for individual fields of the AccountSP field. This can be accomplished by setting property DisplayMode = DisplayMode.View

Some of the properties of the fields cannot be changed right away, and to change their properties, you need to open the context menu and click “Unlock” in it, or the same can be done through the panel on the left. After selecting “Unblock” we can already change all available properties for visual controls

Let’s implement the ability to save the changed elements of our form in our data source – the Sharepoint list. To do this, you need to add a Save button to save data for the event of a click on the button (onSelect).

When you click on the “Save” button, we will call the SubmitForm function, where the name of our ReviewEditForm is used as a parameter

If the form is successfully submitted, we describe the OnSuccess event. For this event, we perform several functions separated by a double semicolon “;;”.

As actions:

we clear our ReviewEditForm with the NewForm function
we display the notification “Nice! Good job! ”😊 using the Notify function
switch to the ScreenMain screen using the Navigate function

The notification is displayed as a bar at the top. The color and icon of the notification is set using the second parameter of the Notify function.

As a result of successful conservation, we obtain the following result

Let’s test the work and add a line with data for our secondary list, which is associated with the Mr Big element. As we can see when adding data “..TEST ..” through the form, they appeared in the list on the screen and also appeared in the Sharepoint ReviewSP list with a link to AccountSP

To be able to get to the form for adding reviews in our ReviewSP list, add the “New” button, which opens the form we need, which we made above.

Let’s go to the “Action” tab and create a navigation event using the Navigate function

If the user does not want to add data to our form, then in this case we will provide a “Cancel” button.

For the cancel button, let’s configure the click events (OnSelect):

go to the main ScreenMain screen using the Navigate function
clearing the filled data on the form using NewForm

In addition to adding new comments for different accounts of the main AccountSP list, we will add the ability to edit individual comments in our auxiliary list.

To do this, we will initially add the names of the columns Title, Name, Rating, Text, Created above our list for the convenience of the user, and on each line we will add an icon for editing this particular record.

For the button to work, set up the action when clicking on the button (OnSelect) to edit the selected item of the ReviewSP auxiliary list

To set up a link with the element that was clicked, you need to set the Item property for the form. We want to bind this property to the selected item in the GalleryReview using the Selected property. Thus, when the form is opened, the selected element on which the edit icon was clicked will be displayed

Let’s check the work by running the application and make sure that our bundle is working. As we can see when editing the ReviewSP list item on the main form with Title: test4@gmail.com, we see this very entry on our edit screen for this comment

For greater user convenience, we will prescribe so that when creating new records for the date, the current time value Now () is taken, which, if necessary, the user can already change

In addition to the function of creating and editing records, the user may need a function to delete items. The Remove function is provided for this.

In the graphical interface, we will take the form of the icon for deletion in the form of a basket and place it next to the edit icon.

The Remove function will be called when the icon is clicked when the OnSelect event is called.

As the first parameter, we indicate the name of the list from which the deletion occurs, and as the second parameter, we indicate the link to the element that needs to be deleted.

At the current time of the application implementation, not all columns from the main AccountSP list are displayed. Add the remaining elements at the top of the remaining white space.

To prepare the area for placing these elements, add a white rectangle filling the space in the upper right part of the shape.

Next, add visualization elements in the form of an image (avatar) and Status properties from the original AccountSP list.

As a parameter, set the Image property equal to the selected item (Selected) from the main AccountSP list (the AccountPhoto column)

To display the status field, use the icon. For the icon, we use two of its display types in the form of a check mark (Check) and a cross (Cancel)

As a result, we get such an application that allows us to display our main list, taking into account the search field and for each item of the AccountSP list selected on the left, display the associated comments, as well as edit them, delete and add