cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BenDonahue
Continued Contributor
Continued Contributor

Old formulas overwriting table values

I am adding the following formula to every line that is added via flow:

=INDEX('RosterAndAssignments'!A2:E17,MATCH("FCN",'RosterAndAssignments'!E2:E17,0), 3)

 

The formula as it appears in my "Add a row into a table" block is this:

example of code in flow.png

 

When I add a second row from the same form submission (I can add up to 5), the above formula changes to:

=INDEX('RosterAndAssignments'!A1:E16,MATCH("FCDLC",'RosterAndAssignments'!E1:E16,0), 3)

This is deprecated code that has been universally updated in my flow. 

 

In testing, when I manually add a dash (or anything) to column A, data in other columns appears. Note that column I has a version of the deprecated formula, with the dynamic value filled it so the formula is not being generated dynamically from Flow, but instead is hard-coded.

example of manually adding data to table.png

 

When I add that formula to other cells without using Flow, the formula works perfectly:

BenDonahue_4-1594043108204.png

 

When I enter only one row using the form in Flow, it works perfectly, also:

BenDonahue_5-1594043129670.png

 

When I add a second row using Flow, the formula in the first row changes:

BenDonahue_6-1594043154328.png

 

When I directly add data to the first row of a blank table, (in this case an "X" in column H), other values populate, including the deprecated formula:

BenDonahue_7-1594043193326.png

 

Where is this data coming from, and how do I stop it?

1 ACCEPTED SOLUTION

Accepted Solutions
BenDonahue
Continued Contributor
Continued Contributor

@burrisca,

I was able to get my flow running as expected only by re-writing the flow, and that solution was supplied to me by a Microsoft Support tech, and high-level, too. What I, and I suspect you, also, are trying to do is leverage the functionality of Flow AND of Excel, which still seems an excellent idea to me, like using some PHP to add functionality to your HTML or JavaScript.


However, best practices says that all logic should be put into Flow OR into Excel. With the help of the support tech, we created a flow (him, really) that ended up being both a solution and a workaround. Workaround in that it did not address my issue that Excel was re-writing all of my formulas in the column to be exactly like the most recently added formula; solution in that it works and is in alignment with best practices.

 

My end goal was to have a unique ID for each row when I am collecting 5 rows/form submission. The strategy of the solution was to grab the highest number in that column, load up an array of the values I needed, and enter those static values into the cells, and that is the key/end goal, to enter static values into the cells, instead of formulas.

 

If you would like some further assistance from me, humble though it may be, post your question and add an (at sign)BenDonahue to it at the end so I can find it; that way you can get help from everybody, and I can add my 2 cents. Also, failing any solutions provided by the community, you have excellent basis to open a ticket, if you haven't already done so.

 

Good luck, and good coding!

 

View solution in original post

4 REPLIES 4
burrisca
Advocate I
Advocate I

I am having the same issure, but with the Hyperlink formula. All ~20 of my hyperlinks are set to the last hyperlink from "Add a row into a table", but in the flow run history they are clearly 20 distinct hyperlinks. 

BenDonahue
Continued Contributor
Continued Contributor

@burrisca,

I was able to get my flow running as expected only by re-writing the flow, and that solution was supplied to me by a Microsoft Support tech, and high-level, too. What I, and I suspect you, also, are trying to do is leverage the functionality of Flow AND of Excel, which still seems an excellent idea to me, like using some PHP to add functionality to your HTML or JavaScript.


However, best practices says that all logic should be put into Flow OR into Excel. With the help of the support tech, we created a flow (him, really) that ended up being both a solution and a workaround. Workaround in that it did not address my issue that Excel was re-writing all of my formulas in the column to be exactly like the most recently added formula; solution in that it works and is in alignment with best practices.

 

My end goal was to have a unique ID for each row when I am collecting 5 rows/form submission. The strategy of the solution was to grab the highest number in that column, load up an array of the values I needed, and enter those static values into the cells, and that is the key/end goal, to enter static values into the cells, instead of formulas.

 

If you would like some further assistance from me, humble though it may be, post your question and add an (at sign)BenDonahue to it at the end so I can find it; that way you can get help from everybody, and I can add my 2 cents. Also, failing any solutions provided by the community, you have excellent basis to open a ticket, if you haven't already done so.

 

Good luck, and good coding!

 

View solution in original post

burrisca
Advocate I
Advocate I

@BenDonahue 

Thanks Ben! It is unfortunate that excel formulas don't work through flow, we are just going to send the plain hyperlink and the user will have to format it or paste it in a browser. 

Thanks again!

BenDonahue
Continued Contributor
Continued Contributor

I'm curious, what is this hyperlink formula you are using? Is it something that can be constructed Flow side and then entered as static data into Excel? 

Helpful resources

Announcements
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

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (86,468)