Formula Question

[nervous2]

I'm trying to create a report at lists all our stock products in BLue, but I'm having a hard time figuring out how to do this. I have managed to figure out how to list one of our products in blue by using

if {INLOC.Itemkey}="EB-103-S" then color(0, 0, 255) but I have about 300+ products to list so as of now the only way I know how to do more than one product is using a tedious listing suh as...


if {INLOC.Itemkey}="EB-103-S" then color(0, 0, 255) else if {INLOC.Itemkey}="EB-104-L" then color(0, 0, 255) else if {INLOC.Itemkey}="EB-109-L" then color(0, 0, 255)


Is this the easiest way to create this formula?? Isn't there a means of creating a formula such as

if {INLOC.Itemkey}="EB-103-S and "EB-104-L and "EB-109-L" then color(0, 0, 255), or somewhere along these parameters?

thanks

 

[danjausten]

Hi,

If the {INLOC.Itemkey} field is always has the same amount of characters and is the same format ie EB-(3 digit number)-(letter) then you can use one the like command to format the field.

Go to the format properties of the field, go to Border, tick background, select blue then in the formula editor type the following command

{INLOC.Itemkey} like "EB-???-?"

? stands for a single wildcard character

Alternatively, you could use in :

{INLOC.Itemkey} in ["EB-103-S,"EB-104-L","EB-109-L"]

But then you have to hard code all the items

Hope this helps,

Dan

 

[Katy44]

To avoid the long list of "if"s, you could use the switch statement:

switch({INLOC.Itemkey}="EB-103-S" or {INLOC.Itemkey} = "EB-104-L" or {INLOC.Itemkey}= "EB-109-L", crBlue, {INLOC.Itemkey}="some other code", crGreen, {INLOC.Itemkey}="some other code", crRed)

 

[nervous2]

thanks it worked but now I'm trying to make this a little less tedious

i have a list of about 10000 products that I'm trying to make a report on, They are in excel and can easily be cut and pasted to a new location, I am using crystal reports for the report and the report requires me to take these products and list them in a way that the formula can work.


as of now the products are listed in excel in a single row such as

product1
product2
product3
etc etc

for my formula to work i need it listed as

"product1","product2","product3" etc

Is there any way I can automate this process, this is a very tedious job that can have a large margin of error. Any suggestions would be greatly appreciated.

Thanks

 

[kchaudhry]

I am not sure if this process can be autmoated. A slightly different approach would be to use "not in" and then insert the values(this will only help if your "not in" values are less than "in" values.

Kchaudhry