One value in 4 seperate fields

[crachic]

I am using Crystal XI.

I want to group by code. The problem is that this code can be in four seperate fields. The four seperate fields are labeled code_1, code_2, code_3, code_4. An example of the code would be like 005.8. This code that I want to group by is put into code_1, code_2, code_3, or code_4 by the end user.

How can I group by this code that may be in code_1 on one record but in code_3 on another record. For example if code 005.8 is in code_1 in one record but in code_3 in another record I want to group by 005.8 and count it twice.

-crachic

 

[IanWaterman]

If the date is in code_1 are other codes null.

You can create a formula to test and then group on that.

@group
If not(isnull(code_1)) then code_1 else
If not(isnull(code_2)) then code_2 else
If not(isnull(code_3)) then code_3 else
code_4

Ian

 

[CR85user]

Ian - What if the field is not null but also is not a matching code?

For example:

Code 1: Null
Code 2: 003.6
Code 3: Null
Code 4: 005.8

Code 4 would not be returned in this instance.

Would a better approach be to use a subreport and link by code? Group by Code 1 in the main report and then in the subreport link the code 1 value using selection as:

{pm-code1} in [{code2},{code3},{code4}]

Suppress the results within the subreport then where the value does not matched the passed value for the group.

'J

CR8.5 / CRXI - Discovering the impossible

 

[IanWaterman]

J

You are right, I was just trying to keep it simple and assume Nulls.

If Crachic tells us otherwisethen we may have to follow your approach, depending what is in them.

Ian

 

[crachic]

The 005.8 could be in any of the code fields (meaing 1, 2, 3, or 4).

The following examples could occur

Code1 - 098.3
Code2 - 005.8
Code3 - null
Code4 - null

Code1 - 005.8
Code2 - null
Code3 - null
Code4 - null

Code1 - 245.7
Code2 - 657.6
Code3 - 005.8
Code4 - null

As you can see code3 would not be filled in unless code 2 and code 1 field is filled in. They have to be filled in order.

What I need to do is group on a code (doesn't matter what the code is) but the code may be in one of the four fields.

Let me know.

-crachic

 

[IanWaterman]

YOU will have to build a query to split the data into 4 sets

eg

Select field 1, field2, field 3, Code1 as Code
from table
where code1 is not null
Union all
Select field 1, field2, field 3, Code2 as Code
from table
where code2 is not null
union all
Select field 1, field2, field 3, Code3 as Code
from table
where code3 is not null
union all
Select field 1, field2, field 3, Code4 as Code
from table
where code4 is not null

YOu can then group on Code.

Ian

 

[chelseatech]

Ian's solution makes a lot of sense as it also handles the situation where the code is in field1 and field3. In that situation, the formula approach will only use the first value, while Ian's UNION solution will create a record for both occurances.

The real problem is that your data design doesn't follow the rules of normalisation. One of the rules states that you should take repeating values Code1, Code2, etc and place them in a subordinate table. You are wasting space if you only have 1 code and the others are blank, and hit a brick wall if you ever need more than 4 codes. A subordinate table has neither of these problems.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm