How can I query a sharepoint list on a different site from a site workflow?


Badge +3

Hello,

I've been assigned to work on the following Nintex workflow and I'm really hoping you guys can help:

  • List in question that requires to be Queried is an imported spreadsheet into a new SharePoint List on Site A
  • A new site workflow on Site B created with variables to be completed by the user (all required and exist in the imported list as columns in Site A)

185034_pastedImage_2.png

  • The idea is: Once a request has been submitted. Query the list's specific items in Site A and check if:
  1. User exists but has a disabled account? Then IT gets a task to update item to enabled
  2. User exists and has an enabled account? Just a notification stating the case
  3. User is new? Add information entered in these fields to the list in Site A.

Sorry for the long post and thanks for any help!

I've attached the workflow as of now - It's a mess (sorry). I've been trying different scenarios and none of them seem to work.

But it should serve as a good reference to what I would like to achieve exactly.


12 replies

Badge +16

I am unable to look at your workflow but here is how I would approach it.

  • Query the list and find item where user exists.  Return ID and status of the employee (ie enabled/disabled) in the query into an ID variable and text variable with no default values.  Let's call them vIDUser and vTextStatus.
  • Set a condition action to check if vIDUser is blank.
    • If it is then you know it is a new user entirely.  Then you can add the item to Site A.
    • If it is not, then you know the user exists, so have another set a condition to check the status = enabled.  If it is, then email that user exists.  If not, then set task to update item.
Badge +3

Hi Cassy,

Thank you so much for responding!

This might be a silly question, but how do I find item where user exists in a QL? If it is by adding a filter, the issue for me is that I don't really have one item as a unique identifier. As you can see in the screen shots, there are: Given Name and Family Name fields which would be my first go-to. Do I then add 2 filters in the QL?

Badge +16

Yes you can add multiple filters to suit your needs. How many users will be inputting data on that form? I think it will be hard to find a match on those fields as no two users will enter the data the same. The email address should be unique to a user but I can't work out if that is the email address to be created or not?

Badge +3

Yes! Actually the Primary Email field was my other go-to. It would probably be the only other 1 unique identifier at this point.

In this particular scenario (check if user exists and is enabled), the email already exists in the list.

Would that be the ID variable then?

Badge +16

Yeah I would say that's the best one to match on. Return the list item "ID" field and whatever field you capture if the user is enabled or disabled. Then you can do your checks with that data as per my previous response.

Badge +3

Okay, so I ran the workflow for a test but it seemed to have errored at the QL stage with this message: "Failed to query list data. Cannot find a field with internal name 'mariam.kabesh@ssi-corporate.com' on list."

This email address is on the list though - It's mine actually and I added it for testing purposes.

185149_pastedImage_0.png

185156_pastedImage_1.png

This is how my QL Action looks like at the moment with vID = List Item ID and and TextStatus = Single line of text.

Badge +16

Your variables are right but the fields they are being assigned to are wrong. You need to type "ID" in the field text box and click add, then do it again with "account status". You can try run now at the top to check the query but you will have to replace item property : primary email with the email address you typed above.

Badge +16

These are the names of the fields from site A you want to pull back.

Badge +3

Sorry to bug you! I've been getting the same error but on the Account Status portion :/

Just to make sure I understand correctly: I should type in "Account Status" in the field box and assign it to TextStatus variable, yes? (So sorry!)

Badge +16

Are you able to screenshot the list settings of the list you are querying (ie list A that holds the users and their status of enabled/disabled) and I will be able to tell you exactly how to configure your query list action.

Badge +3

Hi Cassy,

Sorry for the late reply and thank you again for your help!

After battling with this workflow for a week, I was able to finally get it to work as I want. The QL was the trickiest part due to the fact that it is on another site.

I thought I'd share with you and everyone what the final product looks like:

This is only a part of the workflow - it's the most important though:

1.png

I configured the QL action item on the actual site where the list resides, then copied the CAML query to the QL on the other site where the workflow is.

The requestor's Primary Email was my unique identifier in this case and I used it for validation of users.

I used "Create Items in another site" to add the entered fields to the list. I also used it to create a Discussion item on yet another HR site, which was pretty cool!

This is what the QL action item looks like:

2.png

Important note:

When adding the Fields, make sure they are typed/ written the exact same way as they are in the CAML query. This was a major issue at first for me, as entering the fields using the Variable Reference button did not cut it. It looked different and so I just typed the variable in the Field box myself. Example: I typed the Primary Email as it appeared in the CAML query "Primay_x0020_Email".

I hope this helps anyone facing a similar issue!

Badge +16

So glad you got it sorted in the end and thanks for posting the final approach! Be sure to mark an answer as correct to close out the question happy.png

Reply