How to substitute within a field?

[Chalkster]

I am trying to work out how to change part of a text string without changing the entire field

Basically I generate a text string by a calculation combining data from up to 14 fields adding required syntax between fields to create a text string.

Case (IsEmpty(FROM)=1;"";

(IsEmpty ( RFROMa ) =1) or (IsEmpty ( RTOa )=1) or (IsEmpty ( INC )=1);"_V(" & FROM & ")-[" & AL & " " & CL & "]-V(" & TO & ")" ;

IsEmpty ( RTOb )=1;"_V(" & FROM & ")-[" & AL & " " & CL & INC & "<"&RFROMa & RTOa & "]-V(" & TO & ")"; IsEmpty ( RTOc)=1;"_V(" & FROM & ")-[" & AL & " " & CL & INC & "<"&RFROMa & RTOa &"][" & AL &" " & CL & INC & "<"&RFROMb&RTOb& "]-V(" & TO & ")";

IsEmpty ( RTOd )=1;"_V(" & FROM & ")-[" & AL & " " & CL & INC & "<"&RFROMa & RTOa & "][" & AL &" " & CL & INC & "<"&RFROMb & RTOb&"][" & AL &" " & CL & INC & "<"&RFROMc & RTOc& "]-V(" & TO & ")";

" _V(" & FROM & ")-[" & AL & " " & CL & INC & "<"&RFROMa & RTOa& "][" & AL &" " & CL & INC & "<"&RFROMb & RTOb & "][" & AL &" " & CL & INC & "<"&RFROMc & RTOc &"][" & AL &" " & CL & INC & "<"&RFROMd & RTOd &"]-V(" & TO & ")")

The result is fine except that there can be one special entry in either the To or From fields which does not get enclosed within parenthises. All values entered would normally be enclosed... thus "NYC" would become "(NYC) ", "JFK MIA CDG" would become "(JFK MIA CDG)" etc.

The exception would be one special allowed entry "???" which must be entered in the final string as "???" and not "(:)". But without affecting the entire rest of the string. There is one rule which makes this easier if the value "???" is entered in the field then it must be the only data in the field..... i.e. "???" is allowed but "??? NYC" is not.

So a string such as

_V(:-[DL QE<00050778][DL QE<10601270][DL QE<24755000][DL QE<70007999]-V(NYC MIA LAX)

Should read

_V???-[DL QE<00050778][DL QE<10601270][DL QE<24755000][DL QE<70007999]-V(NYC MIA LAX)

I guess the best way is to produce the string as at present and then remove the offending parenthises when they occur, but how do I get the "()" to change to "???" without affecting the rest of the string?? I tried the substitue function in FM but the instructions seem rather vague

Andy

[-Queue-]

First, I would clean up your Case statement to eliminate redundancies and clarify it a little.

Case(

not IsEmpty(FROM);

"_V(" & FROM & ")-[" & AL & " " & CL &

Case( not (IsEmpty(RFROMa) or IsEmpty(RTOa) or IsEmpty(INC));

INC & "<" & RFROMa & RTOa & "]" &

Case( not IsEmpty(RTOb);

"[" & AL & " " & CL & INC & "<" & RFROMb & RTOb & "]" &

Case( not IsEmpty(RTOc);

"[" & AL & " " & CL & INC & "<" & RFROMc & RTOc & "]" &

Case( not IsEmpty(RTOd);

"[" & AL & " " & CL & INC & "<" & RFROMd & RTOd & "]" ) ) ) ) &

"-V(" & TO & ")"

)

Then, all you need to do is Substitute( yourcalc; "()"; "???" ) as in

Substitute(

Case(

not IsEmpty(FROM);

"_V(" & FROM & ")-[" & AL & " " & CL &

Case( not (IsEmpty(RFROMa) or IsEmpty(RTOa) or IsEmpty(INC));

INC & "<" & RFROMa & RTOa & "]" &

Case( not IsEmpty(RTOb);

"[" & AL & " " & CL & INC & "<" & RFROMb & RTOb & "]" &

Case( not IsEmpty(RTOc);

"[" & AL & " " & CL & INC & "<" & RFROMc & RTOc & "]" &

Case( not IsEmpty(RTOd);

"[" & AL & " " & CL & INC & "<" & RFROMd & RTOd & "]" ) ) ) ) &

"-V(" & TO & ")"

); "(:)"; "???" )

[Chalkster]

Thanks... that works great... I appreciate the cleaning up of the case statement and now I understand how to use the substitute too:))

[Søren Dyhr]

"[" & AL & " " & CL & INC

This particular part could be made simpler by Let(

--sd

[-Queue-]

Yes, it could. I just wanted to keep it consistent and logical to Andy. Plus, since there's only concatenation involved, using Let wouldn't streamline it to the extent that a numerical calculation would be streamlined.