You are viewing limited content. For full access, please sign in.

Question

Question

How to count the number of rows in a table?

asked on February 27, 2017

We have a form with a table that is populated from a sql query.   I need to know how many rows the table contains once the query runs and put that value into a field.   Is there a simple jquery that will count the rows after the sql query runs and populate a new field on the form?  My attempts have not worked well and I am hitting a deadline for this.    I don't even need an exact count of the rows, just if there are any rows that were added to the table, a yes/no - 1/0, would suffice.

Normally I would just add this information into the sql query itself but it's a rather complex query using  a linked server with Active Directory and does not lend itself to a count.

Any help or guidance appreciated   ( Forms 10.1.0.642 )
Thanks ,
Andrew

0 0

Replies

replied on February 27, 2017 Show version history

If you only need to update table count when lookup finishes, you could use script like this (q4 is table id while Field6 is target field input id):

1function CountTable() {
2  $("#Field6").val($("#q4 tbody tr").length);
3}
4$(document).ready(function(){
5  $(document).on('onloadlookupfinished', CountTable)
6})

p.s. onloadlookupfinished is available on Forms 10.1 and above.

1 0
replied on February 28, 2017

Hi Rui,

Thanks for the help but I still can't get this to work.    I believe I need to update to 10.2 so that I can use the lookupcomplete event instead of onloadlookupfinished.   My table is populated via a trigger condition, not during the initial form load.
Until we get to 10.2 I will need to run an additional time consuming query to get the row count or figure out a different trigger event on the page.

Andrew

0 0
replied on February 28, 2017

For 10.1, here is a workaround:

First set formula "=Table.Column_1" on a single line field (Field7 in my case), then use custom script:

1function CountTable() {
2  $("#Field6").val($("#q4 tbody tr").length);
3}
4$(document).ready(function(){
5  $("#Field7").on('change', CountTable)
6})

 

0 0
replied on June 13, 2018

In case someone needs this kind of script in the future, here is my solution:

01$(document).ready(function () {
02$.fn.rowCount = function() { //this is the Function we will call
03    return $('tr', $(this).find('tbody')).length;
04};
05 
06  $("#q14").on('change', function(){ //on table change, this is the id of the table
07 
08  rowctr = $('#q14').rowCount(); //function to calculate the rows of the table
09  //alert(rowctr); //you can enable this line in testing
10    var count = $("#Field18").val(rowctr);  //this is my single line field
11    });
12});

#q14 is my table id

#Field18 is my single line field id

 

There is nothing else you need to set, no functions on the fields themselves in the Layout tab.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.