cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ja18
Helper I
Helper I

Update a row by dynamic content in table and key value columns

Hi,

I wonder if anyone could help me. I am trying to build a flow (in fact 2 flows) which is saving Purchase Orders attachments and count them based on daily supply.

I have already created scheduled flow creating new Workload table each working day. The flow looks like below and it is automatically creating new table Monday-Friday. Table has only 2 rows (ID and PO Name).

Ja18_1-1715002614670.png


I have also created automated flow saving purchase orders from all flagged by my team members emails inside shared drive. Now I am struggling with adding dynamic content into Table row and Key Value row ((marked on yellow color)( EXCEL update a row)).

Ja18_2-1715003090498.png

Daily_Workload table is the table automatically created from the scheduled flow I have build earlier. The problem is that I don't know how to add dynamic content before the Table name to reflect Sheet Name which in my scenario is the date of each day created by expression :     formatDateTime(utcNow(),'yyyy-MM-dd')

Second issue I have is I don't know how to fill Key Value with dynamic content. In my flow it has to be integer starting from 1 and increasing by 1 each time when new attachment is saved on share point (first PO number 1, second PO number, third PO, number 3 etc).

Part with filling second column of my table with PO Number I've managed myself.

Ja18_3-1715003145573.png

 

In case my explanations aren't clear the result supposed to look like below (filled manually):

Ja18_4-1715004136934.png



Thanks a lot in advance for all of your replies. Hope someone could help me. 

With best regards.
NewUser



1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Ja18 

 

I hope the highlighted loop has no connection with the actions present inside it as it is just looping through list of tables right ?

Nived_Nambiar_0-1716828681299.png

 

if yes- just place the action add row to table outside the for each loop and delete that loop then - issue will be resolved 🙂

Nived_Nambiar_1-1716828771768.png

 

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

 

View solution in original post

12 REPLIES 12
Ja18
Helper I
Helper I

@Nived_Nambiar ,

Could you support me again? Would be grateful.

Hi @Ja18 

 

One doubt, how could you able to create table with same name in single workbook - it is not possible right ? Or are you creating new workbook for every run and then inside you are creating a new table by creating worksheet with date ?

 

Also with respect to adding the attachment details to excel table> you should use add row to table instead of updating the row 

 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

 

Ja18
Helper I
Helper I

Hi @Nived_Nambiar,

Thank you for your answer. I didn't know that I can't have one table name in single workbook as long as they don't share one worksheet. No problem, thank you for this remark, I've done the correction. let's now call my tables : utcNow('date_ddmmyyyy').

With respect to using "add row into a table" instead of "update a row", it is not clear for me. Tables have already been created by scheduled flow and they contains 2 columns and 40 rows, so I have my rows already prepared. Assuming I don't understand something, which probably is the case here as I am newbie I've tried to change my flow and add the step you have mentioned in between. Now the questions.

1.How do I make following rows to be growing by 1 with each saved attachment? meaning 1,2,3 etc.?
2.Is my table row filled correctly to reflect each sheet and than each table?

'formatDateTime(triggerOutputs()?['body/receivedDateTime'],'yyyy-MM-dd')'!'utcNow('date_ddmmyyyy')'

 

Ja18_0-1715251185672.png

3. Assuming that above step is completed and I can add update a row step to my flow how do I fill next fields?

Ja18_1-1715251402762.png

Key Column isn't appearing for the table naming, so I've presume it is incorrect + when adding expression content into table field another field appeared "provide the item properties" not sure what is this for.

Will be very  grateful for all your support.

best regards.

NewUser.






Hi @Ja18 

 

The reason why i suggested add a row instead of update a row is because , as per your flow design - you are creating a worksheet and inside that worksheet you are creating a table, so worksheet would be empty /or table would be same as well.

 

Coming to your question

 

1. How do I make following rows to be growing by 1 with each saved attachment? meaning 1,2,3 etc.?

Answer - you will be using a loop where you will iterating through list of attachments- at the start of loop, assign a variable of integer type with value 1, then at end of every iteration, increment it by 1 all the time.

That would helps !

 

2. Is my table row filled correctly to reflect each sheet and than each table?

 

I have noted some mistakes regarding your expressions-

The expression utcNow('date_ddmmyyyy') is wrong, it should be formatDateTime(utcNow,'"date"_ddMMyyyy') instead. The expression - 

'formatDateTime(triggerOutputs()?['body/receivedDateTime'],'yyyy-MM-dd')'!'utcNow('date_ddmmyyyy')'

 

should be 'formatDateTime(triggerOutputs()?['body/receivedDateTime'],'yyyy-MM-dd')'!formatDateTime(utcNow,'"date"_ddMMyyyy')

 

Also the above expression should be used for table creation not for other actions where table is needed, i.e - add a row, update a row etc. 

 

See below- you can try like this

Nived_Nambiar_0-1715493279858.png

 

Use the same dynamic content when updating a row as well 🙂

 

Regarding rows - you can json content like below

Nived_Nambiar_1-1715493330646.png

 

if you have 3 columns- let's say A,B and C in your table , then json content would be like this 

 

{"A":100,

"B":20,

"C":30

}

 

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

 

Hello @Nived_Nambiar ,

One more time thank you for reply and for your time. Unfortunately I don't feel like I am getting closer to the resolution. Let me clarify all my doubts one by one.

Firstly, your correction doesn't work for me. Not sure if this may have something to do with settings or whatsoever but I can't save the flow with expression formatDateTime(utcNow,'"date"_ddMMyyyy'). Nevertheless my expression works and creates new table daily as intended.

Ja18_0-1715950748593.png

Secondly, if I now want to add row to the table I've created by the first flow I don't know how Power Automate may know which table to update by using dynamic content of Table Name? I feel like this isn't linked anyhow at this moment. The idea is to update each row from each table with the Purchase Order numbers which were saved in Create file step above. Right now, I don't see how this supposed to work.

Ja18_1-1715951067602.png


Thirdly, I understand that we need to add increment value to the each new saved attachment from each day but I have no clue how to do this, tried multiple setups, where the loop supposed to be and how it supposed to look like? I've tried something like below but it didn't work.

Ja18_2-1715951608029.png

When I put it here don't have any dynamic content reference to the create file step on share point and when I create it under share point it is telling me this can't be used under condition.

Ja18_5-1715952375341.png


If you could lead me through those unclarities above I would be grateful, without completed step with dynamic row adding to each table I can't go further with dynamic row update with PO numbers although it works for me when I just put row ID as number manually instead of dynamic content.

This is how all the flow looks right now, but I don't know how to fill fields.

Ja18_6-1715952559386.png

Hope you can help, if my flow isn't clear for you perhaps you can share your email address and I can reach you through MS Teams.



 

 

Hi @Ja18 

 

Sorry i should have checked earlier- you should use the below expression to create table concat('date_',formatDateTime(utcNow(),'ddMMyyyy'))

 

Now coming to how to get table by table name and then use that table for adding a row, use Filter array like below

Nived_Nambiar_0-1716099688593.png

 

Filter by table name - date_(dateformat in ddMMyyyy) in your case

 

And since this has been long time i have answered to your question, so lost the track, so just want to confirm how your flow is working

 

First Flow

 

1. Create a table in workbook with name as date_ddMMyyyy in sheet with name date_ddMMyyyy in scheduled flow

 

Second flow

 

1. Grab attachments from a received email and add the details to created table using the excel file attachment present in email.

 

Could you confirm whether this is the flow? Because i still have doubts why you are using update row after add a row, also show me how u are filling parameters for create table action.

 

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

 

 

Hi @Nived_Nambiar ,

Thank you very much for your time, I am using update a row connector because I want to update rows in earlier prepared tables, by data based on attachments, from emails flagged by my team.

So the tables are just fine, and they already have columns and rows (assuming I understand my own flow :D)

2nd.JPG

4th.JPG

Let me refer to your queries directly:

"First Flow

 

1. Create a table in workbook with name as date_ddMMyyyy in sheet with name date_ddMMyyyy in scheduled flow                                                                                        YES

 

Second flow

 

1. Grab attachments from a received email and add the details to created table using the excel file attachment present in email."                                                            THE ATTACHMENTS ARE .PDF

So based on your corrections, which I have already implemented, right now I have something like below, and assuming I understand it correctly, I only need to o find a way to update dynamically yellow field by the increment value representing each new saved attachment.

1st.JPG

but this, as discussed earlier will require setting a variable somewhere higher in the flow to assign the increment number to each new saved attachment and to be used as Key Value reflecting new PO Number.

Right now, when I put Key Value manually, like for example 2 and if I update my table with the same number in ID column it works fine, so the Name of the attachment is appearing next to Key Value number.

So I think, this is quite close from this moment.

In case you don't understand my conception yet let me clarify it again.

My Team Members are flagging manually emails containing Purchase Orders (PO), and the flow is saving those attachments on shared drive. From this moment the flow is updating prepared earlier tables by the PO Names in column B next to the number of PO from each day in column A. So for example the email containing two attachments was flagged and the flow does something like below:

WQEQEWQEQ.JPG

At the end of the day I have something like below, and I have all PO names stored in column B and the exact amount of received POs in last updated row of column A.

21321321321.JPG
The same process is performed by the flow each working day as long as my team will be flagging emails to trigger the second flow.

ADEAdDdAD.JPG
Hope this is clear, at the end I will need to do one more thing with this flow but as for now let's not complicate it more and stick to what we miss now.

Thank you very much in advance for your response. In case of any questions please let me know.

Ja18

   




Hi @Ja18 

 

Thanks for your explanation, 

 

see what i understood is your second flow runs on a trigger when email is flagged, assume on that day, first- an email is flagged having 2 attachments, Then it should 2 attachments details in 2 rows correct- on same day, an email is flagged again having let's say 4 attachments- 4 extra rows are added in excel representing those 4 attachments.

 

Now you have to use add a row to table instead of update a row to table as update a row is used to update a row, while add a row is used to add a row to table.

 

Also one correct you can do, in your first flow- in Create table, specify range as A1:B1 instead of highlighted one as shown below

Nived_Nambiar_0-1716144259987.png

 

Because creating a table with range A1:B1 creates a empty table with no rows, now when adding rows to table- rows will be added automatically to it.

 

So make the following changes

 

1. Update the table range in create table to A1:B1

2. Also use add row to table to add the attachment details. Also before adding the attachment details- you may need to find total number of attachments added to table before adding new row so that u can calculate the ID value.

 

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

 

Hello @Nived_Nambiar ,

You can't create a table with range A1:B1, the lowest possible range is A1:B2. Power automate will let you save the flow with range A1:B1, but anyway it will create table longer by 1 row, so all the counting starts from initial value +1.

When it comes to adding a row to the table, if I am not adding dynamic content to the table field (so this isn't what satisfies me) I can use advanced options and select column names, which if I will do, is doing something like below:

aDdD.JPG

this is result from the below configuration

23131313.JPG

 

So with each new saved attachment I have some unrecognized  ID number in ID column and the proper name in PO number column + each attachment creates one desired row and one needless empty row.

When I try to use dynamic content into table field (so this is the scenario I need to achieve) I can't get rid of error.

Ja18_0-1716214265250.png

No matter what I do, this is the same, could you advice me on this please? Tried all the possible options:

Ja18_1-1716214598121.png

 

1.JPG

 

So I've never get to the actual add a row into a table step because I am stopped on For each step.

 

3.jpg

Those are my current issues + Attachment ID isn't an increment number, not sure how to approach this fact as well.

Thank you in advance for your assistance, I am not there yet, but without you I am certain I couldn't get even that far.

Ja18

Ja18
Helper I
Helper I

Hi @Nived_Nambiar 

I've managed to adjust the flow to be working, I have added variable reflecting table name and I just skipped the idea with incrementing ID column. All works fine with just one issue.

Ja18_0-1716818540307.png

 

The problem is that I have the loop inside the loop and the number of added rows is multiplied by the number of existing tables, so for example, if flow have saved 2 attachments and this is 4th day of the second flow running I have 8 rows added to my table instead of 2. Could you please help me with this last thing? I would be really grateful.

Ja18 

Hi @Ja18 

 

I hope the highlighted loop has no connection with the actions present inside it as it is just looping through list of tables right ?

Nived_Nambiar_0-1716828681299.png

 

if yes- just place the action add row to table outside the for each loop and delete that loop then - issue will be resolved 🙂

Nived_Nambiar_1-1716828771768.png

 

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

 

Ja18
Helper I
Helper I

Hi @Nived_Nambiar ,

I would never do this without you. Thanks a lot.

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!

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.    

Summer of Solutions | Week 3 Results | Win free tickets to the Power Platform Conference

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  

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 (3,876)