Search for text in two different fields

[nuttyernurse]

I am new, but getting better thanks to all the great information here! I am trying to use two different fields to search for specific words and return a value of 1 if present. I then sum the field and get my total ambulance arrivals. The reason I am doing this is the data could have been entered in either of these fields, but only want to count it once.

I have tried the following (simple)formula:

if "Ambulance" in {EDMS_ARC.F3} then 1 else
if "Ambulance" in {EDMS_ARC.F4} then 1 else
if "EMS" in {EDMS_ARC.F3} then 1 else
if "EMS" in {EDMS_ARC.F4} then 1 else
if "Rural" in {EDMS_ARC.F3} then 1 else
if "Rural" in {EDMS_ARC.F4} then 1 else
if "Grady" in {EDMS_ARC.F3} then 1 else
if "Grady" in {EDMS_ARC.F4} then 1 else
if "AMR" in {EDMS_ARC.F3} then 1 else
if "AMR" in {EDMS_ARC.F4} then 1 else 0

There are several values to go in the quotes, but is there a more accurate way to do this? I think my data is dirty.

Thanks in advance!

 

[CoSpringsGuy]

IF {EDMS_ARC.F3} IN ["Ambulance","EMS","Rural","Grady","AMR"] then 1
else IF {EDMS_ARC.F4} IN ["Ambulance","EMS","Rural","Grady","AMR"] then 1
else 0

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nuttyernurse]

I tried the new formula, returns fewer values, if the text is present returns a zero, and nothing if the F3 field is blank.

This is the formula I used:
IF {EDMS_ARC.F3} IN ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central","Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry","Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other"]
then 1
else
IF {EDMS_ARC.F4} IN ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central","Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry","Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other"]
then 1
else 0

 

[CoSpringsGuy]

try....

IF {EDMS_ARC.F3} IN ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central","Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry","Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other"]

or

{EDMS_ARC.F4} IN ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central","Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry","Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other"]

then 1

you dont need the else 0, as it will be the default result


in your formula editor you probably want to use default values for null.... there is a drop down box on the menu bar



_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nuttyernurse]

Unfortunately, the same results. I believe the most accurate I will get is based on the F4 field, as this field is required, the other (F3) is the one that is hit or miss.

 

[lbass]

Are you saying that F4 can be null?

And are you saying that each of the quoted values mean the same thing, i.e., ambulance?

I also think you should show some sample data.

-LB

 

[nuttyernurse]

EDMS_ARC.F4 can not be null(required field), EDMS_ARC.F3 can.

As for a data sample-
Arrival Method (EDMS_ARC.F3) data looks like this:

AMR
Atlanta South
Central
Clayton County

Arrival Method (EDMS_ARC.F4) data looks like this:

Method of arrival: Ambulance, Atlanta South (2009-11-08 14:10:00);Method of Arrival: Atlanta South, (2009-11-08 17:00:50);

or

Method of Arrival: Rural Metro, (2009-11-01 02:39:43);Method of arrival: Ambulance, Rural Metro (2009-11-01 02:46:47);

or

Method of Arrival: EMS, (2009-11-02 20:54:22);

And yes, each quoted value means the same, patient arrived by ambulance.

 

[CoSpringsGuy]

I assume there are circumstances where both F3 and F4 are populated? Are there also instances where F4 is populated and F3 is not?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

the reason your formula didnt work is because

{EDMS_ARC.F4} IN ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central","Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry","Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other"]
is looking for the exact string.. so your gonna have to use a different function for F4.. try this formula and let me know if you get better results.

//
local stringvar array criteria := ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central"
,"Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry"
,"Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other"];
local numbervar b := ubound(criteria);
local numbervar a;
local numbervar output;
for a := 1 to b Do
(
if {EDMS_ARC.F3} in criteria then output := 1
else if instr({EDMS_ARC.F4},criteria[a]) > 0 then output :=1
else 0;
);
output


its not very good programming style but I think it should work....

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

Nurse...

Im slow today so I have been playing with the code I sent you and made a change..
use
// USE DEFAULT VALUES FOR NULL
local stringvar array criteria := ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central"
,"Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry"
,"Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other","3100","5002","INSURANCE"];
local numbervar b := ubound(criteria);
local numbervar a;
local numbervar output;

if {EDMS_ARC.F3} in criteria then output := 1;

for a := 1 to b Do
(
if instr({EDMS_ARC.F4},criteria[a]) > 0 then output := 1;
);


output


I was also going to make a suggestion... not sure what you want your report to look like but would it possibly be a benefit to group by those fields? This would give you the ability to report on locations. If so try this code and group on the formula. From there you can get counts of ambulance arrivals for specific areas.. Just a thought

//USE DEFAULT VALUES FOR NULL

local stringvar array criteria := ["Ambulance","EMS","Rural","Grady","AMR","Atlanta","Capitol","Central"
,"Clayton","Dekalb","First","Forrest","Global","Hapeville","Henry"
,"Hospital","Life","Med Stat","Metropolitan","Pro Care","Rescue","Southern","Other","3100","5002","INSURANCE"];
local numbervar b := ubound(criteria);
local numbervar a;
local stringvar output;

if {EDMS_ARC.F3} in criteria then output := {Disposition_Fields.DISPOSITION_CODE};

for a := 1 to b Do
(
if instr({EDMS_ARC.F4},criteria[a]) > 0 then output :=criteria[a];
);
if output = "" then output := "Nothing";

output

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

whups ...
replace {Disposition_Fields.DISPOSITION_CODE} with {EDMS_ARC.F3} // that was from my database

There are also a few strings in the criteria array you can remove

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nuttyernurse]

CoSpringGuy,

Still the same results. Your programming is way better than anything I have to offer and I really appreciate the help. There are instances where both fields are populated, and this is the case 85% of the time.

Thanks again!
Michael

 

[CoSpringsGuy]

Someone will jump on here with better solutions than I do but until then I can help troubleshoot...

Question....
From your sample data post... it seemed F3 would probably be the most reliable field to obtain the desired information. Is that the case?
In the 85% of the time both are populated, is either F3 or F4, 100% of the time, the information you need or is it one or the other?

I guess what I'm wondering is if the formula we are looking for should first evaulate F3 and if it meets our criteria then dont worry about F4. If F3 is null or does not contain our criteria then see if it exists in F4.


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nuttyernurse]

CoSpringGuy,

Sorry, must have been posting the same time you were. Did get results except for those fields where F3 was null. I did like the grouping!

Thanks!

 

[CoSpringsGuy]

ok .... be sure and select DEFAULT VALUES FOR NULL in the drop down box

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nuttyernurse]

CoSpringGuy,

That was it, how dumb am I! Works great and quick and dirty data analysis makes the data clean!!!!!

Thank you!

 

[CoSpringsGuy]

Glad it worked!

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection