Answered

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

(0) Share

Report

Posted on by 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?

Categories:

General topics

I have the same question (0)

All responses (10)

Answers (2)

Rhiassuring 8,690 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
RLira1992 18 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

Rhiassuring 8,690 Moderator on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
RLira1992 18 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Rhiassuring 8,690 Moderator on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
RLira1992 18 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Rhiassuring 8,690 Moderator on at

Like (0)

Report
Copy link

Link copied!

Which type of column is Disp_Fabric?

Was this reply helpful? Yes No
RLira1992 18 on at

Like (0)

Report
Copy link

Link copied!

choices

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

Was this reply helpful? Yes No
Verified answer

Rhiassuring 8,690 Moderator on at

Like (1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
RLira1992 18 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No