cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tallwalks
New Member

Using a Form submission to either create or update a row in an excel table.

Good Evening,

 

I am requesting assistance with developing a flow that can do what is described in the subject line.  The form is very simple and only contains the following questions:

 

  1. What is your first name?
  2. What is your last name?
  3. What is the name of the certificate that you have completed?
  4. What is the date in which you completed the certificate?

 

I have been trying to make a flow that when a submission is received that a new entry will populate into the table, but if the entry matches (First Name, Last Name, and Certificate Name), I want it to just update the certificate completion date.  This is to track a multitude of employees that are submitting certificates that they complete.

 

Is this possible?  I have attempted to build a flow that does this, but when I run the flow.  It will create a new row for each row that currently exists in the table.  For example: If there are already four rows, and I submit a new form it will create four identical rows into the document.

 

Thank you in advance for any assistance on this.

2 REPLIES 2
edgonzales
Super User II
Super User II

@tallwalks 

Hi there.  Totally possible, here's one approach:

  • Make a column in your spreadsheet that is those three fields all together.  The result might look like "EdGonzalesMCSA" or whatever.  But this will essentially create a unique row identifier for each FirstLastAward combination.
  • In your flow, after the Get Form details step, use a Compose Action to do the same thing with the information from the form.  That expression might look something like Concat(<firstName>,<lastName>,<award>) where the stuff in <> is your dynamic values.
  • Then use your Get Rows action and you can either use an Odata filter in that step that is <compose> eq <ExcelKeyColumn> or add a Filter Array step after that does the same check.
  • Add a condition after to see if the resulting array is empty().  If it is, then you can go to your Add a Row step, if not, use the Update Row path using the Row ID from the Filter Array (or Get Rows) step.

Keep in mind that any time Power Automate is expecting an array (even if there is only supposed to be one item in that array) it will throw subsequent actions into an Apply to Each loop.  Watch @benitezhere 's video on avoiding that below:

 

 

Give that a go, and let us know how it works out.  Good luck.

-Ed

 

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

@edgonzales 

 

Thanks for the response!  I used a lot of what you suggested, but I am still running into an issue with referencing the array using the information in the video.  I tried to recreate what she was doing in the video with my information, but I was returned with an error:

 

Unable to process template language expressions for action 'Condition' at line '1' and column '26162': 'The template language expression 'first(body('Filter_array')?['value'])?['Identifier']' cannot be evaluated because property 'value' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

 

She laid out the following for her situation:

 

first(body('1.5_Get_Time_Zone_Name_of_User')?['value'])?['standardname']

 

I replaced the name of action with my Filter Array, and the standard name with Identifier (my unique row identifier).

 

I imagine at this point it is still something that I am not understanding, or I am not using the correct names while trying to copy what she was doing in the video.

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,015)