6 Replies Latest reply on May 21, 2014 6:17 AM by AppGuy

    Using Offset and Fetch First... Execute SQL to return first 50 records.. then the next 50 and so...

    AppGuy

      Title

      Using Offset and Fetch First... Execute SQL to return first 50 records.. then the next 50 and so on..

      Post

           What I would like to do is use the new feature in execute sql called offset and fetch first..  ( I think this would be the easiest and fastest way.)  

           Tables I am pulling from is TimeCards and EmployeeTime.  

           They are joined together by the TimeCards::__pk_ID --> EmployeeTime::_fk_TimeCards

           This is a hosted solution and due the the speed of sorting (pretty slow).  I need to limit the number of records that are shown at a time and then have a next and back button.

           The best way seems to be using this new method but I can't find much documentation on it yet..

           Documentation : Here

           I am hoping someone who is proficient in Execute SQL can lend me a hand.. :)

           How would I go about creating the next and back buttons? Do I just pass a parameter to the script?

           I would imaging something like this..

            

           I assume this would be the initial search...

      SELECT * FROM TIMECARDS FETCH FIRST 50 ROWS ONLY  -  I would need to specify the search criteria somewhere..??  For instance..  on the List View I use - TimeCards::Approver and TimeCards::Date fields in the TimeCards Table

           Then - Next and back buttons.

      SELECT * FROM TIMECARDS OFFSET 50 ROWS FETCH FIRST 50 ROWS ONLY  -  NEED HELP HERE..  ??  I assume I need a variable or parameter passed here to go next and back..??  not sure..

           Any help or suggestions would be great..  

           attached is a screenshot of the list view..

           Thanks,

           JP

      Screen_Shot_2014-05-12_at_11.06.04_AM.png