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.

  • You can create relationships 1:M between Countries and Accounts (billing and shipping relationships), this way you will be able to render relate fields of Countries into Accounts views.

    Any related field on record view is rendered as a dropdown field, so it certainly will fit your requirement.

    Regards

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 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

  • Are you getting any errors in your logs?

    It looks like you have an extra quote in your code at the abc string.

  • Sorry... No errors, the extra quote comes from a copy/paste error... I took out the blank option
    this was the original:

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

  • I assume you defined the field with the default dropdown as suggested here?

    "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. "

    Checked file permissions? QRR?

    ...sorry, trying to think of what may be different in your implementation.

  • Thank you very much for taking the time.

    It's a windows environment, file is read/write Utils-folder (which I had to create) is accessible by the webserver, QRR is done, no unusual log entries and no error (php and sugar)

    I don't get it to work

    image:
    top - Dropdown
    bottom - Multi select

    The function is located in the folder: custom/Extension/application/Ext/Utils/

    Vardefs:
    custom/Extension/modules/Contacts/Ext/Vardefs/<fieldnames>
    custom/modules/Contacts/Ext/Vardefs/vardefs.ext.php (after QRR)

    Just want let you know that (not wasting time anymore)... If anything else comes to your mind, maybe you let me know. I'll put it aside for a moment.

    Best
    Rene