Crystal Formula Clean-up 1

[jpstrzoch]

Greetings,

Long time Crystal Reports formula novice. I wrote the below formula, it works, but wondered if there was a better way to represent the variation that are shown below?

Your assistance would be greatly appreciated!

Thank you!


if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "01" and {@Position} = "02" to "23" and {@Level} = "A"
then "703-A1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "01" and {@Position} = "02" to "23" and {@Level} = "B"
then "703-B1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "01" and {@Position} = "02" to "23" and {@Level} = "C"
then "703-C1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "01" and {@Position} = "02" to "23" and {@Level} = "D"
then "703-D1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "01" and {@Position} = "02" to "23" and {@Level} = "E"
then "703-E1"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "03" to "04" and {@Level} = "A"
then "703-A1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "03" to "04" and {@Level} = "B"
then "703-B1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "03" to "04" and {@Level} = "C"
then "703-C1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "03" to "04" and {@Level} = "D"
then "703-D1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "03" to "04" and {@Level} = "E"
then "703-E1"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "05" to "26" and {@Level} = "A"
then "703-A2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "05" to "26" and {@Level} = "B"
then "703-B2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "05" to "26" and {@Level} = "C"
then "703-C2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "05" to "26" and {@Level} = "D"
then "703-D2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "02" and {@Position} = "05" to "26" and {@Level} = "E"
then "703-E2"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "03" to "04" and {@Level} = "A"
then "703-A1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "03" to "04" and {@Level} = "B"
then "703-B1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "03" to "04" and {@Level} = "C"
then "703-C1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "03" to "04" and {@Level} = "D"
then "703-D1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "03" to "04" and {@Level} = "E"
then "703-E1"

else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "05" to "26" and {@Level} = "A"
then "703-A2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "05" to "26" and {@Level} = "B"
then "703-B2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "05" to "26" and {@Level} = "C"
then "703-C2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "05" to "26" and {@Level} = "D"
then "703-D2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "03" and {@Position} = "05" to "26" and {@Level} = "E"
then "703-E2"

else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "01" to "02" and {@Level} = "A"
then "703-A1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "01" to "02" and {@Level} = "B"
then "703-B1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "01" to "02" and {@Level} = "C"
then "703-C1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "01" to "02" and {@Level} = "D"
then "703-D1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "01" to "02" and {@Level} = "E"
then "703-E1"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "03" to "04" and {@Level} = "A"
then "703-A2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "03" to "04" and {@Level} = "B"
then "703-B2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "03" to "04" and {@Level} = "C"
then "703-C2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "03" to "04" and {@Level} = "D"
then "703-D2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "03" to "04" and {@Level} = "E"
then "703-E2"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "05" to "10" and {@Level} = "A"
then "703-A3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "05" to "10" and {@Level} = "B"
then "703-B3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "05" to "10" and {@Level} = "C"
then "703-C3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "05" to "10" and {@Level} = "D"
then "703-D3"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "11" to "24" and {@Level} = "A"
then "703-A4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "11" to "24" and {@Level} = "B"
then "703-B4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "11" to "24" and {@Level} = "C"
then "703-C4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "11" to "24" and {@Level} = "D"
then "703-D4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "04" and {@Position} = "11" to "24" and {@Level} = "E"
then "703-E4"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "01" to "02" and {@Level} = "A"
then "703-A1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "01" to "02" and {@Level} = "B"
then "703-B1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "01" to "02" and {@Level} = "C"
then "703-C1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "01" to "02" and {@Level} = "D"
then "703-D1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "01" to "02" and {@Level} = "E"
then "703-E1"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "03" to "04" and {@Level} = "A"
then "703-A2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "03" to "04" and {@Level} = "B"
then "703-B2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "03" to "04" and {@Level} = "C"
then "703-C2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "03" to "04" and {@Level} = "D"
then "703-D2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "03" to "04" and {@Level} = "E"
then "703-E2"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "05" to "10" and {@Level} = "A"
then "703-A3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "05" to "10" and {@Level} = "B"
then "703-B3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "05" to "10" and {@Level} = "C"
then "703-C3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "05" to "10" and {@Level} = "D"
then "703-D3"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "11" to "24" and {@Level} = "A"
then "703-A4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "11" to "24" and {@Level} = "B"
then "703-B4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "11" to "24" and {@Level} = "C"
then "703-C4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "11" to "24" and {@Level} = "D"
then "703-D4"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "05" and {@Position} = "11" to "24" and {@Level} = "E"
then "703-E4"



else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "01" to "04" and {@Level} = "A"
then "703-A3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "01" to "04" and {@Level} = "B"
then "703-B3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "01" to "04" and {@Level} = "C"
then "703-C3"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "01" to "04" and {@Level} = "D"
then "703-D3"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "A"
then "703-A2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "B"
then "703-B2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "C"
then "703-C2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "D"
then "703-D2"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "E"
then "703-E2"


else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "A"
then "703-A1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "B"
then "703-B1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "C"
then "703-C1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "D"
then "703-D1"
else if {Sheet1_.wh_num} = "703" and {@Zone} = "RK" and {@Aisle} = "06" and {@Position} = "05" to "08" and {@Level} = "E"
then "703-E1"

else "BIN Type Unknown

 

[lbass]

You could return all but the final character by using a formula:

{Sheet1_.wh_num}+"-"+{@Level}

So what does the last character indicate? Is there another field that corresponds to it? The zone and aisle appear irrelevant to the formula result--without further information.

-LB

 

[jpstrzoch]

Greetings lbass,

Thank you for your response. I appreciate your time.

jpstrzoch

 

[jpstrzoch]

Greetings lbass,

I was hoping to return to this topic for just a moment. When I thanked you for your time, I knew that I had an additional question and yet didn't know how to propose it to you. My follow-up question is this. Directly below are two portions of the above formula. This formula will help me to assign a BIN TYPE assignment for a WMS, Warehouse Management System, for each of the location codes that are in the system. It will serve as my seed file for the new WMS I am installing. As you can see in the two conditions I am expressing below that I'm considering multiple ranges of Rack Location codes, and yet only one level of racking bay resulting in a common BIN TYPE result, listed here the BIN TYPE for an "A" level within the specified ranges is "703-A1".

How can I combine these two statements into a single line?

else if {Sheet1_.bin_num} in "RK0303A" to "RK0304E" and {@Level} = "A"
then "703-A1"

else if {Sheet1_.bin_num} in "RK0203A" to "RK0204E" and {@Level} = "A"
then "703-A1"

I want to do something like this but when I do so it indicates I require a boolean statement and I don't know how to do so.

if {Sheet1_.bin_num} in ("RK0303A" to "RK0304E") or ("RK0203A" to "RK0204E"), and {@Level} = "A"
then "703-A1"

Thank you in advance for your time and consideration!

jpstrzoch

 

[lbass]

if {Sheet1_.bin_num} in ["RK0303A" to "RK0304E","RK0203A" to "RK0204E"] and {@Level} = "A"
then "703-A1"

-LB

 

[jpstrzoch]

Thank you very much!