PowerApps - One Collection feeds another, with List lookups

Question

There are a few steps I'm trying to hit, here.

STEP 1:

I have created a Collection (ScanDataCollection) with the following command:

ClearCollect(ScanDataCollection,Split(ScanData.Text,Char(10)));

Where ScanData is a multiline text control, containing data strings such as this:

REQ1805965.RITM2055090.01
REQ1805965.RITM2055091.01
REQ1805982.RITM2055144.01
REQ1805982.RITM2055145.01

This produces a Collection of:

RESULT
REQ1805965.RITM2055090.01
REQ1805965.RITM2055091.01
REQ1805982.RITM2055144.01
REQ1805982.RITM2055145.01

The unique lookup value in this list is the RITM string (for example: RITM2055091)

I want to build a Collection that looks like this:

CUSTOMERNAME    CUSTOMEREMAIL   MANAGERNAME MANAGEREMAIL    ITEMLIST
Edward          [email protected] Tony        [email protected]   <li><strong>REQ1805965 - RITM2055090 - Vulcan Banana</strong></li>
Edward          [email protected] Tony        [email protected]   <li><strong>REQ1805965 - RITM2055091 - Vulcan Grape</strong></li>
Joseph          [email protected]   Kate        [email protected]   <li><strong>REQ1805982 - RITM2055144 - Romulan Catfish</strong></li>
Joseph          [email protected]   Kate        [email protected]   <li><strong>REQ1805982 - RITM2055145 - Romulan Salmon</strong></li>

The values in the rows come from a List (called "Spiderfood" at the moment) in SharePoint (this is where RITM value is typically unique, and can be used as the lookup):

Title       REQUEST     RITM        TASK        OPENED_DATE ITEM_DESCRIPTION    VIP CUSTOMER_NAME   CUSTOMER_NT     MANAGER_NAME    MANAGER_NT  TASK_DESCRIPTION    CUSTOMER_LOCATION
8-5-2021    REQ1805965  RITM2055090 TASK123     7-27-2021   Vulcan Banana       false   Edward      [email protected]     Tony        [email protected]   a string        a string
8-5-2021    REQ1805965  RITM2055091 TASK123     7-27-2021   Vulcan Grape        false   Edward      [email protected]     Tony        [email protected]   a string        a string
8-5-2021    REQ1805982  RITM2055144 TASK123     7-27-2021   Romulan Catfish     false   Joseph      [email protected]       Kate        [email protected]   a string        a string
8-5-2021    REQ1805982  RITM2055145 TASK123     7-27-2021   Romulan Salmon      false   Joseph      [email protected]       Kate        [email protected]   a string        a string
...[among hundreds of other records in this List]

Then...

STEP 2:

Take the Collection I built above, and deduplicate, based on CUSTOMEREMAIL, but in the process of deduplicating, concatenate the items in the ITEMLIST column.

The result would be a Collection with only two rows, for example:

CUSTOMERNAME    CUSTOMEREMAIL   MANAGERNAME MANAGEREMAIL    ITEMLIST
Edward          [email protected] Tony        [email protected]   <li><strong>REQ1805965 - RITM2055090 - Vulcan Banana</strong></li><li><strong>REQ1805965 - RITM2055091 - Vulcan Grape</strong></li>
Joseph          [email protected]   Kate        [email protected]   <li><strong>REQ1805982 - RITM2055144 - Romulan Catfish</strong></li><li><strong>REQ1805982 - RITM2055145 - Romulan Salmon</strong></li>

I sure would appreciate guidance/suggestions on this, please!

Thank you kindly in advance!

Edward Martin III Edward Martin III · Accepted Answer · 2021-08-12T15:24:07

Okay, for STEP 1:

ClearCollect(ScanDataCollection,Split(ScanData.Text,Char(10)));
ClearCollect(MailingListExploded, AddColumns(ScanDataCollection,
  "CustomerName", LookUp('Spiderfood - RITMs', RITM = Mid(Result, 12, 11), Customer_Name),
  "CustomerEmail", "[email protected]", // this is what I use as a test so that I don't email customers.
  //"CustomerEmail", LookUp('Spiderfood - RITMs', RITM = Mid(Result, 12, 11), Customer_NT),
  "ManagerName", LookUp('Spiderfood - RITMs', RITM = Mid(Result, 12, 11), Manager_Name),
  "ManagerEmail", LookUp('Spiderfood - RITMs', RITM = Mid(Result, 12, 11), Manager_NT),
  "ItemListHTML", "<li><strong>" & Left(Result,10) & " - " & Mid(Result, 12, 11) & " - " & LookUp('Spiderfood - RITMs', RITM = Mid(Result, 12, 11), Item_Description) & "</li></strong>"));

It adds an additional column from ScanDataCollection called "Result", but I can live with that. (I take it out later)

I had to add that specific list ('Spiderfood - RITMs') as a resource to the PowerApp project, which took me a minute to remember. Derp.

It offers a delegation warning about the use of Lookup if the dataset is very large (well, it's gonna be around 15,000, give or take), but for now, I'll not worry about it.

Now, on to STEP 2:

What would have helped me quicker on this would be to better understand the GROUPBY function, and how it can have multiple arguments, and concatenating the strings was a bit of a headscratcher.

But it seems to work, so here it is:

// Trim away the Result column
   ClearCollect(MailingListExplodedTrimmed, DropColumns(MailingListExploded, "Result"));

// Group and concatenate - TransmissionGrid is what we need to send the emails
   ClearCollect(RecordsByCustEmail, GroupBy(MailingListExplodedTrimmed, "CustomerEmail", "CustomerName", "ManagerName", "ManagerEmail", "OrderData"));
   ClearCollect(TransmissionGridExtra, AddColumns(RecordsByCustEmail, "ConcatenatedOrderString", Concat(OrderData, ItemListHTML)));
   ClearCollect(TransmissionGrid, DropColumns(TransmissionGridExtra, "OrderData"));

   Notify("Process complete!");

I might be able to shave away some steps by nesting things, but in this instance I wanted to be super obvious in case I have to look at this in 96 hours.

Anyway, that's what did it for me. Onward!

PowerApps - One Collection feeds another, with List lookups

1 Answers