Converting multiple codes to descriptions (Crystal XI) 1

[ClareR]

Hi all

I need help with a (probably simple) formula that will convert a field code to display a description.

The field holds codes like ,1, ; ,2, ; ,3, etc up to ,230,

I need to translate the code to show a description, which is easy if the field equals a specific code. The problem I am having is the field might have more than one code contained within it and I need to display ALL the descriptions.

I really hope someone can help

Many thanks

Clare

 

[lbass]

Does your field result look exactly like that with all of those commas and semicolons? Or are you leaving out something?

How do you want the results to look? Do you essentially want to replace all numbers in the row with the corresponding description? Or are you summarizing this in a group section?

Do you have a lookup table that provides the descriptions corresponding to the codes?

-LB

 

[ClareR]

Hi LB

A sample of my codes are (which include the commas):

,7,
,8,
,9,
,172,
,121,
,142,
,17,

the corresponding descriptive values are:

Coins
Collectables
Computing
Confectionary
Construction
Consumer
Cookware

The field may include all, some or none of the codes.
I need to display in the details area the descriptive values associated to each code

Eg
field contains ,9, and ,172, and ,121,
needs to display Computing, Confectionary, Construction

I dont have any lookup tables set up in Crystal but would be interested to know how these would help.

Many thanks

Clare

 

[lbass]

So how EXACTLY does that field display? Like:

,9,172,121,

Or what?

-LB

 

[ClareR]

The field displays as you have it

,9,172,121,

C

 

[dgillz]

Is this not already stored in a database somewhere? If not, is the way you can store this in a database?

Failing both of the above, you can do an if-then-else or case statement.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

Create a formula like this:

