ForAll Patch sometimes is not patching all items/records to SharePoint List

[tko0215]

Hi,

            So I have a odd intermittent issue. In short, I created a canvas app that field managers can submit store closures. Basically the field manager would submit through the canvas power app.  Then submission would then trigger an email (summary of the stores they submitted) while simultaneously patching the record(s) to a SharePoint list. The user can also submit a reopen request through the app which would update the existing record on the SharePoint list as well.

            This has been working well for the most part for the better half of the year. However, there were a few instances where records are not patching to the SharePoint list. The users did not receive any error messages for those instances.  I recently had a manager submit a store closure through my canvas app.

             However, only 4 out of the 8 records (stores) were patched to the SharePoint list. I did confirm that we received the summary of closures email with all 8 stores. I tested out the submission myself and was able to get all 8 submissions patched to the SharePoint list.  This has happened a few times in the past but it so infrequent and there isn't any pattern that I can think of. However, with that being said, since this is considered a critical app, I want to ensure fix this so we don't run into this issue again.  Any help is appreciated! 

Below is the on select code:

ForAll(
    Update_TBL,
    Patch(
        'Store Closure',
        Defaults('Store Closure'),
        {
            'Store Number':Store_Id,
            Location:Location,
            District:District,
            'District Manager':District_Mgr,
            RVP:RVP,
            'RVP Email':RVPEmail,
            ALPM:ALPM,
            'ALPM Email':ALPMEmail,
            Region:Region,
            'Closure Reason':Close_Reason,
            'Closure Date':Close_Date,
            'Additional Comments':Comments,
            Bopis:BOPIS,
            Curbside:Curbside,
            SDD:SDD,
            SFS:SFS,
            'Reopen Date':Reopen_Date,
            'Submitted Date':Today(),
            'Reopen Time':drop_ReopenTime,
            'Submitter Email':User().Email
        
        }
    )
);
    Navigate(Closure_Success_Screen);
    Office365Outlook.SendEmailV2("xxxxx@xxxx.com","Store Closure - " & Concat(Distinct(Update_TBL,Region),Result &"") & " - " & Today(),"See below for a list of closures. " & "<p>" & " @"& "<strong>" & User().FullName & "</strong>" & " - Once the store(s) has officially reopened, please go back into the Closure App and mark the store(s) as" & "<strong> Opened </strong>" &"<strong><em>" & "<span style='color:#ff0000'><span style='background:yellow'>" &"(even if you previously provided a reopen date and time)."&"</strong></em></span></span>" &"<p>" &"<strong>Note: This is an automated email sent through the Closure App.</strong>" & HtmlText1.HtmlText,{Importance:"Normal", Cc:RVP_EMAIL_Address.Text &";" & ALPM_EMAIL_Address.Text &";"& User().Email &";"& DM_EMAIL_ADDRESS.Text});
    Clear(Update_TBL);

[TheRobRush]

Super User@RandyHayes is the best at explaining what's wrong here, tagging him to ensure he see's your post.

[TheRobRush]

Sorry, just realized Randy must be on holiday, it has been some time now since he has logged in.

Gist of it is ForAll works a lot better inside the patch, as opposed to Parenting it. Unlike programming languages where ForAll loops through records, here it just kind of returns you a table with all the matching records in it.

Now if you do ForAll(Patch())) you end up patching over and over again and if somethign goes slowly/gets dropped you can end up losing data. whereas Patch(ForAll()) will pathc in all the matching records at one time, once.

Assuming Update_TBL is a table with all the info that is being patched onto your patch list it would look like

Patch(
    'Store Closure',
	ForAll(
    		Update_TBL,
	
        {
            'Store Number':ThisRecord.Store_Id,
            Location:ThisRecord.Location,
            District:ThisRecord.District,
            'District Manager':ThisRecord.District_Mgr,
            RVP:ThisRecord.RVP,
            'RVP Email':ThisRecord.RVPEmail,
            ALPM:ThisRecord.ALPM,
            'ALPM Email':ThisRecord.ALPMEmail,
            Region:ThisRecord.Region,
            'Closure Reason':ThisRecord.Close_Reason,
            'Closure Date':ThisRecord.Close_Date,
            'Additional Comments':ThisRecord.Comments,
            Bopis:ThisRecord.BOPIS,
            Curbside:ThisRecord.Curbside,
            SDD:ThisRecord.SDD,
            SFS:ThisRecord.SFS,
            'Reopen Date':ThisRecord.Reopen_Date,
            'Submitted Date':Today(),
            'Reopen Time':ThisRecord.drop_ReopenTime,
            'Submitter Email':User().Email
        
        }
    ))

