Sorting record list issue

8 posts by 2 authors in: Forums > CMS Builder
Last Post: April 6, 2020   (RSS)

By gkornbluth - March 22, 2020 - edited: March 22, 2020

Hi All,

Hope this post finds you all safe and healthy in this strange time.

I’ve got a record sorting issue that I’m trying to solve.

I’ve got a pull down list field (genre) in a multi record editor (books) that uses ‘Get options from database (advanced)’ to get it’s option values from the 'num' field and its option labels from a field called 'genre_names' in a table called 'genre_categories'.

When I create a new 'books' record, the pull down list of ‘genre’ names appear in dragSortOrder of the 'genre_categories table and are thus easy to assign.

However, when I try to sort the ‘books’ records list by the ‘genre’ column, the records are sorted by ‘genre’, but they are (were, see UPDATE below) displayed in the old DOS order and not in dragSortOrder order. By DOS order I mean: 4, 3,29, ... 21, 20, 2, 19, ... 12, 11, 10, 1

I tried to set up a test genre field using ‘get options from MySQL query’
with the following query:

SELECT num, genre_names
FROM `<?php echo $TABLE_PREFIX ?>genre_categories`
ORDER BY genre_names ASC

That didn’t help and I reset it to ‘Get options from database (advanced)’ using the fields values above (maybe the query is wrong, but it didn’t throw any errors).

UPDATE:
I found what solved part of my issue in post # 2241728, (I changed the ‘genre’ column type to INT and the records now sort by actual descending 'genre_categories' record number) however I still can’t get the record sort to show in ‘genre_categories’ dragSortOrder the way I want them to.

Any thoughts?

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By daniel - March 27, 2020

Hi Jerry,

Just to clarify: you want to be able to order the records in the "books" table based on their assigned "genre" field, but based on the dragSortOrder assigned to the genre_categories records - is that correct? If so, could you clarify where/how you're attempting to sort these records? This kind of thing will likely require a table join which is a more advanced operation, and how it is accomplished will depend on the context.

Thanks,

Daniel
Technical Lead
interactivetools.com

By gkornbluth - March 27, 2020

Hey Daniel,

Thanks for looking at this and responding.

The 'genre_categories' table is sorted by dragSortOrder DESC

The 'books' table with the 'genre' field that gets it's values from the 'genre_categories' table is sorted by the 'title' field (sort tab in the section editor) and that's how the default record list comes up, sorted by title.

When I click on the 'genre' column heading to sort the 'books' table record list by 'genre', the genre column comes up sorted by record number instead of by the table's dragSortOrder.

Every place else that the 'genre' list comes up (the pull down for setting the value for a new record, and in the front end search forms, the genre's are sorted by dragSortOrrder as they should be.

Hope that makes sense.

I can give you access to the back end if that would help make things clearer.

Again, thanks.

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By daniel - March 31, 2020

Hi Jerry,

Unfortunately, the CMSB list pages don't support this kind of advanced sorting without custom programming. Simply put: the "books" table doesn't know anything about the "genre_categories" table, so it can't use its dragSortOrder unless a table join is used, which isn't something the list pages can do by default. Feel free to send us an estimate request (https://www.interactivetools.com/estimate/) and we'd be happy to help out.

Thanks!
Daniel

Daniel
Technical Lead
interactivetools.com

By gkornbluth - March 31, 2020 - edited: March 31, 2020

Hi Daniel,

Kind of what I figured.

Any rough ballpark idea of what it might cost?

Then I can check with the client (yet another pro-bono project) and find out how important this is to him.

Thanks again for all your time,

Stay safe!

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By daniel - March 31, 2020

Hi Jerry,

Creating a plugin to do this I'd ballpark at around 1-2 hours.

Cheers,

Daniel
Technical Lead
interactivetools.com

By gkornbluth - April 6, 2020

Hi Daniel,

'Sorry for spinning your wheels.

The client would rather wait on the plugin for now.

If things change I'll post back.

Thanks again for being there for us.

Jerry

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php