Qdabra Database Accelerator

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

ErnestoM — Mon, 26 Oct 2009 22:08:06 GMT

For a solution, please see this blog post.

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

Jim Cantwell — Wed, 21 Oct 2009 16:22:06 GMT

It sounds like you missed the step of passing in the query string when setting up the data connection. Specify the query columns when setting up the data connection.

<query maxrows='2'><columns><column name='Client_x0020_Name'/><column name='Address'/><column name='Company'/></columns></query>

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

ErnestoM — Wed, 21 Oct 2009 16:06:41 GMT

I'm not sure how to find the first record because it's not something that is usually necessary.

If you stumbled upon this months ago, then it's safe to assume you are a current customer and not on a trial license (since those expire). Besides, there's not much more we can do via the forum (listing the columns worked for me, but doesn't work for you). Please contact support at qdabra dot com and we'll continue.

Thanks!

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Wed, 21 Oct 2009 15:37:52 GMT

Hi there. I get the same results. Since the Data Connection is built before the form code ever executes having all the columns in the form code query doesn't help. The code has not been refereced or executed when you add the Data Connection. It still cannot find any fields that are not populated. I'm not sure how your work around worked but having all the columns listed in both of the Data Connections fields and the code itself makes no difference here?

I'm still trying to find a way to identify the physical first record in the list. If I am able to do this then I can fully populate it and the problem will be resolved. Although this is not a true resolution as for building the Data Connection it should include all the columns that you include regardless of whether they have data in them or not. This makes maintenance very difficult.

I'm not sure how this worked for you but it is still not working for me. I stumbled upon this months ago when I was first setting this up and found that all the fields to be retrieved must be populated in the first record. If you can tell me how to find the first physical record I would appreciate it as I must get this completed soon.

Have a great day!!!!

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

ErnestoM — Tue, 20 Oct 2009 20:12:41 GMT

Sorry for the back and forth, and thanks for hanging in there. I don't think your form is necessary at this point since we won't have access to your data source (Sharepoint list). I usually use rules, and not code, but the result should be the same.

Anyway, I think I've seen the problem.

It is interesting that your <query> used in the data connection lists all the columns you indicated in QueryBuilder, but the <query> used in code does not. If QueryBuilder is returning the queries this way, then it's worthy of an investigation.

What I mean is this: when I generate a query, I get the following three <query> strings in QueryBuilder:

<query maxrows='2'><columns><column name='Client_x0020_Name'/><column name='Address'/><column name='Company'/></columns></query>

concat("<query><columns><column name='Client_x0020_Name'/><column name='Address'/><column name='Company'/></columns><filter><eq><column name='ID'/><value>", xdXDocument:get-DOM()/my:myFields/my:description ,"</value></eq></filter></query>")

String.Format("<query><columns><column name='Client_x0020_Name'/><column name='Address'/><column name='Company'/></columns><filter><eq><column name='ID'/><value>{0}</value></eq></filter></query>", description)

As you can see (underlined), all three queries contain the list of columns to return, where as your code snippet a few posts above does not list the columns.

So, to summarize the workaround I propose, your <query> must always contain the columns you want returned. You seem to have done this for the one <query> that defines the data connection, but not for the query you use in code.

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Tue, 20 Oct 2009 18:34:51 GMT

Maybe this?

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Tue, 20 Oct 2009 18:32:29 GMT

I promise I"m not this dense but I just don't completely understand it.

When it does show the columns it does not always return them in the order that I listed them but they usually are all there if I can get the first record of the file fully populated.

In the Data Connection you get two screens to paste the query too. If you are hard coding values in the query then you paste the #1 string to the first one and the #2 string to the second one. From the tutorial video it indicated that if you were going to set the query field dynamically at run time you just paste #1 into both the first and second screens which is what I have been doing and it works just fine again as long as I can get the full list of fields to appear in the data connection. As with the example I sent at first with the two screen shots of the Data Source screen for the GetListItemsStr the one I get now only shows a few of the columns. When I built the current production one the file was still in a test mode so I can clear it out and insure that the first record was fully populated but now that it is in production I really cannot do this.

If I do get all the columns I need then is JScript the Query string looks like this which should equate to a CONCAT. THis does work:

query.text = "<query><filter><eq><column name='Top_Level_Order_Number'/><value>" + qtoplevel.text + "</value></eq></filter></query>";
XDocument.DataObjects["GetListItemsStr"].Query();

If it is done in a Rule then I do use Concat.

Both work just fine as long as all columns that I need are available in the Data Source window. I have taken several trys at populating al fields in what appear to be the first record in the file but no luck so far and this is not practical anyway.

I do not understand your work around but I'm willing to try it for sure! Would copies of the XSN file help? I wish I could paste screen shots. I have tried saving them as JPG but they will not display for some reason. I have attempted to save it as a GIF here so maybe it might come thru. If it doesm let me know and I'll scroll down and take a shot at the bottom half of the screen and send that also.

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

ErnestoM — Tue, 20 Oct 2009 17:42:17 GMT

Thanks for the <query>.When you use it, does the web service (in the data source taskpane) show the returned columns as specified in the <columns> section?

Does your second <query> start with concat()?

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Tue, 20 Oct 2009 16:46:37 GMT

It always includes that maxrows='2' even though I have not selected maxrows in query builder. But if I can get the Data Connection built it works.

Here's the query string from #1 of Query Builder.

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

ErnestoM — Tue, 20 Oct 2009 16:36:20 GMT

The forum will let you attach certain file types via the Options tab (do not Quick Reply). I don't think an XPS is allowed (I can't open it on my machine either).

You could just paste the queries you've generated. That should be enough to show me whether you selected the QueryColumns or not.

Thanks.

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Tue, 20 Oct 2009 15:28:05 GMT

I tried to attach the Query builder screen shot zipped up. I don't see it but maybe it will come thru. Is there an easier way to past a screen shot on this site?

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Tue, 20 Oct 2009 15:07:22 GMT

Thanks for the reply!!

I'm not sure I completely understand the work around? I have included a screen shot from Query Builder so maybe that will help. I am forced to deploy and develope in Infopath 2003 but of course Query Builder only runs in InfoPath 2007. Not sure if that could be part of the problem but I don't believe that it is?

Based on the instructions I had I have been using the #1 query string from Query Builder in both the the query strings of the Data Connection and then setting the Query parameter at run time in either a rule or JScript.

What would I do different so that I could still dynamically set the query parameter at run time but get all fields to be available populated or not?

I will attach in a post right behind this one a screen shot from Query Builder. I started this on a different computer than where Query Builder is located.

Thanks for your help on this!!

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

ErnestoM — Mon, 19 Oct 2009 13:53:30 GMT

I was able to reproduce this issue. However, I did not use a <query> parameter. I created a data connection that called QuerySharepoint > GetListItemStr. If the first item in the SharePoint list has a blank value in one of its fields, then the data connection does not return those columns (where the first entry is blank).

I worked around this by using QueryBuilder and specifying the Query Columns. Even if the first item had a blank field, the data connection returned the values for the other entries. [This is best practice, in my opinion, since it allows you to return only the data you care about. That's the value of DBXL's webservice, since InfoPath allows you to create a data connection that receives (unfiltered) data from a Sharepoint list.]

Does this make sense? I'll log a bug request, but let us know if this workaround is succesful for your scenario.

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Fri, 16 Oct 2009 17:02:18 GMT

Re: Some fields not present in DataConnection from DBXL GetListItemStr from Sharepoint list

kennebg — Fri, 16 Oct 2009 17:01:36 GMT

Yes, I did use QueryBuilder to create the query. But the problem is not the number of records being pulled in. It's that all the fields are not available in the data source window after adding the Data Connection. I found previously that as long as all fields are populated in the first record in the list then all the fields I have in the data connection show up in the data source but if a field is not populated then it just does not appear in the data source.

I have inserted two screen shots of the data source for it. The first one shows how it looks before I modify the connection with all the curren fields. I have added one additional field that I need to pull in and after rebuilding the query with the additional field I then get only the few fields show in screen shot #2.

Unless there is a trick to making all the fields shoe up regardless of whether they are populated in the first record or not then I need a way of finding the first record in the list in order to populate all fields so I can modify the data connection. Once the data connection is done and working I don't care if some fields are blank but just to get the data source modified it appears that all fields must have something in them.