UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Re-sorting Records, Any Version    
 
   
pere_de_chipstic...
post Jun 6 2018, 06:01 AM
Post#1


UtterAccess Editor
Posts: 10,515
Joined: 8-November 07
From: South coast, England


The attached demo allows the user to change the sort order of any record in a list and reallocates the sort order of the remaining items to be consistent.

On opening the database, open form 'frmConcProgramme'
Select an item from the 'Concert' combo box drop down list
Amend the number in the 'Order' box to re-position the Music item
Items can be added or deleted using the command buttons which will reorder the list appropriately.
Attached File(s)
Attached File  SortOrderDemo.zip ( 40.04K )Number of downloads: 86
 
Go to the top of the page
 
Jaiket
post Jun 7 2018, 01:42 AM
Post#2



Posts: 385
Joined: 3-May 17
From: France


Thanks Bernie,
I am currently trying to implement this, so you hit a bullseye by posting a working example.

For future readers, here's a link to a topic that holds some discussion on the code posted here (in some of the later posts):
http://www.UtterAccess.com/forum/index.php...2049230&hl=

I am looking to make a "Recent Items" list, but on seeing the example, I get the feeling it could easily be tweaked to allow pinning a few items.

To tweak it for just "Recent Items", I think assigning a new Item value to the highest SortOrder row, then assigning the lowest SortOrder value to the same row should work.
This post has been edited by Jaiket: Jun 7 2018, 02:33 AM
Go to the top of the page
 
pere_de_chipstic...
post Jun 7 2018, 03:57 AM
Post#3


UtterAccess Editor
Posts: 10,515
Joined: 8-November 07
From: South coast, England


Hi Jaiket

Pleased you found the demo useful thumbup.gif

On pinning, the items, I assume you would like to see pinned items at the top of each sorted list; this can be achieved by added a boolean field to the sort - see if the attached update to the demo does what you want.
Attached File(s)
Attached File  SortOrderDemo_with_Pin.zip ( 54.72K )Number of downloads: 82
 
Go to the top of the page
 
Jaiket
post Jun 7 2018, 04:44 AM
Post#4



Posts: 385
Joined: 3-May 17
From: France


Useful? I had my head in the handlebars with what I was doing, so I didn't notice how useful it will be to me without having to change a thing.

I am a professional musician. dunce.gif

woohoo.gif
Go to the top of the page
 
Jaiket
post Jun 7 2018, 04:48 AM
Post#5



Posts: 385
Joined: 3-May 17
From: France


I'm going to start again by modifying your form for my use. Adapting my form to your code was causing too much headache.

Before I do, I'd like some enlightenment on the short exchange you had with CheekyBuddha about SQL and robustness.

Would SQL gain nanoseconds but is not necessary? What does robustness mean?
Go to the top of the page
 
pere_de_chipstic...
post Jun 7 2018, 05:25 AM
Post#6


UtterAccess Editor
Posts: 10,515
Joined: 8-November 07
From: South coast, England


Hi Jaiket

When I developed the sorting code, I considered only re-sorting affected records, ie if you move from one item from position 10 to position 5, then you only need to renumber (original) items 5 - 9, similarly moving item 1 to item 4 means you should only need to renumber items 2 to 4. The problem was creating code that addressed every circumstance - adding and deleting records, as well increasing or decreasing a sort order number. Renumbering every item in the sorted group with a recordset was consistent and accurate and hence more robust - attested by the addition of the pinning in the second update, when the re-sorting function itself did not need any change.

SQL is potentially faster, but probably by a minimal amount that you would be unlikely to notice - unless you were dealing with a very large number of records; however by the time any code had calculated the exact SQL required this would further minimise any advantage.

Hope that helps.
Go to the top of the page
 
ZapDude
post Jun 20 2018, 01:36 PM
Post#7



Posts: 296
Joined: 27-February 06
From: Long Beach CA


One of my own concerns is the changing of primary (and in extension, foreign) keys, which should never be changed. A better choice would be to include a "sort order" join table that requires only a simple query and leaves all key fields intact.
This post has been edited by ZapDude: Jun 20 2018, 01:36 PM
Go to the top of the page
 
pere_de_chipstic...
post Jun 20 2018, 03:01 PM
Post#8


UtterAccess Editor
Posts: 10,515
Joined: 8-November 07
From: South coast, England


Hi ZapDude

I am not sure I understand your comment, the demo uses a specific 'SortOrder' Field, in the primary table; no Primary or foreign keys are used to implement the sort, which, I'd agree with you, should not be changed.

