Needing to extact certain data from Memo field in table

[BernieO]

for example buried in the memo field will be PHQ-9 (19) and PHQ(9)- 3. There could be more variations of this..
I am trying to match to the PHQ-9 or PHQ(9) and than if found pull out the 19 or 3.. based on the examples above.

The 19 or 3 would have to be stored as numeric values because I need to check to see if they are greater than 9 before pulling data to the report from the table.

Any suggestion . Believe currently using CR8

 

[IanWaterman]

Try this formula

If left(Split({yourfield}, 'PHQ')[2], 5) = 'PHQ-9 (19)' then mid(Split({yourfield}, 'PHQ')[2], 8,2) else mid(Split({yourfield}, 'PHQ')[2], 5,1)

Ian

 

[xxyyzz49]

Since the state problem used the word buried in a Memo field finding the PHQ with the InStr() function should be the first step with additional processing based on the delimiter found after the PHQ string. The left() function could be applied for that purpose. To get the 19 or 3 as in the examples try data := ExtractString("(19)","(", ")") for example.

 

[BernieO]

Thanks for your responses, I'm a bit rusty on my Crystal and thought I would need to use the InStr() function, but am unfamiliar with using it along with the left(). I would think I would also need the if statement as above because it can be more than one that I'm looking for.


Or pmsawyer are you saying just look for the PHQ, after that could I just look for the next space (because there is one before the (19) or -3. and than use the extractstring().. but how do I know what to look for because the number can always be different.

I have this example that I thought woould be close to what I need to do.. but uncertain what changes I need to make.

whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({YourMemoField},"PHQ");
v_end := instr(mid({YourMemoField}, v_start + 3)," ");
if {YourMemoField} like ["*PHQ*"] then mid({YourMemoField},v_start,(6 + v_end)) else ""

 

[lbass]

Do you mean there can be more than one instance of "PHQ" in each instance of the memo field?

Also I don't think you could be using CR8 if you are able to use a memo field in a formula (other than for a null check).

-LB

 

[xxyyzz49]

Some example code for the two cases in your posting. DataString for your case would be a mid based on the record being processed, assuming of course you can process memo fields as lbass pointed out.

local stringvar DataString;
DataString := "PHQ-9 (19)";
ExtractString(DataString,"(", ")" ); // will yield 19

local stringvar DataString;
DataString := "PHQ(9)- 3 ";
ExtractString(DataString,"- ", " " ); // will yeild 3

 

[BernieO]

lbass - no only 1 instance in each memo field... I actually had to convert the memo field to text in order to use it in the formula.

 

[BernieO]

Thanks alot for your help . I will give it a shot and see what I can come up with.

 

[lbass]

Try this:

local stringvar z := {table.memofield};
local stringvar array y := split(z," ");
local numbervar i;
local numbervar n := ubound;
local stringvar x := "";
local numbervar k := 0;
for i := 1 to n do(
if y like "PHQ*" then (
x := replace(replace(y,"-",""),"(","");
if x like "PHQ9*" then
k := tonumber(replace(replace(y[i+1],"(",""),")",""));
));
k

Do not use "whileprintingrecords"--then you can insert summaries on this formula.

-LB