1 2 Previous Next 17 Replies Latest reply on Oct 9, 2015 4:53 AM by kbleicken

    How to find the next 20 records using a script

      Using a script how can I find the next 20 records in a found set?

        • 1. Re: How to find the next 20 records using a script
          beverly

          Can you explain more what you are trying to do?

          • 2. Re: How to find the next 20 records using a script

            The found set might be 400 in a list view. I want to find and display the first 20 records. Then using a > button go the the next 20 records and so on.

            • 3. Re: How to find the next 20 records using a script
              erolst

              One idea:

               

              • maintain a ListOf summary field for the table's primary key

              • add a global text field

              • use it to create a self-join to another TO of the table

               

              When you have your found set, let your script

              • set a $$listOfIDs to the found IDs in the summary field

              • maintain a $$pageNumber

              • set the global field to MiddleValues ( $$listOfIDs ; ( $$pageNumber - 1) * 20 ; 20 )

              • GtRR to the self-join TO, using <current layout>


              Find a method to set $$listOfIDs only after establishing the found set, not while paging.

              • 4. Re: How to find the next 20 records using a script
                beverly

                There is a script step:

                 

                Go to Record/Request/Page ( By calculation) and you can specify a record number. I use this by calculations next/prev and placing in the script parameter, then the record number goes +/_ by 20 (or 25 or ??)

                 

                Beverly

                • 5. Re: How to find the next 20 records using a script

                  Hi Beverly,

                   

                  That would solve the navigation part, but I'm trying to isolate, create a found set of 20 of the original 400.  And then go to the next set of 20.

                   

                  The reason for this is that with the smaller set, popovers work and the 'refresh object' script step also works, both of which I need. They both don't work with larger found sets.

                   

                  Thank you for your suggestion. I think it may work combined with the suggest from erolst in this thread.

                   

                  Kurt

                  • 6. Re: How to find the next 20 records using a script

                    Thank you, this sounds like it is on the right track. I'll go down this path.

                     

                    Kurt

                    • 7. Re: How to find the next 20 records using a script
                      erolst

                      Right.

                       

                      btw, you will notice this on your own, but the calculation to get the correct list slice is

                       

                      MiddleValues ( $$listOfIDs ; ( $$pageNumber - 1 ) * 20 + 1 ; 20 )

                      • 8. Re: How to find the next 20 records using a script
                        Nehme

                        This is how I imaging things:

                        Create an additional field in your table: Number field (we will call it "Record Nb")

                         

                        And this is the script (you can add this script at the end of your other scripts - exp at the end of the search script):

                        - Replace Field Content [ Table::Record Nb ; Get ( RecordNumber ) ]

                        - Enter Find Mode

                        - Set Field [ Table::Record Nb ; 1...20 ]

                        - Perform Find

                         

                        Script for advancing a page:

                        - Go to record [First]

                        - Set Variable [$NbStart ; Table::Record Nb ]

                        - Set Variable [$NbEnd ; $NbStart + 20 ]

                        - Enter Find Mode

                        - Set Field [ Table::Record Nb ; $NbStart...$NbEnd ]

                        - Perform Find


                        Do the other way for previous records

                        "..." = find a range

                        You might put all those in a single script using script parameters.

                         

                        Just be sure to have your Record Nb field empty before starting the script to prevent having duplicate values

                        - Show All Records

                        - Replace Field Content [ Table::Record Nb ; "" ]

                        (You might put this at the beginning of your other scripts)


                        Hope this helps

                        • 9. Re: How to find the next 20 records using a script
                          alquimby

                          Here’s a method, with little scripting, that I use to view lists of records. It displays the list much quicker, and there is no scrolling. This works best with a full set of records.

                           

                            In the table you want to view, create a number field called Counter, set to auto serial increment by 1.

                           

                            Place the field on a layout, sort, or unsort the records, then use Replace to populate Counter field starting from 1. Since it is an auto serial, new records will get a Counter number after you have done the Replace.

                           

                            Then create this stored calculation in the same table called cGroup: Ceiling ( Counter / 20 ). This puts every record in a group (20 records in each group except the last group that may have 20 or fewer).

                           

                            Now create another table with a global number field called Group. Create a reference from the table where your records are to the new table (cGroup = Group). Create a new layout from the new Group table and put a portal from the records table showing 20 records. Put the Group global field above it and enter “1.” You should get all the records from Group 1 in the records table. Then change the “1” to “2”––you get a new set of records. You can create a short script using Get (ScriptParameter) to move the number in the global field up or down (or just use a value list with the numbers 1 to whatever.

                           

                            Should you delete records in your records table, just use Replace again to re-populate the Counter field, which updates the calculation and, thus, each Group. If you decide you want to see 25 records instead of 20, just change your calculation field.

                           

                          Al Quimby

                          • 10. Re: How to find the next 20 records using a script

                            HI erolst,

                             

                            I get to the point where the global has the list of the first 20. I put a portal in the original layout using the relationship between the global g_listOf and the g_listOf of the  self-join TO. But all found records in the TO are showing up in the portal.

                             

                            I suspect that I did something wrong in the relationship.

                             

                            Capture.PNG

                            Thanks

                            • 11. Re: How to find the next 20 records using a script

                              Hi Allan and Nehme,

                               

                              Thanks for your response. I initially set up my scripts using your approach. Setting up a new record number field, inserting a sequential record number into the field of the found set, then searching for the first 20, the next 20 etc. Worked perfectly.

                               

                              However, after thinking it over if there is another user doing a similar search my record numbers will screw up his/her search since these record numbers are not acting like globals. They are the same for all users. So I abandoned that approach.

                               

                              Am I missing something?

                              • 12. Re: How to find the next 20 records using a script
                                erolst

                                kbleicken wrote:

                                I get to the point where the global has the list of the first 20. I put a portal in the original layout using the relationship between the global g_listOf and the g_listOf of the  self-join TO. But all found records in the TO are showing up in the portal.

                                 

                                Actually, you shouldn't see any related records - a global field cannot be on the right/far side of a relationship. The relationship needs to be defined as

                                 

                                Table::g_List = Table_selfJoin::ID

                                • 13. Re: How to find the next 20 records using a script
                                  alquimby

                                  In my earlier post, I should have written the relationship as Group = cGroup, with Group being the global field.

                                  • 14. Re: How to find the next 20 records using a script

                                    OK, finally all is working beautifully. Thanks to all the help from friends on the Forum.

                                     

                                    I've attached the code in case someone later on might find it useful. The key thing is that the found set is first found in the (in this case) the Alum table, then create the ListOfIds variable with a summary field list of the ids in that found set, then use that to pull the fields into a portal with a relationship with a different table occurrence of the Alums table. Using the global field Alums: g_listOf related to the __pk_alum  field in the TO. (hope this is clear)

                                     

                                    To navigate just change the page number to go to the next 20 records.

                                     

                                    Capture.PNG

                                    1 2 Previous Next