IF THEN Formula

[Former Member]

I have a list of 17,000 customer codes which all belong to one of 20 or so representatives. I am building a report that would break down by salesperson, what shipped out the previous day. Problem is the tables contain no salesperson information.

What I need to do is create a formula based on the customer number, but I'm not sure how to do this.

Basically I need it to be something like....

IF {SHIPHIST.CUST_NUM} = 1,2,3,4,5,6,7,8,9,10 OR 11 THEN "Salesperson 1"

ELSE IF {SHIPHIST.CUST_NUM} = 12,13,14,15,16 OR 17 THEN "Salesperson 2"

ELSE "Salesperson 3"

Is this possible?

[Former Member · Accepted solution]

Put quotes around each of the numbers.

[Former Member · Accepted solution]

Here is the solution. Thanks for the help guys!

IF {arcust.custno} IN "386212,850128" THEN "Salesperson 1"

ELSE IF {arcust.custno} IN "207900,532408" THEN "Salesperson 2"

ELSE "Salesperson 3"

[Former Member · Accepted solution]

You need square brackets around a list of numbers like pandabear's example.

I can't see this working the way you were looking for it to work in the original post:

IF {arcust.custno} IN "386212,850128" THEN "Salesperson 1"

ELSE IF {arcust.custno} IN "207900,532408" THEN "Salesperson 2"

ELSE "Salesperson 3"

You can also use the Select..Case structure:

SELECT {SHIPHIST.CUST_NUM}

CASE 1,2,3,4,5,6,7,8,9,10,11 : "Salesperson 1"

CASE 12,13,14,15,16,17 : "Salesperson 2"

DEFAULT : "Salesperson 3"

I find this easier to follow for larger statements then the IF THEN ELSE IF...

[Former Member]

for some reason the square brackets don't show up when you type them. Panda did some kind of kung fu to make his post show up as quoting himself and his brackets became visible. I tried posting the formula with brackets 4 times before I gave up and just said "add quotes"

so his formula may have the brackets in it, but we can't see them.

[Former Member]

I think you have to add the code tags before and after the logic to get them to show up

"" without the quotes

[Former Member]

Try this

IF {SHIPHIST.CUST_NUM} in [1,2,3,4,5,6,7,8,9,10,11]THEN "Salesperson 1"

ELSE IF {SHIPHIST.CUST_NUM} in [12,13,14,15,16,17]THEN "Salesperson 2"

ELSE "Salesperson 3"

Regards,

Raghavendra

[Former Member]

IF {SHIPHIST.CUST_NUM} in [1,2,3,4,5,6,7,8,9,10,11]  THEN 
          "Salesperson 1"
ELSE IF {SHIPHIST.CUST_NUM} in [12,13,14,15,16,17] THEN 
          "Salesperson 2"
ELSE "Salesperson 3"

[Former Member]

IF {SHIPHIST.CUST_NUM} IN [10075,10081,10741,111058] THEN "Salesperson 1"

ELSE IF {SHIPHIST.CUST_NUM} IN [10160,119800,120281,150459] THEN "Salesperson 2"

ELSE "Salesperson 3"

I get the following error: A String is required here. (Highlighted error is [10075,10081,10741,111058])