Taking a maximum value of a select case formula

[synbios1978]

I have the following code:

If IsNull({EstEntFolding.Wizard-Option}) Then "" Else
Select {LOQSegment.Segment-ID}[1 To 20]

Case " 1":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 2":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 3":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 4":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 5":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 6":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 7":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 8":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 9":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 10":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 11":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 12":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 13":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 14":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 15":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 16":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 17":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 18":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 19":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Case " 20":
IF Minimum({EstEntFolding.Wizard-Option}) = 0 Then "Fold" Else ""
Default :
"";

98% of the time, case " 1" will determine whether the entire part will or will not have the word "Fold" in it. However, we just ran into an example where that is not the case. Case " 1" does not have a Wizard-Option of 0 in it, however, Case " 2" does. When I preview the field in Crystal, it shows the empty value first, then the word "Fold". If there is a "Fold", I want it to display no matter where it comes in at. In many other examples, I use another field and check the maximum value of the above code. However, I'm getting an error when I try to check the maximum value, and it tells me that the field cannot be summarized. I am using Crystal 9. Can anyone give me a workaround for this issue??

Thanks,

Synbios1978

 

[synapsevampire]

A little overkill in your code here.

Conveying requirements is much simpler:

Crystal version
Database/connectivity used
Example data
Expected output (based on example data)

If all you're trying to do is determine if it contains the word "FOLD", use:

If IsNull({EstEntFolding.Wizard-Option}) Then
""
Else
if val(left({LOQSegment.Segment-ID},2)) in [1 to 20]
and
Minimum({EstEntFolding.Wizard-Option}) = 0 Then
"Fold"
else
""

Looks like some pretty whacked logic to me though.

Again, try posting technical inoframtion instead of how you've approached it.

-k

 

[synbios1978]

Thanks for the reply. Let me try to explain what I'm trying to do better.

Crystal version = 9
Database/connectivity = ODBC
Example Data in database = 2 fields:
Segment-ID and Wizard-Option.

i.e.
Record 1:
Segment-ID = " 1"
Wizard-ID = 1
Record 2:
Segment-ID = " 1"
Wizard-ID = 2
Record 3:
Segment-ID = " 2"
Wizard-ID = 0
Record 4:
Segment-ID = " 2"
Wizard-ID = 1

As long as any segment has a Wizard-ID of 0, then I want the word "Fold" to be the result of the formula, otherwise I just want "".

I'm sorry for the goofy approach to the last message. It was my first post also when I designed the code, I had just started CR. I've been at it for a few months now, I've come a long way, but still have a long way to go. But thanks for the help in your prior code. It shortened things considerably. I added a couple lines to it which appears to be allowing me to get the maximum value now of the field.

Thanks again for all your help!!

 

[synapsevampire]

Try:

If {table.Wizard-ID} = 0 then
"Fold"
else
""

-k

 

[synbios1978]

Actually, I spoke too soon. After testing it a little further, I am still stuck. The last response won't work because the fields do not come from the same table, and there can be many segment-ID's per report, plus a segment is the forth level of grouping inside the report.

When I browse the field data based on the original code you provided:

If IsNull({EstEntFolding.Wizard-Option}) Then
""
Else
if val(left({LOQSegment.Segment-ID},2)) in [1 to 20]
and
Minimum({EstEntFolding.Wizard-Option}) = 0 Then
"Fold"
else
""

I get really close to what I need. It shows me an empty line and then underneath it the word "Fold" shows up. However, when I select that field to display it always shows the empty line. CR tells me it cannot do a summary on that field, so I cannot get the maximum value by using the maximum formula. Any ideas on how to get CR to give me the maximum value for the above formula?

Thanks!!

-Synbios1978

 

[synapsevampire]

Since it's only returning "Fold" or "", the maximum will only be one or the other.

Also, there's already an aggregate being used: minimum

You can't perform an aggregate on an aggregate formula.

You keep describing data instead of presenting examples. If it's coming from different tables, show us.

This statement "The last response won't work because the fields do not come from the same table" doesn't make sense either, my formula checks just one table for that value.

I'm not sure why you're using th minimum anyway, perhaps you misunderstand it's function.

Try posting:

Example data (you did this)
Expected output based on the example data

-k

 

[synbios1978]

Okay, I'll get detailed into the issue. This is a subreport, which references 7 different tables, however, this formula is only impacted by two of the tables. (There is one primary table with 6 different tables connecting off of it). Lets call the Primary table "Segment". The subtable I'm concerned about is called "Folding". They are joined by 3 fields as a left outer join. The fields are: System-ID, Estimate-ID, and Segment-ID. System-ID is the company id, which is a required linking field between any table in this system. Before I explain the Estimate-ID and Segment-ID, let me describe the grouping levels in the report. It is grouped based on the Estimate-ID then a Component-ID. The database is structured so that 1 estimate can contain 1 or more components, while 1 component can contain 1 or more segments. However, the "Folding" table does not contain a component id, but it does contain a Segment-ID.

Now to look at the folding table. The folding table contains many fields, however the key fields to look at are the fields that link to the "Segment" table and the Wizard-Option field. The wizard-option is generated everytime an estimate is created, starting with the number 1, or if an estimate has no folding, the table can be null. The only time there is a 0 is if the wizard is accepted and there is folding in the estimate. That is when I want the word "Fold" to be displayed.

However, each segment can contain many wizards in the folding table (although usually no more than 2 per segment), so the table usually has between 0 and 10 wizards depending on how many segments there are. So, what I am doing is checking each segment (up to 20 segments) and the segment table is telling me which component it belongs to for grouping purposes, and checking the minimum value of wizard-option to see if it's a 0, and if it is, then the estimate includes folding, however if the minimum is a 1 then it does not include folding.

Here is the actual example data for the four fields from the Folding table for one estimate:

System-ID Estimate-ID Segment-ID Wizard-Option
WCP 1295000 1 1
WCP 1295000 2 1
WCP 1295000 2 0
WCP 1295000 3 1
WCP 1295000 3 1
WCP 1295000 3 2
WCP 1295000 3 2
WCP 1295000 3 3
WCP 1295000 3 3
WCP 1295000 3 0
WCP 1295000 3 0

If the first segment folds (has a wizard-option of 0), everything works fine as it's the first value used, it's the first to appear. However, as in the example above, if it doesn't fold, and one of the other segments does, I get a blank output ("") and if I browse field data, I see the first row is blank ("") and the second row has the word "Fold" in it. If there is a "Fold" anywhere in the component, I want it to show "Fold" regarless of whether it is in the first segment or not.

I hope this helps out a little more, and thanks for all the help so far. Like I said, I'm really new to Crystal, although I am familiar with working with formulas in other programs, i.e. Excel, VBA, Access, etc. I'm still not familiar with the way Crystal formulas work and stuff.

Thanks in advanced!!

-Synbios1978

 

[synbios1978]

synapsevampire,

Thanks for all your help. I just figured it out. I put a criteria of null or 0 for the wizard-option, and that allowed me to remove the minimum function from the formula. Then I was able to take the maximum value.

Thanks again for all your help!!

-Synbios1978