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
Super User
Super User

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.
 
 

______________________________________________________________


Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a thumbs up.

I answer questions on the forum for 2-3 hours every Thursday!



View solution in original post

Rhiassuring
Super User
Super User

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?

______________________________________________________________


Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a thumbs up.

I answer questions on the forum for 2-3 hours every Thursday!



View solution in original post

10 REPLIES 10
Rhiassuring
Super User
Super User

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

______________________________________________________________


Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a thumbs up.

I answer questions on the forum for 2-3 hours every Thursday!



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

 

RLira1992_1-1656979849281.png

 

 

 

Rhiassuring
Super User
Super User

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.
 
 

______________________________________________________________


Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a thumbs up.

I answer questions on the forum for 2-3 hours every Thursday!



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

Rhiassuring
Super User
Super User

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.

______________________________________________________________


Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a thumbs up.

I answer questions on the forum for 2-3 hours every Thursday!



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

What can it be?

Rhiassuring
Super User
Super User

Which type of column is Disp_Fabric?

______________________________________________________________


Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a thumbs up.

I answer questions on the forum for 2-3 hours every Thursday!



RLira1992
Regular Visitor

choices

 

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

Rhiassuring
Super User
Super User

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?

______________________________________________________________


Did this answer your question? Please mark it as the solution.
Did it contribute positively towards finding the final solution? Please give it a thumbs up.

I answer questions on the forum for 2-3 hours every Thursday!



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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,859)