Multiple Variables in a Formula

[Lanie]

Me again - working at home..
Okay, I've got another problem. I have a salesmans field that holds a salesmans_code of for example ETROIDL, and there are multiple names such as VGEORGE, JGIBSON, JFAIR etc.

Whenever it finds ETROIDL I need it converted to Emogene Troidl, the same for VGeorge to Vinnie George, etc.

Now i got it to work if I have a single statement in there which is:

if {ORDER_QUOTATION\.SALESMAN_CODE}="GIBSON" then "Joseph Gibson"

it returns: Joseph Gibson

But when I add the rest in I only get blanks.
if {ORDER_QUOTATION\.SALESMAN_CODE}="JMCARDLE" then "Jim McArdle";
if {ORDER_QUOTATION\.SALESMAN_CODE}="REPPERLY" then "Rolph Epperly";
if {ORDER_QUOTATION\.SALESMAN_CODE}="TDUKE" then "Tish Duke";
if {ORDER_QUOTATION\.SALESMAN_CODE}="DRATCLIFF" then "Darth Ratcliff";
if {ORDER_QUOTATION\.SALESMAN_CODE}="TYANDLE" then "Timothy Yandle";
if {ORDER_QUOTATION\.SALESMAN_CODE}="JFAIR" then "Jason Fair";
if {ORDER_QUOTATION\.SALESMAN_CODE}="MJACKSON" then "Max the Man"



What I'm doing wrong here? Thanks so much, Lanie
Oh, please respond to laniet@ij.net
Lanie
laniet@ij.net
etroidl@conaxfl.com

 

[Ngolem]

Hi Lanie

"
if {ORDER_QUOTATION\.SALESMAN_CODE}="GIBSON" then "Joseph Gibson"

it returns: Joseph Gibson

But when I add the rest in I only get blanks"

The problem is that you are presenting this field with a series of IF statements of which the last one is the only that is analyzed for the final result. The correct answer is masked because of it.

There are several ways to fix this formula

1. Assign the result to a variable then show the variable at the end

WhilePrintingRecords;
//this assignment catches the "new guy"
StringVar result := "Unknown Salescode";

if {ORDER_QUOTATION\.SALESMAN_CODE}="GIBSON" then
result := "Joseph Gibson";
if {ORDER_QUOTATION\.SALESMAN_CODE}="JMCARDLE" then
result := "Jim McArdle";

...(other cases added)...

if {ORDER_QUOTATION\.SALESMAN_CODE}="MJACKSON" then
result := "Max the Man";

result;

2. If you don't want the variable then use If-ElseIf

WhilePrintingRecords;

if {ORDER_QUOTATION\.SALESMAN_CODE}="GIBSON" then
"Joseph Gibson"
else if {ORDER_QUOTATION\.SALESMAN_CODE}="JMCARDLE" then
"Jim McArdle"

...(other cases added)...

else if {ORDER_QUOTATION\.SALESMAN_CODE}="MJACKSON" then
"Max the Man"
else
"Unknown Salescode :" + {ORDER_QUOTATION\.SALESMAN_CODE};

3. Use Select Case

WhilePrintingRecords;

Select {ORDER_QUOTATION\.SALESMAN_CODE}
Case "GIBSON" :
"Joseph Gibson"
Case "JMCARDLE" :
"Jim McArdle"

... (add other cases)...

Default :
"Unknown SalesCode:" + {ORDER_QUOTATION\.SALESMAN_CODE};

You could also use the select Case and assign the value to a variable as in option 1.

Take you pick...they all will work. I lean to Option 3 since it is easier to maintain.

Hope this helps.





JimBroadbent@Hotmail.com

Reward good advice with a star, it reinforces us and helps others find answers to their problems.

 

[Lanie]

Ngolem,
I sued your #3 Select Case that worked great! I don't have much experience writing formulas, but it looks like I'm going to start getting a workout with it and your help is much appreciated.
Lanie Lanie
laniet@ij.net
etroidl@conaxfl.com