Managing Supplier and Subcontractor Evaluations: Workflow Automation

[smt]

Hi I'll explain the flow that I want. It's a workflow triggered following the submission of a response from a supplier and subcontractor evaluation form. Users can evaluate up to 5 suppliers or subcontractors at once. Subsequently, the flow retrieves the form information such as name, date, criteria ratings, comments, and project number. These details are then stored in an Excel sheet, with suppliers on one sheet and subcontractors on the other. Excel calculates the final rating for each supplier or subcontractor. However, after a while, the Excel sheet becomes disorganized and chaotic. Therefore, I would like to know if it's possible to create a flow that checks whether the supplier mentioned in the form is a new or existing one. If it's already present, it will update a summary line with the supplier's evaluations, including the average rating. If it's not present, a new line will be created for the upcoming form responses. So, is it possible to achieve this? I already did the first part where the excel is updated with all the answer of the forms.

Thanks,

[trice602]

Hi @smt ,

Yes, you can do this with a conditional statement.  The first thing is your data must reside in tables so in both of your supplier and subcontractor sheets, your data is formatted in, i.e. Table1, Table2.

So your conditional statement is going to check if the Supplier or Subcontractor already exist.  

When a form result is submitted, you check your company name and if it exists, you are using Updating a Row action, if no, you are Adding a Row action.

For your ratings and other details, those are just Excel formulas, independent of a form response so just include those in the template you are writing to "When a new form response is submitted".

This is very effective and works great!

Please mark as a solution ✅ and/or give me a thumbs up 👍🏽 for replying and volunteering my time! 🎗️  Always glad to help! Tom ⭐⭐⭐⭐⭐

[smt]

Okay, perfect, that's what I thought.

Do you have any idea which conditions I should use? I don't know what conditions to set since the names vary with each form submission. I'm unsure how to compare the new names with those already in the form.

[trice602 · Accepted solution]

Hi @smt ,

Here's a sample framework to implement.  Please mark as a solution!

Ok let's walkthrough this.  Trigger and Get responses...

Next, I have a condition to check if the responder is a supplier or subcontractor from a choice question.

Next, I have a condition that creates two paths:  one for suppliers, one for subcontractors.  I do that by checking the value of the choice question in my conditional step.

So, if Yes, go through my Supplier flow side, No, go through my Subcontractor flow side.

Next, I list rows present in my table.  For my supplier, I have a sheet with a Table1.  For my subcontractor, I have a sheet with a Table2.

Now, here's the cool part!  We have another conditional that checks if the value of the company name is already in the Table!  If Yes, we Update a Row (shown in example), if No, we Add a row (not shown in example).  So on the No side, you would Add a Row and include the relevant form entries..

Ok, all the way back to the first conditional:  The supplier value.  If no, then they must be a subcontractor, and you have the exact same framework, check if they exist, Yes, update or row, No, add a row.

I didn't build this out entirely but you can see in my test, I entered a form response with "Tom and Company" and in fact, my row was updated, not added because I already exist as a supplier.

Please mark as a solution ✅ and/or give me a thumbs up 👍🏽 for replying and volunteering my time! 🎗️  Always glad to help! Tom ⭐⭐⭐⭐⭐