Help with formula

[huytonscouser]

I have a field {SRMSWITCHACTZONESETMEM.ZONENAME} from which i want to create a joined string.
Until recently the contents of (SRMSWITCHACTZONESETMEM.ZONENAME} always had the data i wanted foloowing the 1st "_" delimiter.

Now i have discovered a change, and my formual needs to join until it encounters "numerictext". But i'm not quite sure of the loop syntax to accomplish this.
I'm sure a "formula" expert can know this out very quickly.


So in a nutshell :-

When SRMSWITCHACTZONESETMEM.ZONENAME = z_SGPILM002_617191_0687_A2
My current formula :-

IF "_" IN {SRMSWITCHACTZONESETMEM.ZONENAME} THEN
currenthostorhbaalias :=uppercase(Split ({SRMSWITCHACTZONESETMEM.ZONENAME},"_")[2])
ELSE currenthostorhbaalias:={SRMSWITCHACTZONESETMEM.ZONENAME}

Returns :-
SGPILM002



But when

SRMSWITCHACTZONESETMEM.ZONENAME = z_SGP_AESC_APP01_61cc89_0687_A0

it returns SGP

i want to return SGPAESCAPP01 (SGP+AESC+APP01
i.e. only join elements that are non-numeric text..

The field always has four(4) "_" entries.


Thanks in advance.

 

[CoSpringsGuy]

APP01 has numeric text in it..... did you mean you want it to return SGPAESC?

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[huytonscouser]

The 2nd element after z_ is always TEXT.

I want to create the string from {SRMSWITCHACTZONESETMEM.ZONENAME} until it hits an element that startswith numerictext.

i.e. from this string z_SGP_AESC_APP01_61cc89_0687_A0

it would return :- SGPAESCAAPP01

for z_SGPILM002_617191_0687_A2
it would return :-SGPILM002 as the next element "617191" starstwith numerictext.

Thanks for your help.

 

[CoSpringsGuy]

stringvar array b := split({SRMSWITCHACTZONESETMEM.ZONENAME},"_");
stringvar a;
numbervar c;
if instr({SRMSWITCHACTZONESETMEM.ZONENAME},"_") > 0 then
For c := 2 To ubound(b) Do
(
if isnumeric(left(b[c],1)) = true then exit for;
if isnumeric(left(b[c],1)) = false then a:=a+b[c]
);
if instr({@test},"_") = 0 then a := {SRMSWITCHACTZONESETMEM.ZONENAME};
a

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[huytonscouser]

Thank you CoSpringsGuy your solution worked fine, i just made 1 change:

stringvar a :="";

so that it's unique each record.

 

[CoSpringsGuy]

I don't think you need that but I'm glad it's working...

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection