In which file can i add custom sql complex queries to fetch data from another database table and how?

Are you trying to offer a read-only view into another system's data?

I would consider a Dashlet in an Intelligence Pane for that, especially if the query to retrieve the data will need parameters from one of your Sugar records.

For example, we have Contacts in Sugar but Licenses elsewhere. I have a License Dashlet in the Intelligence Pane on Contacts that queries the License system and displays that Contact's Licenses.

There is an example of a dashlet with external data written by Olivier Nepomiachty here:

Write a dashlet that uses an external data source: the Football World Cup dashlet 

You will need to adapt it to pass the parameters from the Contact to your query to build the data to be displayed.

I can't find examples of this right now but what you want is a contextual dashlet.

Hope this helps,
FrancescaS

OMG Francesca, you dug up that old post from July 2014  ^_^

The code still works and the approach is still accurate. 

/Oli

Hello Francesca Shiekh !!!

Thank you for your reply but let me explain through an example what i

exactly want..

Suppose i have a module let's say it as Patients and in the patients

module,i have further informations of patient including patient city and

near by hospitals.Now when i select patient's city from dropdown then in

the near by hospitals field,i want to display hospitals in that dropdown

fetched from another db table which contains all hospitals according to

cities.

On Thu, Jan 10, 2019 at 4:17 AM Olivier Nepomiachty <

I understand now! You want to populate dropdown options based on a parameter passed to a query.

I've done what you need a long time ago (when v7 first came out), and it wasn't easy or pretty... there may be better ways to do this now as well as cleaner code to be used (I was new to javascript at the time).

I'll give you an example. I have a custom Opportunity Products module. In that module I have a product_type_c enum field whose dropdown values are populated based on the Product's definition in a custom Opportunity Products Catalog module. The values for the product type are retrieved from the catalog using a custom API  oppp_Opportunity_Product_Catalog/getProductDetails/ to which the product is passed as a parameter

Note that if there are no options for that field for that particular product, then instead of presenting an empty field the field is simply hidden from view.

I extended the Enum field type for the Opportunity Products module to handle populating the product type based on product, this involves listening to the changes for product but also loading the right dropdown list when an existing record is open, which contains a given value in the product field: 

custom/modules/oppp_Opportunity_Products/clients/base/fields/enum/enum.js

({
  extendsFrom: 'EnumField',
  initialize: function(opts){
    this._super('initialize',[opts]);
    this.initEvents();
  },
  initEvents: function(){
      this.productDropdownFieldChange();
      this.enumDropdownFieldsChange();
  },
  productDropdownFieldChange: function() {
     //when product is changed look up the proper values for the dropdowns
     //from the Opportunity Products Catalog 
     if(this.name == 'product'){
        //trigger when product is loaded
        this.on('render', this.triggerEnumDropdownFieldsChange, this);
        //trigger when product changes 
        this.model.on('change:'+ this.name, this.triggerEnumDropdownFieldsChange,this);
     }
  },
  enumDropdownFieldsChange: function(){
     if(this.name  == 'product_type_c'){
           this.model.on('parent:module:change', function(ddOptions){
             var opts = {};
             switch( this.name){
               case 'product_type_c':
                 if(!(_.isUndefined(ddOptions['product_type_c_options'])) && !(_.isEmpty(ddOptions['product_type_c_options'])) && ddOptions['product_type_c_options']!={'':''}){
                   this.def.options = ddOptions['product_type_c_options'];
                   this.items = {};
                   this.render();
                 }else{
                   $('span[data-fieldname="product_type_c"]').hide();
                   $('.record-label[data-name="product_type_c"]').hide();
                 }
                 break;
             }
           }, this);
     }
  },
  triggerEnumDropdownFieldsChange: function(){
     var ddOptions = '',
         self = this,
         product = this.model.get('product'),
         url = app.api.buildURL('oppp_Opportunity_Product_Catalog/getProductDetails/'+ product);
     if(!_.isEmpty(product)){
       App.api.call('GET', url, '', {
         success: _.bind(function(ddOptions){
           //change dropdown options
           this.model.trigger('parent:module:change',ddOptions);
       }, this),
         error: _.bind(function(o) {
           console.log(o);
         }, this),
       });
     }
  },
})
                                                                     
       

In the API I populate the options for the product type based on the product which was passed as a parameter

<?php
class ProductDetailsApi extends SugarApi
{
  public function registerApiRest() {
    return array(
      'getProductDetails' => array(
        'reqType' => 'GET',
        'path' => array('oppp_Opportunity_Product_Catalog', 'product_details', '?'),
        'pathVars' => array('','','product'),
        'method' => 'getProductDetails',
        'shortHelp' => 'Lists what details to display in Opportunity Products for this product',
        'longHelp' => '',
      ),
    );
  }
  function getProductDetails($api, $args)
  {
    global $app_list_strings;
    $product = $args['product'];

    $sugarQuery = new SugarQuery();
    $sugarQuery->select(array('id'));
    $sugarQuery->from(BeanFactory::newBean('oppp_Opportunity_Product_Catalog'));
    $sugarQuery->where()->equals('name', $product);
    $opp_prod = $sugarQuery->execute();
    $pid=$opp_prod[0]['id']; //use the first one, should be unique

    $prod = BeanFactory::retrieveBean('oppp_Opportunity_Product_Catalog', $pid);
    $product_type_options = array();
    $product_type_options = unencodeMultienum($prod->product_type_c);
    foreach($app_list_strings['opp_product_type_dd'] as $key=>$value) {
       if (!(in_array($key, $product_type_options))){
          unset($app_list_strings['opp_product_type_dd'][$key]);
       }
    }
    $product_type_options = !empty($app_list_strings['opp_product_type_dd'])?$app_list_strings['opp_product_type_dd']:'';
    $result = array('product_type_c_options'=>$product_type_options);
    return($result);
  }
}

Please note that I actually return about a half a dozen other dropdown field options in these same scripts, I narrowed it down to product_type_c for simplicity but you should double check the syntax as I removed many lines of code.

Take this as a guideline, not a complete solution.

I hope this helps put you on the right track,

FrancescaS