cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oktay
Frequent Visitor

Microsoft Form - Uses Case - Status Update for Orders

Hi all,

 

I have a SharePoint list in which orders for contractors are stored. I would like to retrieve the status of each order using PowerAutomate.

I was thinking that maybe Microsoft Form would be applicable here.

My idea: I would like that as soon as an order is entered in the SharePoint list, that an email with a Microsoft Forms survey is sent to the contractor concerned, confirming to me, for example, that he has accepted the order.

Once he has answered the Microsoft Forms surveys, I would like the answer to be added back to the affected job in my SharePoint list and updated.

Does anyone have any idea how I can set up such a flow? Is it even possible to do it this way? Best regards
Oktay

3 ACCEPTED SOLUTIONS

Accepted Solutions

Alright @Oktay  I made an example.

 

This has 2 flows. Flow 1 triggers when an item is created in the orders SharePoint list and sends an email to the contractor. Flow 2 triggers when a form is submitted and will verify the submission information and then update the item in the orders SharePoint list.

 

Flow 1 - Create Email - OverviewFlow 1 - Create Email - Overview

Flow 1 is pretty straightforward. It just needs to send an email when the item is created. In my example I added some variables that can be used to format the hyperlink that is in the email.

Flow 2 - FormResponse UpdateItem - OverviewFlow 2 - FormResponse UpdateItem - Overview

Flow 2 may seems more complicated, but it is really just using some Condition actions to double check what the contractor submitted in the form. 

 

First I am starting by showing my Orders SharePoint list and the info that I have in the columns. Primarily the OrderID is a text type column where it can be a unique identifier for the order. There should be no other orders in the list with the same OrderID. 

 

The column ConfirmationStatus_Choice is a choice type column that is default to "Pending", but can have the options for "Confirmed" and "Decline". These choice responses match the options that will be on the contractor's confirmation form.

SharePoint List - Confirmation Choice ColumnSharePoint List - Confirmation Choice Column

 

The column Confirmation_DateTime is a datetime type column and will be filled in with the time that the contractor submits their form. So it is blank when an item is created. 

SharePoint List - Confirmation DateTime ColumnSharePoint List - Confirmation DateTime Column

The automatic ID number column for the SharePoint list is what I will be referring to as OrderNumber.

OrderID = unique text type identifier for an order such as "AB1234"

OrderNumber = SharePoint Automatic ID number column

 

So I created my first item/order in the SharePoint list

SharePoint List OrdersSharePoint List Orders

 

This will trigger the Flow 1 which sends an email to the contractor.

Flow 1  Overview - Send EmailFlow 1 Overview - Send Email

Here are the individual actions.

Flow 1 Actions DetailFlow 1 Actions Detail

 

The first action - Initialize Variable - is where you will copy and paste the link to the form that the contractor will submit.

The second action - Initialize Variable 2 - is where you will type the text that you want the hyperlink to the form to say in the email. If you want the form hyperlink to say "Click Here for Form" then you will just type that text into this variable.

The third action - Initialize Variable 3 - Is the variable that you will put into the email. It uses the previous variables to format the HTML link. To format a hyperlink in an email you have to use the html format:

<A HREF = "{link}">{hyperlinktext}</A>

I have put the variables into the {link} and {hyperlinktext} spaces. So you don't need to do anything with this variable. It will work exactly like the example.

 

The fourth action is Send an Email (V2) - this is the email that will go to the contractor. You can edit the text however you want obviously. 


Below is an example of the email received:

Email to ContractorEmail to Contractor

 

The contractor will follow the hyperlink to complete the submission form.

 

I made an example submission form below. 

Example Confirmation FormExample Confirmation Form

 

The main parts of the form are the first question which is a text response for them to put the OrderID. The second question which is a number response for them to put the OrderNumber which is basically the SharePoint list item number. And the last question which is a choice for them to Confirmed or Decline the order.

 

When they submit the form, the second flow will trigger.

Flow 2 Overview - FormResponse UpdateItemFlow 2 Overview - FormResponse UpdateItem

 

The first part of the second flow is to get the form response and put the OrderID and OrderNumber that the contractor submitted into variables. These variables will be used throughout the flow.

