Conditional Group formula not sorting correctly.

[NipsMG]

Ok, here's the deal,

I'm grouping based on a formula.

I have 3 groups.
Each group is being sorted by a forumla.

In each formula, I have

if {@RecordType} = 'SingleLocation' then
{MyTable.SomeField}
else
{MyTable.AnotherField}.


If the Record type is MultipleLocation, I want it to group by item, then location code, then location.

If the record type is SingleLocation, I want it to group by location code, then location, then item.

So my grouping structire goes:

Group @ReportType
|
Group @FirstGroup
|
Group @SecondGroup
|
Group @ThirdGroup.

In the group footer for @SecondGroup, I enabled "New Page After", conditionally if @ReportType = 'SingleLocation'.


Here's the problem, the results from @SecondGroup AREN'T SORTED, so even though there may be 10 records with a @SecondGroup (LocationCode) Value of 40, I get each on a new page instead only everything with 40 on one page, because the records have the following location codes in order:

10
40
RM
40
10
A0
40
A0
RM
A0

Etc.

Why would it not sort by the results of the function I passed it. Shouldn't it always sort by the group value? Is there a modifier like BeforePrintingRecords or something that I have to give the function before I use it as a group value?

Thanks in advance!

--NipsMG

 

[Madawc]

I'm not clear what's going on. If your've got 3 groups, it will sort by lowest within middle within highest. Does this explain it?

In Crystal 10, you can change the order of groups by choosing Report>Group Expert and using the arrows on the right.

You can also use a formula field to assemble a single 'key' from serveral fields, if that helps.

Madawc Williams (East Anglia, UK)

 

[NipsMG]

Basically, I have 3 groups.

If a flag is set one way, the formula that I'm grouping by returns a value from field, if a flag is set the other way, it returns a value from a different field.

I first group by that flag, then I group by the 3 formula fields.

However, it seems that the group is not being sorted by the value returned by the formula field as it should.

 

[Naith]

Nips, place copies of @ReportType and @FirstGroup fields in the @SecondGroup header. I think you'll find that one or both of these values is different from the @SecondGroups predecessor.

You may need to reassess your grouping. Your NewPage problem will then take care of itself.

Naith

 

[lbass]

In your examples, you sometimes use "RecordType" and sometimes use "ReportType". Did you by any chance repeat this error within your formulas?

-LB

 

[NipsMG]

No, it's was a typographical error in the forum post, not in the report.

Thanks though.

 

[lbass]

I think you should share the exact contents of each of your group formulas.

-LB

 

[NipsMG]

Sure.

My groups: IN ORDER:

WO (Work Order, DB Value, No Formula)
\_ @LocationString -

if {PickListData.LocationCount} <= 1 then 'Single Location' else 'Multiple Locations'

\_ @FirstGroup

if {@Location String} = 'Single Location' then
    {PickListData.t_sitm}
else
    {@LocFirst}

\_ @SecondGroup

if {@Location String} = 'Single Location' then
    {@LocFirst}
else
    {PickListData.MinLocation}

\_@ThirdGroup

if {@Location String} = 'Single Location' then
    {PickListData.MinLocation}
else
    {PickListData.t_sitm}

NOTE: @LocFirst used in the formulas is the first 2 letters of a location code (i.e. MinLocation = 'A04398', @LocFirst = 'A0')

 

[lbass]

The reason the second group isn't sorting as you wish for the single location is that you have item as the higher order group. There will be only one loc code and location per item I assume, and the item number is driving the sort. You can see this by looking in the detail section. If you want the clustering by location code, then you probably need to use the same grouping for both single and multiple locations.

-LB

 

[NipsMG]

ok I got the ordering of the groups fixed.'

Right now, everything is sorting correctly, but here's the problem:

My Group (third group) returns the first 2 letters of the location code in a formula {@LocFirst}. I have in the group footer a "new page after" command.

here's my problem

Page 1: {@LocFirst} = 10 (1 record)
Page 2: {@LocFirst} = 20 (1 record)
Page 3: {@LocFirst} = 20 (1 Record)
Page 4: {@LocFirst} = 20 (1 record)
Page 5: {@LocFirst} = 30 (1 record)
Page 6: {@LocFirst} = 30 (1 record)

Pages 2, 3, 4 should be ONE PAGE. Pages 5,6 should be ONE PAGE. I'm doing a new page after in the group footer of this group, it should be GROUPING THEM TOGETHER..

Any idea what gives?

 

[NipsMG]

Nevermind.

It's amazing to me how badly my attention to detail is deteriorating.

I think it's lack of sleep/stress.

The reason why it wasn't grouping the way I want is becuase i was overlooking the fact that the FirstGroup group is the one I want to break by. so I had to arrange grouping. I was thinking about it 100% wrong. Thanks for your help everyone.

--NipsMG