Prompt + Two column custom LOV + Multi option = Parse failed

[JohnClark]

I have set up a custom list of values (LOV) that consists of two columns, Unit and Area. The purpose is to show the Area with it's related Unit to make it easier for the users to select the appropriate Area.

I have created a prompt to use this custom LOV in. The prompt work perfectly with the following syntax:

@Prompt('Select an Area by Unit:' ,'A', 'List of Values\Area_by_Unit', mono, constrained, not_persistent)

The users want to be able to select more than one Area so I changed the prompt syntax to:

@Prompt('Select an Area by Unit:' ,'A', 'List of Values\Area_by_Unit', multi, constrained, not_persistent)

Now it fails with the following message:

Parse failed: Exception: DBD, Microsoft SQL Server Native Client 10.0 SQL Server Statement(s) could not be prepared.State: 42000

I am assuming that the two column list of values will not work together with the multi option.

Has any one else encountered this?

Did you come up with a solution?

Is there some other way that I can accomplish this? Neither field is a primary key so I can't use the Primary Key option in the Prompt.

[Former Member]

I would suggest you to customized your LOV.

Create a new object in th eunivese. lets say Area.

Go to object property.

Second tab. go to customized LOV.

Click on SQL. Here in the SQL add one more object, unit, in the object pane. Save and run it.

Now click on Show list of values. You can see both Area and Unit in the list.

Now use this object in your prompt.

Hope this will hep you out.

[JohnClark]

I did that already. That isn't the problem. The user wants to select more than one combination of Unit and Area.

[MariannevL]

Hi,

I suspect the error is in the remaining part of the SQL that makes up the condition, not the @prompt() syntax.

if it said table.column = @prompt(....)

you need to change it to table.column in @prompt(....)

In some databases you can put extra brackets around the @prompt(), like

table.column in (@prompt(....))

In some this doesn't parse, try out for yourself which one works...

Hope this helps, if it does't, post the rest of your SQL here too.

Good luck,

Marianne

[JohnClark]

OK, how embarrassing. Thanks for pointing out the obvious. I knew that of course, just didn't catch it.