2nd time posted, pls help, issues with sorting output

[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!

 

[BlurredVision]

You could change your group to a specified order - This will allow you to set the order that you wish.
Alumni
Associate
etc... Brian

http://www.crystalinfo.net

 

[mwhalen]

Thanks but I have to have my main grouping on the id number.

 

[chris777]

It seems like the order you want their status to appear is the same order as your Status.Type field. If so, can you add your Status.Type field in the Sort Order window and sort them "Ascending" Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[heppme]

Create a formula to establish the type heirarchy (examp. if status = 'Alumni' then 1 else if ...). Sort on this field. Create a formula to display the minimum (if top of heirarchy is assigned a one) status field [examp. minimum({@heirarchy},id) and place this in the id header or footer.

 

[mwhalen]

Thanks both of you. Chris my order of my hierarchy unfortunately is not the same as the order in the table.