I hope everyone is well.
I am developing a dashboard which I want to show me the assists according to selected parameters.
I am connecting my dashboard to two sharepoint lists, these lists have the following structure:
In the SessionMetaData list I have:
Title: unique field
ClassId: class identifier (team ID in Microsoft Teams)
LessonDate: class date
StartTime / EndTime: start time and end time of the class
ClassName: Class name (team in Microsoft Teams)
TeacherEmailAlias: email of the teacher who takes the attendance.
In the AttendanceRecord list:
Title: unique field
LessonId: Code of the sessionMetaData list (with whom I relate both lists)
LessonDate: date of attendance.
Attendance: I save the student's attendance (present, absent, late, excused)
StudentEmail: student's email.
Notes: a comment.
AttendanceTakenBy: name of the teacher taking attendance.
ClassId: class identifier (team ID in Microsoft Teams).
StartTime / EndTime: start time and end time of the class.
StudentName: name of the student.
ClassName: Name of the class (team in Microsoft Teams).
I require to display the information like this:
The problem is in how to generate the columns of the dates, since they are going to vary depending on the class.
Please have any idea how to display the information.
Thank you very much
Solved! Go to Solution.
Yes, so, since the items are grouped by the ClassId, the resultant _records of the group will have all of the records that match that ClassId. If ClassId and ClassName are connected (i.e. the classid and the classname are always the same) then you can just get the First record of that group and the classname from it.
AddColumns( GroupBy( Filter(SessionMetaData; TeacherEmailAlias = Office365Users.MyProfile().Mail && (LessonDate >= fromDate.SelectedDate && LessonDate <= toDate.SelectedDate)); "ClassId"; "_records" ) As _sessions; "_className"; First(_records).ClassName; "_classAttendance"; GroupBy( SortByColumns( Filter( AttendanceRecord; ClassId = _sessions.ClassId ); "StudentName") ; "StudentEmailId"; "_studentRecords" ) )
This will add a _className column to it that you can use for the Dropdown display.
Yes...busy and now trying to catch up on about 5 days of messages and responses I've missed!! 🙄
What have you tried so far?
You could try this:
1. Add a Dropdown that allows users to set/select your ClassID, eg OnChange of Dropdown = Set(vClassID, Dropdown1.Selected.Text), then
2. Use ForAll to build that table, this may work
ClearCollect( colTable, ForAll( ShowColumns(Filter(AttendanceRecord, LessonID = vClassID), "StudentName") As Class, AddColumns( Class, Class.LessonDate, LookUp(AttendanceRecord, StudentName = Class.StudentName, Attendence) ) ) )
I haven't tested this code but I think it's what you need? Note: 'Class.LessonDate' may throw an error but let me know if it does.
You can then use colTable in a Gallery or DataTable to display it. Check in View --> Collections first to make sure it builds what you need.
Thank you very much for replying.
I haven't really tried much as I don't have much experience.
This module is not to be consulted by students but by academic staff.
The idea is to select the teacher and then the class, and there list all the students that belong to that class.
In this case, I require that the dates do not show them as rows but as columns, so it will be variable because it will depend on the classes that the teacher has given
Thank you @Eelman
Did you try the code I gave you? What was the outcome?
If that code works and gets the correct class then it only a matter of adding one more filter for the teacher to the output.
Hi @Eelman ,
It was not very clear to me where to put the code.
I try to place it in the gallery but it gives me an error, I really am disoriented. From what I understand you are creating a collection but when I go to View-> Collections there is nothing.
In the dropdown list I put the code but I had to put .result because .value gives me an error, but it does not take the ID but the name.
Set (vClassID; listClass.Selected.Result)
You can simply add that code to a button. Or, if you remove
ClearCollect( colTable, and the trailing ")" you could add it to the Items property of a gallery.
Hello @Eelman ,
Yes, I did. Place the code in a button but when using your code I get an error.
It tells me that the AddColumn function has some invalid arguments.
With this code it does not generate an error and it lists the data
I hope you can help me.
I tried a few other iterations but could figure out how to get dynamic column names. The error is cause don't by PowerApps wanting a text literal ... eg "10/08/2020" ... rather than a dynamic column name.
I'll have a play around with a few other options and get back to you. This may take a few days though.
I understand what you are asking me, thank you very much for your collaboration, I will try to move forward and study more to learn.
Attentive to your comments.
So where are you on this issue? As I see it you will first need a Gallery with a nested Gallery for the display part. The formula for the columns and values is pretty easy, but wanted to see where you were on this before jumping in.
I am assuming that the student list (from AttendanceRecord) is dependent on the chosen class from the SessionMetaData list. You mention "LessonId: Code of the sessionMetaData list (with whom I relate both lists)" - what is "Code"? Is that the Title column of the SessionMetaData?
Join us for the next call on August 17, 2022 at 8am PDT.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.