Need help with multi-select field

[ltidquist]

This post refers back to thread766-1436069.

I'm using CR 9.0.

I have a field that is multi-select and stores the codes separated by a comma.

So, for example the field has the following:

301,302,303

There is a lookup table that stores the descriptions for those codes that I need to display in my report.

I can successfully display the description if the field only has one value but if it's got more than one it displays blank.

Here's what I did in my report (again refer to thread766-1436069)
Insert a subreport that uses the lookup table. Link it to the main report by using the multiselect field from the main report as the linking field. Then go into the subreport->report->selection formula->record and change it to read:

{lookup.code} = split({table.multiselectfield},",")

Then create two formulas in the subreport:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {lookup.description}+",";

//{@display} to be placed in the subreport footer:
whileprintingrecords;
stringvar x;
if len(x) > 1 then
left(x,len(x)-1)

Suppress all subreport sections but the report footer and place the sub in the detail section of the main report.


Any ideas?

 

[lbass]

Not sure, but try changing the selection formula to:

{lookup.code} in split({table.multiselectfield},",")

-LB

 

[ltidquist]

That didn't work.

If anyone has any other ideas, I'd sure appreciate it.

Thanks again.

 

[lbass]

I justed tested this method and it worked perfectly. Please explain where your subreport is looked in the main report.

-LB

 

[ltidquist]

It's in the Detail section. Can I send you the report?

Thanks.

 

[lbass]

No, we have to stay within the forum. I just noticed something. After linking, the subreport record selection formula should look like:

{lookup.code} in split({?pm-table.multiselectfield},",")

...In other words, it should be the linking field that appears in the formula, not the field itself. Please show the exact formula you used.

-LB

 

[ltidquist]

Thanks LB for all your help.

Here is the record selection formula in my subreport:

{Gen_Tables.CODE} in split({SURVEY_INFO_WEB.Apparel},",")
and
{SURVEY_INFO_WEB.ID} = {?Pm-Name_Company.ID}

 

[lbass]

It should be:

{Gen_Tables.CODE} in split({?Pm-SURVEY_INFO_WEB.Apparel},",")
and
{SURVEY_INFO_WEB.ID} = {?Pm-Name_Company.ID}

Check the linking screen to make sure the apparel field is passed as a link from the main report, and then make sure the formula reads as above.

-LB

 

[ltidquist]

Well, now I get a 'Failed to open a rowset' error followed by 'Query engine error: An invalid range operator has been enountered' error, so I've obviously got something else wrong now.

I added the apparel field as a link to the subreport from the main report and that's when I started getting this error.

 

[lbass]

I'm not sure what that error signifies. Please show the revised selection formula.

Also, please verify that your field {SURVEY_INFO_WEB.Apparel} always displays as you said, like the following:

301,302,303

...and that this is an actual database field. If it is instead based on a formula, show the contents of the formula.

-LB

 

[ltidquist]

{Gen_Tables.CODE} in split({?Pm-SURVEY_INFO_WEB.Apparel},",")
and
{SURVEY_INFO_WEB.ID} = {?Pm-Name_Company.ID}

The {SURVEY_INFO_WEB.Apparel} field is a true database field in a SQL database. Not all records have data in them. Some could only have one code and others will have multiple codes.

If you don't want to deal with this anymore just let me know and I'll find some other way around this.

Thanks for all your time and help.

 

[lbass]

In the main report, create a formula {@yourapparelformula}:

if isnull({SURVEY_INFO_WEB.Apparel}) then
"" else
{SURVEY_INFO_WEB.Apparel}

Then use this formula as your linking field, and change the sub selection formula to:

{Gen_Tables.CODE} in split({?Pm-@yourapparelformula},",")
and
{SURVEY_INFO_WEB.ID} = {?Pm-Name_Company.ID}

Not sure this will help, but it's worth a try.

-LB

 

[ltidquist]

No luck. Still get the same error.

Thanks so much for your help but it doesn't look like this will work for me.

I'll start looking for another solution.

Thanks again.