[tko0215]

Thank you Rob and no worries! So it sounds like since I was parenting ForAll, it was causing occasional records to be lost/dropped through the patch process? That is interesting and makes sense. I'll have to test and let you know if it works! I definitely appreciate your expertise and guidence. 

So on the same topic, this Canvas app also allows the manager to view current closures (via a gallery grid) that were patched over to the SharePoint list. The user can update the store closure status from "Closed" to "Open" and submit the changes. This would patch and update the existing record in SharePoint list. However, similar to the issue above, there were at least several instances where the user submitted the update but it failed to patch to the existing SharePoint list. I would say 98% of the submission/updates succeeded but these issues are annoying.

Below is the onselect code. I'm thinking maybe it's the way I have written the code?

Patch(
        'Store Closure',
        colGridData

    );

ClearCollect(
    colGridData,
    Filter(
        colGridData,
        StartsWith(
            District,
            DistrictSearch_Input.Text
        ) && (IsBlank(openclose_drop.SelectedItems) || IsEmpty(openclose_drop.SelectedItems) || CurrentStatus.Value = "Opened")
    )
);
Office365Outlook.SendEmailV2(
    "xxxx@xxxxx.com",
    "Store Reopening(s) - " & Today(),
    Concatenate(
        User().FullName,
        " has updated the close store list."
    ) & " Please review the <a href='xxxxxxxx'> SharePoint list </a>" & "to view stores that has been recently reopened." & Re_Open_Email_HTML.HtmlText & RVP_Reopen_Emails.Text & ALPM_Reopen_Emails.Text ,{Importance: "Normal"}
);

Office365Outlook.SendEmailV2(
    "xxxxx@xxxx.com;" & User().Email,
    "Store Reopening(s)",
    "This is a confirmation email of your submission. The BC team has been notified. In the final email, the ALPM and RVP would be copied in this confirmation email." & Re_Open_Email_HTML.HtmlText,
    {Importance: "Normal"}
);
Navigate(Edit_Success_Reopen);
Select(btnLoadData);
Set(
    varGridEdit,
    !varGridEdit
);

Reset(DistrictSearch_Input);

[TheRobRush]

On that example is the collection of the griddata /supposed/ to be after the patch?

Is

colGridData

a collection of records to be updated, that match exactly ID wise the info in 'Store Closure'? and just need status updates?

If so it's actually quite simple

Patch(
    'Store Closure',
            ForAll(colGridData,
                    {   ID:ThisRecord.ID,
                        store closure status column name goes here:"Open"}))
                   

in the above jsut change the store closure status column name goes here, to the name of the status column in your source. This patches everything in your colgrid collection, using ID to match it up against its correct record in the source and updates just the one column to Open

[powernaps27]

Hi Rob -I'm having a similar issue as TKO but can't quite seem to figure out the Patch() & ForAll() syntax for my scenario.

Basically, I have a collection (ColSubResponses) that is a snapshot of the SharePoint list (EFBSubcoordinatorResponses) that I want to eventually patch to. Users input data and the collection gets updated accordingly, but I've been experiencing similar dropouts when attempting to patch to the collection as TKO described above. See my current code to patch collection that exists in the OnChange of each input box below:

