Query Dynamic List with Dynamic ViewFields


Badge +8

Scenario : a site workflow will read a configuration list which has details of other lists like (list name, list GUID, list Url, query condition, query view fields etc), based on the input the site workflow needs to query those lists and given condition to get respective view fields data for further processing.

Expected query result column values will vary for each list been queried and will be mentioned as part of <ViewField>. 

Question:

Is it possible to have ViewFields to be added dynamically using QueryList or Call WebService workflow actions? If yes how, else any alternate option.

Thanks in advance.

202903_pastedImage_1.png


17 replies

Userlevel 5
Badge +14

it should be possible.

following worked for me

202970_pastedImage_2.png

202960_pastedImage_1.png

Badge +8

Thanks for the response Marian Hatala

The workflow variable {WorkflowVariable:QueryViewFields} will vary everytime.

Example :

When querying List A, {WorkflowVariable:QueryViewFields} will have <FieldRef Name="EmpName" /><FieldRef Name="EmpAddress" />.

next when querying for List B <FieldRef Name="ID" /> will have <FieldRef Name="CustomerName" /><FieldRef Name="CustomerAddress" />

In which Fields needs to be populated at runtime.

Is it possible with Call Web Service action?

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/sharepoint/soap/">
<soap:Header>
</soap:Header>
<soap:Body>
<m:GetListItems>
<m:listName>{WorkflowVariable:ListName}</m:listName>
<m:viewName>string</m:viewName>
<m:query>{WorkflowVariable:QueryToGetBlankSignOff}</m:query>
<m:viewFields>{WorkflowVariable:QueryViewFields}</m:viewFields>
<m:rowLimit>string</m:rowLimit>
<m:queryOptions>xml</m:queryOptions>
<m:webID>string</m:webID>
</m:GetListItems>
</soap:Body>
</soap:Envelope>

202976_pastedImage_1.png

Userlevel 5
Badge +14

with web service call you should be able to get whatever you like.

you just need to build correct CAML - in your example there are some not populated input parameters (view name, row limit, etc.). so either populate them with valid values or remove them from CAML.

if you have properly formatted content of workflow variables, it should work.

Badge +8

The below is the XML which fails as "The remote server returned an error: (500) Internal Server Error.". Can please have a look and advise?

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/sharepoint/soap/">
<soap:Header>
</soap:Header>
<soap:Body>
<m:GetListItems>
<m:listName>OTC 5</m:listName>
<m:query>
<Query>
<Where>
<IsNotNull>
<FieldRef Name="OTC_x0020_07_x0020_47_x0020_Signoff_x0020_1" />
</IsNotNull>
</Where>
</Query>
</m:query>
<m:viewFields><ViewFields><FieldRef Name='OTC_x0020_07_x0020_47_x0020_Reviewer_x0020_1'/></ViewFields></m:viewFields>
<m:queryOptions><viewfieldonly>true</viewfieldonly></m:queryOptions>
</m:GetListItems>
</soap:Body>
</soap:Envelope>

Userlevel 5
Badge +14

1. correct syntax/structure for query options should have been (ie. one extra <QueryOptions> XML element)

<m:queryOptions><QueryOptions><viewfieldsonly>true</viewfieldsonly></QueryOptions></m:queryOptions>

2. viewfieldonly should have been viewfieldsonly (ie fieldS)

3. viewfieldsonly just like IncludeMandatoryColumns are often reported as not working

c# - How to Return Only Certain Columns of a List When using the SharePoint Web Service? - Stack Overflow 

Badge +8

Still face the same error with below call:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/sharepoint/soap/">
<soap:Header>
</soap:Header>
<soap:Body>
<m:GetListItems>
<m:listName>OTC 5</m:listName>
<m:query>
<Query>
<Where>
<IsNotNull>
<FieldRef Name="OTC_x0020_07_x0020_47_x0020_Signoff_x0020_1" />
</IsNotNull>
</Where>
</Query>
</m:query>
<m:viewFields><ViewFields><FieldRef Name='OTC_x0020_07_x0020_47_x0020_Reviewer_x0020_1'/></ViewFields></m:viewFields>
<m:queryOptions><QueryOptions><viewfieldsonly>true</viewfieldsonly></QueryOptions></m:queryOptions>
</m:GetListItems>
</soap:Body>
</soap:Envelope>

Userlevel 5
Badge +14

structure seems to be correct, check correct spelling of names.

(shouldn't eg. list name be OTC_x0020_5?)

I've taken your CAML, updated names to my testlist, and it worked for me (SP2013)

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/sharepoint/soap/">
<soap:Header>
</soap:Header>
<soap:Body>
<m:GetListItems>
<m:listName>testlist</m:listName>
<m:query>
<Query>
<Where>
<IsNotNull>
<FieldRef Name="slot" />
</IsNotNull>
</Where>
</Query>
</m:query>
<m:viewFields><ViewFields><FieldRef Name='Title'/></ViewFields></m:viewFields>
<m:queryOptions><QueryOptions><viewfieldsonly>true</viewfieldsonly></QueryOptions></m:queryOptions>
</m:GetListItems>
</soap:Body>
</soap:Envelope>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Badge +8

This issue was in the field name. 

Here is the details:

  1. The mentioned fields are InfoPath fields which appear in side ows_MetaInfo and not as z:row properties as highlighted below
  2. I can see those columns in list settings page as well in U2U builder too

Any advise? 

203021_pastedImage_1.png

203022_pastedImage_2.png

Userlevel 5
Badge +14

I do not have experience with infopath and do not know what is U2U.

but if you can see columns in list settings you should be able query them.

navigate into column settings page and check URL what column name appears there. use that name in CAML.

Badge +8

In the list settings page the column name do NOT appear are hyperlinks, using U2U caml query builder tool I am able to get column names. U2U also give the output for the query built, which fails when the same query is used in Call web Services in Nintex workflow.

203108_pastedImage_1.png

203109_pastedImage_2.png

P.S: U2U is a caml query builder tool.

Userlevel 5
Badge +14

and if you execute query within U2U tool, does it work?

try to remove view fields and query option specification from web service call and leave there just query specification.

btw, are on SP2010 or 2013?

Badge +8

Yes, it works in U2U caml query builder and the SharePoint environment is SP2013.

I did try removing the web service call with just the query (without ViewFields/QueryOptions) it still the same outcome.

Userlevel 5
Badge +14

that's weird.

could you try it with default fields like ID, Title ....?

could you try to use list GUID instead of list name?

Badge +8

When default fields like ID, Title is used in the query it works. It fails when InfoPath form fields are used in the query.

The outcome, when the default fields are used, is like:

the highlighted text are the InfoPath fields

203233_pastedImage_1.png

203246_pastedImage_2.png

Userlevel 5
Badge +14

as far as I know sharepoint limits max length of field names to 32 chars.

however your field name "OTC_x0020_07_x0020_47_x0020_Signoff_x0020_1" is over 40 characters.

isn't that the problem?

Badge +8

Agree with the limitation, but could this be the reason for missing Key-Value pair in the web service call outcome?

Userlevel 5
Badge +14

I don't think so, it looks like infopath manages list fields a different way.

can you try REST API to see how sharepoint sees list configuration

to get all list fields

<site URL>/_api/Web/Lists/getbytitle('OTC 5')/fields‍‍

to get single field (change the field name accordingly)

<site URL>/_api/Web/Lists/getbytitle('OTC 5')/fields/getbyinternalnameortitle('Title')‍

Reply