Automatically send created invoice to different groups of people

[jmm07g2789]

Good morning! My department creates monthly invoices that we send to other departments. They are created in excel and then saved to PDF. AS of right now, my staff manually sends these 50 invoices to the end users. I am trying to come up with a solution that automatically sends invoices to the right people. 

My initial thought was using a flow that would send an email when a file was uploaded to SharePoint, but i think the issue I'm running into is how to tell the flow who to send each invoice to. Each invoice has to go to a unique Principal Investigator and then 1-2 support staff. 

I was hoping to create some sort of database for the flow to query but I cant quite figure it out.

Any ideas would be greatly appreciated.  Thanks!

[eric-cheng]

Hi @jmm07g2789 ,

I would create a SharePoint list as a lookup table.  If you have Dataverse, you could look into that also.

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

Connect on LinkedIn

[ScottShearer · Accepted solution]

@jmm07g2789 

Another idea might be to create folders for each department.  The Flow could send emails based on the folder name.

If you have a standard naming convention for the files and the department can easily be parsed from the file name, your Flow  could send emails based on the file name.

In either of these cases, it would be best to store the departments and emails in a SharePoint list and then lookup the names in the list from your Flow.

[jmm07g2789]

I like where this is going. Would I create a separate SharePoint list to include the names of the recipients for each department? And then the flow would basically be "if this department, look at recipient list, and email those people"?

[ScottShearer]

@jmm07g2789 

Exactly

[jmm07g2789]

Hey Scott, me again. So I've tried to figure out how to parse out the name of the department but seem to be stuck. Here's what I have so far:

I assume the Filter query in the Get Items action is where I need to parse the beginning of the file name out. The file name is Music July 2021. I tried first(split(triggerOutputs()?['headers/x-ms-file-name-encoded'],' ')) but that resulted in an error. 

Can you point me in the right direction in terms of the expression that needs to go in that field? The column in the list of departments is the "Title" column and contains "Music".

Thanks!

[ScottShearer]

@jmm07g2789 

Try this:

substringof('Music',Title)

[jmm07g2789]

I'm getting a "The expression is invalid". I've tried similar expressions, and it doesnt seem to like using the column name. I always seem get that same error message.

Also the "Music portion is going to be dynamic (based on the file name which will always be in the "Department Month Year" format. I think hardcoding Music is good to test, but i will need to make that change eventually.

[jmm07g2789]

Update: I was able to get this to work. Now i just need to figure out how to replace 'Music' with a dynamic substring of all the characters preceding the first space. Thoughts?

[ScottShearer]

Try using a split() expression and splitting on a space.  

In my example, I assume that department is always a single word.  Split generates an array.  The [0] in my expression gives me the first element in the array.

[jmm07g2789]

So, probably a more efficient way to do this, but i was able to get it to work.

Thanks for all the help Scott. I appreciate it!