How can we populate a drop down from database table dynamically ?

mitulp95 · 24 February 2018 11:51

amariussi · 24 February 2018 15:29

Have a look at this article by Jason Eggers:
https://www.eggsurplus.com/content/populate-a-dropdown-from-the-database

PS: I copied the whole article as I fear that it may get lost

Populate a Dropdown from the Database
by jason on October 2, 2012 in Developer, SugarCRM
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):

//custom/Extension/modules/Project/Ext/Vardefs/from_template_c.php
$dictionary['Project']['fields']['from_template_c'] = array (
	'name' => 'from_template_c',
	'vname' => 'LBL_FROM_TEMPLATE',
	'function' => 'getProjectTemplates',
	'type' => 'enum',
	'len' => '100',
	'comment' => 'The template that the project was created from.',
);

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)

//custom/Extension/application/Ext/Utils/getProjectTemplates.php
function getProjectTemplates(){
	static $projectTemplates = null;
	if(!$projectTemplates){
		global $db;
		
		$query = "SELECT id, name FROM project where deleted = 0 and is_template = 1 order by name asc ";
		
		$result = $db->query($query, false);

		$projectTemplates = array();
		$projectTemplates[''] = '';

		while (($row = $db->fetchByAssoc($result)) != null) {
			$projectTemplates[$row['id']] = $row['name'];
		}

	}
	return $projectTemplates;
}

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

28 Responses to “Populate a Dropdown from the Database”

Angel MagaÃ±a October 16, 2012 at 2:53 am # Reply
Cool stuff! Thanks for sharing.

Nick Chen April 14, 2013 at 9:28 pm # Reply
It works. Thank you very much.

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.

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?

Sixx February 1, 2014 at 9:35 am # Reply
Same problem

jason February 2, 2014 at 1:25 pm # Reply
You may need to do a process_record logic hook to set the value to the display label for the field for it to display properly on the list view.

adam April 1, 2014 at 8:46 pm # Reply
pls, give me more detail.

Aldo May 31, 2014 at 4:58 am # Reply
Could you please explain how and where to add the process_record logic hood for your example?
Thanks a lot!

jason May 31, 2014 at 1:10 pm # Reply
Here you go: http://support.sugarcrm.com/02_Documentation/04_Sugar_Developer/Sugar_Developer_Guide_6.7/03_Module_Framework/Logic_Hooks/02_Module_Hooks/process_record/

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.

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.

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.

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)

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’] ;
}

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.

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.