Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

Loop through the DataConnection records

Yair-Nevet
Level 4
Level 4

Hello,

I made a connection to excel file in my form.

The connection is good and I'm able to bind data to my objects with out any problem.

Now, someone can tell me how can I loop through the records I received from the excel file in my code? any example?

I need it for some reason.

Thank you in advanced,

Yair

4 Replies
tgoodmannz_bss
Level 4
Level 4

OK .. here's an example:

StudentNameText.rawValue = xfa.event.newText;

var sDataConnectionName = "MyConn"; // name of the data connection from which to retrieve data

var sColName = "myColumnName";

//    Search for <source name="{sDataConnectionName}"> node

var nIndex = 0;

while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName)

{

    nIndex++;

}

var oDB = xfa.sourceSet.nodes.item(nIndex); // the <source> node pertaining to the data connection specified in sDataConnectionName is our "database"

oDB.open(); // load the data from the connection into the form, resolving any bindings

oDB.first(); // move to the first record in the connection

//    Search node with the class name "command" (this is <source><command/></source>)

var nDBIndex = 0;

while(oDB.nodes.item(nDBIndex).className != "command")

{

    nDBIndex++;

}

//    Backup the original settings before assigning BOF and EOF to stay

var sBOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("bofAction");

var sEOFBackup = oDB.nodes.item(nDBIndex).query.recordSet.getAttribute("eofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayBOF", "bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute("stayEOF", "eofAction");

// Search for the record node with the matching Data Connection name (looking for <{sDataConnectionName}> node within the current record data)

nIndex = 0;

while(xfa.record.nodes.item(nIndex).name != sDataConnectionName)

{

    nIndex++;

}

var oRecord = xfa.record.nodes.item(nIndex);

var oNode = null;

for(var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++)

{

    if(oRecord.nodes.item(nColIndex).name == sColName)

    {

        oNode = oRecord.nodes.item(nColIndex);

    }

}

var lFinished = false;

while(!(oDB.isEOF()||lFinished))

{   

    // As this loop iterates, the variable "oNode" is set to the value of the requested column

    // you could use this to add instances to a subform or items to a dropdown etc

     oDB.next();

}

//    Restore the original settings

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sBOFBackup, "bofAction");

oDB.nodes.item(nDBIndex).query.recordSet.setAttribute(sEOFBackup, "eofAction");

//    Close connection

oDB.close();

Yair-Nevet
Level 4
Level 4

Thank you very much!

This is a fantastic example.

Yair

Sent from my BlackBerry® smartphone from orange

Yair-Nevet
Level 4
Level 4

Hi Tim,

Line: while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName) throwing: "Index value is out of bounds" exception.

And my form is already containing the DataConnection settings to the excel file...

Any idea why?

Yair

tgoodmannz_bss
Level 4
Level 4

Hi Yair,

Sorry, you did say "any" example 🙂

There may be a better way to do what you are doing:

See the sction "Using a button to populate fields from a database" here: http://help.adobe.com/en_US/livecycle/9.0/designerHelp/index.htm?content=000329.html

See that the javasxript references the data connection by name explicitly..

Hope this helps

Thanks

Tim