UX List Control based on a SQL Select statement using Like with an Argument

[Davidk]

I've been battling this for some time.

I have a UX, not Mobile, with a List Control based on a MySQL Table. This List is displaying Images. The table supplies the image path and name. All that's great.

My UX is running in a Tabbed UI. I never need to see the full List of images. Always a filtered list. And, in fact, the List is hidden on the UX until it's needed.

The filter is a single word typed in by the user and then a search button is clicked. Then, the List is displayed and shows a filtered set of images.

The filter is going against a mediumtext field... lots of text in there... and so it needs to be a "Like" Where clause.

I'm trying to get the List Control to not render anything until I ask for it. This is because the table has about 2000 records and that takes about 15 seconds to display all those images.
I need to filter this server-side.

I think the best way is to use an argument in the List Select statement. I've tried this... but it displays everything in the List. In the SQL Builder it works.

SELECT ImageId, ImageName,  Concat('Images/', ImageName)  AS ImagePathName, ImageCategory, annotation 
FROM tbltcvimages 
WHERE annotation LIKE  CONCAT('%', :argImageAnnot, '%')  
ORDER BY ImageName

If I do this... all is good.... both SQL and the List work. But that's doesn't really help much.

WHERE annotation LIKE  '%heart%'

but doing the following doesn't work...

WHERE annotation LIKE  :argImageAnnot

and this doesn't work...

WHERE annotation LIKE  '%:argImageAnnot%'

Any thoughts? Thanks.

[coleresources]

Re: UX List Control based on a SQL Select statement using Like with an Argument

How are you binding the value to the argument?

[Davidk]

Re: UX List Control based on a SQL Select statement using Like with an Argument

Through the List Property, Arguments, on the Data Source tab.

[Michael1954]

Re: UX List Control based on a SQL Select statement using Like with an Argument

David

Have you tried building the value before setting the Argument? Say have a calc field that builds the 'concat' %heart% and the set that.

Michael

[Davidk]

Re: UX List Control based on a SQL Select statement using Like with an Argument

Michael,

That's actually where I'm pulling the argument value from... kind of. I've got an unbound textbox on screen and set the value of the textbox to the value of my search. I do this because with all that's going on my search field gets cleared.

Originally I had tried formatting the argument dialog as %{searchAnnotation}%. No errors but it didn't work.

So I tried your suggestion and just tried typing into my argument field %heart%. Still didn't work. But that got me thinking. What if it's not the argument at all. What if it's our old nemesis... timing.

I put a button on the UX to refresh the list data. I'm doing this with Action Javascript... but there's a little bit going on so...

The button, with the argument field set to %heart% works.

I've wrapped the list refresh in a timeout and all is good.

So... thanks guys for having me look at this another way.