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):
function CountTable() { $("#Field6").val($("#q4 tbody tr").length); } $(document).ready(function(){ $(document).on('onloadlookupfinished', CountTable) })
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:
function CountTable() { $("#Field6").val($("#q4 tbody tr").length); } $(document).ready(function(){ $("#Field7").on('change', CountTable) })
In case someone needs this kind of script in the future, here is my solution:
$(document).ready(function () { $.fn.rowCount = function() { //this is the Function we will call return $('tr', $(this).find('tbody')).length; }; $("#q14").on('change', function(){ //on table change, this is the id of the table rowctr = $('#q14').rowCount(); //function to calculate the rows of the table //alert(rowctr); //you can enable this line in testing var count = $("#Field18").val(rowctr); //this is my single line field }); });
#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.