Returning Single value based on decision 1

[AlvarHanso]

I am using Crystal Reports 5 to create a report on a SQL database. I have a subreport on a report that I want to return a spcific value for. For example the table looks like this:

guar_name guar_no
--------- ---------
Will Clark 1
Barry Bonds 2
Jose Uribe 3

The report is to display one guar_name on the subreport. In the situation in which the report will be run, the user will just print the report as usual if there is one guar_name that they can see in the user interface. But if there is more than one guar_name then the user will put a * in front of the name, and that is the name they want to print on the report. I.E. *Will Clark.
What I need the subreport to do is to grab the name of the person with the * in front of the name or else just pick the next on the list. If there is no * then just the name of the one guar_name will print. I have the formula field for the guar_name in the details section of the subreport and it currently will print all the names. The formula for guar_name is like this:
numbervar currlength := length(guar_name)
If Left(guar_name,1) := "*" then
guar_name[2 to currlength]
else
guar_name

The main problem is that the formula is evaluated on each record, and not a comparison to the other records. It just does this formula for each line. Is there a way to show the output based on a comparison to each record in guar_name?

Thanks a whole lot.

 

[lbass]

Can't follow this. You haven't explained how the subreport relates to the main report. Please explain the structure of your main report, how the subreport is linked to the main report, and where the subreport is located.

-LB

 

[AlvarHanso]

Sorry about that. The main report displays patient information for a particular visit (case_visit_num). The report is intended to show information about the visit (which is workmans comp related) so the main report is really a form the doctor would fill out during the visit (manually). In the main part of the report (Page footer) there are no fields being retrieved. There is one grouping by case_visit_num that displays visit date and type and case number from the database in the group header. The page header essentially shows patient and employer information from database fields. Also in the page header is an adjuster field in which that information is being pulled by the subreport. That subreport is linked to the main report by a guarantor number (which is one of the fields in the guarantor table of the main report). there is one guarantor for many adjusters. The subreport only has the adjuster table.I seem to be getting in the right adjusters for each specific guarantor but it is printing all of them or not the right adjuster, and all I want is the one that either: Has a "*" in front of the adjuster name (if there are multiple adjusters) or the name of the adjuster if there is only one for that guarantor. So far I can't get the report to decide that if there are two adjusters, then pull just the one with the "*". It pulls one or the other based on how I suppress the details section of the subreport. And I can't really just suppress one or the other because there may be a situation where it would need to decide which one. Hope this clarifies thanks for the response.

 

[lbass]

I think that if you sort by the adjuster field (the field, NOT the formula below) and then place the formula:

if left({table.field},1) = "*" then
mid({table.field},2) else
{table.field}

...into the report header of the subreport and suppress all other subreport sections, you will get the correct value, since the asterisk will sort first.

-LB

 

[AlvarHanso]

<code>
Once again you are <b>the</b> man. Thanks alot for the answer. Such a small thing I overlooked. Good job, submitted my vote for "Tipmaster"!!
</code>