william hindle asked 9 years ago
Using formatoptions in the example from integrations/autocomplete.php
$col["formatoptions"] = array( "sql"=>"SELECT name as k, name as v FROM clients","search_on"=>"name", "update_field" => "name");
I have a similar table but many of the entries are for the same name (and more for quite similar names) so when I want to add a new item and start entering a name I get a long list which includes loads of duplicates. That's visually poor and when there's more than one match it can get difficult to find the right one as it may be way down the list, maybe off-screen
I tried "SELECT name as k, name as v FROM clients GROUP BY name" which seems to do what I want when I try a SQL statement using phpmyadmin but it errors when I use it in the formatoptions context.
Abu Ghufran answered 9 years ago
This is an issue in library and autocomplete query does not currently support group by clause.
For alternate approach, you can use distinct. e.g.
SELECT distinct name as k, name as v FROM clients
