If i want to fetch data from one database table and display that data in my custom module using complex sql queries then in which file should i add sql query and how??
and also how can i make dropdown with values fetching from another database table using sql queries??
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 <
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