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

[RLira1992]

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?

[Rhiassuring]

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

[RLira1992]

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

[Rhiassuring · Accepted solution]

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.

[RLira1992]

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

[Rhiassuring]

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.

[RLira1992]

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

What can it be?

[Rhiassuring]

Which type of column is Disp_Fabric?

[RLira1992]

choices

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

[Rhiassuring · Accepted solution]

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]

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!