cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BPNJMC1
Helper I
Helper I

Append specific records from a gallery to a collection based on what is already in the collection

Hi everyone,

 

I'm creating an application to take classroom attendance.  

 

  • galAttendanceTaken correctly lists all the students that have already had attendance taken for the classroom and selected date (the source is a SQL Server)
  • galEnrolled correctly lists all students enrolled in the selected class on the selected date (the source is a SQL Server)
  • colAttendanceRoster needs to contain all students in galAttendanceTaken and students from galEnrolled that are not in galAttendanceTaken.  
  • galAttendanceRoster  will use colAttendanceRoster as it's source and attendance will be patched to the DailyAttendance SQL table when a save button is clicked.

I have the code below in the OnSelect property of the dteAttendDate date picker.

 

How do I alter it so that the second ForAll will append records to the colAttendanceRoster where the PersonKey does not already exist in the collection? (or that don't exist in galAttendanceTaken, if that is easier)

 

Thanks,

Jill

 

 

ForAll(
    galAttendanceTaken.AllItems, Collect(
        colAttendanceRoster, {
              ProgramSubTypeKey:lblProgramSubTypeKey_at.Text
            , ClassroomKey:lblClassroomKey_at.Text
            , PersonKey:lblPersonKey_at.Text
            , AttendDate:dteAttendDate.SelectedDate
            , AttendStatus:lblAttendStatus_at.Text
            }
        )
    );
ForAll(
    galEnrolled.AllItems, Collect(
        colAttendanceRoster, {
              ProgramSubTypeKey:lblProgramSubTypeKey_e.Text
            , ClassroomKey:lblClassroomKey_e.Text
            , PersonKey:lblPersonKey_e.Text
            , AttendDate: dteAttendDate.SelectedDate
            , AttendStatus:lblAttendStatus_e.Text  //always empty
            }
        )
    );

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@BPNJMC1 

Assuming your PersonKey is the indicator of that unique person, then your formula should be like this:

Collect(colAttendanceRoster,
    ForAll(galAttendanceTaken.AllItems,     
        {
            ProgramSubTypeKey:lblProgramSubTypeKey_at.Text, 
            ClassroomKey:lblClassroomKey_at.Text, 
            PersonKey:lblPersonKey_at.Text, 
            AttendDate:dteAttendDate.SelectedDate, 
            AttendStatus:lblAttendStatus_at.Text
        }
    )
);

Collect(colAttendanceRoster
    ForAll(
        Filter(galEnrolled.AllItems, !(lblPersonKey_e.Text in colAttendanceRoster.PersonKey)),
        {
            ProgramSubTypeKey:lblProgramSubTypeKey_e.Text, 
            ClassroomKey:lblClassroomKey_e.Text, 
            PersonKey:lblPersonKey_e.Text, 
            AttendDate: dteAttendDate.SelectedDate, 
            AttendStatus:lblAttendStatus_e.Text  //always empty
        }
    )
);

Note: ForAll returns a table, so using it like a For/Loop is not needed.

 

I hope this is helpful for you.

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

3 REPLIES 3
BCLS776
Super User
Super User

Consider this code:

 

ForAll(
    galAttendanceTaken.AllItems, Collect(
        colAttendanceRoster, {
              ProgramSubTypeKey:lblProgramSubTypeKey_at.Text
            , ClassroomKey:lblClassroomKey_at.Text
            , PersonKey:lblPersonKey_at.Text
            , AttendDate:dteAttendDate.SelectedDate
            , AttendStatus:lblAttendStatus_at.Text
            }
        )
    );
ForAll(
    galEnrolled.AllItems, 
        if(IsBlank(LookUp(colAttendanceRoster,PersonKey=ThisItem.PersonKey)),
            Collect(
                colAttendanceRoster, {
                    ProgramSubTypeKey:lblProgramSubTypeKey_e.Text
                    , ClassroomKey:lblClassroomKey_e.Text
                    , PersonKey:lblPersonKey_e.Text
                    , AttendDate: dteAttendDate.SelectedDate
                    , AttendStatus:lblAttendStatus_e.Text  //always empty
                    }
            ),
        )
    );

 

The second ForAll loop now contains a quick check for the PersonKey through a Lookup. If it does not find the PersonKey, it executes the Collect. If it does find it, the ForAll does nothing.

I think that's what you're trying to achieve, but let me know if I missed the mark.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
RandyHayes
Super User
Super User

@BPNJMC1 

Assuming your PersonKey is the indicator of that unique person, then your formula should be like this:

Collect(colAttendanceRoster,
    ForAll(galAttendanceTaken.AllItems,     
        {
            ProgramSubTypeKey:lblProgramSubTypeKey_at.Text, 
            ClassroomKey:lblClassroomKey_at.Text, 
            PersonKey:lblPersonKey_at.Text, 
            AttendDate:dteAttendDate.SelectedDate, 
            AttendStatus:lblAttendStatus_at.Text
        }
    )
);

Collect(colAttendanceRoster
    ForAll(
        Filter(galEnrolled.AllItems, !(lblPersonKey_e.Text in colAttendanceRoster.PersonKey)),
        {
            ProgramSubTypeKey:lblProgramSubTypeKey_e.Text, 
            ClassroomKey:lblClassroomKey_e.Text, 
            PersonKey:lblPersonKey_e.Text, 
            AttendDate: dteAttendDate.SelectedDate, 
            AttendStatus:lblAttendStatus_e.Text  //always empty
        }
    )
);

Note: ForAll returns a table, so using it like a For/Loop is not needed.

 

I hope this is helpful for you.

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

@RandyHayes , 

That worked - thank you so much!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,495)