Gallery with date columns

[firektl]

Hello Community,

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

[Eelman]

@firektl 

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.

[firektl]

Hi @Eelman 

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.

Teacher

Class

Students

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 

[Eelman]

@firektl 

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.

[firektl]

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)

[Eelman]

@firektl 

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.

[firektl]

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.

Thank you

[Eelman]

@firektl 

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.

[firektl]

Hi @Eelman,

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.

[RandyHayes]

@firektl 

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?