Qdabra Tools

Re: Query Builder "and or" structure

Marzipan — Thu, 26 Aug 2010 20:07:48 GMT

The query needed to be:

concat("<query><columns><column name='ID'/><column name='Title'/><column name='Status'/><column name='DueDate'/><column name='CalendarItem'/><column name='Modified_x0020_Comment'/><column name='Status'/><column name='AssignedTo'/><column name='Editor'/></columns><filter><and><contains><column name='AssignedTo'/><value>",xdXDocument:get-DOM()/my:Tasks/my:UserInfo/my:DisplayName,"</value></contains><ge><column name='StartDate'/><value>", addDays(today(), -30), "</value></ge></and></filter><sort><column name='DueDate' order='DESC'/></sort></query>")

Re: Query Builder "and or" structure

Marzipan — Thu, 26 Aug 2010 16:38:52 GMT

Still getting error "Some rules not applied". (see picture)

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Xml.XmlException: Data at the root level is invalid. Line 1, position 1.
   at System.Xml.XmlTextReaderImpl.Throw(Exception e)
   at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
   at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
   at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at Qdabra.Dbxl.WebService.QuerySharePointService.XmlFromString(String str)
   at Qdabra.Dbxl.WebService.QuerySharePointService.GetListItemsStr(String listUrl, String query, String queryOptions)
   --- End of inner exception stack trace ---

Here is the query string:

concat("<query><columns><column name='ID'/><column name='Title'/><column name='Status'/><column name='DueDate'/><column name='CalendarItem'/><column name='Modified_x0020_Comment'/><column name='Status'/><column name='AssignedTo'/><column name='Editor'/></columns><filter><and><contains><column name='AssignedTo'/><value>", my:UserInfo/my:DisplayName, "</value></contains><ge><column name='StartDate'/><value>", addDays(today(), -30), "</value></ge></and></filter><sort><column name='DueDate' order='DESC'/></sort></query>")

Re: Query Builder "and or" structure

Jimmy — Thu, 26 Aug 2010 14:54:49 GMT

Just position your cursor where userName() is currently located, delete the text userName() and use the Insert Field... dialog to insert the DisplayName field into the formula. That should do the job.

Re: Query Builder "and or" structure

Marzipan — Thu, 26 Aug 2010 14:47:39 GMT

I'm trying to move from evaluating the "AssignedTo" field against the userName() function to the value in /my:Tasks/my:UserInfo/my:DisplayName in the querystring for [GetListItemsStr] .

concat("<query><columns><column name='ID'/><column name='Title'/><column name='Status'/><column name='DueDate'/><column name='CalendarItem'/><column name='Modified_x0020_Comment'/><column name='Status'/><column name='AssignedTo'/><column name='Editor'/></columns><filter><and><contains><column name='AssignedTo'/><value>", userName(), "</value></contains><ge><column name='StartDate'/><value>", addDays(today(), -30), "</value></ge></and></filter><sort><column name='DueDate' order='DESC'/></sort></query>")

Can someone give me an example of the syntax to use "/my:Tasks/my:UserInfo/my:DisplayName" value vs. "userName()"

Re: Query Builder "and or" structure

Marzipan — Wed, 02 Jun 2010 16:55:59 GMT

That worked. Thanks Jimmy, sometimes it is the obvious.

Re: Query Builder "and or" structure

Jimmy — Wed, 02 Jun 2010 16:38:24 GMT

That query looks ok. Have you checked to make sure there's a row in the table where AssignedTo contains mbj3f, and status is not Completed or Deferred?

Re: Query Builder "and or" structure

Marzipan — Wed, 02 Jun 2010 16:28:51 GMT

ok. that worked (wierd), not getting error but also not getting any results.

the query field value ends up being set as:

<query><columns><column name='ID'/><column name='Title'/><column name='Status'/><column name='DueDate'/><column name='CalendarItem'/><column name='Modified_x0020_Comment'/><column name='Status'/><column name='AssignedTo'/><column name='Editor'/></columns><filter><and><and><contains><column name='AssignedTo'/><value>mbj3f</value></contains></and><and><ne><column name='Status'/><value>Completed</value></ne></and><and><ne><column name='Status'/><value>Deferred</value></ne></and></and></filter><sort><column name='DueDate' order='ASC'/></sort></query>

Re: Query Builder "and or" structure

ErnestoM — Wed, 02 Jun 2010 16:18:15 GMT

When entering the concat() function into the rule, first click on the function button (fx), then paste the concat() function into the next dialog.

Re: Query Builder "and or" structure

Marzipan — Wed, 02 Jun 2010 16:09:59 GMT

To answer Ernesto's question:

Here is the 1st query and yes it works fine.

I have attatched images of the form rule where I am setting the query field. I am entering the statement from the form in the value field which is a concat statement, what seems odd is the program seems to be appending another concat statement (see images).

Jimmy, I will change the logic struture and see if that helps.

Re: Query Builder "and or" structure

Jimmy — Wed, 02 Jun 2010 14:47:16 GMT

The XML looks valid but the error suggests that you are using that concat() expression in the Data Connection Wizard. You should be using the sample query (without concat()) in the Data Connection Wizard.

I think there is an error in your reasoning and that OR is not really merited here. It looks like you are trying to create this expression:

(AssignedTo = my:UserName) AND ((Status != 'Completed) OR (Status != 'Deferred'))

This will return records regardless of their status because one of (Status != Completed)/(Status != 'Deferred') will always be true.

It seems to me that what you actually need is a regular 3-way and expression:

(AssignedTo = my:UserName) AND (Status != 'Completed) AND (Status != 'Deferred')

Re: Query Builder "and or" structure

ErnestoM — Wed, 02 Jun 2010 14:26:55 GMT

Hello,

I think it would be helpful to know the following:

1. What is the first query generated by QueryBuilder? (just paste the <query> string into your reply.

2. If you use that first query to create the data connection, do you get an error in InfoPath, or is the data connection created succesfully?

3. How is the concat query string used in the form? (a screenshot of the rules would be fine)

Thanks in advance for the additional information.

Query Builder "and or" structure

Marzipan — Wed, 02 Jun 2010 14:16:47 GMT

I am trying to use the query builder to construct a and or query (see attatched jpeg), I assume I am not structuring it properly.

the query results is:

concat("<query><columns><column name='ID'/><column name='Title'/><column name='Status'/><column name='DueDate'/><column name='CalendarItem'/><column name='Modified_x0020_Comment'/><column name='Status'/><column name='AssignedTo'/><column name='Editor'/></columns><filter><and><and><eq><column name='AssignedTo'/><value>", xdXDocument:get-DOM()/my:Tasks/my:UserInfo/my:UserName ,"</value></eq></and><and><or><ne><column name='Status'/><value>"Completed"</value></ne></or><or><ne><column name='Status'/><value>"Deferred"</value></ne></or></and></and></filter><sort><column name='DueDate' order='ASC'/></sort></query>")

I'm getting the following error:

The query cannot be run for the following DataObject: GetListItemsStr
InfoPath cannot run the specified query.
The SOAP response indicates that an error occurred on the server: