Qdabra Database Accelerator

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Wed, 15 Jul 2009 15:23:44 GMT

I had tried almost the same thing but did not have the .text in building the string.

Thank you Thank you Thank you!!

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

Hilary Stoupa — Wed, 15 Jul 2009 15:18:02 GMT

Just get your main data source field value the way you have other main data source fields in your script:

var field = XDocument.DOM.selectSingleNode("/my:myFields/my:TopLevelInput");

Then, you can do something like this:

query.text = "<query><filter><eq><column name='Title'/><value>" + field.text + "</value></eq></filter></query>";

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Wed, 15 Jul 2009 15:00:30 GMT

Thanks Hillary. The static query works but I need to pass a variable to the query. I have tried to concat everyway I can think of the string to pull in the input field which is in the main. I copied the code generated by Query Builder and I copied the piece of code that I was using in the Rule to set the query field of the secondary DBXL but the script will blow for various reasons. I've used

xdXDocument:get-DOM()/my:myFields/my:TopLevelInput

which is what Query Builder generates but when I put this in the form will not load with a "expecting )" error but I am not missing any right parens. I've tried using the

XDocument.DOM.selectSingleNode("/my:myFields/my:TopLevelInput")

They either blow on form load or if the form loads then when it executes it get an Object Required error.

I swear I have never had this much trouble with code before! I wrote COBOL for years and then VB but this script stuff is crazy!!!

Thanks for your help!!!

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

Hilary Stoupa — Tue, 14 Jul 2009 21:19:12 GMT

For example:

var query = XDocument.GetDOM("GetListItemsStr").selectSingleNode("/dfs:myFields/dfs:queryFields/tns:GetListItemsStr/tns:query");

query.text = "<query><filter><eq><column name='Title'/><value>Test</value></eq></filter></query>"

XDocument.DataObjects["GetListItemsStr"].Query();

The first line gets the 'query' field in the queryFields of the data connection. The second line sets the value to our new query (mine filters out any items that don't have title equal to Test) and the third actually executes the query to retrieve the data. Hope that helps!

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Tue, 14 Jul 2009 19:26:18 GMT

OK. I lied! Well not really. I have progressed nicely since this morning. Everything is going fine but I moved on to finishing the code for this. What I now need to do is combine the query function to DBXL with the rest of the code.

I had been doing the query in Rules but now I want to call the query from script instead so I can do it in one click. I know that query builder makes code to insert in code but what I can't seem to do is set the query field in the DBXL connection from script so that I can then execute the query and get the results back.

Again, I'm sure it's simple but in my search in infopathdev I have not found what I'm trying to do so far.

I'm thinking we're talking one or two lines of code to set the query field(currently performed in a rul) to the query string. Then code to cause the query to the DBXL connection to occur.

Thanks again and I hope that all my grief benefits other vistors to this forum!

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Tue, 14 Jul 2009 15:56:29 GMT

Well Hillary to not be a Java Script girl you are doing wonderfully. That did it. I had tried similar combinations all around that but had not hit that combination I guess.

Works like a charm. Now lets try and add all the other fields and see what I get into!

I'll leave you alone for the rest of today unless I hit some huge roadblock.

Thanks Again!!!

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

Hilary Stoupa — Tue, 14 Jul 2009 15:40:50 GMT

Your script is different from my sample. You are selecting the text of the node. If the node is null, you'll get an object required error when you try to select text.

So, what you have:

   var field11 = newTestDetails.selectSingleNode("my:field11");
    XDocument.UI.Alert(node.selectSingleNode("Diameter").text.length);
    if (node.selectSingleNode("Diameter").length != 0)
    {
    var diameter = node.selectSingleNode("Diameter").text;
    if (diameter != "" & field11.getAttribute("xsi:nil"));
    field11.removeAttribute("xsi:nil");
    field11.text = diameter;
    }

Could be:

var field11 = newTestDetails.selectSingleNode("my:field11");

var diameter = node.selectSingleNode("Diameter")

//check to see if the diameter node is null

if (null != node.selectSingleNode("Diameter"))

{

//if it isn't null, check to see if the field has a value

    if (diameter.text != "")

    {

        //if the field has a value, check for the nil attribute

        if(field11.getAttribute("xsi:nil"))

        {

            //remove nil if present

            field11.removeAttribute("xsi:nil");

        }

        //set the field to the diameter value if diameter.text has a value

        field11.text = diameter.text;

    }

}

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Tue, 14 Jul 2009 15:20:52 GMT

Here's the current script. I added the Alert for the length of the node just for debuggin. It will show me the node size for the records just fine up until it hits that one where this none has not been filled in yet and I Object Required.

I had already tried to test for null just like mentioned:

var field = node.selectSingleNode("Diameter");

but this line will error out with object required.

So I was trying to use the length thinking that if it was zero I could identify it that way but it still comes back with object required.

/ Get a copy of the main DOM node.
     var clone = XDocument.DOM.selectSingleNode("/my:myFields/my:group3/my:Test_Details").cloneNode(true);
     // Copy nodes from 2DS to main DOM.
     var nodes = XDocument.GetDOM("GetListItemsStr").selectNodes("/dfs:myFields/dfs:dataFields/ns1:GetListItemsStrResponse/ns1:GetListItemsStrResult/Rows/row");

     var node;

     while(node = nodes.nextNode())
     {
          var newTestDetails = clone.cloneNode(true);
          newTestDetails.selectSingleNode("my:field10").text = node.selectSingleNode("Test_Serial_Number").text;

    //newTestDetails.selectSingleNode("my:field11").text = node.selectSingleNode("Diameter").text;


    var field11 = newTestDetails.selectSingleNode("my:field11");
    XDocument.UI.Alert(node.selectSingleNode("Diameter").text.length);
    if (node.selectSingleNode("Diameter").length != 0)
    {
    var diameter = node.selectSingleNode("Diameter").text;
    if (diameter != "" & field11.getAttribute("xsi:nil"));
    field11.removeAttribute("xsi:nil");
    field11.text = diameter;
    }
    XDocument.DOM.selectSingleNode("/my:myFields/my:group3").appendChild(newTestDetails);
     }
// Write your code here
}

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

Hilary Stoupa — Tue, 14 Jul 2009 15:10:49 GMT

Hey, could you also post the most recent copy of your script, in case there is something else going wrong? Because the field should be there, even if it doesn't have data...so it seems like something else might be the problem.

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

Hilary Stoupa — Tue, 14 Jul 2009 15:09:01 GMT

To test for whether a field is null, using the node variable declared in your while loop:

var field = node.selectSingleNode("Diameter");

if (null == field)
{
//do something
}
else
{
//do something else
}

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Tue, 14 Jul 2009 14:46:35 GMT

OK, It still appears that since that field is not populated it is just not there? It keeps giving me an object required error when I get to that point. I tried using the null comparison but that doesn't work either. I tried using .length and it gives me the lengths for the items up to the one without anything and then still will blow for an object required. So it's really like it does not exist yet.

Can anyone tell me how to test to see if an object exists before I try to reference it which blows the script up? I don't really want to fill the fields with blanks or zeros when the record is created because there are valid reasons that it may not yet have been filled in. I guess I could set them all to spaces when the record is created and perhaps that would solve the problem but I would think there is more elegant way to deal with it? Also, just in case because one could get by and I don't want it blowing up if it does.

If someone has experienced this and knows what to do, please share with me. I may be on the completely wrong track but since it works fine until it gets to the record where the field has not been populated and the error I get is Object Required then I am assuming it means the object doesn't exist?

Hillary, thanks so much for all your help on this and I'm in good shape if I can learn how to test for this.

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

Hilary Stoupa — Mon, 13 Jul 2009 21:36:54 GMT

The field should exsist in the secondary data source -- it will just have a blank value. You should remove this line: newTestDetails.selectSingleNode("my:field11").text = node.selectSingleNode("Diameter").text; as you are setting that after you check for and remove the nil attribute. However, to test to see if a node is null, I believe you can use null == node with node being the variable you want to test (sorry, not really a javascript girl, so I'm guessing on that one).

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Mon, 13 Jul 2009 21:05:34 GMT

Good grief Charlie Brown. One step at a time!

So with a little work I got the nil attribute to work.

Now I have encountered a situation where there i a record but it has no Diameter input in it as of yet. This could happen. I have tried to test it comparing for null and undefined but neither seems to work. I may be testing against the wrong thing.

I've spent the better part of the afternoon trying to find the answer myself. Basically the record has been created but that field is yet to be populated at all. It errors out with object required error when it tried to appendchild. I'm asuming that since it was not populated it just doesn't exists in the XML yet?

Here is the current code minus my attempts to test for null or undefined. This works fine as long as there is a value in the Diameter field.

function CTRL188_5::OnClick(eventObj)
{
// Get a copy of the main DOM node.
     var clone = XDocument.DOM.selectSingleNode("/my:myFields/my:group3/my:Test_Details").cloneNode(true);
     // Copy nodes from 2DS to main DOM.
     var nodes = XDocument.GetDOM("GetListItemsStr").selectNodes("/dfs:myFields/dfs:dataFields/ns1:GetListItemsStrResponse/ns1:GetListItemsStrResult/Rows/row");

     var node;

     while(node = nodes.nextNode())
     {
          var newTestDetails = clone.cloneNode(true);
          newTestDetails.selectSingleNode("my:field10").text = node.selectSingleNode("Test_Serial_Number").text;

    newTestDetails.selectSingleNode("my:field11").text = node.selectSingleNode("Diameter").text;
    var field11 = newTestDetails.selectSingleNode("my:field11");
    var diameter = node.selectSingleNode("Diameter").text;
    if (diameter != "" & field11.getAttribute("xsi:nil"));
    field11.removeAttribute("xsi:nil");
    field11.text = diameter;

    XDocument.DOM.selectSingleNode("/my:myFields/my:group3").appendChild(newTestDetails);
     }
// Write your code here
}

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

Hilary Stoupa — Mon, 13 Jul 2009 18:59:28 GMT

For fields other than text, the xsi:nil attribute may be on the node -- for example, if you have a main data source element that is of a type decimal and it is NOT marked 'Cannot be blank', the xsi:nil attribute will be present. You'll need to remove it. Try looking at the script on some of the sample forms installed with InfoPath for examples. The Asset Tracker sample uses this to remove xsi:nil:

// The xsi:nil needs to be removed before we set the value.
if (value != "" && xmlNode.getAttribute("xsi:nil"))
xmlNode.removeAttribute("xsi:nil");

So, for your code, maybe something like...

var field11 = newTestDetails.selectSingleNode("my:field11");
var diameter = node.selectSingleNode("Diameter").text;
if (diameter != "" & field11.getAttribute("xsi:nil"));
xmlNode.removeAttribute("xsi:nil");
field11.text = diameter;

I didn't test this, so there is every chance in the world I might have a typo, but hopefully that will give you the idea...

Re: Copy/Clone data from DBXL Secondary Data Connection to InfoPath 2003 Repeating table in Main Data in JS

kennebg — Mon, 13 Jul 2009 18:46:30 GMT

OK. That worked for the first field, field 10. So now I tried to proceed cautiously by expanding to one for more field and it's blowing up with this error:

'#PCDATA' is in nil content: <{http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-04-28T14:41:50}field11>.

File:script.js
Line:143

Here's the current code:

function CTRL188_5::OnClick(eventObj)
{
// Get a copy of the main DOM node.
     var clone = XDocument.DOM.selectSingleNode("/my:myFields/my:group3/my:Test_Details").cloneNode(true);
     // Copy nodes from 2DS to main DOM.
     var nodes = XDocument.GetDOM("GetListItemsStr").selectNodes("/dfs:myFields/dfs:dataFields/ns1:GetListItemsStrResponse/ns1:GetListItemsStrResult/Rows/row");

     var node;

     while(node = nodes.nextNode())
     {
          var newTestDetails = clone.cloneNode(true);
          newTestDetails.selectSingleNode("my:field10").text = node.selectSingleNode("Test_Serial_Number").text;
          newTestDetails.selectSingleNode("my:field11").text = node.selectSingleNode("Diameter").text;
          XDocument.DOM.selectSingleNode("/my:myFields/my:group3").appendChild(newTestDetails);
     }
// Write your code here
}

What fun. The only difference I see is that in the table it is a Double Decimal field instead of a text field. It is stored in SharePoint as a Double Decimal but I notice when I look in the DBXL secondary data source that it shows it as a string. Do I need to change something in my DBXL query or do I have to perform some type of conversion on it from a string back to a number before trying to insert it?

The error is occuring right at the appendChild command of the script. Not the line just above it where it gets set?

I know I'm asking you lots of basic JS here but I'm in a bind.