1

I have two list: Members / Members Training

A Member's 'Status' can be Active or Inactive.

The Member's Status column appears in both lists, but is only updated in the Member list, by Power Apps.

Is there a way to make the 'Status' column in the Members Training list a Lookup column that will automatically pull the 'Status' of each Member (based on the their UnqID - a column on both lists), and update that Status in the Member's Training list every time it is changed in the Member list?

0

I got my accounts crossed. Posted a response to you comment as another questions.

Your suggestion worked.

However, the Update item in the flow is forcing (as a "required" field; not my choice), for some reason; I'm not even using in in my work.

This is forcing the Update to update the wrong items because the SP Id no's are not the same for each Member on both lists.

How do I disengage the forced 'required' so I can use a UnqID I created, that is the same on both list for each member?

0

The way I would handle that is not by having a lookup back to the Members list, but by creating a Flow (Power Automate) that kicks off when items in the Members list are updated, and then finds the item in the Members Training list that corresponds to the "member" that just got updated (via a unique ID, if you have that, or maybe by member name, or some other way to find the right "training" item), and then just update the Members Training item's Status to match the Members item Status.


Note: When I was originally answering this question, I misspelled OP's UnqID field as UniqID. I have tried to correct that misspelling where I could in the text here, however I realize it is also misspelled in some of the earlier screenshots, which I cannot correct.

Update - in response to your posted answer here (which should be a comment):

Ah, I see what you mean, the Update item Flow step requires the use of the built in SharePoint list item ID. So, what I would try in this situation is, before the Update item step, insert a Get items step that you use to get the correct item using your custom UnqID field. Then you will have the correct item from the Members Training list, and you can use its regular ID value in the Update item step.

Here's a screenshot of what I came up with. This is untested, so you may have to play with the syntax a little bit, but you can see how you can set up a filter query to get the item from the Members Training list by checking that the UnqID is equal to the dynamic value of the UnqID field coming from the item that was updated:

Screenshot of "Get items" flow step

Then, you can use the ID of the item retrieved in the Get items step in the Update item step:

Screenshot of "Updateitem" flow step


Edit to address your second comment:

This still forces me to use the SP ID and only updates the single item associated with ID.

Yes, exactly. The idea here is that we are accepting the limitation that the Update item step requires the SP ID. We don't know the SP ID of the item we want to update in the Members Training list. We do know that we want to update the item where the UnqID value matches the UnqID value of the item in the Members list that was updated. So, we use the UnqID value of the item that was updated in the Members list to find the matching item in the Members Training list in the Get items step. Once we have that item, we can get its SP ID off of it (which we did not know before), and then use that SP ID in the Update item step.

...only updates the single item associated with ID.

So wait, are you saying that there might be more than one item in the Members Training list that have the same UnqID value? That was not made clear in your original question, I had assumed that UnqID was... unique. In both lists. If that is not the case, then what you will have to do is remove that Top Count = 1 from the Get items step (setting top count will restrict the results to whatever number you specify, setting no value will return all results of the query), and then loop through the results so you can update all of the matched items.

As far as your struggles with the filter query, ODATA filter queries are generally constructed like this:

[Field Internal Name] [comparison] [Value to check]

where the [Field Internal Name] is the internal name of the field we are trying to check in the list where we are looking, so in this case, the internal name of the field in the Members Training list. (As far as the comparison syntax, there are some exceptions when it comes to certain options like startswith or endswith for strings... I highly recommend you read through the ODATA spec to understand what options are available and their syntax.)

So, assuming your UnqID field's internal name is in fact 'UnqID', we can see why the query

CT0000=CT0000

is a bad query. That query is telling SharePoint to look for a field with the internal name of CT0000, and of course there is no field with that name. Secondly, it uses the = sign, when in an ODATA query you have to use the letters eq to represent "equals", as I have shown in the screen shot I provided. (Again, I highly suggest reading the ODATA spec I linked to above in order to learn the syntax.)

Similarly, for the query

UnqID=UnqID

even if the = was a correct eq, it is telling SharePoint to look for an item where the field UnqID has a literal value of "UnqID".

From the example value you have given, a valid ODATA filter query would be

// fieldname comparison value
   UnqID    eq         'CT0000'

Now, I have added extra spaces in there to make the parts line up with my comments. You should only have single spaces between the field name, comparison, and value. And also notice, for a text field, I enclose the value I want to check for in single quotes. This allows for the presence of spaces, if there are any in the string you are trying to match.

Now, going back to my original screenshot of the filter query:

Screen shot of filter query line

While that may look at first glance like it says

UnqID eq UnqID

notice that the first UnqID looks like text entered into a text box, but the second UnqID looks different, it has the Flow SharePoint icon next to it, and has a bit of a greenish background. What this is indicating to you is that this is a dynamic value. What is a dynamic value? It's a way for you to tell Flow that you want to get the value of a certain field from a SharePoint item passed into the Flow.

In this particular case, what we are telling Flow is to get the value of the UnqID field from the SharePoint item that was output from the When an item or a file is modified step, which, in this case, would be the item in the Members list that was modified.

So, if the item that was modified in the Members list has a UnqID value of "CT0000", this:

Screen shot of filter query line

would translate to

UnqID eq 'CT0000'

and if it had a value of "CT4567", it would translate to

UnqID eq 'CT4567'

both of which are valid ODATA filter queries to use against the Members Training list to try and find those items where the UnqID field matches those values.

Now, in the last screenshot, of the Update item step, in the "Dynamic content" popup window, you can see two sections, each with an ID option available. But you will notice that each section has the title of the step in the flow they represent. That is why I crossed out the section with the title When an item or a file is modified, because if we chose that ID dynamic value, it would be giving us the SP ID of the item that was output from that step, which, again, is the original item in the Members list that was modified. We don't want that ID here. What we do want is the SP ID of the item that was output from the Get items step, which should be the item that we retrieved using the filter query, so it should be the item in the Members Training list that has the UnqID value matching the UnqID value of the item that was modified in the Members list.

I hope that makes things a little more clear, as to how this all would work.

And again, you seem to imply that there may be more than one item in the Members Training list that would match the UnqID of an item modified in the Members list. If that is the case, as I said above, you need to clear the Top Count value, and you will need to put the Update item inside some sort of loop in order to loop through all the items retrieved in the Get items step, and update all of them one by one.

I am not going to get into the specifics of looping through items in a Flow in this answer, but I am sure you can find plenty of resources on the internet to help you figure it out.


Another update based on further comments:

You do not need to use the Get items step to get the item that was updated in List 1 (Members). That item is already provided to the Flow as output from the When an item or a file is modified step. The Flow should be set up as follows:

  • Use the trigger When an item or a file is modified. Set that trigger up to be connected to List 1 (Members). That way, when an item in List 1 is modified, it will trigger the Flow, and that step will output the item that was modified in List 1 to be available in the Flow.

Screenshot of Flow trigger

  • We want to update an item (or items) in List 2 (Members Training). We know we need to know the built in SP ID of those items in List 2 to be able to use Update item, but we don't have those yet. We know that we want to update the items in List 2 that have a matching UnqID value to the item that was modified in List 1. Luckily, we already have that item available to us as output from the When an item...is modified trigger. So all we need to do is use the UnqID value from that item to look up the items in List 2 (Members Training) that have a matching UnqID value.
  • Therefore, we need to add a Get items step that looks up items from List 2. In the Filter Query portion of the step, we tell it to look for items where the value of those items' UnqID field is eq to the dynamic value of the UnqID field of the item that was modified in List 1, which is the item that was output from the When an item...is modified trigger.

Screenshot of Get items step

  • Now we should have the items from List 2 that have the matching UnqID value to the item that was modified in List 1. So we need to loop through those items and use each of their built in SP IDs to be able to update their Status values. To loop through the items we need an Apply to each step, with the Update item step nested inside The input to the Apply to each should be the list of items output from the Get items step, since those are what we want to "apply to each" to.

Screenshot of Apply to each step

  • Then we click "Add an action" in order to add the Update item step inside the loop. We want to update the items in List 2 (Members Training), so select that as the list, and we use the built in SP ID of the items returned from the Get items step as the ID of the item to update, because these are the items that match the UnqID of the item that was updated. Note that at this point we no longer have to refer to the UnqID field, we have used it for what we needed it for, it is no longer relevant.

Screenshot of Update item step

  • Now, as far as what we want to update, we want to update the Status column, but we want to update it to the value of the Status value of the item from List 1 that triggered the workflow, which was output from the When and item...is modified trigger step. This is available in the "Dynamic content" window, but note that you may have to scroll very far down in the Dynamic content window in order to get to those values.

Screenshot of selecting Status value

In the process of getting these screenshots, I actually built that Flow and tested it, and it works.

To address one other thing from your comments:

When I try UnqID eq (or =) ID in Get item I get a "Enter valid Integer" error.

Yes - why would you want to make that comparison anyway? You are not trying to find items whose UnqID value matches the built in SP ID value. I am assuming that none of your UnqID values would match the built in SP ID anyway, so that comparison really makes no sense.


Edit in response to the most recent comment on 8/28:

I've used BOTH the " = " and the letters " eq ". BOTH kick back errors.

