Support
Login
Your Password

Sales
North America
Phone: +1 (905)-655-9262
Fax: +1 (905)-655-9395
Email: info@add-on.com

Europe
Phone: +45 7944 7000
Fax: +45 7944 7001

China
Phone: +86 (21) 2221 8396
Email: info-china@add-on.com
Support
Denmark
+45 7944 7002

Europe
+44 (0) 203 002 3889

North America
+1 (202)-536-4165
Knowledgebase: Resource Central
KB0039 - Locations are not sorted in ResourceFinder

 

Summary

After user tries to sort the locations/categories in the backend, these changes are not reflected in ResourceFinder.

 

Cause

This issue happened after user upgrades database from RC 3.0 and there might be some records with invalid sort order value in his database (duplicate records with the same sort order).

 

Resolution

Step 1: Find all incorrect records

Run the following script to find all incorrect records:

SET NOCOUNT ON;                  

/*

       Find all location that have problem with sort order (at least two nodes under the same tree level have the same sort order)

*/

declare @rootNodeID int = 47--@Location

      

DECLARE @AllTreeTable TABLE

(

        [id]              INT,

        [created]         DATETIME,

        customcalendarid  INT,

        [createdby]       INT,

        [lastupdated]     DATETIME,

        [lastupdatedby]   INT,

        [parentnodeid]    INT,

        [key]             NVARCHAR(256),

        [caption]         NVARCHAR(50),

        [sortorder]       INT,

        [description]     NVARCHAR(256),

        isapplicationnode TINYINT,

        [iconurl]         NVARCHAR(256),

        [functionid]      INT,

        treeurl           NVARCHAR(256)

)

 

DECLARE @ResultTreeTable TABLE

(

        [id]              INT,

        [created]         DATETIME,

        customcalendarid  INT,

        [createdby]       INT,

        [lastupdated]     DATETIME,

        [lastupdatedby]   INT,

        [parentnodeid]    INT,

        [key]             NVARCHAR(256),

        [caption]         NVARCHAR(50),

        [sortorder]       INT,

        [description]     NVARCHAR(256),

        isapplicationnode TINYINT,

        [iconurl]         NVARCHAR(256),

        [functionid]      INT,

        treeurl           NVARCHAR(256),

        [level]                          INT

)

 

INSERT INTO @AllTreeTable

SELECT DISTINCT t.id,

                           created,

                           customcalendarid,

                           createdby,

                           lastupdated,

                           lastupdatedby,

                           parentnodeid,

                           [key],

                           t.caption,

                           sortorder,

                           description,

                           isapplicationnode,

                           i.url AS IconURL,

                           functionid,

                           t.url AS TreeURL

FROM   tree AS t

          LEFT OUTER JOIN icon AS i

                                  ON t.iconkey = i.iconkey

ORDER  BY sortorder

 

;WITH OutputTable ([id], [created], customcalendarid, [createdby], [lastupdated],

                                   [lastupdatedby], [parentnodeid], [key], [caption], [sortorder],

                                   [description], isapplicationnode, [iconurl], [functionid], treeurl, level)

 AS (

       --Anchor

       SELECT e.[id],

            e.[created],

            e.customcalendarid,

            e.[createdby],

            e.[lastupdated],

            e.[lastupdatedby],

            e.[parentnodeid],

            e.[key],

            e.[caption],

            e.[sortorder],

            e.[description],

            e.isapplicationnode,

            e.[iconurl],

            e.[functionid],

            e.treeurl,

            0 AS Level

     FROM   @AllTreeTable AS e

     WHERE  e.id = @rootNodeID

    

     UNION ALL

     --Recursive query to build the tree hierarchy

     SELECT e.[id],

            e.[created],

            e.customcalendarid,

            e.[createdby],

            e.[lastupdated],

            e.[lastupdatedby],

            e.[parentnodeid],

            e.[key],

            e.[caption],

            e.[sortorder],

            e.[description],

            e.isapplicationnode,

            e.[iconurl],

            e.[functionid],

            e.treeurl,

            level + 1

     FROM   @AllTreeTable AS e

            INNER JOIN OutputTable AS d

                    ON e.parentnodeid = d.id)

 

insert into @ResultTreeTable                  

SELECT DISTINCT * FROM   OutputTable ORDER  BY level, sortOrder

 

select * from Tree where id in

(select distinct r1.id

       from @ResultTreeTable r1

              INNER JOIN @ResultTreeTable r2 ON r1.id <> r2.id and r1.parentnodeid = r2.parentnodeid and r1.level = r2.level and r1.sortorder = r2.sortorder)

 

Step 2: Update the sort order manually

After running the script, the result will be displayed with a list of incorrect records:

After that, find the maximum SortOrder value in Tree table manually: (e.g.,Mx)

Then, update this Mx + 1 value to sortOrder column of Tree table for the 1st error record (using Edit top 200 Rows of SSMS or write an updated script by themselves).    

Repeat step 2 for the next error record.

 

Properties

Applies to: RC 3.6 HF 1+

Reference: TFS #4861

Knowledge base ID: 0039

Last updated: Feb 23, 2016