Case statement with sub-cases?

[CCSVermont]

I'm new to Crystal formulas, and I've got this formula that I've created from reading posts here, that's not working.

For every misc1 that's either "3" or "6", I'm getting BOTH values, not JUST the one I want (ie; if loccode is "01", I want a certain department name returned, if loccode is "02", I want ONLY the other one returned). My report is showing subgroups for BOTH on one report, doubling the data.

Can anyone tell me what is wrong?
---------
Select {invonhand.misc1}[1]

case "0", "1":
'Department 1'

case "2":
'Department 2 - Bella Flora'

case "3":
(Select {invloc.loccode}[1 to 2]
case "01": 'Department 3a'
case "02": 'Department 3b'
default : 'Unknown Department')

case "4":
'Department 4'

case "5":
'Department 5'

case "6":
(Select {invloc.loccode}[1 to 2]
case "01": 'Department 5a'
case "02": 'Department 5b'
default : 'Unknown Department')

case "7":
'Department 1'
---------

 

[synapsevampire]

The formula cannot return 2 values, ever.

If you are getting 2 groups for some value then there are 2 different rows.

I suggest that you place both of the fields alongside the formula in the details section, you'll see what I mean.

If you're using the formula in a group section then you will get a return for every group, whatever the group is (you need to post technical information, such as WHERE you are placing this formula).

I would also suggest adding in a last DEFAULT to allow for values that may exist beyond your understanding of the rules.

I tested your formula this way for sanity and it seems OK:

Select "6"[1]

case "0", "1":
'Department 1'

case "2":
'Department 2 - Bella Flora'

case "3":
(Select "07"[1 to 2]
case "01": 'Department 3a'
case "02": 'Department 3b'
default : 'Unknown 3 Department')

case "4":
'Department 4'

case "5":
'Department 5'

case "6":
(Select "09" [1 to 2]
case "01": 'Department 5a'
case "02": 'Department 5b'
default : 'Unknown 6 Department')

case "7":
'Department 1'

-k

 

[CCSVermont]

Thanks for the quick reply.

I'm attempting to use this in a group, to create sub-total breaks and for sorting. The number that I'm converting is like '0010', '1000', '2000', '2001', but not all '1's belong to 'Department 1', etc.

for instance, the item's department may begin with a '0' or '1' and be "assigned" to the group 'Department 1'.

If it begins with a '2', then it's in group 'Department 2'.

If it begins with a '3' AND the loccode is '01', then it's in 'Department 3a', but...
if it begins with a '3' AND the loccode is '02', then it's in 'Department 3b'. etc.

is this clearer?

Maybe I cannot use it there? maybe I have to create something in the "Group Name fields"?

Thanks!

 

[synapsevampire]

Crystal doesn't have subgroups, and I suspect that your architecture won't work.

Anyway, if you intend to create your own groups, then the formula should go in the details so that each row is analyzed.

As for your IF ELSE architecture, that might work as well, in lieu of BUT you use ELSE.

Rather than stating how people must do things, the following generally defines requirements best:

Crystal version
Database/connectivity used (we may offload processing to the database)
Example data
Expected output

Example data doesn't mean how the report looks, that's how a report looks, it means what is in the recordset being returned to Crystal.

Expected Output shows what you need based on the example data.

As you've mentioned, you don't understand Crystal well, so don't state how to do things, rather explain what you have and what you need.

It may be that you can just select the formula you've created a a GROUP within the group that you already have, but you still didn't even bother to state what you are currently grouping by.

And make sure that the formula accounts for ALL rows or it can't be used as a group.

-k

 

[CCSVermont]

>>As you've mentioned, you don't understand Crystal well, so don't state how to do things, rather explain what you have and what you need.

You sort of lost me here... I wasn't stating how to do anything, but attempting to describe what I need or am trying to do in a clearer way, so it would be easier to help me.

For clarification:
Crystal Reports XI
VFP database/ODBC
Data: There are several databases with relationships. The invonhand.misc1 contains the numeric department reference, '0010', '1000', etc. This is used in the formula, along with the invloc.loccode, which is related by the department reference in invonhand.misc1

I think the "all rows or it can't be used as a group" part may be my problem, because I'm getting TWO group headers/sections for the two areas in question, ie;

Dept 1
.
.
.
Dept 2
.
.
Dept 3
.
.
Dept 3a
.
.

I only WANT to see this if loccode = '01':
Dept 1
.
.
Dept 2
.
.
Dept 3
.
.
or this, if loccode = '02'
Dept 1
.
.
Dept 2
.
.
Dept 3a
.
.

 

[synapsevampire]

Where's the example data and the expected output as a result?

Not sure why you prefer to just post whatever comes to mind on the topic, what is required is your environment, and the required output.

Again, the formula CANNOT return more than one value per row.

I still have no idea what the report is grouped on, or if this formula is the only group, nor where you are using the formula.

note the previous post of "If you're using the formula in a group section then you will get a return for every group, whatever the group is (you need to post technical information, such as WHERE you are placing this formula)". So are you using this as a group, or is there anotehr group, and this is a child group, or do you know what I'm asking, or?

Perhaps some understanding of Crystal is in order, and the groupings therein.

It is very similar to a group in Fox or SQL, in that whatever column you group by will result in a distinct value appearing in the group.

I used to code in Fox years ago, and you might be better served to just group the data within Fox if that's sinmpler for you, but you need to demonstrate what you have in the recordset and what you need.

-k

 

[CCSVermont]

I got it to work. I think I had a broken link/relation that was causing one table to produce two records for each detail record.

Thanks.

 

[synapsevampire]

Sounds like row inflation, glad you worked it out.

-k