If (table.field) = 1 then 'Yes' Else 'No'

[Garyjr123]

I have a report I'm writing that needs to just tell me if implants were used on the case. The subreport I used has a if, then, else formula but when I add the formula to the details field I get all the results back as yes or no.

If {caseproitemlist.surgsuptype_id} = 3 then 'Yes' else 'No'

There are many items used during a case and surgsuptype_id = 3 is an implant so the formula is working the way it should but I need to just limit the formula to 1 Yes or 1 No so if there is an implant it will return only 1 yes (with no 'No' returned) and if there are no implants it will return only 1 no.

LBass...thanks for all the help. This website is great and is very useful.

 

[hilfy]

So, if I understand you correctly, you have a list of items and you want to return 'Yes' if any item in the list is an implant and 'No' if there are no implants in the list, correct?

Assuming you're grouping by case, this is not difficult. I can think of two ways to do this:

1. The easiest way is to keep the formula you have. In the group header or footer, put a summary that is the Max of this formula. Since 'Yes' is alphabetically greater than 'No', this will return 'Yes' if any of the detail records are an implant.

2. Using a variable in a formula that looks something like this (assuming you're grouping on a field called {case.case_id}):

StringVar has_implant;
if PreviousIsNull({case.case_id}) or {case.case_id} <> previous({case.case_id}) then has_implant := 'No';
if {caseproitemlist.surgsuptype_id} = 3 then has_implant := 'Yes';
has_implant

This will initialize the variable to 'No' at the start of the report and whenever the case id changes. If there are any items that are implants, the value will change to 'Yes'.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Turkbear]

Hi,
If I understand it, each record may have the field
{caseproitemlist.surgsuptype_id}
and it can have many values but you are only interested with the value 3 ; if it is 3 then show 'Yes' and if it is not 3 show 'No' .

If that is the case, all other values for that field will show 'No' since the IF test is not met. To avoid this you might need to supply the other possible values in your If..Then..Else test something like

If {caseproitemlist.surgsuptype_id} = 3 then 'Yes'
 else 
If {caseproitemlist.surgsuptype_id} IN (1,2,4,5,6) then
""
Else
'No'

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Garyjr123]

Turkbear/Hilfy,

Both of you are correct but now I want to take it one step further, If I have a 'Yes' in any of the returned records I just want to return the one 'Yes' and suppress all the 'No' and if there are no returned records with 'Yes' then just return one of the 'No' for the records.

@TURKBEAR: I think you were trying to do that for me but when I put in your formula as @implant, I get the message: "the ) is missing" and then shows me where it is missing (in this case after the 1 highlighting the comma. Any reason why?

@Hilfy: For your first option, would I be able to do that off a formula in the details section (I'm going to try it now) and for your second option, does that return either a 'Yes' or 'No' dependent on the criteria I stated above?

 

[Garyjr123]

I think I spoke to soon guys and Hilfy, the summary with Max worked, thank you again but I was wondering if there was another formula I could use to limit those details to one result the way I described.

 

[Turkbear]

Hi,
Sorry about that ( too long away fron CR) Try:

If {caseproitemlist.surgsuptype_id} = 3 then 'Yes'
 else 
If {caseproitemlist.surgsuptype_id} IN [1,2,4,5,6] then
""
Else
'No'

I used parens not the expected brackets.





To Paraphrase:"The Help you get is proportional to the Help you give.."