I was brainstorming the best way for monthly numbers to be submitted via the Microsoft suite of products. This lead me to believe that creating Forms for each unit, utilizing Power Automate to have each Form automatically add a row to a corresponding Excel worksheet. I would like to prevent repeat submissions, however. Instructions state to indicate the month by using the 15th for the day of each submission. I wish the day did not have to be included on the submission, but that is what it is for the sake of Forms and Excel. My next thought, which I thought would be easy, was to create a way to prevent transcription of data for which the date already existed in the worksheet. I have tried to pull the table data, and use an array filter to match the date from the Forms submission. This leads to no output, as I believe the Forms date field is in a (d)d/mm/yyyy format and the Excel table is pulled as the numerical Excel format. I have tried the help offered at https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/, but always get a blank array output. Any advice for this topic would be much appreciated.
Columns in each report are date, then metrics which could easily not be unique over time. These include things total orders, entry errors, etc. Date is the only thing that is guaranteed to be different, except for duplicate entries.
@mikec4 Could you share a screenshot of what a duplicate entry would look like? Would all columns of data be the exact same (except the response ID of course).
Thank you for the resources. I figured I could somehow use the format data type function applied to the Excel table to get the dates in the same format, but I have had no luck. The one time submission does not work, as these Forms are to be used to gather monthly reports. It is possible that someone may forget that they submitted already, or another staff member may duplicate their effort. I could forget this idea, and instead manually review all dozen or so worksheets periodically.
@mikec4 The is equal to operator looks for an exact match. In your case, the values you are comparing to each other will never match. Can you provide more insight as to how your form is set up and how you would get duplicate submissions? Have you considered setting your form so only one response will be accepted per person?
In the meantime, you might be interested in these YT Tutorials I've linked below.
How to Get Microsoft Form File Uploads Attached to an Email 📧
In this Microsoft Power Automate Tutorial I’m going to cover where file uploads from your Microsoft Forms are saved. I’ll also show you how attach the uploaded files to an email and how to dynamically name the files and customize the recipient of the email based on the selections made in your Microsoft Form. I will also cover how to handle responses that don’t include any file uploads.
IN THIS VIDEO:
‌✅‌ Two types of MS Forms
‌✅‌ Where Microsoft Personal (OneDrive) Form File Uploads are Saved
‌✅‌ Where Microsoft Group Form File Uploads are Saved
‌✅‌ How to Add a File Upload Question to an MS Form
‌✅‌ How to get a Microsoft Form ID
‌✅‌ How to get a Microsoft Form Response
‌✅‌ How to Get the Dynamic Content Microsoft Form File Upload Content
‌✅‌ How to handle Single and Multiple Microsoft Form File Uploads
‌✅‌ How to use a Scope action to Organize and Group Your Flow Actions
‌✅‌ How to Get the File Content from an MS Form File Upload
‌✅‌ How to Collect All Files Uploaded to a MS Form and Attach to an Email
‌✅‌ How to Handle MS Form Response When a File Isn’t Uploaded
‌✅‌ How to Create an Email Key
‌✅‌ How to Send an Email to a Specific Recipient Based on Form Selection
‌✅‌ How to Create a Dynamic Output Based on Form Selection
-----
How to Add Microsoft Form File Uploads to a SharePoint List Item
In this Microsoft Power Automate Tutorial I’m going to cover how to add file uploads to a SharePoint list item. First, I’ll show you how to get the file name and content from the File Uploads from your Microsoft Form, then I’ll show you how to add those files to a SharePoint item.
IN THIS VIDEO:
✅ Two types of MS Forms
✅ Where Microsoft Personal (OneDrive) Form File Uploads are Saved
✅ Where Microsoft Group Form File Uploads are Saved
✅ How to Add a File Upload Question to an MS Form
✅ How to get a Microsoft Form ID
✅ How to get a Microsoft Form Response
✅ How to Get the Dynamic Content Microsoft Form File Upload Content
✅ How to handle Single and Multiple Microsoft Form File Uploads
✅ How to use a Scope action to Organize and Group Your Flow Actions
✅ How to Get the File Content from an MS Form File Upload
✅ How to Collect All Files from Multiple File Upload Questions
✅ How to Handle MS Form Response When a File Isn’t Uploaded
✅ How to Attach an MS Form Single File Upload to a SharePoint List Item
✅ How to Attach Multiple MS Form File Uploads to a SharePoint List Item
✅ How to Attach MS Form File Uploads to an Email
-----
How to Get a Microsoft Form RSVP Response into a SharePoint List
IN THIS VIDEO:
✅ How to get a Microsoft Form Response into SharePoint
✅ How to get a Microsoft Form ID
✅ How to get a Microsoft Form response
✅ How to Build a Microsoft Form with Conditional Fields
✅ How to used Branching in Microsoft Forms
✅ How to format Microsoft Form multiple choice responses for a SharePoint multi-choice column
✅ How to parse a Microsoft Form multiple choice response as string of text
✅ How to convert a text response to a number
✅ How to use the Switch action
✅ How to create a custom email confirmation for a Microsoft Form submission
✅ How to Create a Custom View in a SharePoint list
✅ How to use the Compose Action
✅ How to write Power Automate Expressions