I am not sure that there is any need to implement a Sort Order join table except maybe in some circumstances, e.g. if you need different sort orders dependent on context, when you may need several sort order join tables, but even then you could implement the sort with several sort order fields in the primary table (?).
Go to the top of the page
 
ZapDude
post Jun 20 2018, 07:55 PM
Post#9



Posts: 296
Joined: 27-February 06
From: Long Beach CA


Ahhh...you are right! (Rubs eyes...) I'm sorry. Actually, you do use a join table (tblConcertProgs) and that is where you assign the sort order. I had only looked at the general module.

Go to the top of the page
 
Jaiket
post Jun 21 2018, 12:02 AM
Post#10



Posts: 385
Joined: 3-May 17
From: France


From what I see, the join table (tblConcertProgs) is needed regardless of whether a Sort field is added. No change in the table structure is necessary when adding the Sort.

I have tweaked this function to manage recently used Item names, with pinning. It works fine, but Sort breaks when I use instances of the subform to display the list in columns. This is forcing me to understand exactly how the Sort is managed. There is a variable which allows the Sort Order to count the records, and it is passed between hidden fields and a field default value. It's a cool trick I'm keen to understand.

I'm considering adding a record count somewhere to stop the default value from going askew when I split the continuous form into filtered columns. I haven't used Watch yet to follow variable changes, but it looks like I should for this. I'm also hoping to limit the entries in the list table to about 60, to implement the "Recent" part of "Recently used Items".



Go to the top of the page
 
pere_de_chipstic...
post Jun 21 2018, 04:56 AM
Post#11


UtterAccess Editor
Posts: 10,515
Joined: 8-November 07
From: South coast, England


Hi ZapDude

You are quite right - my turn to apologise! the demo does use a join table doh.gif! Though there is no reason why you can't put a sort order field in a primary table directly, it does not need to go into a join table, the demo does because it sorts on groups defined by a second primary table.

hth
Go to the top of the page
 
pere_de_chipstic...
post Jun 21 2018, 05:16 AM
Post#12


UtterAccess Editor
Posts: 10,515
Joined: 8-November 07
From: South coast, England


Hi Jaiket

The demo uses the DefaultValue property of the SortOrder control to save the next value of the sort order - this is useful if you allow users to add new records directly to the form (though the demo doesn't do this).

When a new group ('Concert' in the demo) is selected then the number of records in the group is determined, and this number +1 is set as the Default value of the control
CODE
With Me.subfrmConcProg.Form
...
.SortOrder.DefaultValue = .RecordsetClone.RecordCount + 1
...
End with
The code can then read the SortOrder.DefaultValue property whenever needed

When a new record is added the DefaultValue is increased by one directly; if a record is deleted then the DefaultValue is decreased by one (by the fSortOrder function).

Not sure what you mean by "I use instances of the subform to display the list in columns" - you will need to calculate the maximum sort order number over the whole group, which, if you have split over several instances of the form, probably means you cannot (easily) use the default value method demonstrated; but hope the above helps.
This post has been edited by pere_de_chipstick: Jun 21 2018, 05:59 AM
Go to the top of the page
 
Jaiket
post Jun 21 2018, 08:43 AM
Post#13



Posts: 385
Joined: 3-May 17
From: France


Thanks Pere_de_Chipstick.
My 3 subforms are arranged horizontally, filtered by <21, BETWEEN 21 AND 40, BETWEEN 41 AND 60 respectively. This shows the first sixty records as if it was in three columns.

I have also made single click copy the Item Name to clipboard, and double-click deletes the entry from the list. It's just recent names, designed to stop me getting typos in variable/object names , so it is more a notepad than a data storage. I'm trying to make it open enough to be used with any Recent Items list.

Adding, sorting and deletion will need to take into account whatever column they take place in. Thanks for the explanation of the default Value, which I'm sure willl allow me to complete my tweak.
Go to the top of the page
 
Jaiket
post Mar 1 2019, 04:11 PM
Post#14



Posts: 385
Joined: 3-May 17
From: France


Hi,
I managed to nest this SortOrder Demo inside itself to manage nested lists.
This post has been edited by Jaiket: Mar 1 2019, 05:11 PM
Attached File(s)
Attached File  NestedListsWithSortOrderAndPin.zip ( 107.78K )Number of downloads: 46
 

--------------------
Life is too short to drink bad wine.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 11:46 PM