I'm a relative newbie, and not a programmer at heart. So please bear with me 🙂
My client has an Excel file for each year and within that file, one Worksheet for each Cohort.
I have built a number of Flows where by when they add a new Student into one of the Cohort Worksheets in the Excel file, saved in SharePoint, the flow for that Cohort creates a folder for that Student as we well as a subset of folders, under the Cohort root folder:
Each of the Students has a unique Student ID, based on the Cohorts so:
So far these flows have saved their admin team around 25% or their working week so they are very happy.
Next process they wanted to automate is the saving of email attachments that come into a Shared Mailbox, for each Student. The subset of folders I mentioned above, lets say there are 4 sub folders - each containing different content matter, each of the 4 has its own Shared Mailbox .
So I built out a new flow that does this perfectly:
This is just for one type of Document that is received ( always into the same Shared Mailbox). So in the above case, this flow will look at the 'Enrolment Forms' Shared Mailbox, and save the PDF attachment of that enrolment form into the relevant Student folder. So for Student ID 'TCLAS20220123', when a new emails comes into that Shared Mailbox, as long is it has that Students ID in the Subject, any attachment gets saved into:
Might not be the most elegant but it works.
But the issue is that the above flow is just looking at Cohort A's Excel Spreadsheet. And rather than having 5 flows for each Cohort:
meaning a lot of flows, I wanted to see if there is a better way of doing this, with the least amount of flows.
One way I was looking at doing this is, after the 'Mark Email Is Read' step inserting a Condition:
Under the 'No' path we get:
Any thoughts on a cleaner way to do this?
Hey!
Try a SWITCH control, it's like an IF, but with up to 20 (I think) IF branches.
Also, if you use the switch to set some variables. (Use the "initialize variable" step at the beginning of the flow, and the "set variable" step under each switch case). Then, you can use those variables values after the switch so you don't have to duplicate the steps inside each case.
Hi @ryleybauer
Thanks for coming back to me so fast and the SWITCH options looks very promising.
As I said in my first port, I am pretty new to this and not a coder by trade.
So, I will need to set up an Initialize variable step before all other 'do' actions. Can I assume that I need to make this an Array and with that Array I set the Cohort Names, so:
"name": "Cohort Name",
"type": "array",
"value": "\"Cohort A\", \"Cohort B\", \"Cohort C\", \"Cohort D\""
And then the next step is to set the Codes against those Cohorts:
Cohort A: %Student ID% begins with 'TCLA'
Cohort B: %Student ID% begins with 'TCLB'
Cohort C: %Student ID% begins with 'TCLC'
Cohort 😧 %Student ID% begins with 'TCLD'
So then, within the SWITCH I can then select the corrects 'equals to' for that SWTICH Branch to carry out the saving of attachments into the correct Student.
I am sure the only way to do this, is using the Set Variable but using an Expression.
Any thoughts?
I would suggest your second step right after the trigger and before the switch is "initialize variable" and use a string type instead of array. Call it "cohort name" like you did.
Then, for each switch case (expression would look like): startswith('student id', 'TCLA')
Note: 'student id' would be the official column name
The only action inside the switch case would be "set variable" - "cohort name" - value = "cohort a".
Then you would have your steps below using the "cohort name" variable, instead of copying the same steps under each cohort name.
Example:
Create folder step after the switch. Set the folder path in the create folder step to: "/2022/COHORTNAMEVARIABLE" instead of making 4 steps, one under each case.
If you put it in the subject line of your email, you can use the variable. If you change out who you send it to, you can make a second variable to set in the switch and then you can use the variable in the "to" field to only have one email step instead of multiple.
I'm on my phone so I can't send you screenshots, but let me know if that doesn't make sense!
I'm glad to offer assistance. If I've answered your question, please mark my reply as the solution.
If you like my response, please give it a thumbs up!
@ryleybauer - sorry, not sure I have had enough coffee.
Can you provide screen shots for me please?
Thanks
Mark
Hey @markgalvin ,
I couldn't tell what you were doing with your excel steps, but it seems you have a handle on them. Give this a try and you can probably do your excel step below as well if you look at the formula I used for the subject line and adapt it.
I'd recommend also doing your "Mark as read" step at the end as I like to make my flows "confirm" user visible things only once the flow has completed. This would help you track ones that failed without marking them as read and making it ambiguous.
Final tip: I didn't rename my steps, but you should like you started:
I'm glad to offer assistance. If I've answered your question, please mark my reply as the solution.
If you like my response, please give it a thumbs up!
User | Count |
---|---|
88 | |
37 | |
26 | |
13 | |
13 |
User | Count |
---|---|
127 | |
54 | |
37 | |
24 | |
21 |