Director App

[Anonymous]

I'm trying to build an app for Directors to be able to track the training that their employees are doing.  I'm using data that is on a single sharepoint site, but I'm using two different lists.  One list is called "Management" the other is called "Departments"

Departments Columns:

• Department Name
• Department Code
• Supervisor
• Director

Management Columns:

• Title
• Participant
• Course Title
• Department Code
• etc...

The Departement Code column in "Management" gets its choices from the Department Code in the list "Departments"

I'm trying to make the screen hierarchy: Home->Department->Employee->Detail

• Home Screen Codes:

     OnStart

Set(CurrentUser, User())

Department Gallery Data Items

Filter(AddColumns('Departments', "DirectorEmail", 'Director'.Email), "DirectorEmail"=CurrentUser.Email)

• Department Screen Code:

Training Gallery Data Items

GroupBy(Filter('Management', "Department_x-0020_Code" = 'Departments'.Selected."Department_x0020_Code"), 'Participant')

• Employee Screen Code:

Employee Gallery Data Items

Sort(Filter('Management', "Participant" = "Department Screen".Selected.'Participant'), Completion_x0020_Date, Ascending)

• Detail Screen Code:

Employee.Selected

I'm getting Errors on the Department Screen and the Employee Screen.

On the Department Screen I want it to only show Items from the "Management" list that has the department that was selected, and then grouped by the employee.

On the Employee Screen I want it to show all the training down by the selected employee that is gathered by the "Management" list and then sorted by the the Completion Date

[v-monli-msft]

Hi @Anonymous,

First of all, to use the information of current user, you do not need to use a Set function. So your first formula could be simplified to:

Filter(Departments,Director.Email=User().Email)

For GroupBy function, the syntax should be:
GroupBy( Table, ColumnName1 [, ColumnName2, ... ], GroupColumnName )

Table/ColumnName1/GroupColumnName are all required. But yours only have the first two and you didn't give the grouped column a new column name. 

To achieve this, I would like to suggest that you create the collections with the list.

1. Add a button and set the OnSelect property to:

ClearCollect(Collect1,Management)

2. Set the > icon in the first gallery, Gallery1, to:

ClearCollect(Collect2,GroupBy(Filter(Collect1, Code.Id = Gallery1.Selected.Department_x0020_Code),"Title","NewColumn"))

Notice:

• The "Code" column is the lookup column in Management list and in your case the name should be "Department_x0020_Code". I'm using "Code" because if you have both lists as data sources in the same app, duplicated column names would be renamed automatically by PowerApps.
• I'm using "Title" as the based column and grouped other columns because that the "Participant" column seems to be a Person and Group column, and when I collect the Collect1, I directly collected all the columns as I do not know which columns you will use in the Gallery2/3. So in Collect1, you could see that "Participant" is already a grouped column, and that's why you cannot directly use it as the based column in GroupBy.

 

          But this can be changed while collect Collect1, you could change my formula according to what columns you need in this              collection. You could only collect the DisplayName of "Participant" using formula like:

ClearCollect(Collect1,{ParticipantDisplayName:Participant.DisplayName,CourseT:Course_x0020_Title,......})

For your reference about collection:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-clear-collect-clearcollect

3. Add the 2nd gallery and set the Items property to:

Collect2

About the last Gallery3, use the similar way that used in Gallery2.

1. Set the OnSelect property of the > icon in Gallery2 to something like:

ClearCollect(Collect3, Filter(Management, Participant.DisplayName = Gallery2.Selected.ParticipantDisplayName))

2. Set the Items property of Gallery3 to:

SortByColumn(Collect3, Completion_x0020_Date, Ascending)

Regards,

Mona

[Anonymous]

Hey @v-monli-msft,

I've removed the OnStart from the Home Screen. I changed the Items for the first gallery, Departments , to 

Filter('Departments', Director.Email=User().Email)

I'm currently receiving a delegation warning from that.

I also added the button to Home Screen, and changed the NextArrow for "Departments" to

ClearCollect(Collect2, GroupBy(Filter(Collect1, Department_x0020_Code.Id = Departments.Selected.Department_x0020_Code), "Title", "NewColumn")

That is giving me a Invalid Argument type Error. It says Collect2 is an invalid name.

On the second screen, Department Screen, I changed the Data Items for the second gallery, Training, to Collect2. Getting an Invalid Name Error. I'm also getting a Delegation warning on the NextArrow for that gallery. I have the OnSelect set to

ClearCollect(Collect3, Filter('Management', Participant.DisplayName = Training.Selected.ParticipantDisplayName)

On my third screen, Employee Screen, I have the gallery data items

SortByColumns(Collect3, Completion_x0020_Date, Ascending)

This one is giving me an Invalid Name Error as well.

If I wanted to make a detail screen coming from the Employee Screen I would set the code to this right?

Navigate('Detail Screen', None,{item: LookUp('Management', ID=ThisItem.ID)})

Thank you for replying so quickly! Sorry I'm still trying to really figure out PowerApps also

Cheers,

John

[RusselThomas]

Just a comment on the User().Email function;

While the Filter function can resolve variables placed into a query 'in-line' and pass the resolved query to SPO, it doesn't resolve functions and pass the result of those functions in-line. 

It has to apply that function only once the data is returned, which means it will return the first 500 rows of data, then then apply the User().Email filter condition to whatever was returned - hence the delegation warning.

You find this true for everything except those functions and conditions specifically approved for SPO delegation,

The only conditions it generally passes for delegation are operators and simple variables that are resolved as part of the query sent to SPO.

In my opinion, the best way to address this is the way you had it initially - I often use a Set() function to put the result of User().Email into a global variable on app start, so I can then use that variable in my source filters and avoid delegation issues.   

More often, I'll go get the full user profile from the Office365Users connector and place what I need into a collection so I can create condition variables from that as and when I need them.

I also avoid person/group, or any other complex columns in SPO - I prefer to use a plain text column and put the users email in there when creating the data, so I can index it and easily filter in future.   My approach is to keep the data source as flat and uncomplicated as possible and use PowerApps to present things like choices or dropdowns to users, saving the flat data back to SPO.

Just my 10c 🙂

Kind regards,

RT

[Anonymous]

Thank you for the suggestion. Sadly I'm not in control of making the SharePoint, but I'll bring it up to those who do from here on out.  Thanks again for the suggestions, I'm still figuring out PowerApps

[RusselThomas]

Hi Jmehrman,

That is a pity - but you may want to check.  If your company has Office 365, then you may have the ability to create an Office group in Outlook or a team in Microsoft Teams or even a Plan in Microsoft Planner.

Creating a group in Office 365 (using Outlook) or a team in Microsoft Teams will also create a SharePoint site for that group to share documents and collaborate.  As the creator of this group you will be its owner and you can invite others.  This is the usual behaviour by design, unless your IT department has explicitly disabled it.

If you can create a group and you visit the site files page (through the link emailed to you when you create the group), you can navigate from there to the site home page - once there, click on "New" -> "List".

The List will start with a Title column - you can add columns and give them names (although try and keep them simple text, date or numbers - avoid calculated or complex columns if you can)

But yes, if for some reason you cannot do this, then it is very sad 😉

Kind regards,

RT