In the first Initialize Variable OrderNumberFormSubmission this is an integer type variable. The form response must be put into the int() integer expression so that the value will be an integer. (I don't know why the MS Form will have a number response that goes into Power Automate as a string, but we have to convert the string into a number using int() expression.)

 

The second Initialize Variable 2 OrderIDFormSubmission is a string variable that will hold the OrderID that the contractor typed into the form. If you wanted to wrap this in a trim() expression to remove any spaces you could, but I didn't in the example. 

Flow 2 - Detail 1 - FormResponse Initialize VariablesFlow 2 - Detail 1 - FormResponse Initialize Variables

 

The second part of Flow 2 will be to Get Items from the Order SharePoint list while using a Filter Query with the OrderID from the form response. This is used to check if there even is an OrderID that matches what the contractor wrote in their form. You can use the variable OrderIDFormSubmission or the dynamic content from the form response, but in the example I used the form response dynamic content.

 

 

The Condition - Check Results from Get Items will check how many items were returned from the Get Items action by using the length() expression on the values of Get Items. If no values were returned, that means the contractor put in an incorrect OrderID and the length() of the Get Items will be 0 zero. 

So if the contractor put in the incorrect OrderID, it will send the condition to Yes which is an action to send You an email to go double check the issue.

Flow 2 - Detail 2 - Get Items Condition CheckFlow 2 - Detail 2 - Get Items Condition Check

 

The third part of the flow is based on the Condition - Check Results of Get Items which checked the length of the Get Items. So if no OrderID is found in the SharePoint list you can send yourself an email or something to go doublecheck the issue.

If the length of Get Items is not 0 zero; then some item or items were found with the same OrderID that was written by the contractor. 

So there is a Get Item step which uses the variable OrderNumberFormSubmission as the Id for the Get Item. This will return the item with the same number.

 

The next step is to compare the item that was just pulled from Get Item and the OrderID for that item with the OrderID that was submitted by the contractor. This condition compares the two OrderID to determine if it is the same that matches the OrderNumber (ItemID). 

Flow 2 - Detail 2 - Get Item Compare OrderIDFlow 2 - Detail 2 - Get Item Compare OrderID

 

NOTE: There is a option ... on the Condition which is Configure Run After. Select this option and set the configuration to Is Successful AND Has Failed. This is so that if the contractor puts in an OrderNumber that doesn't exist, it will still send an error email instead of failing the entire flow at the Get Item action.

Condition Configure Run AfterCondition Configure Run After

 

The final part of Flow 2 will update the item with the Confirmation Status choice from the form response question the contractor selected and the Confirmation Datetime with the form response submission date. 

 

If the OrderID from the Get Item and the OrderID from the form response variable do not match (or the Get Item failed due to non-existent Id number), you can send an email to yourself to check on the issue.

Flow 2 - Detail 4 - Update ItemFlow 2 - Detail 4 - Update Item

 

When the flow is successful and the OrderID and the OrderNumber match correctly then the item in the SharePoint list will have to ConfirmationStatus_Choice and Confirmation_DateTime columns updated accordingly.

SharePoint List Item ConfirmedSharePoint List Item Confirmed

 

Your flows do not have to look exactly like mine, but I wanted to add the option for using OrderID and OrderNumber as a way to confirm the contractor used the correct information. 

 

If this helps please mark this as a solution to your post, and if you have any specific questions or issues please ask them, but also include screenshots with whatever error you are having.

View solution in original post

Oktay
Frequent Visitor

@wskinnermctc Thanks for the great solution. I will try to implement it in the next few days. I noticed that I can't send a Microsoft Form outside my organization. I still need to clarify that with our IT department.

 

View solution in original post

Do you not have the option in the MS Form Settings to select "Anyone can respond" so that people can fill out the form with the link? 

MS Form SettingsMS Form Settings

 

Also @Oktay , can you mark the response I previously made with all of the instructions as the solution to this your post?

Right now the post solution is showing as your reply response. Marking my response as the solution helps other people looking for solutions to get directly to the answer and most importantly it gives me those sweet sweet fake internet points 🏆

View solution in original post

6 REPLIES 6

I think all of that is possible, but the most difficult part will be to get the Contractor's form response to go directly to the affected job in the SharePoint list. The reason is due to needing a key/link/id or some way to reference the sharepoint list item to the contractor's form response.

 

For example, lets say you have a SharePoint list item for a job. The list item id is the number 8, which is the automatic ID applied to sharepoint list items. An automatic email flow is sent from ListItem 8 to go to the contractor.

 

You could send an email to the contractor with the ID number in the email and a link to the form. In the form, there would be a question about, "What ID number of the item from the email?" Where the contractor would put the number 8 in the form. Then when the flow is triggered off that form response, it could look up the list item ID 8 and then update that specific item.

 

I think you can see how prone to error that would be. It would be great if every contractor put the correct Item ID number from the email, but that's wishful thinking.

 

You would need some kind of ID/Reference to be input by the contractor on their form that could be used to lookup the specific item and then update it.

 

The flow/s are relatively simple, but the issue will be linking back to the item since you are dependent on a form response input.

@wskinnermctc 

Thank you for your reply.

I think there is only the way that the contractor manually enter the ID number. Assuming I would want to go this way, what would a flow look like for this?

There are some videos in your the reception and the data transfer is described. But the start of the process in a flow I could not find until now.

Alright @Oktay  I made an example.

 

This has 2 flows. Flow 1 triggers when an item is created in the orders SharePoint list and sends an email to the contractor. Flow 2 triggers when a form is submitted and will verify the submission information and then update the item in the orders SharePoint list.

 

Flow 1 - Create Email - OverviewFlow 1 - Create Email - Overview

Flow 1 is pretty straightforward. It just needs to send an email when the item is created. In my example I added some variables that can be used to format the hyperlink that is in the email.

Flow 2 - FormResponse UpdateItem - OverviewFlow 2 - FormResponse UpdateItem - Overview

Flow 2 may seems more complicated, but it is really just using some Condition actions to double check what the contractor submitted in the form. 

 

First I am starting by showing my Orders SharePoint list and the info that I have in the columns. Primarily the OrderID is a text type column where it can be a unique identifier for the order. There should be no other orders in the list with the same OrderID. 

 

The column ConfirmationStatus_Choice is a choice type column that is default to "Pending", but can have the options for "Confirmed" and "Decline". These choice responses match the options that will be on the contractor's confirmation form.

SharePoint List - Confirmation Choice ColumnSharePoint List - Confirmation Choice Column

 

The column Confirmation_DateTime is a datetime type column and will be filled in with the time that the contractor submits their form. So it is blank when an item is created. 

SharePoint List - Confirmation DateTime ColumnSharePoint List - Confirmation DateTime Column

The automatic ID number column for the SharePoint list is what I will be referring to as OrderNumber.

OrderID = unique text type identifier for an order such as "AB1234"

OrderNumber = SharePoint Automatic ID number column

 

So I created my first item/order in the SharePoint list

SharePoint List OrdersSharePoint List Orders

 

This will trigger the Flow 1 which sends an email to the contractor.

Flow 1  Overview - Send EmailFlow 1 Overview - Send Email

Here are the individual actions.

Flow 1 Actions DetailFlow 1 Actions Detail

 

The first action - Initialize Variable - is where you will copy and paste the link to the form that the contractor will submit.

The second action - Initialize Variable 2 - is where you will type the text that you want the hyperlink to the form to say in the email. If you want the form hyperlink to say "Click Here for Form" then you will just type that text into this variable.

The third action - Initialize Variable 3 - Is the variable that you will put into the email. It uses the previous variables to format the HTML link. To format a hyperlink in an email you have to use the html format:

<A HREF = "{link}">{hyperlinktext}</A>

I have put the variables into the {link} and {hyperlinktext} spaces. So you don't need to do anything with this variable. It will work exactly like the example.

 

The fourth action is Send an Email (V2) - this is the email that will go to the contractor. You can edit the text however you want obviously. 


Below is an example of the email received:

Email to ContractorEmail to Contractor

 

The contractor will follow the hyperlink to complete the submission form.

 

I made an example submission form below. 

Example Confirmation FormExample Confirmation Form

 

The main parts of the form are the first question which is a text response for them to put the OrderID. The second question which is a number response for them to put the OrderNumber which is basically the SharePoint list item number. And the last question which is a choice for them to Confirmed or Decline the order.

 

When they submit the form, the second flow will trigger.

Flow 2 Overview - FormResponse UpdateItemFlow 2 Overview - FormResponse UpdateItem

 

The first part of the second flow is to get the form response and put the OrderID and OrderNumber that the contractor submitted into variables. These variables will be used throughout the flow.

In the first Initialize Variable OrderNumberFormSubmission this is an integer type variable. The form response must be put into the int() integer expression so that the value will be an integer. (I don't know why the MS Form will have a number response that goes into Power Automate as a string, but we have to convert the string into a number using int() expression.)

 

The second Initialize Variable 2 OrderIDFormSubmission is a string variable that will hold the OrderID that the contractor typed into the form. If you wanted to wrap this in a trim() expression to remove any spaces you could, but I didn't in the example. 

Flow 2 - Detail 1 - FormResponse Initialize VariablesFlow 2 - Detail 1 - FormResponse Initialize Variables

 

The second part of Flow 2 will be to Get Items from the Order SharePoint list while using a Filter Query with the OrderID from the form response. This is used to check if there even is an OrderID that matches what the contractor wrote in their form. You can use the variable OrderIDFormSubmission or the dynamic content from the form response, but in the example I used the form response dynamic content.

 

 

The Condition - Check Results from Get Items will check how many items were returned from the Get Items action by using the length() expression on the values of Get Items. If no values were returned, that means the contractor put in an incorrect OrderID and the length() of the Get Items will be 0 zero. 

So if the contractor put in the incorrect OrderID, it will send the condition to Yes which is an action to send You an email to go double check the issue.

Flow 2 - Detail 2 - Get Items Condition CheckFlow 2 - Detail 2 - Get Items Condition Check

 

The third part of the flow is based on the Condition - Check Results of Get Items which checked the length of the Get Items. So if no OrderID is found in the SharePoint list you can send yourself an email or something to go doublecheck the issue.

If the length of Get Items is not 0 zero; then some item or items were found with the same OrderID that was written by the contractor. 

So there is a Get Item step which uses the variable OrderNumberFormSubmission as the Id for the Get Item. This will return the item with the same number.

 

The next step is to compare the item that was just pulled from Get Item and the OrderID for that item with the OrderID that was submitted by the contractor. This condition compares the two OrderID to determine if it is the same that matches the OrderNumber (ItemID). 

Flow 2 - Detail 2 - Get Item Compare OrderIDFlow 2 - Detail 2 - Get Item Compare OrderID

 

NOTE: There is a option ... on the Condition which is Configure Run After. Select this option and set the configuration to Is Successful AND Has Failed. This is so that if the contractor puts in an OrderNumber that doesn't exist, it will still send an error email instead of failing the entire flow at the Get Item action.

Condition Configure Run AfterCondition Configure Run After

 

The final part of Flow 2 will update the item with the Confirmation Status choice from the form response question the contractor selected and the Confirmation Datetime with the form response submission date. 

 

If the OrderID from the Get Item and the OrderID from the form response variable do not match (or the Get Item failed due to non-existent Id number), you can send an email to yourself to check on the issue.

Flow 2 - Detail 4 - Update ItemFlow 2 - Detail 4 - Update Item

 

When the flow is successful and the OrderID and the OrderNumber match correctly then the item in the SharePoint list will have to ConfirmationStatus_Choice and Confirmation_DateTime columns updated accordingly.

SharePoint List Item ConfirmedSharePoint List Item Confirmed

 

Your flows do not have to look exactly like mine, but I wanted to add the option for using OrderID and OrderNumber as a way to confirm the contractor used the correct information. 

 

If this helps please mark this as a solution to your post, and if you have any specific questions or issues please ask them, but also include screenshots with whatever error you are having.

Oktay
Frequent Visitor

@wskinnermctc Thanks for the great solution. I will try to implement it in the next few days. I noticed that I can't send a Microsoft Form outside my organization. I still need to clarify that with our IT department.

 

Do you not have the option in the MS Form Settings to select "Anyone can respond" so that people can fill out the form with the link? 

MS Form SettingsMS Form Settings

 

Also @Oktay , can you mark the response I previously made with all of the instructions as the solution to this your post?

Right now the post solution is showing as your reply response. Marking my response as the solution helps other people looking for solutions to get directly to the answer and most importantly it gives me those sweet sweet fake internet points 🏆

Oktay
Frequent Visitor

@wskinnermctc 

 

thank you again for your support. I still have a question about the 2nd flow. I have a date field in my sharepoint list that I use to query the scheduled activity on the part of the contractor. I have added a date field in Forms as well for this query. However, if the contractor does not add a date because, for example, he has not yet scheduled the execution, I get an error in my flow because he cannot update the expected date value in Updat Item.

How can I solve this?

Would be great if you could give me another tip.

 

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (1,919)