cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

Re: Workaround for ClearCollect in ForAll

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
Highlighted
Community Support
Community Support

Re: Workaround for ClearCollect in ForAll

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

Highlighted
Frequent Visitor

Re: Workaround for ClearCollect in ForAll

That might work out, thanks for the tip! 

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,675)