Update calculated column based on values from different list

[TC22]

Hello!

I have two lists in sharepoint. List A is a list that contains a choice column that can be edited by users and multiple rows may be assigned to one user. 

list B is a list that contains unique values of users that is meant to display how many choices they have not yet completed. 

my flow is supposed to update List B when a choice is made in List A. For example, if a user selects yes in the choice column of List A, the count of choices not complete updates to decrease from 2 to 1. 

my flow works fine when it comes to updating user names or adding a user newly added to List A. I have a condition using a length expression to achieve this. If yes, update list. If no, create item. 

the problem I’m having is I cannot get an accurate count of choices. I have a calculated column in List A, that displays a 1 for any choice that has not been made and a 0 if a choice has been made. I tried having my flow sum the values of the calculated column each specific user, then use that value for in the update item block, however the numbers are not accurate. 

I’ve tried initializing a variable to increment and totaling an array using xpath/xml function but I’ve got nothing to work correctly yet. 

any ideas on if this is possible?

thanks in advance!

[Nived_Nambiar]

Hi @TC22 

would it be possible to share a screenshots of List A and B if possible ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[TC22]

Hi Nived,

Here are examples of my lists. 
List B are unique values from List A. If a user is added the create item action in the flow will add the new user.
Based on List A, the values in the column "Not Completed" for list B should be updated by the flow to show as:

Test1: 1
Test 2: 1
Test 3: 2

[Nived_Nambiar]

Hi @TC22 

So as you said, you tried to take the sum of calculated column sum as to see the list of not completed for each user, what was problem in achieving that ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[TC22]

the value is displaying as the aggregate running total for the full list rather than the individual user. For example, the not completed column is showing as:

test 1: 1

test 2: 2

test 3: 4

I have since abandoned trying to sum the calculated column and am now trying to sum the occurrences of a specific value (as I really only need to know the count of incomplete), but am running into the same result. 

My flow is as follows:

1. manually trigger a flow

2. Get items from List A where choice column equals “Choose an Option”

3. Initialize integer variable named “Count”

4. Apply to each

• output = List A (Value)

4a. Get items from List B

4b. Condition

length of List B body/value outputs is not equal to 0

if yes:

apply to each 2

• output = list B (Value)

condition action:

user email from list A is equal to user email from list B

if yes:

1. increment variable count

then 

2. Update item List B

with count input for “Not Completed” column

[Nived_Nambiar]

Hi @TC22 

Let me share an updated logic

1. manually trigger a flow

2. Get items from List A where choice column equals “Choose an Option”

3. use select action to take the user email column from value dynamic content to an array

4.use that array to loop through using apply to each  after applying union function so to maintain a list of unique elements in array.

Inside the loop do the following:

 1. use get items from List B with an Odata query which checks User email eq 'current iterating username'

2. use condition to check length of output from get items from list B is not equal to zero.

if Yes: use apply to each on List B value and then update the item with Not Completed Count equals to sum of calculated column of list B output

if no, create an item in List B with required details.

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[TC22]

Hi, Nived!
I'm stuck on how to configure the "If yes" to sum a calculated column. I no longer have a calculated column as there is no 'sum' option in PA. I changed the flow to increment variable to count 'choose an option' values. The first two actions in my flow are:
1. Manually Trigger a Flow
2. Get items from List A where Choose Option equals 'Choose an Option'

My results are being displayed as a running total:

Not Completed:

Test 1: 1

Test 2: 2

Test 3: 4

I need the results to display the accurate count for each user such as:
Test 1: 1
Test 2: 1
Test 3: 2

 

 

 

[Nived_Nambiar · Accepted solution]

Hi @TC22 

I hope this logic would helps !

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[TC22]

Here are my flow results

 

[Nived_Nambiar]

Hi @TC22 

try the appraoch which i have shared !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[TC22]

Unfortunately, that did not do the trick either. My outputs after running were 4, 8, 12

[Nived_Nambiar · Accepted solution]

Hi @TC22 

i think i have forgotten one step , to reset count to zero at the end of every iteration, use set variable after update item action within loop to set its valu again to zero.

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[TC22]

That did the trick! Thank you!