cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
firektl
Helper III
Helper III

Gallery with date columns

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.

 

sessionMetaData.png

 

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).

 

attendanceRecord.png

 

I require to display the information like this:

 

ATTENDANCE.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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

43 REPLIES 43
Eelman
Community Champion
Community Champion

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

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

profesor.png

 

 

 

 

 

 

 

 

 

Class

clase.png

 

 

 

 

 

 

 

 

 

Students

alumnos.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
Community Champion
Community Champion

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

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
Community Champion
Community Champion

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

 

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.

 

error addcolumn.png

 

With this code it does not generate an error and it lists the data

 

correcto.png

I hope you can help me.

 

Thank you

 

Eelman
Community Champion
Community Champion

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

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
Super User
Super User

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

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,970)