Building an email householding email function in PowerApps

Question

Would appreciate guidance on how to proceed with a householding email function in PowerApps/Automate. (householding is a specific mailing industry term)

I have a PowerApps tool that collects scan codes in the field (literally, we are using a QR code scanner and scanning boxes like crazy). For example, "RITM1234567" is a scan code.

Each scan code is associated with a Customer. For example, "RITM1234567" belongs to, say, "Edward". And of course, "Edward" maps out to Edward's email address (for example "Edward@EdwardDesk.com")

Each scan code is also associated with a description, for example "banana."

In my list of scan codes, there are many scans (can be anywhere from 1-200 or more scan codes in one pass). And the ones that are going to Edward are anywhere from 0 to all of them. But I only want to send Edward a SINGLE EMAIL (householding), something like this:

Hey Edward, the following items are ready for you to eat:

RITM1234567 - banana
RITM7654321 - banana
RITM7162534 - grape

Bring a fork and come on over!

So, those associations I mentioned, those are part of a Sharepoint List, something like:

[RITM VALUE] [CUSTOMER NAME] [CUSTOMER EMAIL]       [ITEM DESCRIPTION]
RITM1234567  Edward          Edward@EdwardDesk.com  banana
RITM7654321  Edward          Edward@EdwardDesk.com  banana
RITM7162534  Edward          Edward@EdwardDesk.com  banana

And if there are scans for other people, then I want to look them up in the same SharePoint List and send them an email as well.

So if a collection of scans were 10 items: 5 for Edward, 3 for Pat, and 2 for Kit

Then it should send THREE emails, not ten.

How might you start approaching that problem, given that PowerApps seems to have some weird limitations (no arrays or other cardinal way to access data)? (see footnote)

I admit it's a little high-level, so even high-level suggestions may be useful. And I can generally follow pseudocode.

Thank you kindly,

Edward

1. Thought: is it possible to construct a Collection/Data Table by looking at a list of scan values and saying “if this value appears in column x of a reference list, then add a new item to the Collection consisting of these other things in the same record on that reference list”…?

-=-=-=-=-

Footnote: I had previously solved this in an Excel-VBA app by creating a new worksheet with all the individual entries, sorted by Customer name, and then de-duplicating starting from the bottom. Then I did a lot of concatenation in a big loop to create each email's subject/body info. But I'm not sure how I would do that here. The Excel VBA app has lasted a couple years, but I am tasked to replace it with something that is more broad across our environment (thus a Powerapps Canvas app). Would Data Tables be a structure that lends itself to this?

Answer 1

If ypu want do this from powerapps the first step would be create a collection with all scanned codes grouped by person, then you iterate over taht collection with a ForAll, each group is like a collection inside a collection.

The idea is an email for each person who has a group of codes, then is posible iterate over each recor like a collecion.

ForAll(
    my_colecction.persons,
    ForAll(
        ThisRecord.persons,
        Office365Outlook.SendEmail(
            person.email,
            "Subject",
            "body"
        )
    )
);

It's possible to do it directly form sharepoint with a flow.

Answer 2

Given this collection:

• Adapt for your use case

ClearCollect(colPersons,
    {id: 1, email: "test@test.com"},
    {id: 2, email: "test@test.com"},
    {id: 3, email: "test@test.com"},
    {id: 4, email: "test@test.com"},
    {id: 5, email: "test@test.com"},
    {id: 6, email: "test1@test.com"},
    {id: 7, email: "test1@test.com"},
    {id: 8, email: "test1@test.com"},
    {id: 9, email: "test2@test.com"},
    {id: 10, email: "test2@test.com"}
)

This will email only the distinct email addresses:

• Loosely translates to:
  • Group the collection by distinct email
  • ForEach email in the group (3), send an email to them with "Subject" as the subject and "Body" as the body.

ForAll(
    GroupBy(
        colPersons, 
        "email", 
        "other"
    ),
    Office365Outlook.SendEmailV2(
        ThisRecord.email, 
        "Subject", 
        "Body"
    )
)

Answer 3

Okay, so the data prep part (where I build the main Collection that feeds the email routine), I completed and the answer to that is located here:

PowerApps - One Collection feeds another, with List lookups

-=-=-=-=-

The emailing part I completed and this is my example of that (some content redacted):

// Current test email
Set(TestEmailAddress,"edward@fish.com");

// Appropriate subject for FIRST CONTACT email
Set(TestEmailSubject,"IMPORTANT - Your Equipment is Ready to Deliver");

ClearCollect(Mailings,{CustomerName: "Edward Martin III",CustomerEmail: "edward@fish.com",ManagerName: "Edward's Boss",ManagerEmail: "bossman@fish.com",ItemListHTML: "<li><strong>REQ1791779 - RITM2037855 - Microsoft Surface Dock 2</strong></li><li><strong>REQ1791779 - RITM2037856 - Mouse - Wireless</strong></li><li><strong>REQ1791779 - RITM2037854 - Microsoft Surface laptop 4 15in</strong></li>"}, {CustomerName: "Joe Customer",CustomerEmail: "joe@fish.com",ManagerName: "Bosso Joe",ManagerEmail: "bosso@fish.com",ItemListHTML: "<li><strong>REQ1791780 - RITM2037855 - Microsoft Electric Banana</strong></li><li><strong>REQ1791780 - RITM2037856 - Banana - Wireless</strong></li><li><strong>REQ1791780 - RITM2037854 - Microsoft Electric Banana Holder</strong></li>"});
//ForAll(Mailings,
//Office365Outlook.SendEmailV2(CustomerEmail,TestEmailSubject,
//  "<p>Dear " & CustomerName & ",</p>"
//  & "<p><em>Great news!</em> Your equipment has arrived and is ready for deployment from "
//  & DD_Location.SelectedText.Value & " (hours of operation: " & LocationHours & ").</p>"
//  & "<p>Items in your order include the following:</p>"
//  & "<ul>" & ItemListHTML & "</ul>"
//  & "<p>Thank you so much for your time. Let's complete this order.</p>"
//  & "<p>Sincerely,</p>"
//  & "<p>" & User().FullName &"</p>",
//  {Importance:"High",ishtml:"true",From: User().Email,ReplyTo: User().Email});
//);

I am calling this done and done.

Onward!