CODEWITHSUNDEEP
powerappspowerapps-formulapowerapps-canvas
Mike Tomaino
Mike Tomaino

Reputation: 170

Sorting a Filtered Table based on data in another table

I have two tables (SharePoint Lists). Employees contains a record for each employee and general information about them. Overtime Records contains a record for each overtime shift worked, including the employee that worked the shift and the date. Therefore Overtime Records has a many-to-one relationship with Employees. 'Overtime Records'.Employee.Id JOINS TO Employees.ID

I have successfully placed a text field in a custom gallery that counts the number of Overtime Shifts each employee has worked with the following code. TimesWorked is a Text Label object within the custom gallery.

TimesWorked.Text = CountRows(
   Filter(
       'Overtime Records',
       Employee.Id = ThisItem.ID
   )
)

My issue is in getting the gallery itself to sort by this value. Here is the Gallery.Items property I tried to code:

Gallery.Items = Sort(Employees,
    CountRows(
        Filter(
            'Overtime Records',
            Employee.Id = ID
        )
    )
)

This should sort by the CountRows() expression. ID (all caps) is a field in the Employee table. Employee.Id is a field in the Overtime Records table. I suspect there may be an ambiguity issue with which ID field I'm referencing since the Filter expression is nested within the Sort expression. It won't allow me to specify Employees.ID; I get a syntax error.

Upvotes: 1

Views: 2192

Answers (1)

Murilo Santana
Murilo Santana

Reputation: 665

Before you can sort the Employees table using the CountRows, you will need to make that information available by adding a column - and no, not adding a column to the SharePoint List :)

Power Apps gives you the ability to add column to tables using the the AddColumns Function

Step by step:

  1. RenameColumns Function: RenameColumns(Employees,"ID", "IdentifierEmployees")

  2. AddColumns Function: AddColumns(RenameColumns(Employees,"ID", "IdentifierEmployees"),"CountOfOvertime", CountRows(Filter('Overtime Records',EmployeeId = IdentifierEmployees)))

  3. SortByColumns Function: SortByColumns(AddColumns(RenameColumns(Employees,"ID", "IdentifierEmployees"),"CountOfOvertime", CountRows(Filter('Overtime Records',EmployeeId = IdentifierEmployees))),"CountOfOvertime",Descending)

Renaming the column in step one is the way I find to work for me since the Filter part won't work if I have "EmployeeId = Id"

Hope it helps

Upvotes: 1

Related Questions