cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ResatCanerBas
Frequent Visitor

Workaround for ClearCollect in ForAll

Hello there, I've developed an availability planner on Powerapps and it is built on Sharepoint Lists due to license restrictions, unfortunately.

Here is some information

  • I got 150 + users to be edited with Unique ID numbers
  • I got a massive SPList (Availability_DB) which contains daily availability of the user (5 date columns and 2 flags)

 

The code I ran for a single user works perfectly. I collect the 365 rows by onVisible and patch requested rows with the ID numbers by filtering the collection.

 

The problem is, I was requested to have a batch process for multiple users. I can't collect the availability IDs with onVisible because 1) I don't know which user(s) will be selected and 2) it can't be all 365*all, is massive, and not possible due to many aspects.

 

Here is my code:

 

 

 

 

//__Selected_Supporters have the ID number and isChoosen column (coming from checkbox list)
ForAll(__Selected_Supporters,If(isChoosen,
//Selection Check,
If(
    CC_Day_Range_1.Selected.value = "Single Day",

    //__Selected_Supporter_Availability is the collection mentioned. Since This is empty, it can't generate the code. It's collection is normally done by ClearCollect(__Selected_Supporter_Availability,Filter('2020_Availability_DB',Supporter_ID=Value(_Supporter_Selected)));

    Collect(
        __Selected_Days,
        Filter(
            __Selected_Supporter_Availability, 
            day_of_year = CC_Date_Pick_3.SelectedDate
        ).ID
    );
    
//single - only available v2 done
If(
        !CC_Off_Days_Checkbox_1.Value && !CC_Unavailable_Checkbox_1.Value,
        ForAll(
            RenameColumns(
                __Selected_Days,
                "ID",
                "SID"
            ),
            Patch(
                '2020_Availability_DB',
                LookUp(
                    '2020_Availability_DB',
                    ID = SID
                ),
                {
                    All_Day_Available: true,
                    Unavailable: false,
                    Available_From1: CC_Date_Pick_3.SelectedDate + Time(
                        Value(CC_Hour_6.Text),
                        Value(CC_Minute_6.Text),
                        0
                    ),
                    Available_To1: CC_Date_Pick_3.SelectedDate + Time(
                        Value(CC_Hour_5.Text),
                        Value(CC_Minute_5.Text),
                        0
                    ),
                    Available_From2: Blank(),
                    Available_To2: Blank()
                }
            )
        );)))

 

 

 

 

I need to call 

ClearCollect(__Selected_Supporter_Availability,Filter('2020_Availability_DB',Supporter_ID=Value(_Supporter_Selected)));
for each ID that is chosen (isChoosen).
 
If I'd go for only Collect, then with each loop, it will try to apply patch incrementally.
 
Thanks in advance,
Caner
1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @ResatCanerBas ,

 

Clear() and ClearCollect() function can't be invoked in ForAll function. 

However, you can use RemoveIf() function to clear the collection data in each loop.

//__Selected_Supporters have the ID number and isChoosen column (coming from checkbox list)
ForAll(__Selected_Supporters,If(isChoosen,
//Selection Check,
If(
    CC_Day_Range_1.Selected.value = "Single Day",
RemoveIf(__Selected_Supporter_Availability, true);    ClearCollect(__Selected_Supporter_Availability,Filter('2020_Availability_DB',Supporter_ID=Value(_Supporter_Selected)));

    Collect(
        __Selected_Days,
        Filter(
            __Selected_Supporter_Availability, 
            day_of_year = CC_Date_Pick_3.SelectedDate
        ).ID
    );
    
//single - only available v2 done
If(
        !CC_Off_Days_Checkbox_1.Value && !CC_Unavailable_Checkbox_1.Value,
        ForAll(
            RenameColumns(
                __Selected_Days,
                "ID",
                "SID"
            ),
            Patch(
                '2020_Availability_DB',
                LookUp(
                    '2020_Availability_DB',
                    ID = SID
                ),
                {
                    All_Day_Available: true,
                    Unavailable: false,
                    Available_From1: CC_Date_Pick_3.SelectedDate + Time(
                        Value(CC_Hour_6.Text),
                        Value(CC_Minute_6.Text),
                        0
                    ),
                    Available_To1: CC_Date_Pick_3.SelectedDate + Time(
                        Value(CC_Hour_5.Text),
                        Value(CC_Minute_5.Text),
                        0
                    ),
                    Available_From2: Blank(),
                    Available_To2: Blank()
                }
            )
        );)))

Hope this helps.

Sik 

View solution in original post

2 REPLIES 2
v-siky-msft
Community Support
Community Support

Hi @ResatCanerBas ,

 

Clear() and ClearCollect() function can't be invoked in ForAll function. 

However, you can use RemoveIf() function to clear the collection data in each loop.

//__Selected_Supporters have the ID number and isChoosen column (coming from checkbox list)
ForAll(__Selected_Supporters,If(isChoosen,
//Selection Check,
If(
    CC_Day_Range_1.Selected.value = "Single Day",
RemoveIf(__Selected_Supporter_Availability, true);    ClearCollect(__Selected_Supporter_Availability,Filter('2020_Availability_DB',Supporter_ID=Value(_Supporter_Selected)));

    Collect(
        __Selected_Days,
        Filter(
            __Selected_Supporter_Availability, 
            day_of_year = CC_Date_Pick_3.SelectedDate
        ).ID
    );
    
//single - only available v2 done
If(
        !CC_Off_Days_Checkbox_1.Value && !CC_Unavailable_Checkbox_1.Value,
        ForAll(
            RenameColumns(
                __Selected_Days,
                "ID",
                "SID"
            ),
            Patch(
                '2020_Availability_DB',
                LookUp(
                    '2020_Availability_DB',
                    ID = SID
                ),
                {
                    All_Day_Available: true,
                    Unavailable: false,
                    Available_From1: CC_Date_Pick_3.SelectedDate + Time(
                        Value(CC_Hour_6.Text),
                        Value(CC_Minute_6.Text),
                        0
                    ),
                    Available_To1: CC_Date_Pick_3.SelectedDate + Time(
                        Value(CC_Hour_5.Text),
                        Value(CC_Minute_5.Text),
                        0
                    ),
                    Available_From2: Blank(),
                    Available_To2: Blank()
                }
            )
        );)))

Hope this helps.

Sik 

That might work out, thanks for the tip! 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

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