Nintex Workflow 2013 - Query List Action questions

[#1]

Hi,

I am working on one PoC project using Nintex Workflow 2013. I found the Query List Action cannot work as expected and would like to ask advice. Here is the scenario:

• Create two lists: Order and Order Detail.
• Order has the columns: Title, Order No, Order Date
• Order Detail has the columns: Title, Order No(Lookup column from Order table), Product, LogData(single line of text)
• Workflow Logic
  • When there is new record created in Order list, it will trigger to create a new record in Order Detail with proper lookup column
  • It requires to query Order Detail list to get LogData information

I put a Query List Action and configure as below.

• select the list is Order Detail
• Using filter: Select items only when following is true, Order No is euqal to <ItemProperty: Order No>(this is from current item in Order list)
• Output: specifiy item limit to 1, select field LogData and store into queryResult
• Both <ItemProperty:Order No> and queryResult are logged in history
• queryResult tried Single lin, multiple line, collection and all are empty
• use Run Now to to verify the configuration. Replacing with the Order No value and it works perfectly
• However it return empty when run the workflow. It is verified by history log.

Can anybody provide suggestions on the issue?

[patrick_hosch]

This is due to the filter of the lookup. The lookup field you are trying to filter on doesn't just contain the ID of the item. The value it filters on is actually in the format {ITEM ID};#{ITEMVALUE} which in your case (assuming the first OrderNo reference has the ID "1" in your source list) would be 1;#99999.

My blog post is based on multi value lookups, but it is the same format for single value lookups. String format for multi-value lookup columns in SharePoint | ProjectPoint

To solve your problem you will have to construct the lookup value using build string:

1. Do a query list on the Order list and get the item ID as well as the OrderNo field
2. Then use a build string action to construct the string as follows {ItemID};#{OrderNo}
3. Then use the result of the build string in your original query list action to get the order details

Hope that helps

[#3]

Patrick,

Thanks for the informtion. It does not work with your suggestion.

I use the Run Now function to test and the one with your format return no result.

<Query>

  <Lists>

    <List ID="{212E3043-279D-449D-9A0A-6127BEFB4FE2}" />

  </Lists>

  <ViewFields>

    <FieldRef Name="Product" />

  </ViewFields>

  <Where>

    <Eq>

      <FieldRef Name="Order_x0020_No"  />

      <Value Type="Lookup">19;#45678</Value>

    </Eq>

  </Where>

</Query>

When I change the parameter to actual value(45678) and it did return the result. Please refer to below screenshot.

[patrick_hosch]

oh sorry, my bad. I mixed it up with actually setting the value. In which case you would construct the string.

The solution for your problem is to use the trim function in your query list action. For some reason the value of the lookup column has a trailing whitespace. Which means when you do the query it tries to match {ORDERNO}WHITESPACE which it can't find.

This inline function should solve the issue:

[#5]

Patrick,

I wish I could say the issue is resolved. Unfortunately it does not. Please see below screenshot.

[eharris04]

One thing to do is to ensure that you have a parent/child relationship setup between the two list.  This is done independent of Nintex and through the lists themselves.  The Order number column on the child list should be set up to pull in the "item ID" of the item from the Parent list. When this is done, you will notice on the child list that you can also pull in other fields as well such as the order number.  They do not actually live on the list, but are there as additional metadata pulled through with the connected ID (if you know this, sorry for the repeat).

On your workflow, you want to pull in the "item ID" of the parent and set the child field to that same "item ID".  This is important because this is the best connection you can have. You can create your own keys as well, but its easier to use the one that is there.

As it comes to querying the list, why exactly are you using a query?  I may be wrong on this but if the user is creating an Order and then populating an Order detail, the items won't already exist until you save both records.  Because of such you may be running into an issue where the information is not there to be queried in the first place because the record to be queried is being created by the workflow doing the querying. At least that is how I read it.

So if you are using a workflow to create a new list item, you should have everything you need in that workflow without querying the Order Details list.  The LogData would not exist on create unless that is coming from somewhere else that I didn't see in your description. 

Hope that helps you in formatting your query to work that way you want.  I have experience working the query list action as well, but the above is where I saw a small breakdown in your workflows logic.

[#7]

Eric, I tried to create a new workflow for the following Parent/child list. There is no result returned. I have no idea on any possible wrong configurations.

Order List:

Order Detail List:

Order WorkFlow:

Query List configuration:

Result:

[eharris04]

Sam Ma,

I rebuilt this in my test VM to see what you were trying to do...  Still not sure why you are trying to query anything.  See the steps below.  I may be completely off, but I think you can achieve what you are wanting without the need to query the list.

I have listed out the two list I used and showed that Sample4 was a new order and the resulting Sample4 Detail was the corresponding list item in the Order detail list created by the workflow:

1. Order List - Title, OrderNo, OrderDate

2. Order Detail list - Title, OrderNo (lookup). Product, LogData

Created a workflow as simple as I could.  Workflow runs when new items is created and runs on Order List

1. Set Variable from current items Order Number to workflow variable "OrderNo"

1. Create New Item in Order Detail list - set title to "Title" of current item and added Detail to show up in new list

1. Set OrderNo (Lookup) variable equal to workflow data variable "OrderNo"

Hope that helps.

[#9]

Eric Harris, thanks for your detail information. I might not be able to explain my intention clearly here and sorry for the confusion.

The name of Order and Order Detail list is a bit misleading. In the workflow design, there will be one master list and with many other child lists under it. Sometime, the workflow may require to query one child list and display the information into  a task form. That's why I focus on Query List function here.

The one you described is already achieve in my workflow but I failed to retrieve the desirec record by filtering lookup field.

[eharris04]

Sam Ma,

Are you still having some troubles from this. Sorry its been a while...