Assistance needed: How best to approach the design for a weekly Open Role (open job) email notifier

[ericshufro]

Hello.

I am looking for some guidance on creating automation to send a customized email to 1 or more recipients with content specific to each recipient.  Ideally, each recipient would receive an email containing a list of open roles that they are listed as a preferred vendor.

The SharePoint list data or organized as such:

VendorNames - A single column list of all Vendor company names; one name per row using the Title column.
For example, "Company X", "Company Y", "Company Z".

OpenRoles - List consisting of columns for job description, fulfillment status (open/closed), and a multi-choice list of preferred hiring company names.  Most importantly, the multi-choice preferred hiring companies field is sourced from the VendorNames list as a lookup (non-linked).
For example:
"Android Developer 1", "Open", {"Company X", "Company Y", "Company Z"}
"Android Developer 2", "Open", {"Company Y", "Company Z"}
"Android Developer 3", "Open", {"Company Z"}

Finally, there is another list named VendorContactInformation which contains the company name (sourced from VendorNames above), the First/Last name (combined field) of each contact, and the contacts email address.

Psuudo code:
for each vendor in VendorNames
{
    select * from OpenRoles where OpenRoles.PreferredVendor contains vendor.Title
    if (results returned)
    {
        VariableEmailBody[vendor.Title] = createHtmlWithSpecificFields(FromOpenRoleQueryResults)
    }
}

for each vendor in VariableEmailBody
{
    destinationEmailAddressesList = GetAddressessFromVendorContactInformation(vendor)
    EmailContent = VariableEmailBody[vendor.Title]        
    email.send(destinationEmailAddressList, EmailContent)
}

Challenges

1. OpenRoles.PreferredVendor is a multiple-choice list and joining the Lists isnt simple.  Maybe loops needed?

2. Storing the email content and getting the list of email addresses in the proper format seems tricky.

Thoughts?

What is the overall approach to take? 
1. Loop through the vendor names and find open roles that include the vendor? - Can be inefficient if a vendor is not included in any open roles (extra iterations through the open role list?).
2. Loop through the open roles and build a list for each vendor as their names are encountered in the preferredvendor list?  Harder.
3. Other data handling query magic / ideas ?

I hope the description is clear. 

Thank you for your ideas in advance,

-Eric

[VictorIvanidze]

Just hire a programmer.

[ericshufro]

Nah, it would take the fun out of it.  This is a great flow starter project for learning.  I think it would be easier as an app using SQL, but not as cool.

[ericshufro]

Any suggested flow?  I have considered iterating the vendor list and finding their roles, but there is (n^2) in complexity and considering the choice field within each role, actually requires a 3rd loop and takes 10 minutes to complete the iteration.  I have no idea why it runs so slowly considering there are only 20 vendors and 30 roles defined.  

If i iterate the roles and the choice field and then build a role list for each vendor dynamically, the problem is more complex to solve, but theoretically would run more efficiently.  However, I don't know if Flow has the tools necessary to implement the algorithm in this fashion since there is no map / dictionary like data structure would would make appending roles to each vendors list tougher with arrays.