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

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

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.

RandyHayes
Super User III
Super User III

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

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,640)