(UTC)
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Date Sort (Read 1363 times)
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Date Sort
Jul 4th, 2013 at 5:17pm
Print Post Print Post  
Can you please tell me how I can sort records, using a date field, by either month, date, or year.

In other words, what if I want to sort all dates by the day of the month (regardless what month or year it is in).

Thanks!
  
- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
WWW WWW  
IP Logged
 
PianoMan
Member
Members
*
Offline


No personal text

Posts: 44
Joined: Oct 13th, 2004
Re: Date Sort
Reply #1 - Jul 5th, 2013 at 3:22am
Print Post Print Post  
One method would be to create three new elements named Day, Month and Year. Write an On Element Change event for the Date element that populates these with the day, month and year of the entered date. Now you have your fields for sorting.
  
Back to top
WWW WWW  
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Date Sort
Reply #2 - Jul 5th, 2013 at 4:50am
Print Post Print Post  
Thanks Pianoman.  I had thought of the 3 separate fields, but that requires redesign and a lot of extra work to populate the fields.

I know that it was do-able in Q&A based on the following notes that I used to use:

To sort forms with no regard to the year:
Givens:    Name:  #10          Date:  #20
METHOD ONE requires two derived columns and is very straightfoward:
           Formula:  @month(#20)
           Column Spec:  15,AS,I

           Formula:  @dom(#20)
           Column Spec:  16,AS,I

This procedure simply sorts the month in one invisible column and the day of the month in another.

METHOD TWO requires one derived column.  It capitalizes on the fact the string functions operating on a date field
force the use of the internal date format.
           Formula:  @right(#20,5)
           Column Spec:  15,AS,I
The rightmost 5 chars of a date field (based on the internal format) will always be formatted as MM/DD, perfect for sorting.

Is there no comparable (or other) way to do this in Sesame - using the date field as is?

Thanks
  
- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
WWW WWW  
IP Logged
 
PianoMan
Member
Members
*
Offline


No personal text

Posts: 44
Joined: Oct 13th, 2004
Re: Date Sort
Reply #3 - Jul 6th, 2013 at 5:08pm
Print Post Print Post  
Because Sesame reports sort using existing database fields, you won't be able to sort on an Unbound Value Box (Sesame's equivalent to a Derived Column). There really isn't a lot of work to populating Day, Month and Year elements. This same code could be used as a Mass Update to update existing records.

Code
Select All
// On Element Change for a date element
// Day, Month and Year are Number fields
If @IsBlank(ThisElement)
{
Clear(Day, Month, Year)
}
Else
{
Day = @Dom(ThisElement)
Month = @Mt(ThisElement)
Year = @Yr(ThisElement)
}
  
Back to top
WWW WWW  
IP Logged
 
SpencerWulwick
Senior Member
Members
*****
Offline



Posts: 677
Location: Wilton Manors, Florida
Joined: Jan 16th, 2005
Re: Date Sort
Reply #4 - Jul 6th, 2013 at 5:49pm
Print Post Print Post  
Pianoman -

Thanks very much.  If the only way I can accomplish what I want is with three extra fields (in several databases), you have certainly made it much easier on me, by providing the code. 

I really appreciate it!
  
- Spencer

    ** Practice random kindness & senseless acts of beauty!
Back to top
WWW WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge