2nd time posted, pls help sorting issue

[mwhalen]

any thoughts on how to do this?


Individual Table
ID name Status
4455 Bob Smith Student
4455 Bob Smith Staff
4455 Bob Smith Parent
4455 Bob Smith Faculty
4455 Bob Smith Friend
4455 Bob Smith Alum
4455 Bob Smith Associate



Status Table
Type Status
1 Alumni
2 Associate
3 Parent
4 Staff
5 Friend
6 Faculty


I want to pull all records relating to Bob Smith regardless of his status. However, as far as status is concerned, we have a certain hierarchy (not actually in the table but just in principle) being in the following priority: Alum, Associate, Parent, Staff, Friend, Faculty. The problem is the data is not in some sort of hierarchy in the table and so when I drop the line in the footer, I get say Bob's Alumni status. When I drop it in the header I get his Staff status which because Alum takes greater priority I want to get his higher status. When I do a formula (if type = 1 then Alumni, if type = 4 then Staff) I don't believe the output is sorted based on the order in the formula. I really want to avoid using a secondary group in my main report or using a secondary group in a subreport. I find it slows it down too much. My first thought was to use a sort on my main report in ascending or descending order either based on Type or Status but as I said, it's in no particular order in the Status Table so that doesn't seem to work.

Anyways, any thoughts on this at all? We're using Crystal Version 7.

Much appreciative as always of your time!

 

[Naith]

What's your set up?

GH1 on {ID}: {Name}
Details: {Status}?

Any other grouping?

 

[mwhalen]

Hi, thanks! I have nothing in details and I am grouping on the id. So in either the header or the footer (doesn't matter) I have the id, name, status and all the other info pertaining to Bob Smith.

 

[Naith]

I don't get that.

If you want all Bob's statuses, you need to have something in the details section. The way you're grouping (with all the details in the group header or footer), you're only ever going to get the first or last Bob record in the database, and miss all the rest.

Naith

 

[mwhalen]

well I'm going to pulling all id's in our database - I don't just want bob's - I was using one id as an example of all the different pieces of data. So I want to return one row per person but within that row I want to see their status according to our hierarchical status

 

[Naith]

I figured you wanted more than just Bob, but I didn't realise you only wanted his uppermost status. (I'm guessing that's what you mean from your last post, because it does go against what you said in your 1st post about wanting all Bob's statuses.)

That being the case, and please come back to me if I've misunderstood, I would order the report like the following:

Group on {ID}
Place the record fields in the details section.
Conditionally suppress the details section on:
{Type} < Maximum({Type},{ID})

Or, if you do want all the records for each status, and I've just misunderstood you, then drop all the detail records in the detail section, group on {ID} and order by {Type} descending.

Naith

 

[mwhalen]

thanks - to clarify, I'm going to select all id's so by default I'm going to get all statuses for Bob (and all the other people). But I only want to return one line for each person which is why it has to go into the header or the footer. But in that one line I want to see only their hierarchical status.

I'm not exactly an expert Crystal developer so maybe you can explain your reasoning behind putting it in the details... I'm dealing with hundreds of thousands of id's in our databse so will that be too much to put in the details?

 

[mwhalen]

Plus, I won't always be the same status for different individuals. For example, if Bob has an alum status, that's his main status. But if Bob doesn't have an alum status but has a student status and a parent status then student has the higher priority than parent.

 

[Naith]

Placing the details in the details section with the conditional suppression makes sure that (a) you can order by the status type, and (b) you can suppress the details of any row that isn't the highest status row for that individual.

i.e.

GroupHeader {ID}
Details: 4455 Bob Smith Alumni
4455 Bob Smith Associate
4455 Bob Smith Parent
4455 Bob Smith Staff
4455 Bob Smith Friend
4455 Bob Smith Faculty

The bold row gets revealed. The rest gets hidden by the conditional suppressor: {Type} < Maximum({Type},{ID})

Naith

 

[Naith]

It doesn't matter what the highest status is. If Bob's best status was Alumni, and Kevin's best status was Friend, both those rows would get shown.

Naith