Workaround for ClearCollect in ForAll

[ResatCanerBas]

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

[v-siky-msft · Accepted solution]

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 

[ResatCanerBas]

That might work out, thanks for the tip!