Solved

Get a list field value from 'Query a list' Object variable

  • 13 November 2020
  • 7 replies
  • 897 views
Bards
Userlevel 1
Badge +4

This should surely be a simple task, but since the recent change to the Object model I've found no easy way.

 

I have a list with 2x columns: a city code (SYD, MEL, etc) and an email. Each row on the list has a unique city code. The goal is to enter a city code and then have the WF query the list and pull back the corresponding email and store it in a variable. This was super simple prior to the change to the Object model. I won't go over that however; I'll explain what I see now.
 

Add a Query a List action: set the conditions to only pull back results where the Code on the form matches the Code on the list. The output is an Object: a special kind of collection that includes number of items returned, a sub-collection of Item IDs and a sub-Collection of the Items themselves (that includes al fields).

 

Now I know the Object has a single Item - but how do you extract column 2 (email) from that Item? You can't use 'Get item from collection': you can set that to look at the sub-collection of Items in the Object but then you need store that data in text value. All the fileds for that Item in a long string with field names, etc. as CSV. You would then need to use string operations to try and pull that apart.
 

The only option I can see is to use a 'Loop for each' action that allows you to target the sub-collection of Items. That seems a waste since I know the Index of the item I want and also know the collection only has one item, but whatever. The next problem then: say you put a 'Set a variable value' action in the loop to take the variable 'Loop for each > current item > email' and store that in a new variable (say txtMail). Turns out that variable doesn't hold a value once you exit the loop. So again, that doesn't work.

 

The only way I have found to get a field/column value from 'Query a list' is to create a new Collection variable, put an 'Add item to collection' action in the loop, and then outside the loop 'Get item from collection' That's 4x actions (Query a list, Loop for each, Add item to collection, Get item from collection) and multiple varaibles just to get a single field value from a specific item on a list. 

Please someone tell me I've missed something here and there is a simpler way.

icon

Best answer by WouterT 18 November 2020, 02:45

View original

7 replies

Gavin-Adams
Userlevel 4
Badge +12

You'll need to use the 'Retrieve an Item' action to get the email.


That action will take a list item ID as the parameter to get the item that you are after.


It will return an object that will include all the fields for the item.


 


Option 1)


To get the item ID you can use you 'query a list' action.


Then do a 'loop for each' action target the 'Item ID's' collection returned by the query a list.


If you only return 1 item in the 'query a list' it will only run once.


In the loop, place the retrieve an item action.


 


Option 2) 


if you are using a form to start the workflow use the data connection to also store the city item ID in a workflow variable so you know what that lookup item ID when the workflow starts. This way you do not need the query a list and loop for each.


 


 

Bards
Userlevel 1
Badge +4

Thanks for the reply but unfortunatley neither of those options is able to pull a value from a field for a specific item as far as I can tell.

Option 1: the output of Retrieve an Item is itself an Object. So whether I query a list to get a collection of objects or Retrieve an Item to get one specific object either way I then have to use a Loop for each to pull a field (email) > add it to a collection > get item from collection. This is the same set of 4x actions.


 


Option 2: this would work if the field on the from is a Data lookup field, and if you want to store only the email associated with the city code but not the Code itself. Otherwise I don't believe this is possible in NWC. If an O365 WF then there's a lookup function you could use to build a variable, but that function doesn't exist in NWC.

rickdemarco
Userlevel 3
Badge +8

Hi @Bards,


 


What you are trying to do it totally possible and not sure why it's causing you problems, the update to Query list to an object is so we now return you all the columns from the list instead of previously where you had the id's only and then required the retrieve an item action to then get additional columns. You can still do this as we still expose the ID's collection as pass that into get item from  from collection but as mentioned you still then need the retrieve. 


 


Even though you know you only have one row coming back you still need to use the loop and use the current item and point to the email field. What you are doing in the loop is correct and this should certainly hold outside the loop as it's a global type variable. What happens to the value in the variable when you exit the loop? 


 


 

Bards
Userlevel 1
Badge +4

Trying again here: my last reply was apparrently deleted as 'spam'.


 


The test WF I've created is as follows:


1. Query a list: list has 2x fields: Code and email. Conditions are set to match on Code (which is unique) > output is an object 'ObjCode'.


2. Loop for each: Target collection is:  SharePoint Online > ObjCode > Items (collection).


3. Set a variable (inside loop): Variable is: Loop for each > current item > Email. Value is a new variable 'varMail'.


4. Send an email (outside loop): message body includes variable 'varMail'.


 


Now this WF will publish and run but the email received does not include a value for 'varMail'. Now if I put the Send an email action inside the loop then it works - the 'varMail' variable displays. However, this is just a test: I want to use Query a list to get a value I can store in a variable to use elsewhere in the WF. From this test it looks like that's not possible. The only way I have found to get this to work is to include the 'Add item to collection' action (inside loop) and 'Get item from collection' action (outside loop).

WouterT
Userlevel 3
Badge +9

Hi Bard, allow me to answer a bit more extensively as I have gone through the exact same learning curve it seems.


First the straight forward example. Then an easy alternative.


Example 1:


Your list:



You want a form with a lookup for the city and a return variable with the email address. Right?


This is your workflow:



First the form:



We have a lookup in the list so that we will always have a valid value there. The CityAlternative I will explain in Example 2.


Let's select AMS.


With the return value from the form we query the list with the right conditions.



This will give us the full object with all we need. As we are pretty sure we get one value back from the list we get the first ID from the collection.



Finally we retrieve the item as an object with all the info that comes with it:



Finally a log to show the value.


And this is the running result:



It might look a bit cumbersome for a simple example like this. But after building more workflows with more complexities you will find that this model is actually saving you a lot of actions and it is so much more easy to maintain.


As a Best Practice I have created quite a few component workflows where I enter some query data (like your City) and the Component Workflow returns the ID. Then I only have to [1] call the Component Workflow and [2] do a Retrieve item to get the whole object. You will find the object so much more useful when e.g. having to send an email to this address. All data (also with nice Display Names) is already there.


 


Example 2:


This is the even more simple example for your specific question.


No workflow even needed. The form does it for you.


The field is defined like this:



It shows the label = the city. But it stores as a value the email address.


Nothing to do then logging the value. I added this just for fun as it is so useful.


Hope this helps you and maybe some others as well.

Process Developer with Nintex and SharePoint. Contact: w.terlunen@gmail.com
Gavin-Adams
Userlevel 4
Badge +12

Hey @WouterT ,


nice use of the 'get item from collection' action.


I hadn't considered its use like that before.


 


Totally agree with example 2 though, if you can do it on the form in the data lookup, a lot easier and the workflow runs faster.

Bards
Userlevel 1
Badge +4

Thanks, your solution is bang on. I went halfway down this path (Example 1) and abandoned it - clearly too early as it turns out. 


 


The second example (using the Data source) would not work in my use case - I need both values (the option selected and a related value) - but this is a nice reminder of what's possible.


 


And bonus points for suggesting use of a component workflow! This is what I will ultimately do as I have to call this set of steps multiple times as part of a state engine setup. Thanks again.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings