8 Replies Latest reply on Feb 14, 2009 11:01 AM by daniel.cunnings

    Sorting or reindexing specific records

    daniel.cunnings

      Title

      Sorting or reindexing specific records

      Post

      I have a quotes database where components are added to a quote for a specific client. The quote information is held in a separate table (linked by the quote number) and viewed in a portal.

       

      Quotes can become fairly large (100+ lines) with different components added or adjusted over time and inevitably there is a requirement to add a new component further up the list rather than at the end. Currently users tend to leave blank lines which can be filled up later but this is not a good working solution, especially if insufficient blank lines have been left. Also there are times when a group of lines need to be moved together.

       

      I am aware that portals can be sorted and I suppose I could give each line a line number to sort by and change this number if I need to move a line. However with larger quotes there would still be the possibility that I would need to renumber many lines.

       

      Additionally the data from the portal is used in a print layout (and elsewhere) and this obviously sorts on record order so I would prefer a solution that is more permanent than a portal sort.

       

      I would also like to use the same idea for contacts where one client / organisation may have many contacts. Each individual person or contact is accessed via a portal but as people come and go or change roles it is necessary to change the order. Again, using line numbers to sort works fine in the specific layout which uses the portal but doesn't work when this information is displayed elsewhere or when just the main contact is displayed as the default is the first record in the table.

       

      So, is there a way to reindex records, change the record number, 'swap' data from one record to another or otherwise make a permanent change to the order of specific records? Otherwise do you have any other suggestions?

       

      Also any suggestions to easily move lines on a quote and add new lines in between existing lines would be appreciated.

       

      BigD

        • 1. Re: Sorting or reindexing specific records
          davidhead
            

          Hi BigD

          The first thing to realise is that the default order of records in any table is the order in which they were created. This means that any alteration required to that order requires the records to be sorted. This can be done for the order of records in a portal and for records in a list.

           

          The solution I use is one where a number field is used to sort the records. This field is never seen by the user - only its effect is seen. 

           

          The field is set to auto-enter a number which can be simple or smart depending on how you want it to work. The script will 'look' at the record above or below in the list and swap numbers with it. You can also add the ability to move an item to the top or to the bottom of the list.

           

          I can't post sample files here but suffice to say it is all possible with scripts. 

          • 2. Re: Sorting or reindexing specific records
            daniel.cunnings
              

            Hi David,

             

            Thanks for the reply. It is good to know that "...it is all possible"!

             

            I have played around with using number fields to sort records but only in a manual sense. This worked fine for the portal I was viewing but didn't seem to work for other things. Is it possible to use a number field to sort the records consistently wherever they are viewed including a print layout where each record/line is a new line in the body?

             

            The idea of a script to move records up and down sounds good and it would definitely be helpful to see a sample script if that were possible. Can you think of a way to be able to insert a blank line / record in the middle of a set of records too? Obviously I could add a new line at the end and then move it up line by line but this could get tedious on larger quotes.

             

            Thanks for your assistance.

             

            BigD

            • 3. Re: Sorting or reindexing specific records
              daniel.cunnings
                

              Any further information, suggestions or example scripts to assist me in finding a solution to this problem would be really appreciated.

               

              On a related note, is there a way to calculate a record / line number that is associated with the linked master record and always starts from the same place. For example, lets say I have a quote Q01 then the first line of this quote should start at 01 giving me a line number of Q01-01 and the second line being Q01-02 etc. But if I then add a line to my next quote Q02 this should start it's numbering sequence from the start too giving me Q02-01 etc.

               

              BigD

              • 4. Re: Sorting or reindexing specific records
                daniel.cunnings
                  

                While it is somewhat encouraging to know that there is a solution, it is also frustrating not to be told what it is or how it would work...!

                 

                Anyone fancy helping??

                 

                BigD

                • 5. Re: Sorting or reindexing specific records
                  davidhead
                     I will put together an example file for you. This forum does not allow us to post files (grrrr!!) so I will put it up on my web site for you to download from there. Will let you know where when it is done.
                  • 6. Re: Sorting or reindexing specific records
                    davidhead
                      

                    OK, I have put together a sample file that allows you to move items up and down in a portal, and also to insert items before or after a specific item.

                     

                    http://www.ulearnit.com.au/samples/PO_SetPortalOrder.zip

                     

                    Let me know what you think. 

                    • 7. Re: Sorting or reindexing specific records
                      daniel.cunnings
                        

                      Hi David,

                       

                      Thanks for taking the time to do this. 

                       

                      I have just downloaded the file so I will have a look at it and let you know how I get on.

                       

                      BigD

                      • 8. Re: Sorting or reindexing specific records
                        daniel.cunnings
                          

                        Hi David,

                         

                        Thank you for the example script.

                        I have tried it with my quotes and it works a treat!

                        Now going to use it else where!

                         

                        Thanks for your help with this.

                         

                        BigD