I can assure you that in regards to which of these to use, the letters eq are the correct choice, and if you are getting an error when using eq instead of =, the error does not have to do with the fact that you are using eq, it is an error with something else in the query.

And in fact, based on some of the details you posted in your comment about the error message, we might be able to figure out what it is:

UnqID eq '@{triggerOutputs()?['body/Title']}' Error: Column 'UnqID' does not exist. It may have been deleted by another user.

Aha! Remember back in one of my previous updates when I said

...filter queries are generally constructed like this:

[Field Internal Name] [comparison] [Value to check]

where the [Field Internal Name] is the internal name of the field we are trying to check... So, assuming your UnqID field's internal name is in fact 'UnqID'...

It looks like maybe that assumption was wrong. If we look at the detail of that filter query from your error, we can see that when you selected the UnqID dynamic value, under the hood SharePoint/Flow generated a small bit of code for itself, which it uses behind the scenes, and the code it generated uses the internal name of the field it is looking at when getting the dynamic value of the UnqID field from the item that was updated:

triggerOutputs()?['body/Title']

The triggerOutputs() part I think is fairly self-explanatory: it is the output of the trigger of the Flow, which in this case is the When an item...was modified step, so that's the item from the Members (List 1) list. But the field that it's looking to get a value from? ['body/Title'] It is trying to get the value of the Title field. What this tells me is that you probably renamed the default SharePoint Title field to have a new display name of UnqID in the Members list.

Did you do the same in the Members Training (List 2) list?

We can at least deduce that the field you see as UnqID does not have an internal name of UnqID, because when trying to find that field using an internal name of UnqID in your filter query, you are getting the error

Error: Column 'UnqID' does not exist.

One very important thing to know about SharePoint - when you create a column/field, then name you give it at the time of creation becomes the internal name of the field. If you then go back and edit the field and change its "name", you are only changing the display name, what you see in the UI. Behind the scenes, the internal name stays the same. The built in SharePoint Title field initially has both an internal and display name of "Title". But if you go and change that, while you will see something else in your list and forms, behind the scenes the internal name is still Title.

How can you find out what the internal name of a field is? Pretty easy:

  • Go to the list where the field is that you want to check.
  • Go to the List Settings for that list.
  • In the "Columns" section, click on the field you want to check, as if you were going to edit the settings of that field.
  • Look in the address bar of your browser. At then end of the URL you will see the query string parameter Field=[some name]. Whatever the [some name] value is is the internal name of that field.

Here is a screenshot of what that looks like if I look at the default SharePoint Title field:

Screenshot of URL

So, what you need to do is go to the Members Training (List 2) list, and find out what the true internal name of the UnqID field is, and whatever that is, use that in your filter query. So, if it happens to be the Title field was renamed and the internal name is "Title", then your filter query will look like this:

Screenshot of filter query

If the internal name of the UnqID field in the Members Training (List 2) list turns out to be "Kumquat", then your filter query will look like this:

Screenshot of another filter query

The important thing to remember is that the first part of that filter query has to be the internal name of the field you are trying to check in the list you are trying to query (in this case, the Members Training/List 2 list).

20
  • Yep, makes perfect sense. Just not sure how to make all the pieces fit in automate. Which 'things' to choose throughout the flow. Working on it...really hard, though. :-)
    – Curtis
    Aug 24 '20 at 18:31
  • 1
    @Curtis updated my answer. Aug 26 '20 at 14:46
  • This still forces me to use the SP ID and only updates the single item associated with ID. When I attempt (several different attempts), I'm getting the following error; that implies the UnqID CT0000 is a column which it is not, or that it doesn't exist; which it does, on both list. Its 'my' test UnqID. The expression "CT0000=CT0000" is not valid. Creating query failed. clientRequestId: In the Filter Query of the Get Items I have tried UnqID (the column I want from the list as the actually unique filtering tool); just ID; ID=ID; ID=UnqID; UnqID=UnqID.
    – Curtis
    Aug 26 '20 at 23:21
  • @Curtis updated my answer with more explanation. Aug 27 '20 at 14:42
  • Yes, the UnqID may appear once or multiple times on the Member Training list. I made the changes suggested. This is the response I got with the changes in place. The expression "UnqID='KM3100'" is not valid. Creating query failed. clientRequestId: ...... serviceRequestId:.....
    – Curtis
    Aug 27 '20 at 16:39
0

Tried to figure our where to add these without creating another Answer.

I don't see the 'Active' Status automatically updating on the Member Training List. Though it is updating on the Member List, and the flow says the run 'succeeded'

Flow Image 1

Flow Image 2

1
  • added comments in the chat. Aug 29 '20 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.