cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RLira1992
Regular Visitor

relate sharepoint lists and calculate missing parts quantity for each item in the other list

I have two sharepoint lists that can be related through the Title column, in one list I have missing parts data and in other registered engines (Title), how do I calculate the missing parts for each Engine (Title) of the other list and this value be updated in a missing parts column?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Rhiassuring
Community Champion
Community Champion

That helps a lot! Thank you!

All you're going to do is this:

 

1) Create a scheduled flow (or manual - depends on how you want to run it.) 

2) Your first action will be Get Items from List 1 

3) Next, Create another Get Items, and point that at List 2; filter query it to bring back only where (Title eq 'Title') AND (Disp_Fabric eq 1) (where the latter Title is dynamic content from List 1). This will turn it into a loop! We want that! That's good.

4) Create an "Update Item", point it at the current item (in List 1) and update the Quant_Parts with an expression that looks at the length of the get items from List 2. Would look like this:  

length(outputs('Get_items')?['body/value'])
 
That should loop through and update all of them for you. You might need a catch in case of 0 returned, but, I think it will just catch it.
 
 

View solution in original post

Rhiassuring
Community Champion
Community Champion

Is "Disp_Fabric" the internal name of the field or the display name? (Or are they both the same)?

Also, the Title that's in apostrophes in the first one needs to be dynamic content, pointing to the field called "Title" from List 1. Do you have that set-up? Do you need help with it?

View solution in original post

10 REPLIES 10
Rhiassuring
Community Champion
Community Champion

Hi there, 


I feel like we're missing some info that we need in order to help.


Based on what you have told us, 

Registered Engines

Engine A
Engine B
Engine C

and

 

Missing Parts Data
Engine A

Engine B

Engine C

What other columns are there? Is there a column for each "Part" and then a Y/N on whether it's presently there? And the other list is to compare against?  

Is it like..

 

Registered Engines
Title | Part 1 | Part 2 | Part 3 | 

Engine A | 4 | 2 | 3 |

and

 

Missing Parts Data
Title | Part 1 | Part 2 | Part 3 |
Engine A | 4 | 4 | 4 |

 

(Which would indicate that Engine A needs 2 more of Part 2 and 1 more of Part 3)

 

Or is it set up another way?

Can you give us an example of the two list entries & the ideal output / update?

 

Cheers,

 

Rhia

See if you can understand the flow I need help creating.

 

RLira1992_1-1656979849281.png

 

 

 

Rhiassuring
Community Champion
Community Champion

That helps a lot! Thank you!

All you're going to do is this:

 

1) Create a scheduled flow (or manual - depends on how you want to run it.) 

2) Your first action will be Get Items from List 1 

3) Next, Create another Get Items, and point that at List 2; filter query it to bring back only where (Title eq 'Title') AND (Disp_Fabric eq 1) (where the latter Title is dynamic content from List 1). This will turn it into a loop! We want that! That's good.

4) Create an "Update Item", point it at the current item (in List 1) and update the Quant_Parts with an expression that looks at the length of the get items from List 2. Would look like this:  

length(outputs('Get_items')?['body/value'])
 
That should loop through and update all of them for you. You might need a catch in case of 0 returned, but, I think it will just catch it.
 
 

Thanks, the flow ran perfectly but the values ​​of the Column "Quant_Parts" were updated with value 0 "zero", what can it be?

Rhiassuring
Community Champion
Community Champion

Ah - I think it might be because I mis-read something. Is your Disp_Fabric a YesNo column or a Choice column? If it's a choice column, then the filter query should be "Disp_Fabric eq 'NO'" rather than 1.

 

... And if it's a Y/N column that 1 should've been a 0, that was a mistake.

Yes, i tried it both ways and the value returned was 0 "zero". 

What can it be?

Rhiassuring
Community Champion
Community Champion

Which type of column is Disp_Fabric?

RLira1992
Regular Visitor

choices

 

Get Items 2 : filter .  (Title eq 'Title') and (Disp_Fabric eq 'NO')

Rhiassuring
Community Champion
Community Champion

Is "Disp_Fabric" the internal name of the field or the display name? (Or are they both the same)?

Also, the Title that's in apostrophes in the first one needs to be dynamic content, pointing to the field called "Title" from List 1. Do you have that set-up? Do you need help with it?

RLira1992
Regular Visitor

Thanks a lot! The problem was with the 'Title' column in the list 1, it wasn't with the dynamic content lol. Thanks for the help!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,347)