How do I count the number of data records?

[Former Community Member]

Hey guys, how do I count the number of records I got on a data object variable?

Im having a sql query result in a variable like this:

oDB.resolveNode("#command").query.select.value = "SELECT * FROM Clientes WHERE CliNombre LIKE '%" + input1 + "%'";

oDB.open();

oDB.first();

so I need to count the number of records retrieved by that SQL query in that moment. Help please

[Former Community Member]

You coudl move the the cursor to the last record and get the record number for that record. There is no count function that will give you the number of records that have been returned.

Paul

[Former Community Member]

hey paul, when you say move the cursor do you mean the datawindow object??

[Former Community Member]

No the cursor in the recordset that si returned in your call to the DB. You did a first command in the script ...there is also a Last command that will move to the last record and that is what I mean.

Paul

[Former Community Member]

got it but I think that will also move to the next or last record and display it on the field and I dont want that, any ideas? I just want to get a count while being in the same record.

[Former Community Member · Accepted solution]

So move the cursor back to the first record after you have your count.

There is a count keyword in SQL that will allow you to get the count from your query. You could execute that first - get your count, then execute the real query to get the recordset. This woudl require a separate data connection. Here is some example code.

var inName = xfa.event.newText;
if (inName == ""){
app.alert("You must enter a valid name - try again!")
}
var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection2")
{
nIndex++;
}

var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
//app.alert(oDB.saveXML("pretty"));

var nIndex = 0;
while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection3")
{
nIndex++;
}
var oDBCount = xfa.sourceSet.nodes.item(nIndex).clone(1); // the node pertaining to the data connection specified
//set up sql call to DB to get number of records that match the criteria

oDBCount.nodes.item(1).query.setAttribute("text", "commandType");
oDBCount.nodes.item(1).query.select.nodes.item(0).value = "Select count(*) from table1 where AcctNumber = '" + inName + "'";
oDBCount.open()
oDBCount.close()

//set up sql call to DB to get the specified employee number

oDB.nodes.item(1).query.setAttribute("text", "commandType");
oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from table1 where AcctNumber = '" + inName + "'";
//app.alert(oDB.nodes.item(1).saveXML("pretty"));

//now connect to DB and get a record
oDB.open()
oDB.close();

Note the SQL command the uses the count keyword (have a look at the oDBCount section). This will return a count back to the cllaer. When I setup the data connection there was a count(*) node that appeared. I dragged that onto the form and it created a field called count that was bound to this node. When the query is executed the resulting number of records is returned to this field. You can have it hidden so your users cannot see it and also change the binding to none so it is not included in the data file that is submitted when the form is complete.

Paul