List builder sorting

Bruce Boling · July 2023

When programming a custom listbuilder in visual studio there does not appear to be way to sort the results back to the screen. All the suggestions I have read and tried do not work. The list builder itself jumbles the rows around unless the user sorts on screen after rows are rendered to screen. Thanks, Bruce.

Will Mercer · August 2023

@Bruce Boling we tend to create new query views and then create the custom datalist in the GUI so I've not come across this issue. I assume that you've already tried to add an order statement to the SQL? I've had a look and the one custom datalist we have created from a spec it is being sorted by an order by statement in the GUI.

Todd O · August 2023

@Bruce Boling You can have a default sort by setting the <SortOptions> that is nested inside the <ListBuilder> tag.

Bruce Boling · August 2023

@Todd O
I will give it a try. This is great. Can you provide an example of sorting on two columns like a first sort, second sort.

Thanks, Bruce

Todd O · August 2023

@Bruce Boling The SDK only gives the option for a single sort on List Builders. If you need more I would consider a data list instead which would allow you to include an order by in the SQL with as many as you need.

Mitchell Gibbs · August 2023

@Bruce Boling The SDK only allows sorting by one column. However, you can trick it using a derived table in the function. It may negatively impact performance and once the user sorts the listbuilder it will no longer impact anything.

Something like:

CREATE FUNCTION UFN_FUNCTION ()
RETURNS TABLE
RETURN (
SELECT TOP 99999999
*
FROM
(SELECT
ID
, LOOKUPID
, FIRSTNAME
, KEYNAME
FROM
CONSTITUENT) X
ORDER BY KEYNAME, FIRSTNAME
)

Bruce Boling · August 2023

@Mitchell Gibbs

@Todd O

I tried a similar table method function where I created the table and inserted sorted rows before returning the table, and it failed to keep the sort. I have not tried your exact example. I assume I can also create a new column in sql funct and put all the fields in the sort together like cat+dog+bird and sort with the SDK xml tag as Todd suggested with one column containing all the sort fields, I guess that is a back door way to get around the SDK one sort field. Thanks for the info, it's nice when someone provides feed back. -Bruce

Mitchell Gibbs · August 2023

@Bruce Boling One issue we ran into was that if the ListBuilder was sorted (via the spec tag) and that column wasn't visible, if the user tried to export the listbuilder it would error out. I tried a dozen ways to work around this but couldn't get it to work. The derived table sort allowed us to have a default sort that wouldn't be impacted if the user hid that column. Of course, users being clever, if they resort the listbuilder and then hide that column - it will still error out. But that's less common.

You should be able to following my example and sort by as many columns as needed. But - again - once they sort it differently - your efforts will no longer have any impact for that user.

Good luck!

Bruce Boling · August 2023

@Mitchell Gibbs

@Todd O

One last questions if you don't mind. We use several ‘with’ clauses in our functions. Can your table example use the ‘with’ clause and maintain sorting, we find the ‘with’ very useful for complicated requests for custom list builders.

Bruce Boling · August 2023

@Mitchell Gibbs

@Todd O

This example fails to maintain the sort.

create function …name
(@BEGINDATE date,
@ENDDATE date,
)
returns table as return

with rev1 as (
select …
from dbo

), rev2 as (
select …
from rev1

)
select top 9999999 …
from rev2
order by …

Mitchell Gibbs · August 2023

@Bruce Boling You have to wrap the ordered query into a derived table.

CREATE FUNCTION ufn_function
(@BEGINDATE DATE,
@ENDDATE DATE)
RETURNS TABLE
AS
RETURN

WITH rev1 AS
(SELECT
ID
, LOOKUPID
, FIRSTNAME
, KEYNAME
FROM
dbo.CONSTITUENT),
rev2 AS
(SELECT
ID
, LOOKUPID
, FIRSTNAME
, KEYNAME
FROM
dbo.CONSTITUENT)
SELECT
*
FROM
(SELECT TOP 9999999
*
FROM
rev2
ORDER BY KEYNAME, FIRSTNAME) x

Bruce Boling · August 2023

@Mitchell Gibbs

@Todd O

When a user sorts the list builder, then decides they want to go back to the original sort done by sql, how does a user remove their stick-bit sort they no longer want. Can it be done?

Mitchell Gibbs · August 2023

@Bruce Boling Choose the “More” drop down and "Restore grid defaults"

Bruce Boling · August 2023

@Mitchell Gibbs

@Todd O

Can the size of parameter fields be shortened, when you have a Y/N parameter the box is too big. I have tried the max=1 on xml tag but the parameter field box stays big. I have several parameters and the screen has to be scrolled to the right (hassle). Would like to shorten box to save space. The apply button is off the screen.

Bruce Boling · September 2023

@Mitchell Gibbs

@Todd O

I noticed the property screen in CRM for a datalist has a field to prevent the datalist from running until you want it to call autoloadlist=False. When I do a custom listbuilder in visual studio the property screen does not have this option. How and can you prevent a custom list builder from running since this option is not available. Maybe something in the visual studio spec or is it not possible. -Bruce

List builder sorting

Comments

Categories