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
Question
Question
How to count the number of rows in a table?
Replies
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):
1 | function 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.
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
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:
1 | function CountTable() { |
2 | $( "#Field6" ).val($( "#q4 tbody tr" ).length); |
3 | } |
4 | $(document).ready( function (){ |
5 | $( "#Field7" ).on( 'change' , CountTable) |
6 | }) |
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.