AnsweredAssumed Answered

Creating List View with Custom SQL

Question asked by Jeff C on Jun 23, 2016

The Advanced Search functionality in Sugar (6.5) is sorely lacking.  You cannot do complex searches involving groups of conditions with OR, nor can you due multi-level sorts on more than one field.


I can write the SQL statements to get from the module's DB table the records I want.  Seems like there should be a simple/easy way to simply code a "Custom Saved Search" that just executes a custom SQL statement to grab and sort the module records you want to display then pass the results to the list view. 


Essentially if I could have it anyway I wanted, I would want to be able to create a Saved Search simply to specify the columns I want to display, then override the search logic stored in the saved search with my own SQL statement.


A super simple example would say to have a saved search for the Leads module that lists first name, last name, and status fields.  But i want the list to sort by status first then sort by last name so I have last name alphabetical within each status.  You can't do this in Sugar but I could write the SQL statement in a matter of seconds. Again this is just a SUPER simple example.  I actually want to do much more complicated SQL calls involving JOINS, etc, but still just returning fields from the {module} and {module}_cstm table. 


What's the best route?