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 

View solution in original post

That might work out, thanks for the tip! 

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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