Unanswered

Update a list with unique values from another list with no duplicates

(0) Share

Report

Posted on by Blackburd

I have been working on this problem for a week. I have tried many different approaches and had minimum success. My experience with Power Automate is not extensive. I have tried to exhaust all of my resources such as the documentation and the forums for power automate.

The goal here is to populate List B with any new unique value that shows up in List A under the Person Field. There are going to be duplicates in List A regularly. List B will be a stats sheet and each time a new user enters an item in List A they will get their own new row in List B. I want no duplicate entries for people in List B. The data type of the fields is set to person and the schema and person details from Office 365 need to carry over for further processing in List B at a later time. I have struggled with this so much I created my own test environment and dumbed everything down as much as possible.

List A:

List B:

The flow:

Starts simply enough, pull both lists and create two arrays with just the person fields

Then cycle through each value of List A.
For each person in List A: create a new filter array from the select of List B.
@Anonymous(contains(body('Select_Person_from_List_B'), items('Apply_to_each_Select_Person_from_List_A')))

The goal here is to get a length() of Filter that is > 0 if there is a match. That way I know that the person is already in List B.

If the Filter Array is > 0 then:

add a new list item to List B.

length(body('Filter'))

Errors I am getting right now:

Error

Action 'Condition_If_Filter_length_is_greater_than_0' failed

Thank you for any suggestions. Vague answers will be pondered but not understood.

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

wskinnermctc 6,519 Moderator on at

Like (0)

Report

It looks like the Create Item is the part that is failing due to trying to add a user to a SharePoint list Person type column with the full "Current Item" in the field.

You should try just putting the email or principal user name into the "Person Claims" field instead of the "Current Item" which is trying to use all of the person's account info.

https://powerusers.microsoft.com/t5/Building-Flows/Create-update-a-Person-Group-field-Update-Item-SharePoint-only/td-p/527869

Was this reply helpful? Yes No
Blackburd 48 on at

Like (0)

Report

This brings me back around to my number one frustration right now. How do I pull the email address value out of the "current item" array which is constructed from a person type data field?

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

In the field you have to put in an expression for the value. So it will be something like:
items('Apply_to_each')?['Email']

You can put the full Apply to Each step name in there as well.

items('Apply_to_each_Select_Person_from_List_A')?['Email']

Below are screenshots I made for something else, but it is similar.

Was this reply helpful? Yes No
Blackburd 48 on at

Like (0)

Report

I changed my create item form to the following:

I am getting updates to the list now but they are blank rows, here is the output:

This is a very long list including:

I feel like my:
items('Apply_to_each_Select_Person_from_List_A')?['Email']

is not digging deep enough to get the email out of the 'body'? I dont know I'm lost here now.

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (0)

Report

Ok it has to do with it being in the Person Array.

I don't know exactly what will work, but try one of these.

items('Apply_to_each_Select_Person_from_List_A')?['Person/Email']

or
items('Apply_to_each_Select_Person_from_List_A')?['Person']?[0]?['Email']

It will be something like that, I've never had to get employee info like this, so I don't have a great example.

Was this reply helpful? Yes No
Blackburd 48 on at

Like (0)

Report

items('Apply_to_each_Select_Person_from_List_A')?['Person/Email']

This updates the field correctly, weirdly enough when applied to the "Person Claims" field, not the "Email Claims" field.

Now I just need to put my array into a "compose" or another array variable I think in order to update the "get items" after each "Create item" that way I'm not adding duplicates after the initial addition of a new name.

Thank you for the help I think I'm in the home stretch now!

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

What type of SharePoint list column is "Email Claims" when looking at the list settings? Is it a Lookup type column?

Also, thinking about your last issue of creating duplicates. I think you need 2 flows. The first flow will be a 1 time use to get all of the names currently in the List A and make a single row for each name in List B.
The second flow will be an automatic flow that is "When Item is Created in List A" then filter List B and add item if it doesn't exist. That way you are only dealing with 1 item at a time.
I would even go so far as to put the List B item ID into a column of List A, so that each person can be easily pulled for other things.

Was this reply helpful? Yes No
Blackburd 48 on at

Like (0)

Report

"Email Claims" was a person type. That was an error on my part. I changed it to a string type and I have successfully updated it now later in the flow. I have solved my creating duplicates problem. It was just a logic problem and best I can describe it, I just needed to label and document things better. It gets very confusing when things just say "value"

Was this reply helpful? Yes No