Extracting Country # from Comma Delimited Field 1

[mwake]

I am creating a report using Crystal 2008 from an Oracle db. When a user plans an application and adds multiple countries to the application, it is captured in one field, seperated by commas (ex. 21,36,45,86,110). These numbers are country codes that I need to link to a country table to get country names. How do I extract the country codes so that I can use them to determine what countries the application is for?? Is there a function in Crystal that will extract each value??

 

[lbass]

If the countries field is a string, then you could insert a subreport that uses this field as a subreport linking field (to any string in the country table). Then in the subreport, go into report->selection formula and change the formula to:

{country.countrycode} in {?pm-application.countrycodes}

Then display the country name in the detail section and check "select distinct" in the database dropdown or group on the country field and suppress all other subreport sections. This would have to be adjusted in the country codes could be nested within each other (e.g., 11 in 110). In that case, in the main report, create a formula like this:

//{@convertcode}:
stringvar x := {application.countrycodes};
stringvar array y := split(x,",");
numbervar i;
numbervar j := ubound ;
stringvar z;
for i := 1 to j do(
z := z + totext(val(y),"000")+"," //assuming a max of 3 digits in the country table
);
if len(z)>1 then
left(z,len(z)-1)

Link on this and then in the sub, change the record selection formula to:

totext(val({country.countrycode}),"000") in {?pm-@convertcode}

-LB

 

[mwake]

Unfortunately, the countries are in another table and the field is not a string like the field I'm trying to link to. The country table has a unique country id field and a country name field. So somehow I need to be able to extract individual country id's from the "selected countries" field (12, 24, 105, etc) and link to a table with unique country id's (Country_ID=12, Country_Name=USA).

 

[lbass]

That's exactly what I understood you to mean. Did you try this? CAn you explain what you are finding confusing?

-LB

 

[mwake]

I'm a little confused. You want me to create a subreport from the country table that contains country id and country name. Then I create a parameter called pm-application.countrycodes, then add {country.country_id} in {?pm-applications.countrycodes}to the select statement of the subreport. In the main report, I create a formula called @convertcode which equals:
//{@convertcode}:
stringvar x := {application.countrycodes};
stringvar array y := split(x,",");
numbervar i;
numbervar j := ubound ;
stringvar z;
for i := 1 to j do(
z := z + totext(val(y),"000")+"," //assuming a max of 3 digits in the country table
);
if len(z)>1 then
left(z,len(z)-1)

I then add the subreport to country grouping in the main report, linking @convertcode to country.country_id and add

totext(val({country.countrycode}),"000") in {?pm-@convertcode}

to the select statement in the main report??

 

[lbass]

I showed two different ways of approaching this. Please ignore the first, and just create the {@convertcode} formula in the main report. Substitute your actual main report field for the string of country codes that I called {application.countrycodes} in the formula.

Then insert a subreport that uses the country table where you place the country name in the detail section and group on it. Then suppress the detail and other sections. Link the subreport to the main report in the subreport linking screen by moving over {@convertcode} as the linking field for the main report. Ignore the field shown for the subreport. Then go into the subreport->report->selection formula->record and change the formula to:

totext(val({country.countrycode}),"000") in {?pm-@convertcode}

Substitute your actual field name for {country.countrycode}.

You aren't directly creating a parameter.

-LB

 

[mwake]

I'll give it a try and let you know if it works. Thanks for your patience...

 

[mwake]

Thanks LB!! Your solution worked!!

 

[mwake]

Although my list of countries is being generated, I now have another related problem. The countries are being selected by using country groups. A seperate record is created for each group, including a group id, with the selected countries from that group included in the "Selected Countries" field. I want to generate a single list of countries from the multiple records, which is happening now, but I need to append the country group id to the respective country names. How do I pass the group id's to the subreport in order to assign the correct country group id to the country name??

 

[lbass]

Just add the country code from the subreport to the detail or group section--wherever you are displaying the country. Since you are linking on the code, it should be the same in the sub as in the main report.

-LB