| Author |
Message |
tanu
Newbie
Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
|
  
Topic: create a group field by formula
Posted: 05 Jul 2011 at 12:07am |
|
Hi,
I have few fields in my report which also includes a Date field (its a string)...I want my records to be grouped by year given in the date field...
I used group expert but it only sorts according to the database fields...doesn't accept fields created by formulas..
So how do i do that, create a field from a formula and group & sort the records according to the formula??
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Jul 2011 at 3:55am |
you can group on formual fields depending on how the formula was written.
what is your formula?
|
| IP Logged |
|
tanu
Newbie
Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
|
Posted: 05 Jul 2011 at 5:28am |
|
Hi,
Thanks for replying..In the group name formula, i added split function to string date..
Split({a.Date},"/")[3]
it gives me yyyy from dd/mm/yyyy.........I can get these yyyy values in group name field but sorting happens according to the {a.Date}
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Jul 2011 at 7:22am |
ah, i don't think you can use the array returns for grouping.
Just convert the string to an actuaql date field
date({a.date})
group on this formula field
set the group to per year
or
grab the right 4 characters instead of the split
Right({a.Date},4)
|
| IP Logged |
|
tanu
Newbie
Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
|
Posted: 05 Jul 2011 at 8:15pm |
|
Hey,
Are you sure Date() will convert a string into Date format??
And even if i get the right 4 characters from Date field..it'll still be a string..How will i use that for grouping?? it can be used as a Group name string only..
Please explain...
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 6:51am |
sorry,
lost sight of this due to the spammer....
I incorrectly assumed you cannot group on that array result but it seems that you can (tested it just now).
I am rereading this and getting a little lost on exactly what the problem is...
You have a string field that is in MM/dd/yyyy format and represents a date.
You want to group on the Year portion of this field and you need to sort somehow.
did you group on the string then try to show the year only and it is all messed up?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jul 2011 at 6:53am |
note: date(field) will convert the field to a date if the field is in a valid date format.
so
date("01/01/1950") works
date("01/41/1950") does not work
|
| IP Logged |
|
tanu
Newbie
Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
|
Posted: 06 Jul 2011 at 11:45pm |
|
ohkk that is fine....but what i find in the constructors of Date is Date(dateTime) and Date(yyyy,mm,dd) ??
can i put a field into it??
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Jul 2011 at 3:46am |
|
sorry, i do not understand your question. Can you clarify?
|
| IP Logged |
|
tanu
Newbie
Joined: 19 Jun 2011
Online Status: Offline
Posts: 10
|
Posted: 07 Jul 2011 at 7:47am |
|
i meant Date() has some constructors, but none of them allow String as a parameter into it --> Date(String)
means i cant put a String Date into Date(String)...not allowed
|
| IP Logged |
|
|
|
