Solved: Re: Gallery with date columns

firektl · ‎09-25-2020

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

RandyHayes · ‎01-25-2021

@firektl

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.

Example:

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!! 🙄

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Anonymous · ‎09-25-2020

@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 · ‎09-25-2020

Hi @Anonymous

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 @Anonymous

Anonymous · ‎09-26-2020

@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 · ‎09-26-2020

Hi @Anonymous ,

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)

Anonymous · ‎09-26-2020

@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 · ‎09-26-2020

Hello @Anonymous ,

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

Anonymous · ‎09-26-2020

@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 · ‎09-28-2020

Hi @Anonymous,

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 · ‎12-05-2020

@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?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!