Create dropdown from module records

Yes, you can create a dropdown content from a query on another module.

We wanted to build a dropdown of the inbound email queues for our Cases module.

*Please note that this code needs to be updated, I need to change it to using Prepared Statements. I have 10 years of customizations that I am slowly updating and have not gotten around to this one yet... But that is irrelevant to you here, just build your query properly. Use SugarQuery if you can.

Create your query so that it returns a list of the values for your dropdown

custom/Extension/application/Ext/Utils/inbound_email_queues.php

function getInboundEmailQueues(){
  global $db;
  $query = "SELECT id,name 
FROM inbound_email 
WHERE is_personal = 0 
AND mailbox_type not like 'bounce' AND status = 'Active' AND inbound_email.deleted = 0 ";
  $result = $db->query($query, false);
  $list = array();
  $list['']=''; //add a blank option if you wish to have one
  while (($row = $GLOBALS['db']->fetchByAssoc($result)) != null) {
      $list[$row['name']] = $row['name'];
  }
  return $list;
}

Whatever your query, make sure to return a keys=>values array

In Studio when you create the field as a dropdown create a default dropdown list, it can be anything you want, you are going to override it. 
 

Then in the the vardefs for the module where you are going to use it define the function that will retrieve the values.

In my case: 

custom/Extension/modules/Cases/Ext/Vardefs/vardefs.ext.php

unset($dictionary['Case']['fields']['inbound_queue_c']['options']);
$dictionary['Case']['fields']['inbound_queue_c']['function'] = 'getInboundEmailQueues';

Note that there are some caveats: as you probably know, when you set a value in Sugar from a dropdown it is stored in the Database, if you later remove that value from the dropdown the database will still have that value but it will not display. So you need to make sure that the records you use to generate your dropdown DO NOT get deleted, ever. Or you will have values in the DB that don't exist in the dropdown and will appear empty when they are not.

I mark values as legacy and avoid displaying those values as options in create view but I do use them in record and list view... that's a whole other story.

Hope this helps,
Francesca

PS. I wrote this 5 hours ago and didn't notice I had not submitted it... sorry for the delay.

Yes, you can create a dropdown content from a query on another module.

We wanted to build a dropdown of the inbound email queues for our Cases module.

*Please note that this code needs to be updated, I need to change it to using Prepared Statements. I have 10 years of customizations that I am slowly updating and have not gotten around to this one yet... But that is irrelevant to you here, just build your query properly. Use SugarQuery if you can.

Create your query so that it returns a list of the values for your dropdown

custom/Extension/application/Ext/Utils/inbound_email_queues.php

function getInboundEmailQueues(){
  global $db;
  $query = "SELECT id,name 
FROM inbound_email 
WHERE is_personal = 0 
AND mailbox_type not like 'bounce' AND status = 'Active' AND inbound_email.deleted = 0 ";
  $result = $db->query($query, false);
  $list = array();
  $list['']=''; //add a blank option if you wish to have one
  while (($row = $GLOBALS['db']->fetchByAssoc($result)) != null) {
      $list[$row['name']] = $row['name'];
  }
  return $list;
}

Whatever your query, make sure to return a keys=>values array

In Studio when you create the field as a dropdown create a default dropdown list, it can be anything you want, you are going to override it. 
 

Then in the the vardefs for the module where you are going to use it define the function that will retrieve the values.

In my case: 

custom/Extension/modules/Cases/Ext/Vardefs/vardefs.ext.php

unset($dictionary['Case']['fields']['inbound_queue_c']['options']);
$dictionary['Case']['fields']['inbound_queue_c']['function'] = 'getInboundEmailQueues';

Note that there are some caveats: as you probably know, when you set a value in Sugar from a dropdown it is stored in the Database, if you later remove that value from the dropdown the database will still have that value but it will not display. So you need to make sure that the records you use to generate your dropdown DO NOT get deleted, ever. Or you will have values in the DB that don't exist in the dropdown and will appear empty when they are not.

I mark values as legacy and avoid displaying those values as options in create view but I do use them in record and list view... that's a whole other story.

Hope this helps,
Francesca

PS. I wrote this 5 hours ago and didn't notice I had not submitted it... sorry for the delay.

Thanks Francesca Shiekh thats great will see about making something using that method, thank you also André Lopes , i was going to do your suggestion but being able to create a drop down from a sql statment gives me better scope to serve different lists to different modules form a master countries list

Hi Francesca,

an old thread, but quite topical for me right now... Maybe you do remember...

The only difference to your approach is that I want to have a mulitenum field.

I was looking for such a field in the Contacts module, with a value from related accounts. We have a level on top of the account which groups accounts related by a corporate number. I want to display this account numbers in that field. It should be possible to save the selected values.

I tried to implement your exact approach (dropdown/ enum, still without the multienum approach) in a v11 environment. For simplicity I created the function only with initializing an array:

...

$list = array(''abc' => '20001',
'bcd' => '20002',
'cde' => '20003',
'def' => '20004');

return $list;

...

The rest I did exactly as you suggested.

Unfortunately the field in the Record view doesn't show any dropdown selection. May I ask If you have more experience with this kind of customization?

Thank you very much for y quick look
Rene