2nd time posted, pls help re 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!

 

[JeffreyMorrison]

I would help but your description of the report problem is confusing. If you still want help send me your report with the data saved.

jmorrison@second-foundation.com

 

[mwhalen]

sorry can't do that

 

[lyanch]

You can sort on a formula.

Set your formula up like

stringVar status := {table.status};

if status = 'Alumini' then 10
else if status = 'Associate' then 20
else if status = 'Parent' then 30
else if etc
else 200

-------
Then got to sort and sort by the formula.

You don't have to use multiples of 10, it just makes it easier if later they add another that goes between two of the others.

Lisa

 

[Naith]

Isn't that the same as sorting by Type?

 

[lyanch]

Yes, the way she shows the tables... but said the heirarchy isn't maintained in the db.

Lisa