Example of use case of showing multiple distinct columns
A typical example of where duplicates occur is when we join data from multiple tables.
Let's take the example of a set of tables in a helpdesk type application. There are two tables in this app - an issue table, and an 'issue response' table. The issue tables stores the main details of a support ticket. Each interation with a customer is stored as a seperate record in the 'issue response' table.
The schema of these tables are shown beneath.
The requirement is to build a search screen where users can display unique issue records that match the the issue response description.
The starting point is to create a SQL Server view that joins the issue and 'issue response' tables.
CREATE VIEW vwSearchIssue
iss.Description AS [IssueDescription],
isr.Description AS [IssueResponseDesc],
isr.CreateDateTime AS [IssueResponseCreateDate]
Issue iss JOIN
ON iss.IssueID = isr.IssueID
From Power Apps, we can build a search feature by adding search text box, and a gallery control with the Items property set to the following formula:
Search(vwSearchIssue, txtSearchInput.Text, "IssueResponseDesc")
The problem here is that if multiple matches exist in the 'issue response' table, the result will include duplicate issue details.
How to remove duplicates from a gallery control
To illustrate the problem, here's the data that the view would return if the user were to search for the word 'user' in the response description. Notice the duplicates that are highlighted in red.
To display these distinct values in a gallery control, we would call the GroupBy function to group the result by IssueID. We would set the Items property of our gallery control to this same formula.
GroupBy(Search(vwSearchIssue, txtSearchInput.Text, "IssueResponseDesc"),
This effectively produces a result that looks like this.
Each record in this result includes an IssueID field and a child table called 'Data' that contains all the rows that
match the corresponding issue ID.
From the item template of the gallery, we can show the distinct 'Issue Description', and 'Target Close Datetime' fields by looking up the first row in the data child table. The syntax to return the IssueDescription would look like this: