cancel
Showing results for 
Search instead for 
Did you mean: 
darogael

Update a secondary SharePoint list using Flow

Description


In this blog we are going to walk through the steps of building a custom Flow using SharePoint list. When a new item in the primary list is created the Flow will make updates to a secondary list items as well . Both the lists have a similar column which is used to build relationships.

Lists and columns
For this demo we have a Parent list and a Child list. Here are the columns of each list

 

Parent list - This list is used to record the make and model.

 

Capture1.JPG

Capture1a.JPG

 

Child list - This list is used to record the number of accessories available for a certain model.

 

Capture2.JPG

 

Capture2a.png


When a certain make and model is added as a new item to the Parent list, a Flow will run which looks for the same model in the Child list and will subtract the Keyboard quantity by 1.

 

Flow

 

Step 1: Select SharePoint

 

Capture3a.JPG

 

Then select 'SharePoint - When a new item is created'

 

Capture3b.JPG

 

Step 2: Add the site address and list name of your primary list. In this demo, we are using the Parent list.

 

 Capture3.JPG

 

Step 3: Click on New Step and select 'Add an action'

 

Capture4.png

 

 

Step 4: Select the 'SharePoint Get Items action' and add the site address and Child list.

 

Capture5.JPG

 

Step 5: Select New Step, click on More and select the 'Add an apply to each' action.

 

Capture6.JPG

 

Step 6: Here select value from 'Get items'

 

Capture7.JPG

 

Step 7: Select 'Add an action'

 

Capture8.JPG

 

The action we are looking for is called 'Data Operations - Compose'. You can simply type in Compose to search for this action.

 

Capture9.JPG

 

Step 8: This is a critical step. YOU MUST FOLLOW THIS STEP WORD FOR WORD IN ORDER FOR THIS TO WORK. The key point to remember is TYPE THE COLUMN NAME MANUALLY and not use the dynamic content that is available.
In this demo we will be subtracting the keyboard quantity value by 1. Hence go to the Child list and get the accurate column name for keyboard. In this case the column name is 'keyboard'. Below is a screenshot that shows the column name. Pay attention to the uppercase and lowercase. (The demo video available at the bottom shows how you can find these column names)

 

Capture10.JPG

 

Now, in the compose action type in the following "@sub(item()?['Keyboard'],1)". You must add the double quotes as well as shown below. The output of this compose will be available as 'Output' which you will see below.

 

Capture11.JPG


Step 9: This is where we add the condition which will look at the model of the item in the Parent list and compare it against all the items in the Child list.

On the left, select Model from 'When a new item is created'.

 

Capture12b.JPG

 

Keep the middle condition as 'is equal to'

 

On the right, select Model from 'Get items'

 

Capture12c.JPG

 

Here is what the fully populated condition looks like. As you can see, here you cannot identity which model comes from which list.

 

Capture12a.JPG

 

Step 10: In the 'IF YES' section. Add the Update Item action. Add the same site and list name you put in Step 4. The ID, Title and Model are the dynamic content from the 'Get Items'

 

Capture13.JPG

 

In Keyboard, add Output which is the calculated value.

 

Capture14.JPG

 

Note: You have the option to change the 'Output' display name to something meaningful such as 'Subtract'

 

Step 11 (optional) : This step to send out an email notification is option. In this step use the dynamic content from 'Get items'

 

Capture15.JPG

 

Video
This video will walk you through the above steps in detail and ends with demonstrating how the flow works.

 

Helpful links
- Workflow Definition Language
- Getting started with Microsoft Flow 
- Guided learning 

 

Conclusion
This blog has successfully demonstrated how a medium to advance level workflow built in SharePoint Designer can be rebuilt in Flow. The path followed in Flow is different from SharePoint Designer, however, the end result is the same!

Comments
Anonymous

Is it possible to retrieve the parent item if a Lookup column is used in the child list?

 

 

Hi Justin,

Yes, it is possible to retrieve a parent item if a Lookup column is used in the child list. Use the dynamic content to make sure you select the correct column. If you run into any problems then post a question in the community.

Business case is to just update an existing item in the Parent list with an existing item in the Child List (the lists are in 2 different subsites of same collection). Can I use a created "Original ID" value in one list that matches the "ID" value in another list as unique identifiers (see picture) to select and update an item in the Parent List (eg. “Overall Status” field)?  I have tried several variations of this blog with no luck and truly appreciate any suggestions (I surrender) Parent and Child Synch Example.png

Helper I

Hi,

 

Is it possible to update lists when the lists are not identical?

Yes, I do that quite often - just as long as you have a unique identifer to select the item to update. I now create a unique identifier populated a column called orig_id and use that to synchronize updates. 

Hi,

 

Great instructions thank you, I want to do basically this exact thing with a slight modification instead of subtracting by 1 each time I would like to Subtract by the number in the "Quantity" column in the parent list is this possible using this method or would I have to go about it a different way?

 

Thanks in advance.

 

 

Not sure if you figured it out yet, but yes, you can use and expression along with "Quantity" field as an operand instead of 1. Hope that helps.

Thanks for the Reply, sadly no I am very new to this and I havent worked it out yet when I try using the 'Quantity' field to subtract by I get an error saying the sub function only works on integers not other fields 😕 ": 'The template language function 'sub' expects its second parameter to be an integer or a decimal number"  

You could create a compose action (as a step prior to your subtracting a day action) and use the int(value)  expression  -  using Quantity and this will change the data to an integer if possible; for your scenario, I believe this will work:   int(Quantity)

 

Then replace Quantity in the sub expression you tried with the output from the compose action.  This should resolve the error you got.  I have had to get really creative over the past year with compose and select while waiting for new Flow capabilitites. Please let me know if that works - there is definitely a solution to your requirement and hopefully that does it.

 

Cheers,

Todd

Anonymous

Hi,

 

I have a list of clients with their relavant account managers (which is a lookup column).  When someone adds an item to a different list I would like the account manager to be pulled from the client list to the new list.  Is there a way to do this that is similar to flow you showed above?

 

 

Thanks!

Yes, it would be similar, but would want to add a flag - based on what you described; however, would need more details to assist. Is the requirement for the manager was to be removed from lookup list or just have it show them as already being used, so that they could not be selected again?

 

 

Hi, Thank you for this post. I was able to create it in my environment but I run into an error in update item, under condition: I modified this a little bit to have it add a column from my child list and the input displays the number from the child list column but it tells me the input string is not formatted correctly. any ideas?string 1.PNGstring 2.PNG

 

 

this is way cool.  I am trying to use to update both due date and title when changed. I have it working but it is updating all child items due date no matter what the parent is. Can you help make it only update the due date of the child associated with the parent and not the other parents child items?2018-09-12 14_34_24-Edit your flow _ Microsoft Flow.png2018-09-12 14_36_55-Edit your flow _ Microsoft Flow.png2018-09-12 14_36_55-Edit your flow _ Microsoft Flow.png

Learned a lot from this post!! Thanks.

In my case i didn't have to work with the "Compose" step that states "Sub(item... etc)".

I constructed a filter using "Compose" and used the Output in the Get Items step (advanced options.

If your list will get more than 100 items - be sure to update the filter limits of the Get Items step i Settings 

I'm warming up to "Flows" vs Sharepoint designer 🙂 

ComposeFilterGetItems.jpg

 

ComposeFilterGetItemsLimits.jpg

Thank you!

Just tried this workflow in Sept. 2023 - it still works. To those browsing here, I recommend you go straight to the YouTube video and follow is from there. 

A key thing to understand is that the Compose input:
"@sub(item()?['Keyboard'],1)"

refers to the item() as the first argument.
That means the item() within an Apply to each action. Outside of  Apply to each , the item() is a null, which is why I got this error when trying to do the compose before the  Apply to each action / loop:
The template language function 'sub' expects its first parameter to be an integer or a decimal number. The provided value is of type 'Null'

Thanks very much for this post.



 

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/