Another conditional group sorting question 1

[mikeinfbg]

I've throughly read through at least 7 other posts that might be similar to my question but still don't know if my situation is the same.

I'm trying to summarize daily sales and sort based on which cash register they were rung up on.

If the sales were wrung up on Register1, then I need the sales and quantities to be summarized by groups called Departments, and sorted by Deptartment "A","B","C","D". (Details would be hidden, of course.)

If the sales were wrung up on Register2, then same as above but I needed them to be summarized and sorted by Department "B","C","A","D".

So there are only 2 possible arrangements of the sorts. They're constant. Its either one way or the other based totally on which Register the sales were made. I try to use "Group in specified order", but can't get it to do it based on a condition of another field.

Using Crystal XI Pro.

Thanks in advance.

Mike

 

[AndyMc]

I would create a formula called @DeptOrder and then group on it after register:

If {myRegisterfield} = "Register1" then
(
if {myDeptField} = "A" then 1
if {myDeptField} = "B" then 2
if {myDeptField} = "C" then 3
if {myDeptField} = "D" then 4
)
else if {myRegisterfield} = "Register2" then
(
if {myDeptField} = "B" then 1
if {myDeptField} = "C" then 2
if {myDeptField} = "A" then 3
if {myDeptField} = "D" then 4
)

 

[AndyMc]

I forgot the elses ..

I would create a formula called @DeptOrder and then group on it after register:

If {myRegisterfield} = "Register1" then
(
if {myDeptField} = "A" then 1
else if {myDeptField} = "B" then 2
else if {myDeptField} = "C" then 3
else if {myDeptField} = "D" then 4
)
else if {myRegisterfield} = "Register2" then
(
if {myDeptField} = "B" then 1
else if {myDeptField} = "C" then 2
else if {myDeptField} = "A" then 3
else if {myDeptField} = "D" then 4
)

 

[mikeinfbg]

Ok. I think I'm almost there. To shorten the formula, how would I write it so that:

- if The register number falls into a certain numeric range, then use your first example and if it falls into another numeric range use the second example?

- or better yet, is there a way to say "if {myRegisterfield} is one of a given set of register numbers [ex: 1,2,7,8], then sort just sort by the {myDeptField} (which would be just the normal ascending numbers), but if {myRegisterfield} is one of another set of register numbers [ex: 3,4,5,6] THEN sort by the 'customized' order". In other words, if its a particular register number,just sort by a field already in the data set, otherwise sort it by the customized arrangement.

Thanks for your continued assistance.​

Mike

 

[AndyMc]

Something like this:

If {myRegisterfield} in ["Register1","Register2","Register7","Register8"} then
(
{myDeptField}
)
else if {myRegisterfield} in ["Register3","Register4","Register5","Register6"} then
(
if {myDeptField} = "B" then "01"
else if {myDeptField} = "C" then "02"
else if {myDeptField} = "A" then "03"
else if {myDeptField} = "D" then "04"
)

 

[mikeinfbg]

Awesome. That's what I was looking for.
Thanks.
Mike