Populate a Dropdown from the Database

For a recent project I needed an upgrade safe way to populate a dropdown from a database table in SugarCRM. In this case, the dropdown was a custom field that needed to contain a list of projects that where templates so that a user can create a new project based off of an existing project template. I’m going to make this short but sweet.

First the custom field definition via the vardefs extension (defined by the ‘vardefs’ array in a manifest.php if making an installable module):

Notice the function definition. This will be the next step.

(Following is defined in the ‘utils’ array in a manifest.php if making an installable module)

In the custom function an array is returned with the select option values as the key and the option labels as the value.

Note: if you manually add the files to the custom/Extension directory make sure to run a Repair/Rebuild for the changes to propagate through.

Note 2: For SugarCRM 6.3 and up only

about

I make high quality solutions at an alarming rate.

28 Responses to “Populate a Dropdown from the Database”

  1. Angel Magaña October 16, 2012 at 2:53 am # Reply

    Cool stuff! Thanks for sharing.

  2. Nick Chen April 14, 2013 at 9:28 pm # Reply

    It works. Thank you very much.

  3. Basant June 18, 2013 at 12:58 am # Reply

    Hi,

    We are trying to implement the solution provided by you but are not able to figure out where the file manifest.php and Utils directory are located and do we need to create a custom field from admin panel as well or the code itself will do the complete job , can you please descrbibe the end to end solution, we are using sugar crm ce 6.5

    thanks in advance

    Basant

    • jason June 18, 2013 at 7:19 pm # Reply

      A manifest.php is something used in an installable module. This is probably not the case for what you are doing. In this example, you could just create the file (and directory structure) for the custom util at /custom/Extension/application/Ext/Utils/getProjectTemplates.php.

  4. Cucuba February 1, 2014 at 8:56 am # Reply

    In Editview and Detailview, I can get a name properly,
    but Listview, item is displayed.

    How can i fix this?

  5. Adam Jakab May 14, 2014 at 10:37 am # Reply

    Hi Jason! All works! Question: obviously in list view mode when adding this field Sugar visualizes the value and not the label. How would one go about telling sugar to visualize the label?

    • Adam Jakab May 14, 2014 at 10:39 am # Reply

      Whoops, i just saw the question above – so, I need process_record logic hook – ok i’ll try that.

  6. Adam Jakab May 14, 2014 at 10:57 am # Reply

    It looks to me that defining and setting “static $projectTemplates = null;” your condition right after “if(!$projectTemplates){” would always evaluate to true.
    I’d to:
    if(!isset($projectTemplates)) {
    static $impAgentList = array();

    }

    • jason May 14, 2014 at 11:20 am # Reply

      It does look that way, but with it being a static variable it will only initialize it on the first call to the function. You can learn more about how that works here: http://www.php.net/manual/en/language.variables.scope.php

      • Adam Jakab May 14, 2014 at 12:11 pm # Reply

        Ok my db-populated dropdown works and process_record logic hook is in place and working fine in list view. However I’ve got a strange behaviour in Studio with this field. When I put it in list view in the Default(visible) columns and I put it as the last one, on save it jumps on top and become the first field. As a matter of fact wherever I put it it jusmps on the top of the list on save. Do you know what can be the reason?

        • jason May 14, 2014 at 12:32 pm # Reply

          Studio can be odd with the drag/drop piece. Try another browser or come back and try later. It takes a bit of getting used to to figure out how it expects the drag to be executed.

  7. sugarplus June 10, 2014 at 4:01 am # Reply

    Hi Jason,
    I have implemented your code, dropdown works fine, but other fields on my editview are missing. Can you plz help me in this?

    • jason June 10, 2014 at 9:33 am # Reply

      You probably have a bug then that is causing that. Check sugarcrm.log, check your PHP logs, etc. Turn on display_errors in your php.ini if you have to.

  8. Alex July 10, 2014 at 4:20 am # Reply

    Hello, this solution doesn’t work in the search filter of a dashlet. My dropdown-field just keeps empty (see also in the bugracker: https://web.sugarcrm.com/support/issues/423cf17f-4c78-2334-ab74-510b0d90a061).

    Any idea for a fix/workaround?

    • jason July 10, 2014 at 10:16 am # Reply

      Hello Alex,

      I haven’t seen that before and have no idea off-hand how to work around it. I’d advise working backwards throw the code to see where it loads the lists. Then compare to how it’s done otherwise to see why it’s not working.

      • Alex July 11, 2014 at 6:28 am # Reply

        Thank you for the reply. The dropdown list is loading fine if I add it to the editview. Do you know of any working example? I’m using 6.5.17 CE btw.

        What is also funny: What ‘m trying to archive is working just fine without any custom functions from other modules that are related to this one. (just type=relate in the vardef and type=enum in the dasheltviewdef -> all entries are getting listed as an enum for the search-filter)

  9. caim March 8, 2016 at 9:23 am # Reply

    Is there a way to enforce the repair and rebuild from other locations? >.select(array(‘id’,’name’));
    $sq->from(BeanFactory::getBean(‘DP_actco’));
    $result = $sq->execute();
    $GLOBALS[‘app_list_strings’][‘v_objectifs_list’] = array();
    foreach((array) $result as $row)
    {
    $GLOBALS[‘app_list_strings’][‘v_objectifs_list’][$row[‘id’]] = $row[‘name’] ;
    }

  10. montolla March 14, 2016 at 10:40 am # Reply

    Hello on sidecard, it does not show the select value on “detailview” but on database it was stored.
    It does not use the function to retrieve the list on the “detailview”.
    I tried with process_record and after_retrieve logic hooks but they didn’t work the field it draw empty always.

  11. Chris Lynch August 15, 2017 at 3:30 am # Reply

    Hi Jason,

    Nice code, only issue I have is using that field in reports as a filter the dropdown list is empty?

    How can I make the function trigger when using the field in reports?

    Chris

    • jason August 15, 2017 at 9:36 am # Reply

      Hi Chris,

      Unfortunately the reports module is a completely different beast so this won’t work there.

Trackbacks/Pingbacks

  1. Troubleshooting dynamic dropdowns in SugarCRM 6.5 | BlogoSfera - October 17, 2013

    […] and a dev instance running, I decided to give it a shot. The instructions I followed can be found here. After I run the repair and rebuild, I would expect to see the custom field in my Fields list under […]

  2. Troubleshooting dynamic dropdowns in SugarCRM 6.5 | Technology & Programming Answers - November 2, 2013

    […] and a dev instance running, I decided to give it a shot. The instructions I followed can be found here. After I run the repair and rebuild, I would expect to see the custom field in my Fields list under […]

  3. SugarCRM – How to populate dropdowns from database and populate using ajax? | vishwasdeshmane - December 3, 2015

    […] https://www.eggsurplus.com/content/populate-a-dropdown-from-the-database/ […]

Leave a Reply