Displaying a field if a condition holds true 1

[crachic]

I am having trouble getting a field to display when a condition holds true.

I have the following fields that I am trying to work with:
- sport_name from Table Sports
- example values of basketball, baseball, football
- sport_order_number also from Table Sports
- example values of 1, 2, 3

On a person's record they rank there favorite sports.
- for eaxmple - James ranks his in the following order 1-Basketball, 2-Baseball, 3-Football
- for eaxmple - Ray ranks his in the following order 1-Football, 2-Basketball, 3-Baseball


I am trying to get the report to diplay the sport that has a value of 1 for each person.
Output for James should be Basketball.
Output for Ray should be Football.

I have tried the following type formula and have gotten blank results.
if {Sports.sport_order_number} = '1'
then {Sports.sport_name}

Any thoughts would be greatly appreciated!

-crachic

 

[lbass]

If you have a group on the person field, you should be able to go to report->sort records and sort on the order field, ascending. Then drag the sports name into the person group header and suppress the detail section.

-LB

 

[crachic]

lbass

I do not have a group in my report. I put one in and did what you said but it did not work. When I did that, all of my other data that was populating correctly went away. I think it may have something to do with my links. Any more thoughts?

-crachic

 

[lbass]

What tables are you working with? How are they linked? What groups do you have?

-LB

 

[Madawc]

You could use a Running Total, finding the 'Maximum' where {Sports.sport_order_number} = '1'. If you can't use groups, you can reset on 'change of field', which could be the person's name.

If you don't need the other sports for other processing, it would be simpler and quicker to get rid of them in record selection.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[crachic]

Thanks for all of the posts thus far. I got some things to work the way I wanted them to but not all. Here is what I am looking to do.

In regards to the above listed example, I would like the following output.

James Basketball
Baseball
Football

Ray Football
Basketball
Baseball

Basically I want to list the person and the order they chose.

I know that I can do this with groups, but is there a way to print the list of sports in order by using a formula or formulas for each listing 1 - 3.

 

[lbass]

Please explain why you do not want to use groups. Why can't you simply insert a group on name, add a sort by the ranking field, and then place the sport field in the detail section. If you want the first sport to align with the name, then format the group section to "underlay following sections".

For more help you need to be more specific about your fields and overall report design. Finally, please try to respond in a timely manner, so that we who initially responded don't have to reread the entire thread again to remember what it's about because you didn't get back to us until weeks later.

-LB

 

[crachic]

I have no problem using a group, I was just wondering if there is a way to do this through a formula. The reason I was looking to do it through a formula, is because I have multiple reports that I will have to do this very same thing on and it will be easier to use a formula the way the reports are currently designed.

I apologize for not getting back to this post in a timely manner but I just got back to this report myself after working on other reports.

Is there a way to do this through a formula?

-crachic

 

[lbass]

What kind of formula? What do you think a formula can do? Why would a formula be easier than adding a sorting on the rank field?

-LB

 

[crachic]

I feel that if a formula was possible, it would be easier only because of the way the reports are currently designed. If a formula is not possible then so be it.

I do not see why a formula cannot do what I am asking.

Let me try to rephrase it in wording instead of in syntax.

For each survey, a person ranks there favorite sport in order from 1 to 3. Lets just say for the sake of argument, that the same person takes the same survey and gives a different answer. So basically the person has given two different rankings on two different episodes. I am trying to get the rankings to print out for each time he took the survey (Episode 2).

Answers given for episode 1:
(1)Football
(2)Basketball
(3)Baseball

Answers given for episode 2:
(1)Hockey
(2)Football
(3)Rugby

The sport name table is linked to the sport order number table. For each episode of the survey, it stores that for episode 1, the order was Football, Basketball, Baseball and for episode 2 the order was Hockey, Football, Rugby.

I am trying to get the output to print for each episode. I am fully aware that this can be done via groups, and I understand how to do it, but I am asking if this output can be done via a formula. I also understand that the groups are a easier way to go, but the way the report was designed originally, it would have to basically be re-written to use groups.

-crachic

 

[crachic]

Let me also say it this way in words instead of syntax...


If the order number = 1 then print the sport ranked 1, if the order number = 2 then print the sport ranked 2....

-crachic

 

[lbass]

Please show some sample data for at least two people, as it looks now if placed in the detail section, and then show what you want the results to look like. Sorry, but this is still very unclear. I don't really have a clue what you want the results to look like.

Sure, you can write conditional formulas, e.g.,

if {table.sport} = "Basketball" then {table.rank}

Then you could insert a maximum on this at the user level if you want some sort of horizontal display. Or for multiple episodes, insert a Nth largest (1), Nthlargest(2), etc.

You could possible insert a crosstab using name as the row#1, episode as row#2, sport as the column and rank as the summary field.

All depends upon what you are looking for.

-LB

 

[crachic]

I apologize for this being unclear. Let me take a step back and focus on one thing that I am looking for. I feel that if I get this one thing figured out, I should have no problem with the rest.

Let me start by giving you some sample data below and then I will give you what I am expecting for output. Let me say that I am not worried about the format of the output at this time, just the output itself. Like I said above if I can figure out how to get the correct output, I should have no problem figuring out the format I need. I am going to keep this as simple as I can for both of our sakes.

Sample Data:
Person: James
Episode: 1
Order that he ranks the sports: Basketball, Baseball, Football

Person: James (same as above)
Episode: 2
Order that he ranked the sports: Football, Rugby, Hockey

Now this is the part that if I can get, the rest should be no problem. I would like to be able to query the database and say for episode 1, print the sport that was ranked 2. The expected output should be Baseball. Another example would be for episode 2 I want to print the sport that was ranked 1. The expected output should be Football.

The episodes act indepentently of each other. The only link between the episodes are that they pull from the same sport table name.

I do not want to put a selection formula in asking the user to enter a ranking, I want to hard code it whereever in the report I need it. Lets say for one report I need it to only print the #2 ranking. Maybe another report I only want it to print the first ranking.

I hope this better explains what I am needing. Please let me know if you want me to clarify anything.

-crachic

 

[lbass]

Once again, what does your end report look like? Show the report for two people.

-LB

 

[crachic]

This particular report would look like the following.

Name Date Survey Taken Episdoe # Choices
----------------------------------------------
James 12/10/08 1 Basketball
Baseball
Football

James 1/20/08 2 Football
Rugby
Hockey

Another report would look like this:

Name Date Survey Taken Episode # 2nd choice
------------------------------------------------
James 12/10/08 1 Baseball
James 1/20/08 2 Rugby

As you can see some reports may only require the 2nd choice. Some of the reports I have require only the first choice.

Please let me know if you need anything else.

-crachic

 

[lbass]

You want to make separate reports instead of using a parameter? That seems very inefficient. But assuming this is the case, then you could group on name and on episode, and place those fields in the group footer, and suppress the group header and detail section. Then create three formulas:

//{@reset} to be placed in the episode group header:
whileprintingrecords;
stringvar sport := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar sport;
if {table.rank} in [1,2,3] then //change this in each report
sport := sport + {table.sport} + chr(13);

//{@display} to be placed in the episode group footer:
whileprintingrecords;
stringvar sport;

Right click on {@display}->format field->common tab->check "Can Grow".

-LB

 

[crachic]

The two reports that I need are completely different of each other. I just gave you a very small portion of the report. I understand that parameters are much more efficient. The formulas you gave me seem to be working. I just need to thoroughly test the report with a lot of data. Thanks a bunch lbass.

-crachic