You are viewing limited content. For full access, please sign in.

Question

Question

Workflow Custom Query with "In List" Parameter

Workflow Laserfiche Version 10
Updated August 7, 2020
asked on August 6, 2020 • Show version history

Hello all,

If you want to build a Custom Query in Workflow that takes a list of values as a parameter, what would the syntax of the parameter be?  For example, I want to achieve something like this

 


select SomeValue from SomeTable
where Color in ('Red','Green','Blue')
 

with a custom query like this


select SomeValue from SomeTable
where Color in (?)

 

with a param value like this

'Red','Green','Blue'

 

Can it be done?

 

0 0

Answer

SELECTED ANSWER
replied on August 7, 2020

You should give it a try, but I think that would treat the colours as a single string, so that would probably convert it to " where Color in (" 'Red','Green','Blue' ") ".

You would probably have to do " where Color in (?,?,?) " with each colour as a parameter.

1 0
replied on August 7, 2020 • Show version history

Thanks for your reply.  Yeah, I think you're right that setting each "in list" value as a separate parameter would be the way to go.  I'll try that.

I also tried setting the entire query text as a token with the param list (i.e. 1, 2, 3, 4, 5) inserted, then put the token into the Custom Query instead of the actual query text which actually worked, though you can't test your query that way in the designer and it would probably disallow referencing the query result columns further down in the workflow.  Anyway, thanks again.

1 0
replied on August 7, 2020

The queries are parametrized to protect against SQL injection, so you can't have an arbitrarily long list of values as a single parameter.

You can either set them up as separate parameters if they're always a fixed number. Or you can build the entire query outside of this activity as a token and use just that token in the Custom Query box.

1 0

Replies

You are not allowed to reply in this post.
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...