Hi.  I'm running a site for my high school class's 20th reunion.  I use the user directory to show everyone that we already have registered.  I use Dynamic Registration to take in people's maiden names in a new field.  In the user directory I show displayname, which I set to $First $(Maiden) $Last if they have a non-empty maiden name.  If they have an empty maiden name it's just $First $Last.

Right now I'm sorting the user directory on last name.  I'd love to be able to sort it on Maiden and Last.  If Maiden is non-empty, use the Maiden name key, if not use the Last name key.  That way people's names will sort in an order that people are used to, like the yearbook.

Is there a workaround I could do to get this functionality now?  If not, could this be added to the next release?

Regards,
Chris Rock

P.S.  I know now that I should have just had everyone enter their last name during HS like classmates.com does, then I'd have that column to work with.  Alas, I didn't do that.

Hi. Yes unfortunately right now the sort order is handled based on a single sort order field... It would be a nice enhancement to be able to sort on multiple fields such as first on maiden name, then last name... Or allow the user to choose which field they want to sort by. We will review this in future enhancements and see what we can come up with. If you wanted to make it static you could update the stored procedure directly that generates this table (to first add an initial sort) and then from there it would sort the 2nd field directly within the module.
-Chad

Thanks for the lead.  It took me a second (with profiler) to figure out which stored procedure it was because the naming isn't tied in with dynamic user directory.  Then I worried it was used by other modules, but it doesn't appear to be.

In case this helps anyone, or anyone has comments on how I did this, here is my new stored proc.  I found that if I tried dynamic sorting on one of the right join columns (like MaidenName) the performance was terrible, so I run the dynamic SQL the stored proc already creates into a temp table and select and order by from there.  I added one column which is populated by a CASE function between Maiden and Last.

Also, I found that I had to do all of my ordering in the stored proc, and then order by a null column in the module.  If you order by a single column in the module, it completely overrides any ordering done in the stored proc.

All of my changes (inserted lines) are wrapped in
--RNCI
--

 

CREATE Procedure dbo.[DataSprings_RetrieveDNNUserProfileData]

@PortalID int,
@RoleFilter nvarchar(1000),
@LimitResults nvarchar(1000)

AS


Declare curDS Cursor
FOR
SELECT PropertyName, PropertyDefinitionID
FROM dbo.[ProfilePropertyDefinition]
where Visible=1 
AND PortalID = @PortalID 
AND Deleted=0 
AND PRopertyName <> 'FirstName'
AND PropertyName <> 'LastName'

ORDER BY PropertyCategory, Vieworder
FOR READ ONLY;

Declare @PropertyName VarChar(500);
Declare @PropertyDefinitionID Integer;
Declare @SQLToUse VarChar(8000);
Declare @SQLToUseJoin VarChar(8000);
Declare @SQLToExecute Varchar(8000);
Declare @Alias 	  VarChar(10);
-- RNCI
DECLARE @maidenNameAlias varchar(10);
DECLARE @tempTable nvarchar(4000);
--
Declare @intCounter Int;

set @PropertyName = '';
set @SQLToUse = '';
set @intCounter = 0;
set @Alias = '';
set @SQLToUseJoin = '';


OPEN curDS;
FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID;

WHILE @@FETCH_STATUS = 0
BEGIN

	set @intCounter = @intCounter + 1;

	set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']';

	if DataLength(@SQLToUse) = 0
	BEGIN
		set @SQLToUse = 'SELECT ' + @LimitResults + ' a.UserID, A.UserName, A.DisplayName, A.Email, A.FirstName, A.LastName, IsNull(' + @Alias + '.PropertyValue,''N/A'') as [' + @PropertyName + '] '
		set @SQLToUseJoin = ' 
From (
  SELECT  distinct Y.UserID, B.UserName, B.DisplayNAme, B.Email, B.FirstNAme, B.LastName
  from dbo.[UserProfile] As Y
  inner join ProfilePropertyDefinition  as Z on (Y.PropertyDefinitionID = Z.PropertyDefinitionID)
  Left Outer Join dbo.[Users] B on B.UserID = Y.UserID 
  where Z.PortalID = ' + Cast(@PortalID as varchar(20)) + ' 
) As A 
Left Outer join dbo.[UserProfile] as ' + @Alias + ' on (A.UserID = ' + @Alias + '.UserID and ''' + Cast(@PropertyDefinitionID as varchar(50))+ ''' = ' + @Alias + '.PropertyDefinitionID)
'
-- RNCI
		SET @tempTable = 'CREATE TABLE #tempData ( UserID int, UserName nvarchar(100), DisplayName nvarchar(128), Email nvarchar(256), FirstName nvarchar(50), LastName nvarchar(50), ' + @PropertyName + ' nvarchar(3750) '
--
	END
	ELSE
	BEGIN
-- RNCI
		SET @tempTable = @tempTable + ', ' + @PropertyName + ' nvarchar(3750) '
		IF @PropertyName = 'MaidenName' SET @maidenNameAlias = @alias
--
		set @SQLToUse = @SQLToUse + ', ' + @Alias + '.PropertyValue as [' + @PropertyName + '] '
		set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join dbo.[UserProfile] as ' + @Alias + ' on (A.UserID = ' + @Alias + '.UserID and ''' + Cast(@PropertyDefinitionID as varchar(50))+ ''' = ' + @Alias + '.PropertyDefinitionID) 
'
	END;	

   	FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID;
END

CLOSE CurDS;
DEALLOCATE CurDS;

-- RNCI
SET @SQLToUse = @SQLToUse + ',  CASE ISNULL(' + @maidenNameAlias + '.PropertyValue, '''') WHEN '''' THEN LastName ELSE ' + @maidenNameAlias + '.PropertyValue END '
SET @tempTable = @tempTable + ', LastAtAHS varchar(500))'
--
--set @SQLToExecute = @SQLToUse + @SQLToUseJoin  + @RoleFilter
Set @SQLToExecute = Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin) + Convert(varchar(8000), @RoleFilter) 

-- RNCI
SET @SQLToExecute = REPLACE(@SQLToExecute, '''', '''''')
exec (@tempTable + '; INSERT INTO #tempData EXEC(''' + @SQLToExecute + '''); SELECT * FROM #tempData ORDER BY LastAtAHS, FirstName')
--
--print @SQLToExecute
--EXEC(@SQLToExecute);
GO