Hi, I have custom SQL that looks like this:
select * from Table t1 where t1.Name in ({Invoices})
{Invoices} is a text parameter that I want users to enter. (Like a search.)
So, they have to enter: ‘INV-001’, ‘INV-002’, ‘INV-003’
It’s not so easy to generate this list in Excel for regular users, so I was wondering if there’s a way for them to copy paste from Excel or at least add values without quotes, e.g. INV-001, INV-002, INV-003 (EM Server)
Hi @raskarov,
It seems that there is no way to copy-paste values from an Excel column to a “Text or Number” parameter since it doesn’t support line breaks.
As for wrapping values in quotes, if you can be sure that your values don’t contain commas and quotes, you can try to use a calculated parameter with the following expression:
"'" & replace({Invoices}, ", ", "', '") & "'"
But with this expression, you have to be very consistent with the space characters between commas and values. The number of space characters should always be the same and match the number of space characters in the second argument of the replace() function. You can add a validation rule to the initial parameter that will check that values have the expected number of spaces between commas and adjacent values. Something like this:
Or you can try to remove spaces between commas and values with a sequence of replace() functions.
Here is an example project: WrapParameterValuesInQuotes.morph (2.2 KB)
@raskarov, you have mentioned a multiple choice parameter in the subject of this topic.
Is it possible for you to use the multiple choice parameter with a predefined list of values and make your users choose values from that list?
If so - you can use the same expression as mentioned above, but you’ll not have to worry about spaces between commas and values.