//ForAll items in the gallery
ForAll(GalleryHeader.AllItems,

//If a record already exists in collection
    If(
    CountRows(
        Filter(
            ColSubResponses,
            Title = Title2.Text && 
            SharePointIDGroup_Entity = Value(Label_HC_HelperEntityID.Text) &&
            SharePointIDGroup_Child = Value(Label_HC_HelperChildID.Text) && 
            SharePointIDGroup_Grandchild = Value(Label_HC_HelperGrandchildID.Text) && 
            Year = Text(VarCurrentYear)
        )
    ) = 1,

//Patch that record
Patch(ColSubResponses,

LookUp(ColSubResponses, 

Title = Title2.Text && 
SharePointIDGroup_Entity = Value(Label_HC_HelperEntityID.Text) &&
SharePointIDGroup_Child = Value(Label_HC_HelperChildID.Text) && 
SharePointIDGroup_Grandchild = Value(Label_HC_HelperGrandchildID.Text) && 
Year = Text(VarCurrentYear))

,
        {
            Title: Title2.Text,
            'Entity ID': EntityID2.Text,
            Process: LBProcess_2.Text,
            'Question Type': LBQuestionType_2.Text,
            Year: 'Current Year_2'.Text,
            'Current Year Response': Value(SubGroupCurrentResponse2.Text),
            'Previous Year Response': Value(LBpreviousyear_2.Text),
            'Variance %': Variance_2.Text,
            Entity: LBEntityname2.Text,
            Group: Group2.Text,
            'Subcoordinator Name': User().FullName,
            Email: User().Email,
            Question: LBQuestion2.Text,
            'Group 2': SubGroup2.Text,
            SharePointIDGroup_Entity: Value(Label_HC_HelperEntityID.Text),
            SharePointIDGroup_Child: Value(Label_HC_HelperChildID.Text),
            SharePointIDGroup_Grandchild: Value(Label_HC_HelperGrandchildID.Text)
        }
    ),

//Else, create a new record
Collect(
        ColSubResponses,
        {
            Title: Title2.Text,
            'Entity ID': EntityID2.Text,
            Process: LBProcess_2.Text,
            'Question Type': LBQuestionType_2.Text,
            Year: 'Current Year_2'.Text,
            'Current Year Response': Value(SubGroupCurrentResponse2.Text),
            'Previous Year Response': Value(LBpreviousyear_2.Text),
            'Variance %': Variance_2.Text,
            Entity: LBEntityname2.Text,
            Group: Group2.Text,
            'Subcoordinator Name': User().FullName,
            Email: User().Email,
            Question: LBQuestion2.Text,
            'Group 2': SubGroup2.Text,
            SharePointIDGroup_Entity: Value(Label_HC_HelperEntityID.Text),
            SharePointIDGroup_Child: Value(Label_HC_HelperChildID.Text),
            SharePointIDGroup_Grandchild: Value(Label_HC_HelperGrandchildID.Text)
        }
    )
))

The final patch to the SharePoint list looks like this:

Patch(EFBSubcoordinatorsResponses, ColSubResponses);

I've been playing with the syntax to get me from ForAll+Patch to Patch+ForAll (see below), but keep getting "the datasource is invalid" error and I'm guessing it's to do with the schemas not perfectly matching between the gallery and collection. Any thoughts on an approach that could make this work?  Thanks for your help.

Patch(ColSubResponses,

LookUp(ColSubResponses,
        Title = Title2.Text && 
        SharePointIDGroup_Entity = Value(Label_HC_HelperEntityID.Text) &&
        SharePointIDGroup_Child = Value(Label_HC_HelperChildID.Text) && 
        SharePointIDGroup_Grandchild = Value(Label_HC_HelperGrandchildID.Text) && 
        Year = Text(VarCurrentYear),

ForAll(GalleryHeader.AllItems,

        {
            Title: Title2.Text,
            'Entity ID': EntityID2.Text,
            Process: LBProcess_2.Text,
            'Question Type': LBQuestionType_2.Text,
            Year: 'Current Year_2'.Text,
            'Current Year Response': Value(SubGroupCurrentResponse2.Text),
            'Previous Year Response': Value(LBpreviousyear_2.Text),
            'Variance %': Variance_2.Text,
            Entity: LBEntityname2.Text,
            Group: Group2.Text,
            'Subcoordinator Name': User().FullName,
            Email: User().Email,
            Question: LBQuestion2.Text,
            'Group 2': SubGroup2.Text,
            SharePointIDGroup_Entity: Value(Label_HC_HelperEntityID.Text),
            SharePointIDGroup_Child: Value(Label_HC_HelperChildID.Text),
            SharePointIDGroup_Grandchild: Value(Label_HC_HelperGrandchildID.Text)
        }
    )));