whileprintingrecords;
stringvar array x := split({{table.string},",");
numbervar i := 0;
numbervar j := ubound(x);
stringvar display := "";

for i := 2 to j-1 do(
display := display + (
select x
case '9' : "Computing" //add in all possible cases here
case '121' : "Construction"
case '172' : "Confectionary"
default : "Other"
)
+", "
);
if len(display)> 2 then
left(display,len(display)-2)

In this case, if you had an existing table you could generate the text of this formula quickly in another report, export it to Word and then copy it into the formula, instead of manually adding in all the cases.

-LB

 

[ClareR]

Hi LB

Thanks for this - it worked perfectly!!

Further to the above, you mentioned about creating a table or lookup table, is this something straightforward to do in Crystal or do you have to be an expert user?

Thanks again

Clare

 

[Turkbear]

Hi,
Lookup tables are built in the database not in Crystal..Ask your DBA, if you do not have sufficient rights to create tables, to create one. With that very odd data however, lbass's method will probably be more effective and more flexible.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could probably create a table in Excel or Access, use it as a datasource for a report that then generates the cases necessary for the CR formula. In a new report you would select your new table as the datasource. Then add a text box to the detail section, and in the text box, type:

case '{table.code}' : '{table.description}'

Substitute your actual table.field names. Then run the report and then export it to Word. Then copy the results into the case part of your formula.

-LB

 

[ClareR]

Hi LB

Thanks for the above formula, its working brilliantly and I have used it in several areas

What would I need add this into the formula to display "None" if the field {Company.comp_campaign} isnull? I have tried several things but none seem to work.

Many thanks in anticipation for your valued help.

Clare

 

[lbass]

Is it the code field that is null or the description field?

-LB

 

[ClareR]

Hi LB

Its the code field that is null I think.

using the examples from the formula above

case '121' : "Construction"
case '172' : "Confectionary"

121 is the code/fieldID and "Construction" is the description/translation of the code.
However there are some records where there is no entry in {Company.comp_campaign} this is my problem.


Many thanks

 

[lbass]

So {Company.comp_campaign} is the code field?

Try creating a second formula:

if isnull({Company.comp_campaign}) then "None" else
{@originalformula}

-LB

 

[ClareR]

Hi LB

Thanks for this. I have added but its being rejected saying 'the ) is missing'.

The original formula is in the change group options 'use a formula as a group name' part of the report.

So my formula looks like this (as you suggested)


stringvar array x := split({Company.comp_campaign},",");
numbervar i := 0;
numbervar j := ubound(x);
stringvar display := "";

for i := 2 to j-1 do(
display := display + (
select x

case '9' : "Computing" //i added in all the possible cases here
case '121' : "Construction"
case '172' : "Confectionary"
default : "Other"
)
+", "
);
if len(display)> 2 then
left(display,len(display)-2)

where/how do i put in the 2nd formula.

I'm so sorry to be so utterly dense :|

C

 

[lbass]

Use the name of the formula you just posted in my most recent formula suggestion and then use THAT formula in the customize group name->use a formula->x+2 area.

-LB

 

[ClareR]

Hi LB

I'm really struggling with this.
I cannot find that the formula for the 'change group name' or 'display string' has any particular name eg {@formulaname} no matter where I look.
It doesnt matter what i put in i keep getting 'the ) is missing' error.

Do i need to change the drop down at the top of the formula workshop screen to 'Exception for Nulls', would this make a difference?

I really appreciate your help

C

 

[lbass]

Okay, I see. Copy the original formula by going into the customize name formula area and then go into the field explorer->formula->new->give it a name and paste it there. Then it will be available in the customize name area.

Also paste the formula that is giving you the error into this thread. You should be able to see what's missing--for every "(" there must be a corresponding ")".

-LB

 

[ClareR]

Hiya LB

Thanks for the above tip about creating a new formula - that has worked to enable me to add the if isnull part. Below is the formula in its entirety (sorry its so long!)

It is erroring at the new if isnull part, but there does appear to be a "(" for every ")"

whileprintingrecords;
stringvar array x := split({Company.comp_campaign},",");
numbervar i := 0;
numbervar j := ubound(x);
stringvar display := "";

for i := 2 to j-1 do(
display := display + (
select x

case '1' : "Top 1000"
case '100' : "Kitchen Bathroom"
case '101' : "Leisure"
case '102' : "Motor"
case '103' : "Pharmaceutical"
case '104' : "Publishing"
case '105' : "Retail"
case '106' : "Shipping"
case '107' : "Toys"
case '108' : "Baby"
case '109' : "Tools"
case '110' : "Transport"
case '111' : "Music Industry"
case '112' : "Mail Order"
case '113' : "Digital"
case '114' : "Technology"
case '115' : "Wholesale / Distribution"
case '116' : "Manufacturing"
case '117' : "Government"
case '118' : "Computing"
case '119' : "Telecoms"
case '120' : "Textiles"
case '121' : "Advertising"
case '122' : "Recruitment"
case '123' : "Insurance"
case '124' : "Top 100"
case '125' : "ecommerce"
case '126' : "Mailer 1"
case '127' : "Mailer 2"
case '13' : "Banking"
case '130' : "Gambling/Bookmakers/Betting"
case '131' : "Software"
case '132' : "Legal Services"
case '133' : "Clothes, Shoes, Accessories"
case '134' : "Business"
case '135' : "Art"
case '136' : "Photography"
case '137' : "Phones / Mobiles"
case '138' : "Powerseller"
case '139' : "Spode"
case '140' : "Gypsum"
case '141' : "BHA"
case '142' : "Aerospace"
case '143' : "Chemicals"
case '144' : "Glass"
case '145' : "Home Shopping"
case '146' : "Medical"
case '147' : "Packaging"
case '148' : "Paper"
case '149' : "Rubber Plastic"
case '150' : "Stationery"
case '151' : "Steel"
case '152' : "Tobacco"
case '153' : "Cosmetics"
case '154' : "Books, Comics, Magazines"
case '155' : "Cars, Parts Vehicles"
case '156' : "Coins"
case '157' : "Collectables"
case '158' : "Crafts"
case '159' : "Timber Wood"
case '160' : "Air Conditioning"
case '161' : "ATM's"
case '162' : "Gardening"
case '163' : "Furniture Furnishings"
case '164' : "Cookware"
case '165' : "Office Equipment"
case '166' : "Isowool"
case '167' : "Optical"
case '168' : "Brewers"
case '169' : "Fragrance"
case '17' : "Agency"
case '170' : "Luggage, Baggage"
case '171' : "Fuel"
case '172' : "Accountancy"
case '173' : "Airlines"
case '174' : "Vehicle Hire/Leasing"
case '175' : "Hotels"
case '176' : "Property/Real Estate"
case '177' : "Restaurants"
case '178' : "Utilities"
case '179' : "Internet Providers/Software"
case '18' : "Care Homes"
case '180' : "Charities, Trade Associations Pressure Groups"
case '181' : "Postal / Courier Services"
case '182' : "Farming"
case '183' : "Security"
case '184' : "Mining"
case '185' : "Exhibitions/Conferences"
case '186' : "Marketing"
case '187' : "Architectural Services"
case '188' : "Printing"
case '189' : "TXT//AD"
case '190' : "Erotica"
case '191' : "Surveyors"
case '192' : "Tea / Coffee"
case '197' : "Corporate/B2B"
case '198' : "Consumer"
case '200' : "Musical Instruments"
case '2004' : "2004"
case '2005' : "2005"
case '2006' : "2006"
case '2007' : "2007"
case '201' : "PR Week Black Book"
case '202' : "Outdoor"
case '203' : "White goods"
case '204' : "Home Brands"
case '205' : "Bristol"
case '206' : "PR"
case '207' : "Audio Books"
case '21' : "CorpData-1 (Team)"
case '22' : "Data Protection"
case '26' : "EM Travel Jul06"
case '27' : "ES001 (Team)"
case '28' : "Finance"
case '29' : "GAB001 (Team)"
case '30' : "General Insurance"
case '34' : "Investment"
case '35' : "IT"
case '4' : "Top 75"
case '40' : "Leics Jul 06"
case '41' : "Leics May 06"
case '42' : "Priority 1"
case '43' : "Priority 2"
case '44' : "Priority 3"
case '45' : "Media"
case '47' : "Mortgages"
case '55' : "Original Campaign"
case '56' : "Other"
case '58' : "Outside Top 1000"
case '61' : "QBS-1 (Team)"
case '65' : "Regional"
case '66' : "Retail Priority 2 May06"
case '67' : "Trade"
case '68' : "Travel Tourism"
case '7' : "6300 Campaign1"
case '70' : "VC Campaign 1"
case '8' : "6300 Campaign2"
case '82' : "Whitewater Jun06"
case '83' : "World Cup 2006"
case '84' : "Yorks Jul 06"
case '85' : "Yorks May 06"
case '86' : "Alcoholic Drinks"
case '87' : "Electronic Goods"
case '88' : "Energy"
case '89' : "FMCG"
case '9' : "6300 Campaign3"
case '90' : "Food Drink"
case '91' : "Services"
case '92' : "Beauty"
case '93' : "Confectionary"
case '94' : "Construction"
case '95' : "DIY"
case '96' : "Engineering"
case '97' : "Fashion"
case '98' : "Health"
case '99' : "Household/Homeware"
case 'Apr' : "Apr"
case 'Aug' : "Aug"
case 'BusBreak' : "Business Breakfast"
case 'CarlC' : "Carl Courtney"
case 'CD' : "CD"
case 'Competitor' : "Competitor Agency"
case 'Dec' : "Dec"
case 'DuncanG' : "Duncan Gallagher"
case 'DVD' : "DVD"
case 'EmmaW' : "Emma Wright"
case 'Feb' : "Feb"
case 'FlinkAgency' : "# GSA 01-07(FL)"
case 'HelenP' : "Helen Pattison"
case 'Jan' : "Jan"
case 'Jul' : "Jul"
case 'Jun' : "Jun"
case 'June05GeoEnviron' : "Geo Environ"
case 'Letter' : "Letter"
case 'Mar' : "Mar"
case 'May' : "May"
case 'Nov' : "Nov"
case 'Oct' : "Oct"
case 'Oil&GasMar06' : "Oil Gas"
case 'PrivateSchools' : "Private Schools"
case 'Sep' : "Sep"
case 'Sept05EmpAg' : "Employment Agency"
case 'Think' : "Think"
case 'TomH' : "Tom Hargreaves"
case 'TravelShow' : "Travel Show"
if isnull ({Company.comp_campaign}) then "None"
else {@CampaignFormula}

default : "Other"
)
+", "
);
if len(display)> 2 then
left(display,len(display)-2)


=================================
Thanks for your continued help LB

C

 

[lbass]

What is the content of {@CampaignFormula}? What is the name of the formula in your last post and where are you trying to put it?

-LB