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

Question

Question

Using Forms with lookup rules and a stored procedure to search SQL database

Forms Version 10 Search and Retrieval Ideas How To
Updated May 19, 2023
asked on May 16, 2022

We have a form setup to use lookup rules and a stored procedure in SQL to search and return records for our Veterans Services department.  It's normally pretty quick, but when it's a few thousand records, it's really slow when returning in Forms.  But, when run directly in SQL, it's under a second.

Has anyone else run into this problem that may have found a workaround or fix?  Also, are there other/better ways that someone with experience with this may want to share?  Any help or advice with this would be greatly appreciated.  Thanks!

0 0

Answer

SELECTED ANSWER
replied on May 17, 2022 • Show version history

Long instructions...

Note that @████████'s stored procedure example works a little differently than mine.  His takes in a page size and page count, whereas mine takes in the index number and page size.  His is smarter because it would save the intermediate step I'm doing in LFForms to calculate the index from the page number.  But my example is already fully built and tested this way, so I didn't want to edit it.

This is all done in on-prem Forms Version 11 Update 2.

Step 1 - Set-up two stored procedures in your database.  One will do your record search, and the other will just return the total number of records for the same search.

USE [database_name_goes_here]
GO

/****** Object:  StoredProcedure [dbo].[ExamplePaginated]    Script Date: 5/17/2022 10:08:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Matthew Tingey
-- Create date:   May 17, 2022
-- Description:   Returns a list of database records, in a paginated format.
-- =============================================
CREATE PROCEDURE [dbo].[ExamplePaginated] 
    -- Add the parameters for the stored procedure here
       @startRowIndex int,
       @pageSize int,
       --The @startRowIndex and @pageSize are the parameters for the paging, any other parameters are search criteria
       --for use in the WHERE statement and need to match the other stored procedure.
       @param1 VARCHAR(25),
       @param2 VARCHAR(25)
       
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the paginated records
    SELECT
      e.[column1],
      e.[column2],
      e.[column3],
      e.[column4],
      e.[column5]
    FROM [database_name_goes_here].[dbo].[table_name_goes_here] AS e
    -- NOTE THAT THE WHERE STATEMENT MUST MATCH THE OTHER STORED PROCEDURE
    WHERE 
      (
        e.[column1] = @param1                          --this checks for exact match of the parameter
        OR
        e.[column1] LIKE CONCAT('%', @param1, '%')     --this checks for partial matches of the parameter
        OR
        @param1 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
      AND
      (
        e.[column2] = @param2                          --this checks for exact match of the parameter
        OR
        e.[column2] LIKE CONCAT('%', @param2, '%')     --this checks for partial matches of the parameter
        OR
        @param2 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
    ORDER BY e.[column1]
    OFFSET @startRowIndex ROWS FETCH NEXT @pageSize ROWS ONLY

END
GO

 

USE [database_name_goes_here]
GO

/****** Object:  StoredProcedure [dbo].[ExamplePaginatedTotal]    Script Date: 5/17/2022 10:08:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Matthew Tingey
-- Create date:   May 17, 2022
-- Description:   Returns a total count of database records, for use with the paginated report.
-- =============================================
CREATE PROCEDURE [dbo].[ExamplePaginatedTotal] 
    -- Add the parameters for the stored procedure here
       
       --The @startRowIndex and @pageSize parameters are not used on this procedure, but any other parameters are search criteria
       --for use in the WHERE statement and need to match the other stored procedure.
       @param1 VARCHAR(25),
       @param2 VARCHAR(25)
       
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the paginated records
    SELECT
      COUNT(e.[column1]) AS total_count
    FROM [database_name_goes_here].[dbo].[table_name_goes_here] AS e
    -- NOTE THAT THE WHERE STATEMENT MUST MATCH THE OTHER STORED PROCEDURE (THE TOTALS)
    WHERE 
      (
        e.[column1] = @param1                          --this checks for exact match of the parameter
        OR
        e.[column1] LIKE CONCAT('%', @param1, '%')     --this checks for partial matches of the parameter
        OR
        @param1 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
      AND
      (
        e.[column2] = @param2                          --this checks for exact match of the parameter
        OR
        e.[column2] LIKE CONCAT('%', @param2, '%')     --this checks for partial matches of the parameter
        OR
        @param2 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
    
END
GO

 

Step 2 - In Forms, add the following fields to your form.  The labels do not matter because the fields are going to be rearranged/hidden by Javascript.

  1. Drop-down field.  Leave it editable.  Add choices like (10, 25, 50, 100, 250, 500).  Set 10 as the default value.  CSS Class Name (and variable name): page_size
  2. Number field.  Leave it editable.  Zero decimal places.  Thousands delimiter off.  Default value of 0.  Formula:   =((current_page-1)*page_size)   CSS Class Name (and variable name): hidden_field_start_row_index
  3. Number field.  Leave it editable.  Zero decimal places.  Thousands delimiter off.  Default value of 0.  CSS Class Name (and variable name): hidden_field_total_record_count
  4. Number field.  Leave it editable.  Range allowed 1-9999.  Zero decimal places.  Thousands delimiter off.  Default value of 1.  CSS Class Name (and variable name): current_page
  5. Number field.  Mark it read-only.  Range allowed 1-9999. Zero decimal places. Thousands delimiter off. Default value of 1.  Formula:   =ROUNDDOWN((hidden_field_total_record_count)/page_size)+1    CSS Class Name (and variable name): last_page
  6. Add any other fields for the search parameters you included in your stored procedures.

 

Step 3 - Add your table to the form, formatting it as you desire.

 

Step 4 - Add a custom HTML element below your table.  

<div style="padding-bottom:8px" id="tableFooterPagination">
  <div style="display: inline-block; width: 18%; text-align: left" id="tableFooterCurrentRange">
    <b><i>0 - 0 of 0</i></b>
  </div>
  <div style="display: inline-block; width: 52%; text-align: center" id="tableFooterNavigation">
    <div style="display: inline-block; width: 18%;">
    </div>
    <div style="display: inline-block; width: 6%;">
      <button type="button" id="tableFooterNavigateLeft2">
        &lt;&lt;
      </button>
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 4%;">
      <button type="button" id="tableFooterNavigateLeft1">
        &lt;
      </button>
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 10%; text-align: center" id="tableFooterNagivateCurrentPage">
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 2%; text-align: center">
      of
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 10%; text-align: center" id="tableFooterNagivateLastPage">
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 4%;">
      <button type="button" id="tableFooterNavigateRight1">
        &gt;
      </button>
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 6%;">
      <button type="button" id="tableFooterNavigateRight2">
        &gt;&gt;
      </button>
    </div>
    <div style="display: inline-block; width: 18%;">
    </div>
  </div>
  <div style="display: inline-block; width: 18%; text-align: right" id="tableFooterShowItems">
    <b>Items per Page:&nbsp;</b>
  </div>
</div>

 

Step 5 - Set-up lookups to populate your table and the total count from the two Stored Procedures you added in step 1.

 

Step 6 - Add Javascript to the form to rearrange the fields and manage the navigation of the table pages.

$(document).ready(function () {
  
  //Changes to the form layout when it is loaded to facilitate the table footer.
  $('.page_size').hide();
  $('.hidden_field_total_record_count').hide();
  $('.current_page').hide();
  $('.last_page').hide();
  $('.hidden_field_start_row_index').hide();
  $('.page_size select').insertAfter('#tableFooterShowItems').addClass('page_size_select');
  $('.page_size_select').css('width', '7%');
  $('.current_page input').appendTo('#tableFooterNagivateCurrentPage').addClass('current_page_input');
  $('.last_page input').appendTo('#tableFooterNagivateLastPage').addClass('last_page_input');
  $('.current_page_input').css('text-align', 'center');
  $('.last_page_input').css('text-align', 'center');
  $('.current_page_input').css('width', '100%');
  $('.last_page_input').css('width', '100%');
  
  //Set the current page field to a max of the last page field.
  $('.last_page_input').change(function() {
    $('.current_page_input').attr('max', parseInt($('.last_page_input').val()));
  });
  
  //Reset the current page to 1 if the page size is changed.
  $('.page_size_select').change(function() {
    $('.current_page_input').val(1).trigger('change');
  });
  
  //Function that can be called (and re-called as needed) to set-up and manage
  //the tableFooter for navigating the table details.
  function UpdateFooterDetails()
  {
    var startIndex = parseInt($('.hidden_field_start_row_index input').val()) + 1;
    var endIndex = parseInt($('.hidden_field_start_row_index input').val()) + parseInt($('.page_size_select').val());
    var totalCount = parseInt($('.hidden_field_total_record_count input').val());
    if(endIndex >= totalCount)
    {
      endIndex = totalCount;
    }
    var newHTML = '<b><i>' + startIndex + '-' + endIndex + ' of ' + totalCount + '</i></b>';
    $('#tableFooterCurrentRange').html(newHTML);
    $('.page_size_select').css('width', '7%');
    $('.current_page_input').css('width', '100%');
    $('.last_page_input').css('width', '100%');
    if(parseInt($('.current_page_input').val()) == 1)
    {
      $('#tableFooterNavigateLeft2').attr('disabled', 'true').css('opacity',0.5);
      $('#tableFooterNavigateLeft1').attr('disabled', 'true').css('opacity',0.5);
    }
    else
    {
      $('#tableFooterNavigateLeft2').removeAttr('disabled').css('opacity',1);
      $('#tableFooterNavigateLeft1').removeAttr('disabled').css('opacity',1);
    }
    if(parseInt($('.current_page_input').val()) >= parseInt($('.last_page_input').val()))
    {
      $('#tableFooterNavigateRight1').attr('disabled', 'true').css('opacity',0.5);
      $('#tableFooterNavigateRight2').attr('disabled', 'true').css('opacity',0.5);
    }
    else
    {
      $('#tableFooterNavigateRight1').removeAttr('disabled').css('opacity',1);
      $('#tableFooterNavigateRight2').removeAttr('disabled').css('opacity',1);
    }
  }
  
  //Trigger the update of footer details when form loads and when lookups are complete.
  UpdateFooterDetails();
  $(document).on('lookupcomplete', function() {
    UpdateFooterDetails();
  });
  
  //Change the page number when the page navigation buttons are clicked.
  $('#tableFooterNavigateLeft2').click(function() {
    $('.current_page_input').val(1).trigger('change');
    UpdateFooterDetails();
  });
  $('#tableFooterNavigateLeft1').click(function() {
    $('.current_page_input').val(parseInt($('.current_page_input').val()) - 1).trigger('change');
    UpdateFooterDetails();
  });
  $('#tableFooterNavigateRight1').click(function() {
    $('.current_page_input').val(parseInt($('.current_page_input').val()) + 1).trigger('change');
    UpdateFooterDetails();
  });
  $('#tableFooterNavigateRight2').click(function() {
    $('.current_page_input').val(parseInt($('.last_page_input').val())).trigger('change');
    UpdateFooterDetails();
  });
  
});

 

The end result should look something like this:

2 0

Replies

replied on May 16, 2022

Are you returning results to a table? If so, the slowness is probably not the lookup, it's the time it takes to process the response and add all the HTML associated with table rows.

Anything more than a few hundred results in a lookup that adds rows to a table is going to get increasingly slower as the result set grows.

IE could barely handle 100-200 rows; the current versions of Edge and Chrome can handle a lot more, but even they can only process so much data.

I think the workaround/solution is going to depend on what you need to do with the data, how many columns you need, etc. so more information about the use case would help.

1 0
View 6 previous replies
replied on May 16, 2022

Yes sir - we are returning results to a table.  14 columns at sometimes.  We're converting an old Access DB over to SQL that our Veterans Services department uses, and wanting to use Forms as the front end.

One of the searches they are accustomed to having is to click and have all veterans records display at once without typing any search terms.  When doing this or a general search term that returns thousands of results, it bogs it down.  Also have the ability to search by name, ssn, what war(s) they may have served in, still living, deceased, etc.  Ability to insert new records, and update or delete those that are already present. 

We appreciate any help or insight you are willing to offer.  Thanks for your response.

1 0
replied on May 16, 2022 • Show version history

I'm not sure if there's much you'll be able to do to make the page handle/display that many interactive HTML elements quickly and even after they're rendered the page could be slow/unresponsive.

With 14 columns, that means every row is adding 14 new fields, and all their related elements, so with 1,000 rows you're looking at tens of thousands of new HTML elements being added to the page in real time.

Even if not all of the columns are populated on every row, that's still a lot more data to process into the page than I would recommend.

Forms definitely has all the components for an easy-to-build data management front end, but web forms in general are just not optimized for bulk data and even with a custom-built application you'd probably have trouble making something like this work efficiently.

Even SSRS gets a bit slow when rendering large amounts of data like that so it typically defaults to pagination.

If I had to do something like this in Forms, I'd probably paginate the data on the SQL side so it could be retrieved in more manageable chunks.

One thing I played around with in the past was adding a "page" parameter to my stored procedure and using OFFSET to return the data in chunks.

Something like

DECLARE @Page INT;

DECLARE @Offset INT;
DECLARE @PageSize INT;

SET @PageSize = 100;
SET @Offset = (ISNULL(@Page,1) - 1) * @PageSize;

SELECT *
  FROM [database].[dbo].[table]
ORDER BY [id] ASC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY

Basically, instead of returning everything all at once, you would return chunks of data and give the user a way to navigate through it all.

Then, you could add a hidden number field on the form, defaulted to 1, set it up as the Page parameter of the stored procedure, and add some custom HTML buttons to increase/decrease the value.

2 0
replied on May 17, 2022

Nice!  Definitely going to save this snippet.

0 0
replied on May 17, 2022

I have a working example set-up that does pagination in SQL and includes in Forms the functionality to navigate through the pages.

I'd be happy to share instructions and the Javascript that I used for this.

2 0
replied on May 17, 2022

That would be awesome Matthew!

1 0
SELECTED ANSWER
replied on May 17, 2022 • Show version history

Long instructions...

Note that @████████'s stored procedure example works a little differently than mine.  His takes in a page size and page count, whereas mine takes in the index number and page size.  His is smarter because it would save the intermediate step I'm doing in LFForms to calculate the index from the page number.  But my example is already fully built and tested this way, so I didn't want to edit it.

This is all done in on-prem Forms Version 11 Update 2.

Step 1 - Set-up two stored procedures in your database.  One will do your record search, and the other will just return the total number of records for the same search.

USE [database_name_goes_here]
GO

/****** Object:  StoredProcedure [dbo].[ExamplePaginated]    Script Date: 5/17/2022 10:08:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Matthew Tingey
-- Create date:   May 17, 2022
-- Description:   Returns a list of database records, in a paginated format.
-- =============================================
CREATE PROCEDURE [dbo].[ExamplePaginated] 
    -- Add the parameters for the stored procedure here
       @startRowIndex int,
       @pageSize int,
       --The @startRowIndex and @pageSize are the parameters for the paging, any other parameters are search criteria
       --for use in the WHERE statement and need to match the other stored procedure.
       @param1 VARCHAR(25),
       @param2 VARCHAR(25)
       
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the paginated records
    SELECT
      e.[column1],
      e.[column2],
      e.[column3],
      e.[column4],
      e.[column5]
    FROM [database_name_goes_here].[dbo].[table_name_goes_here] AS e
    -- NOTE THAT THE WHERE STATEMENT MUST MATCH THE OTHER STORED PROCEDURE
    WHERE 
      (
        e.[column1] = @param1                          --this checks for exact match of the parameter
        OR
        e.[column1] LIKE CONCAT('%', @param1, '%')     --this checks for partial matches of the parameter
        OR
        @param1 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
      AND
      (
        e.[column2] = @param2                          --this checks for exact match of the parameter
        OR
        e.[column2] LIKE CONCAT('%', @param2, '%')     --this checks for partial matches of the parameter
        OR
        @param2 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
    ORDER BY e.[column1]
    OFFSET @startRowIndex ROWS FETCH NEXT @pageSize ROWS ONLY

END
GO

 

USE [database_name_goes_here]
GO

/****** Object:  StoredProcedure [dbo].[ExamplePaginatedTotal]    Script Date: 5/17/2022 10:08:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Matthew Tingey
-- Create date:   May 17, 2022
-- Description:   Returns a total count of database records, for use with the paginated report.
-- =============================================
CREATE PROCEDURE [dbo].[ExamplePaginatedTotal] 
    -- Add the parameters for the stored procedure here
       
       --The @startRowIndex and @pageSize parameters are not used on this procedure, but any other parameters are search criteria
       --for use in the WHERE statement and need to match the other stored procedure.
       @param1 VARCHAR(25),
       @param2 VARCHAR(25)
       
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Get the paginated records
    SELECT
      COUNT(e.[column1]) AS total_count
    FROM [database_name_goes_here].[dbo].[table_name_goes_here] AS e
    -- NOTE THAT THE WHERE STATEMENT MUST MATCH THE OTHER STORED PROCEDURE (THE TOTALS)
    WHERE 
      (
        e.[column1] = @param1                          --this checks for exact match of the parameter
        OR
        e.[column1] LIKE CONCAT('%', @param1, '%')     --this checks for partial matches of the parameter
        OR
        @param1 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
      AND
      (
        e.[column2] = @param2                          --this checks for exact match of the parameter
        OR
        e.[column2] LIKE CONCAT('%', @param2, '%')     --this checks for partial matches of the parameter
        OR
        @param2 = 'All'                                --this allows the parameter to be sent as "All" and return everything
      )
    
END
GO

 

Step 2 - In Forms, add the following fields to your form.  The labels do not matter because the fields are going to be rearranged/hidden by Javascript.

  1. Drop-down field.  Leave it editable.  Add choices like (10, 25, 50, 100, 250, 500).  Set 10 as the default value.  CSS Class Name (and variable name): page_size
  2. Number field.  Leave it editable.  Zero decimal places.  Thousands delimiter off.  Default value of 0.  Formula:   =((current_page-1)*page_size)   CSS Class Name (and variable name): hidden_field_start_row_index
  3. Number field.  Leave it editable.  Zero decimal places.  Thousands delimiter off.  Default value of 0.  CSS Class Name (and variable name): hidden_field_total_record_count
  4. Number field.  Leave it editable.  Range allowed 1-9999.  Zero decimal places.  Thousands delimiter off.  Default value of 1.  CSS Class Name (and variable name): current_page
  5. Number field.  Mark it read-only.  Range allowed 1-9999. Zero decimal places. Thousands delimiter off. Default value of 1.  Formula:   =ROUNDDOWN((hidden_field_total_record_count)/page_size)+1    CSS Class Name (and variable name): last_page
  6. Add any other fields for the search parameters you included in your stored procedures.

 

Step 3 - Add your table to the form, formatting it as you desire.

 

Step 4 - Add a custom HTML element below your table.  

<div style="padding-bottom:8px" id="tableFooterPagination">
  <div style="display: inline-block; width: 18%; text-align: left" id="tableFooterCurrentRange">
    <b><i>0 - 0 of 0</i></b>
  </div>
  <div style="display: inline-block; width: 52%; text-align: center" id="tableFooterNavigation">
    <div style="display: inline-block; width: 18%;">
    </div>
    <div style="display: inline-block; width: 6%;">
      <button type="button" id="tableFooterNavigateLeft2">
        &lt;&lt;
      </button>
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 4%;">
      <button type="button" id="tableFooterNavigateLeft1">
        &lt;
      </button>
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 10%; text-align: center" id="tableFooterNagivateCurrentPage">
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 2%; text-align: center">
      of
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 10%; text-align: center" id="tableFooterNagivateLastPage">
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 4%;">
      <button type="button" id="tableFooterNavigateRight1">
        &gt;
      </button>
    </div>
    <div style="display: inline-block; width: 1%;">
    </div>
    <div style="display: inline-block; width: 6%;">
      <button type="button" id="tableFooterNavigateRight2">
        &gt;&gt;
      </button>
    </div>
    <div style="display: inline-block; width: 18%;">
    </div>
  </div>
  <div style="display: inline-block; width: 18%; text-align: right" id="tableFooterShowItems">
    <b>Items per Page:&nbsp;</b>
  </div>
</div>

 

Step 5 - Set-up lookups to populate your table and the total count from the two Stored Procedures you added in step 1.

 

Step 6 - Add Javascript to the form to rearrange the fields and manage the navigation of the table pages.

$(document).ready(function () {
  
  //Changes to the form layout when it is loaded to facilitate the table footer.
  $('.page_size').hide();
  $('.hidden_field_total_record_count').hide();
  $('.current_page').hide();
  $('.last_page').hide();
  $('.hidden_field_start_row_index').hide();
  $('.page_size select').insertAfter('#tableFooterShowItems').addClass('page_size_select');
  $('.page_size_select').css('width', '7%');
  $('.current_page input').appendTo('#tableFooterNagivateCurrentPage').addClass('current_page_input');
  $('.last_page input').appendTo('#tableFooterNagivateLastPage').addClass('last_page_input');
  $('.current_page_input').css('text-align', 'center');
  $('.last_page_input').css('text-align', 'center');
  $('.current_page_input').css('width', '100%');
  $('.last_page_input').css('width', '100%');
  
  //Set the current page field to a max of the last page field.
  $('.last_page_input').change(function() {
    $('.current_page_input').attr('max', parseInt($('.last_page_input').val()));
  });
  
  //Reset the current page to 1 if the page size is changed.
  $('.page_size_select').change(function() {
    $('.current_page_input').val(1).trigger('change');
  });
  
  //Function that can be called (and re-called as needed) to set-up and manage
  //the tableFooter for navigating the table details.
  function UpdateFooterDetails()
  {
    var startIndex = parseInt($('.hidden_field_start_row_index input').val()) + 1;
    var endIndex = parseInt($('.hidden_field_start_row_index input').val()) + parseInt($('.page_size_select').val());
    var totalCount = parseInt($('.hidden_field_total_record_count input').val());
    if(endIndex >= totalCount)
    {
      endIndex = totalCount;
    }
    var newHTML = '<b><i>' + startIndex + '-' + endIndex + ' of ' + totalCount + '</i></b>';
    $('#tableFooterCurrentRange').html(newHTML);
    $('.page_size_select').css('width', '7%');
    $('.current_page_input').css('width', '100%');
    $('.last_page_input').css('width', '100%');
    if(parseInt($('.current_page_input').val()) == 1)
    {
      $('#tableFooterNavigateLeft2').attr('disabled', 'true').css('opacity',0.5);
      $('#tableFooterNavigateLeft1').attr('disabled', 'true').css('opacity',0.5);
    }
    else
    {
      $('#tableFooterNavigateLeft2').removeAttr('disabled').css('opacity',1);
      $('#tableFooterNavigateLeft1').removeAttr('disabled').css('opacity',1);
    }
    if(parseInt($('.current_page_input').val()) >= parseInt($('.last_page_input').val()))
    {
      $('#tableFooterNavigateRight1').attr('disabled', 'true').css('opacity',0.5);
      $('#tableFooterNavigateRight2').attr('disabled', 'true').css('opacity',0.5);
    }
    else
    {
      $('#tableFooterNavigateRight1').removeAttr('disabled').css('opacity',1);
      $('#tableFooterNavigateRight2').removeAttr('disabled').css('opacity',1);
    }
  }
  
  //Trigger the update of footer details when form loads and when lookups are complete.
  UpdateFooterDetails();
  $(document).on('lookupcomplete', function() {
    UpdateFooterDetails();
  });
  
  //Change the page number when the page navigation buttons are clicked.
  $('#tableFooterNavigateLeft2').click(function() {
    $('.current_page_input').val(1).trigger('change');
    UpdateFooterDetails();
  });
  $('#tableFooterNavigateLeft1').click(function() {
    $('.current_page_input').val(parseInt($('.current_page_input').val()) - 1).trigger('change');
    UpdateFooterDetails();
  });
  $('#tableFooterNavigateRight1').click(function() {
    $('.current_page_input').val(parseInt($('.current_page_input').val()) + 1).trigger('change');
    UpdateFooterDetails();
  });
  $('#tableFooterNavigateRight2').click(function() {
    $('.current_page_input').val(parseInt($('.last_page_input').val())).trigger('change');
    UpdateFooterDetails();
  });
  
});

 

The end result should look something like this:

2 0
replied on May 18, 2022

You should be lead through the office by co-workers scattering rose petals in your path.

1 0
replied on May 18, 2022

Thanks @████████.  Can't wait to see what we can do with this, and see this coming in very handy down the road.

 

Thanks again for sharing!

1 0
replied on May 18, 2022 • Show version history

Hey @████████- that's hilarious.  Unfortunately, most of my coworkers appreciate what I do but have no actual clue what it is.  But I did win an award from Empower this year, so I have that going for me.  wink

@████████- I'm just happy to help, if this helps you in any way with your current question or a future endeavor, then I've achieved my goal.

1 0
replied on May 18, 2023

This is amazing and looks great!  However, my stored procedures pull in a few seconds in SQL but when I try the form it spins forever and then I can't move around in Forms.  I have to clear all my history and sign back in.  What might I be doing wrong?

0 0
replied on May 18, 2023

Keep in mind that pulling the data in SQL is returning the data in a dedicated UI but pulling it in Forms is a lot more involved.

In a web form, not only does it have to get all the data to the client browser it also has to build all of the HTML elements, assign event handlers, etc.

Performance is really going to depend on the number of rows and columns you're trying to return in the results.

The first sign that it might be too much is that it is taking 3 seconds in SQL (I assume you mean SSMS), because that's a relatively long time for SQL.

1 0
replied on May 19, 2023

Yes SSMS. I adjusted the view that the sps are pulling from and the results come back in 1 sec now, however forms is still having the same issue. It is a lot of data, a little over 2,000 rows which I guess forms cannot handle.

 

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

Sign in to reply to this post.

Details
Related Posts
Loading ...