Site Workflow on External List Won't Run

[dgraves]

I am attempting to build a very simple Site Workflow that will email the point of contact for each item in an external list that's in SharePoint (on-premise SP2016). We are running Nintex for SharePoint Standard. 

• We have a SQL database external to SharePoint that is used to store HVAC temp/humidity readings. It has a few tables and a few views.
• I created a BDC in SharePoint so I can add an External List in SharePoint. It worked great. The list showed up in SharePoint. It is named “Out of Tolerance” (OOT).
• One of the views in the database filters on temp/humidity that is out of tolerance. This is the list that I had added to SharePoint. So it’s a small list of only 37 items.
• The table that was used to create the view DOES NOT have a key identifier (ID) column.

I was able to select the external list in the “Query List” action and my workflow seems complete and I set it on a schedule but it never runs. It doesn’t error or stall, it just never runs and I don’t see anything at all in the SharePoint ULS logs. I’m wondering if it’s not running because the list doesn’t have an ID column? And unfortunately there’s not a column in the table that would ever have a unique value. The columns that you would think would be unique are “Building” and “Room” but there may be several items in the list with the same building or room as there are several readings from throughout the day. Has anyone ever run into this scenario? Could the lack of ID column be why the workflow doesn't even attempt to run? 

Here's the overall workflow and below are some screenshots of the first Query List action and the For Each action.

[kunalpatel]

@dgraves.....First of all log your collection in log history to see if it's empty or not. If not then I can suggest below approach:

1. Since you want to send email notification for each items, you just use 1 query list.
2. Get all the fields you need from external list for your email and store it as collection in query list action
   1. For e.g. I need col1, col3, col5 & col6 for email notification, in query list I'll select these fields and store values in respective collection variables
   2. Then create a num_Index w/f variable....default value will be 0
   3. In For loop, use col1 collection and num_Index and:
      • Using collection operation actions for remaining collections and num_Index, get individual values and store it in appropriate w/f variables
      • Send notification email

[dgraves]

Thanks for the quick reply @kunalpatel . In order to log my collection in log history, I'm assuming I just click on Common in the Query List action and type in some text in the "message to log on completion" field. If this is the case, I just did that and waited for the scheduled time. It never runs and nothing is logged in the Nintex workflow history. 

In my initial workflow design I added the fields in the Query List that is within the For each loop and stored them in Text variables for the email. I hadn't thought of using a collection for each field and putting it outside of the loop. I will try that tomorrow morning. I'm not experienced with using "Collection operation" actions. What would I do with this action? I see there are many options available. 

[kunalpatel]

@dgraves ....There is a seperate action called "Log History". After you use query list and get collection(s) you add that action and in configure just select the collection w/f variable.

When you want to manipulate collection variables, you use collection operations. There are different options on manipulating collections in collection operation action. This is a help document on it.

FYI...Also when you want to know how to configure any action, all you have to do is drop it in your process --> Configure ---> Help. It will give explaination for that particular action. It's the good thing I like about Nintex manual.

[dgraves]

I added the Log History action after the Query List and put some text in it. It does not get logged in the history list. And in the site workflows schedule it shows "Not run" in the Last Run column. It doesn't even seem to try to run. But site workflows work on other sites. This is the only site workflow that I've ever tried with querying a list in SharePoint that is an "external list".  

[dgraves]

I opened up a case with Nintex support. I'll post the resolution here when I have it.

[kunalpatel]

@dgraves....since your data is in SQL...did you try to use SQL query workflow action? I never had a requirement where we had to use SQL query action, so I'm not sure about it. 

[dgraves]

Unfortunately, we have the "Standard" Nintex license so we don't have the SQL query action.