Using ForAll to substitute values in HTML template.

[Digital]

I'm have an email template in a collection. Placeholders are substituted with data from a collection.

When I execute this code the email substitution works but it sends two identical emails.

I want to send two separate emails that correspond to the two rows of data in the colQuestions collection.

I thought using RemoveIf(colEmailData) would clear the collection so the next row of data would be added to the email template. 

Clear(colEmailData);
ForAll(
    Filter(colQuestions,!IsBlank('ActionRequiredComment')) As Response,
    Collect(
        colEmailData,
        {Field: "{InspectionName}", Data: Response.Title},
        {Field: "{AssetName}", Data: glbSelectedAsset.Title},
        {Field: "{AssetNumber}", Data: Text(Response.'Asset Number')},
        {Field: "{InspectionDate}", Data: Response.'Inspection Date'}, 
        {Field: "{InspectedBy}", Data: User().FullName},
        {Field: "{ActionComment}", Data: Response.ActionRequiredComment},
        {Field: "{InspectionRating}", Data: Response.Response},
        {Field: "{InspectionComments}", Data: Response.Comment}
    );

    // Substitute data into email template
    ForAll(
        colEmailData, 
        Patch(
            colEmailTemplate, 
            LookUp(colEmailTemplate, Template="Email"), 
            {
                Value: Substitute(LookUp(colEmailTemplate, Template="Email").Value, Field, Data)
            }
        )
    );
    
    Office365Outlook.SendEmailV2("j.doe@contoso.com","Subject",LookUp(colEmailTemplate, Template="Email").Value);
    RemoveIf(colEmailData,true);
);

[Anonymous]

@Digital 

Whilst I'm not able to reproduce your exact situation I was able to build something that may help using Last()

ForAll(
    Filter( myDataSource, "can" in Descriptor) As Data,
    Collect(
        colEmail,
            {
                Desc: Data.Descriptor,
                Item: Data.Item
            }
    );

    Office365Outlook.SendEmailV2( "myEmailAddress", "Subject", Last(colEmail).Desc & "   " & Last(colEmail).Item)
)

This is a small data source and returned 4 rows of data with "can" in the Descriptor. I then received 4 emails with a single row of data in each one, which correlated to the 4 rows in colEmail.

Hopefully this can help?

[Digital · Accepted solution]

@Anonymous 

Thanks for your suggestion.

I was able to solve the problem. I was trying to substitute values that had changed which was why subsequent iterations of ForAll didn't work.

In the unlikely event someone else stumbles across a similar problem here is my solution:

//Clear(colEmailData);

With(
{
htmlTemplate: First(colEmailTemplate).Value
},
ForAll(
Filter(colQuestions,!IsBlank('ActionRequiredComment')) As Response,
Collect(
colEmailData,
{Field: "{InspectionName}", Data: Response.Title},
{Field: "{AssetName}", Data: glbSelectedAsset.Title},
{Field: "{AssetNumber}", Data: Text(Response.'Asset Number')},
{Field: "{InspectionDate}", Data: Response.'Inspection Date'},
{Field: "{InspectedBy}", Data: User().FullName},
{Field: "{ActionComment}", Data: Response.ActionRequiredComment},
{Field: "{InspectionRating}", Data: Response.Response},
{Field: "{InspectionComments}", Data: Response.Comment}
);
// Substitute data into email template
ForAll(
colEmailData,
Patch(
colEmailTemplate,
First(colEmailTemplate),
{
Value: Substitute(First(colEmailTemplate).Value,Field,Data)
}
)
);
Office365Outlook.SendEmailV2("someemail.com","Test",First(colEmailTemplate).Value);
RemoveIf(colEmailData,true);
Update(colEmailTemplate,First(colEmailTemplate),{Value:htmlTemplate})
)
);

[Anonymous]

@Digital 

Well done mate, good to see you got it sorted 🙂