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 edward@fish.com Tony tony@fish.com <li><strong>REQ1805965 - RITM2055090 - Vulcan Banana</strong></li>
Edward edward@fish.com Tony tony@fish.com <li><strong>REQ1805965 - RITM2055091 - Vulcan Grape</strong></li>
Joseph joey@fish.com Kate kate@fish.com <li><strong>REQ1805982 - RITM2055144 - Romulan Catfish</strong></li>
Joseph joey@fish.com Kate kate@fish.com <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 edward@fish.com Tony tony@fish.com a string a string
8-5-2021 REQ1805965 RITM2055091 TASK123 7-27-2021 Vulcan Grape false Edward edward@fish.com Tony tony@fish.com a string a string
8-5-2021 REQ1805982 RITM2055144 TASK123 7-27-2021 Romulan Catfish false Joseph joey@fish.com Kate kate@fish.com a string a string
8-5-2021 REQ1805982 RITM2055145 TASK123 7-27-2021 Romulan Salmon false Joseph joey@fish.com Kate kate@fish.com 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 edward@fish.com Tony tony@fish.com <li><strong>REQ1805965 - RITM2055090 - Vulcan Banana</strong></li><li><strong>REQ1805965 - RITM2055091 - Vulcan Grape</strong></li>
Joseph joey@fish.com Kate kate@fish.com <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!
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", "edward@fish.com", // 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!