Sorting or reindexing specific records
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.