Create dropdown from module records

Hi all,

Has anyone done this or is it at all passible. We have created a custom countries module which has records of countries  and other data required by our business. We would like to create a dropdown field in the accounts module  put those dropdown options being the records in the country module.

I have read this article https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_9.1/Cookbook/Changing_Address_Country_F…  but this wasn't really what we required, I know I can create a countries drop down in studio but one that uses our countries module would be amazing.

Parents
  • 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.

  • 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

Reply
  • 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

Children