3 Replies Latest reply on Jun 12, 2013 11:49 AM by aaronhalberg

    Sort Script in FTS12 - Chapter 4

    aaronhalberg

      Just exploring Example Solution for Bonsai Gardens in FTS 12 Chapter 4 and noticing that the script for sorting the Interactions Portal on the "Home" layout doesn't appear to be working correctly when sorting by DateCreated... Is anyone else seeing this same issue or did I foul up my file somehow? The sort script appears to be working for DateDue, AssignedTo, etc... but some of the DateCreated fields are not in order after running the Sort script by clicking on the Create Date button.

       

      Any insight would be appreciated!

        • 2. Re: Sort Script in FTS12 - Chapter 4
          jlamprecht

          Hey Aaron. I am also working through the book. I am near the end right now. I missed this hiccup earlier on. I took a look at it and discovered the problem. Right now where you are in the book, it would be difficult to fix. I will provide the explanation of what went wrong and why. It may be hard to follow right now, but you get to it later in the book.

           

          Basically, the problem is there are two fields in your Interactions table called DateDue and DateCreated. They are both saved as dates. To be able to sort, you have fields associated with sorting that are based off a calculation to decide what to sort (e.g. DateDue) and which direction (e.g. ascending). The calculation returns the result as text, which causes a problem for numbers and dates as they will be sorted as text rather than numerically.

           

          The author actually explains this in comments in the sort calculation fields, z_PortalSortAscending and z_PortalSortDescending, within the Interaction table. The problem that occurs is that the DateCreated sort is acually based off another field that is used as a timestamp for creation. This is the problem here. Instead of using that field I simply changed it to the actual DateCreated field. Here are the changes:

           

          Before:

           

          Let (

          [

          vCreation = Right ( "0000" & GetAsNumber ( z_RecordCreationTimestamp )  ; 14 ) ;

          vDateDue = GetAsNumber ( DateDue )

          ] ;

           

          After:

           

          Let (

          [

          vCreation = GetAsNumber ( DateCreated );

          vDateDue = GetAsNumber ( DateDue )

          ] ;

           

          The GetAsNumber() function is used to turn the date into a number. Dates are actually stored as the number of seconds from midnight of year 1.

           

          If you make that change, it should work now. You simply have to edit both of the previously mentioned fields to have the new let block. I hope I made it clear enough. Like I said, you will learn about this in later chapters, specifically in the functions and scripting chapters.

           

          Have fun,

          JohnAustin

          • 3. Re: Sort Script in FTS12 - Chapter 4
            aaronhalberg

            Thanks @JohnAustin!

             

            Very interesting... looking at the table view of the data, it is obvious that all the values for z_RecordCreationTimestamp are exactly the same, so sorting on a calculated value using that is not going to give very good results!  I guess in the developers defense, "real" data timestamped as it was entered would not have this problem, or would it?  maybe if added by an import process it still could happen?  Anyway...

             

            I suppose someone else has come across this little incongruence, but it makes me feel like I'm understanding the software to have the root-cause of a little bug like this revealed for me.  Thanks for your help by doing that revealing.  Not too difficult to follow your steps once you explained what was causing the problem going on here...

             

            Looking at the dev_Interaction layout in table view and adding the fields for z_PortalSortAscending and z_PortalSortDescending really helped me understand (and see) what was going on behind the scenes for the script results I was seeing. 

             

            All the best,

            Aaron