CSV Parameter list with SQL 'in' as part of the where clause

[jfcox]

This ought to be simple but I'm missing something. I have a multiple, distinct parameter called "Type" that I am passing a comma separated list, for example;

interactive,selfpaced,home

I'd like to use something like this in a record selection formula:

{maintable.type} in {?Type}

If I pass only a single value in it works, If I pass more than one value it returns nothing. I have tried adding single or double quotes to the string but it doesn't help. How can I do this?

Thanks.

 

[lbass]

You say this is a multiple value parameter, so I'm assuming you can select multiple comma-delimited strings. The following seems to work, although there is probably a more elegant solution:

if ubound({?type}) = 1 then
{table.type} in {?type}[1] else

if ubound({?type}) = 2 then
(
{table.type} in {?type}[1] or
{table.type} in {?type}[2]
) else

if ubound({?type}) = 3 then
(
{table.type} in {?type}[1] or
{table.type} in {?type}[2] or
{table.type} in {?type}[3]
)

//add clauses up to the maximum number of options that can be selected.

I did try using variables so that the numbers didn't have to be hard coded, but was unable to get that approach to work. I would be interested if someone else can supply simpler code that does work here.

-LB

 

[jfcox]

Interestingly, that doesn't work for me completely;

If I go {table.type} in {?type}[1] that works,

if I go {table.type} in {?type}[2] that creates an error.

The value of that was passed in as Type was: interactive,home

So, although there are two (or more) items in my list, it is not seeing anything beyond the first. Do I have to parse this out somehow first?

Thanks

 

[lbass]

Did you use my exact formula above? You have to have the ubound clause. If you pass in a string:

interactive,home

...that is only one parm value, and this would get picked up by the first clauses in the formula:

if ubound({?type}) = 1 then
{table.type} in {?type}[1]

If you added two strings like:

interactive,home //{?type}[1]

selfpaced //{?type}[2]

...then these three options would get picked up through the second block of the record selection formula:

if ubound({?type}) = 2 then
(
{table.type} in {?type}[1] or
{table.type} in {?type}[2]
)

-LB