Variables not applied properly in Query List CAML

  • 20 September 2017
  • 12 replies
  • 42 views

Badge +1

I created a SharePoint form that prompts users the following:

  • Name of List to be accessed
  • Name of Column to be affected on above list
  • Name of List to collect information to be processed

In the processing I did a little fixing to the inputs such as trim spaces, and replace spaces with "_x0020_" so that it can pull the data properly using   Query List > CAML

to applied the following:

<Query>
<Lists>
<List Title="{ItemProperty:Assignment_x0020_List_x0020_Name}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<IsNull>
<FieldRef Name='{WorkflowVariable:colname_fix}' />
</IsNull>
</Where>
</Query>

The problem I am having is that FieldRef accessing the variable flakes out and gives me this error

Failed to query list data. One or more field types are not installed properly. Go to the list settings page to delete these fields.

although when printing the variable "colname_fix" matches correctly to the column name.

In testing if I replace  workflow variable with <FieldRef Name='X_x0020_X' /> where its the exact output of what the variable outputs from the user input field it works fine...but the problem is I want it to be functional on fixing user inputs such as the spaces users wont know about the "_x0020_" for spaces.  If I statically enter "X X" it will give similar error but will work fine if I do "X_x0020_X", which is the same output as the processed one in the variable above, so does CAML have issues with WorkflowVariables?

My issue is if the VARIABLE == Column Name  including the adding of _x00200_ where there are spaces why is it giving me this error.

Is there another way to pull in data that a user inputs into a input field and apply it to a column name? or is there something I am missing?


12 replies

Badge +16

I believe there is a limit to how long that name is - you will need to look it up.

for example my column “transaction ID status column” might be “transaction_x0020_ID_x0020_sta” or similar. 

Be careful also as caml wants internal column name and not the display. So in my instance it could be internal name “transactionID” and I’ve made it display as above in which case your replacing of spaces is never going to be right.

have you looked to see if there are any web services you can call (lists.asmx for example) which might take display name and give you internal name?

Userlevel 5
Badge +14

Not only are spaces replaced with the code "_x0020_", but sharepoint will also truncate any column name at 32 characters! (as far as I know) 

So you would need to not only replace the spaces, but then afterwards trim the entire corrected column mass down to 32 chars 

In a workflow, if var_ColumnName is the name of the column you're targeting - and - const_SPSpaceReplacement has a default value of '_x0020_', then the following could be used to correctly generate a column name. 

fn-Substring(fn-Replace({WorkflowVariable:var_ColumnName}," ", {WorkflowVariable:const_SPSpaceReplacement}), 0, 32)
Badge +1

The problem I have is NOT the characters length.

Its that in CAML it seems that its not applying the variable like it does the static version.

for example

lets say column is labelled "Test Column"

using my above CAML code example

example 1 (hardcoded)

<FieldRef Name='Test Column' />

---This will error like stated previously

example 2 (hardcoded with space character)

<FieldRef Name='Test_x0020_Column' />

This will be SUCCESSFUL

example 3 (taking user inputs in the form)

user input for column name = "Test Column"

without applying any adjustment for space it will error like example 1

example 4 (taking user input like example 3)

apply a trim (remove any outside spaces), then apply a replace to add _x0020_ to any spacing 

this will output a variable like this:  Test_x0020_Column

now applying such variable (colname_fix) to CAML

<FieldRef Name='{WorkflowVariable:colname_fix}' />

it will still error just like example 1 and 3

yet

the variable colname_fix = "Test_x0020_Column"

just like example 2

so does CAML accept variables? 

example 5 (taking user input with added _x0020_ for spacing)

user inputs: Test_x0020_Column

CAML code: <FieldRef Name='{ItemProperty:Column_x0020_To_x0020_Be_x0020_U}' />

taking that user input

returns successful, but its unrealistic to expect users to know to add "_x0020_" instead of spaces

Badge +1

The issue is not character length but in variable scope within the CAML -- see my note below in the thread

Userlevel 5
Badge +14

I see.

Well I did some testing using variables in the FieldRef Name and it worked just fine. 

Code: 

<Query>
  <Lists>
    <List Title="Calculation Request List" />
  </Lists>
  <ViewFields>
    <FieldRef Name="Calculation_x0020_Status" />
  </ViewFields>
  <Where>
    <IsNull>
      <FieldRef Name='{WorkflowVariable:var_FilterField}' />
    </IsNull>
  </Where>
</Query>


Where variable {var_FilterField} = Quote_x0020_Locked

One way to better get to the bottom of what is happening might be to create your entire CAML query using a Build String action. Send the results to yourself (via email) ,and naturally use the variable that is being used to store the results as your CAML query in the Query action. 

If it succeeds. Good. 

If it fails. Take the resulting text that was sent to you via email, copy / paste into the CAML query body, and see if it still fails. At least this way you can figure out if it's even producing good CAML query text in the first place, and that everything is accounted for. 

If the raw text output from that resulting variable does actually query correctly (which I guess you could quickly test in a Run Now window), then there certainly is a strange conversion problem on the Nintex side. If it fails however, then you know that there is a problem in the actual query that was missed. 

Badge +1

Thanks, I will try that Build String action.

Userlevel 5
Badge +14

Yeah. Keep us updated. I'd be interested to know what the final results are after your testing. 

Badge +1

Thanks for your response -as am still new to nintex- as I overlooked string builder to be able to present me a way to see CAML. 

so here is the thing:

It seems when this was applied

fn-Replace({WorkflowVariable:var_colName_AfterTrim}," ","_x0020_")

it added a ton of whitespace at the end which was causing me the error in CAML

so the value ended up looking like this "x_x0020_x                                                                                       "

which is crazy because I trimmed the original input to remove any whitespace.

So I had to add another trim AFTER the replace and that solved the issue, but it seems odd that white space was added in the first place. 

Userlevel 5
Badge +14

Good to hear that you were able to resolve this! 

Please mark this as solved so that others who might run into this problem can also find an answer! 


Badge +3

Hi

I have similar issue while using workflow varibale in the query.I am build the part of the query dynamically.

For ex:

<Or><Eq><FieldRef Name="Title"/><Value Type="Text">Test abc</Value></Eq><Eq><FieldRef Name="Title"/><Value Type="Text">Test xyz</Value></Eq></Or>

I am saving the above query part in the workflow variable "Query" as title are loaded dynamically and number of condition may vary on the count of titles.

Then I am using the workflow variable "Query" in the CAML query as

<Query>
  <Lists>
    <List Title="Knowledge Documents" />
  </Lists>
  <ViewFields>
    <FieldRef Name="ID" /> </ViewFields>
    <Where>{WorkflowVariable:Query}</Where>
    <ViewAttributes Scope="Recursive" />
  </Query>  

But I don't see the expected results in collection... I see that query returns the entire lisitems as Query is going wrong.

When

Any help is appreciated. Thanks

Rashmi@@

Userlevel 5
Badge +14

I highly would recommend you put all of your query parts into build string actions, and then combine them, again - using a build string action. 

However, to do some testing beforehand. 

Copy and paste everything into a single full CAML Query, and make *sure* that it is a valid query beforehand. You may also have to do things like removing any whitespace around your elements, as I have had Queries fail because of hidden whitespace between the elements...  sad.png 

What you're trying to do should work, but you're going to have to do a little sleuthing in your own environment to discover why it isn't working. Maybe combine the final query and instead of actually querying a list, send yourself an email notification with the resulting Query. Using the RUN NOW portion of the Query List action, paste that query text into the CAML portion of the form, and see if it'll actually return a result. 

That should get you headed in the right direction. 

Badge +3

Thank you for the reply. The issue was that checkbox for "XML encode inserted tokens" was checked . Unchecking the option gave me the expected query.

Regards,

Rashmi

Reply