I hope this will help a lot of people who want to do something similar. I was (after a couple of days efforts) managed to extend the Sugar Advanced search feature on each module and implement a way for admins to enable the public searches:

Below is the feature list that I implemented:

1. Selected user roles can create Public searches.
2. Added a Public check box on advanced search panel.
3. I am using the 'description' field from the saved_searches table of sugarcrm. I didnt add a new fields since I wanted to make it upgrade safe completely.
4. Only selected roles can edit these public searches, other user won't see the Update and Delete buttons when they select these searches.

Following are the files that I copied over to custom directory and then changed to make it happen.

1. custom\include\SearchForm\tpls: this directory has the template files that I use for new Advanced search forms
2. custom\include\SearchForm\CustomSearchForm.php - from include\SearchForm\SearchForm.php
3. custom\include\SearchForm\CustomSearchForm2.php - from include\SearchForm\SearchForm2.php
4. custom\modules\SavedSearch\index.php - from modules\SavedSearch\index.php
5. custom\modules\SavedSearch\CustomSavedSearch.php - from modules\SavedSearch\SavedSearch.php
6. Added listViewPrepare(), prepareSearchForm() and processSearchForm() methods to the module's custom\modules\<module_name>\views\view.list.php, These methods you can find in include\MVC\View\views\view.list.php

I hope this would help the people who are stuck in implementing the same thing, in upgrade safe manner. After you do this, you can modify the SQLs the way you want them.

Comments?