Import: Update by Email Address

When importing in SugarCRM you can update existing records only if you include the unique ID along with each row that is being imported. This can be very impractical as it requires folks to dedicate hours to days of time to scrub lead files if they want their data to be updated for leads that already exist in their system. This is especially true in the case of periodically getting a fresh new version of leads from the same source. What if we alter the import process so that we can grab the ID on the fly by using an email address from the file?

In this example we will be updating the Title field on the Contacts module. Here are the two records that we will be updating. In our import file there is not an ID column. However, you could have ID column and still use this method to fill in the ID for any that are blank based on the email address.

First, the code. The actual import gets kicked off on Step 4. It is at this point where we will need to tell the import to use our custom logic to populate the ID field if blank based on an email address. So we make a custom view.step4.php and extend the original ImportViewStep4 class. In this case, we need to implement everything exactly the same as in the original display() function except for the Importer class instantiation. To clarify, you NEED TO copy the original display() function from the core view.step4.php from the Import module. It may change from version to version so always do this step.

Notice the this block near the bottom:

if($this->bean->module_dir == 'Contacts') { 
    require_once('custom/modules/Import/ContactsImporter.php');
    $importer = new ContactsImporter($importSource, $this->bean);  
} else {
    //Original code from /modules/Import/views/view.step4.php
    $importer = new Importer($importSource, $this->bean);
}

This tells the import process to use our ContactsImporter when we are importing Contacts.

Next up, we implement our logic in ContactsImporter. Much like with view.step4.php we will extend the original Importer class copy the import() function to start. You NEED TO copy this from your modules/Import/Importer.php as it may change from version to version.

Take special note of anything marked with “eggsurplus”. In this file we go through all the columns imported and look for an ID column and an Email1 column. These are related to an integer key so those get stashed for when they are needed on the next step which is to iterate over each row and check to see if the ID is missing and if an email address column exists. If so, our custom existingEmailAddressCheck function is called which looks in the database for a contact with that email address and returns the corresponding ID.

With this code in place we can now do an actual import. It is important that on the first step of the Import that we choose “Create new records and update existing records”.

What if you don’t have an ID column in your file? On Step 3 click on “Add Field” at the bottom and add the ID column.

Next, drag over Email Address to “Fields to Check” on Step 4 “Check for Possible Duplicates”.

Continue on and kick off the import. When finished all of the contacts with a matching email address will be updated.

This same process can be applied to any other module and potentially any other field or combination of fields. You could even look for matches across different modules. For example, avoid importing a lead if an account with the same phone number already exists.

Happy hacking!

4 Comments

about

I make high quality solutions at an alarming rate.

Download a File in a Logic Hook

Today on IRC a fine fellow posted an interesting challenge. How do you prompt a file download while in a logic hook? To make it more interesting he offered scotch as a reward.

I was hooked.

Why return a file in a logic hook? There are many possibilities. Perhaps a quote or invoice gets updated and you want to generate a new PDF of the quote/invoice on any change. Or perhaps whenever an account gets added you need a packing slip created.

To illustrate how this could be done here is a simple example that downloads a text file whenever a contact gets added or updated to have a title of CEO. First is the before_save logic hook that captures when a value gets changed. In the require_download function below we check to see if the title value has changed. If so, set a session variable to be used to both tell the next (after_ui_frame) hook to execute and to pass data that could be used when generating the file.

Next we have an after_ui_frame hook that checks to see if the request is a non-AJAX request, if the current action is the Detail View, and if our session variable is set. If so, then output some javascript to redirect the browser to our custom download file action. In this URL we could pass the bean ID if needed. Notice the to_pdf. If you don’t add this you will get some unwanted content added to your generated file.

Here is the CEO_Download.php file that spits out a text file for the user to save. Notice that the name of the file matches the action param in the redirect URL above. If that isn’t the case in your situation you will want to create a custom entry point instead or add the action to the module’s controller. Yeah, there’s always another way to do something when working with SugarCRM.

Below is what the logic_hooks.php definition looks like:

Note: Ideally, the before_save should be an after_save logic hook to ensure that the save actually happened. Do it that way first, but be aware that you may have issues with the fetched_row having updated values instead of the original values depending on which version of SugarCRM you are on. See the comments on this dev blog post for more information: SugarCRM Developers Blog: Create new project on Opportunity stage change via a logic hook.

Moral of the story? I’ll do anything for a good scotch.

Leave a comment

about

I make high quality solutions at an alarming rate.

Adding a User Task Subpanel

A question was recently asked on the SugarCRM forums on how to add a subpanel to a User detail view that shows all assigned Tasks for the user (http://forums.sugarcrm.com/f55/subpanel-users-assigned-tasks-87147). It’s actually a pretty simple process, although it requires custom code.

Steps:
1. Create custom/Extension/modules/Users/Ext/Vardefs/TasksVardefs.php (actual file name irrelevant)

2. Create custom/Extension/modules/Users/Ext/Language/en_us.Tasks.php (change file name as needed for your language)

3. Create custom/Extension/modules/Users/Ext/Layoutdefs/TasksLayoutdefs.php (actual file name irrelevant)

4. Quick Repair and Rebuild
5. Rebuild Relationships

Then you should see something like:

User Task Subpanel

User Task Subpanel

2 Comments

about

I make high quality solutions at an alarming rate.

Multiple DetailViews for the Same Module

This past September Francesca from the SugarCRM forums asked if there was a way to have a different layout view for each department:

two different Edit Views and two different Detail Views for the SAME module?

The short answer is yes. SugarCRM lets you do just about anything. The question always is – at what cost and/or tradeoff?

To do this for a module (like the Accounts module) copy custom/metadata/Accounts/metadata/detailviewdefs.php and rename for each department. For example, custom/metadata/Accounts/metadata/detailviewdefs_sales.php. Then create a custom view.detail.php and tell SugarCRM to use the appropriate custom layout:

Caveat – you must turn on developerMode. Otherwise, it will just cache the first requested layout and every subsequent request will use that same cache/modules/Accounts/DetailView.tpl. With this code, we need that .tpl rebuilt on every request. Actually, thinking about this more it really does have some annoying limitations:

  • Can’t take advantage of cache.
    Result = performance degradation.
  • Can’t use Studio to edit the custom layouts.
    Result = time consuming code changes.
  • Has to be done for any and every module required and for every department.
    Result = more time consuming work.

I didn’t start writing this post with this intention, but it is clear that if you really want custom layouts just use my SecuritySuite module. Even if you don’t need the teams security portion, the custom layouts feature by itself makes doing this a breeze.

Result = saving time, money, and frustration.

Then again, it may be good enough for a one-time thing.

Update: Angel (check out his awesome blog!) has noted that the cache can also be cleared on demand using:

TemplateHandler::clearCache('Accounts','DetailView.tpl');

This removes the need to turn on developerMode and reduces the lack of caching just to that view in that specific module.

1 Comment

about

I make high quality solutions at an alarming rate.

Organizing Files in a Module Zip

The following is from SugarOutfitters, which is a marketplace for SugarCRM modules, addons, integrations, and more.

Since a given module installation is defined by the manifest.php included in the zip it is basically up to each developer to decide how to package their module. This flexibility is great but can cause both confusion for new developers and for folks trying to understand what a module zip contains. For the latter, this could be end users who like to know every detail or it could be a developer who is taking on or contributing to someone else’s module.

For this article we won’t be recommending a full SugarCRM Module Standardization. Instead we are going to suggest one way of organizing and look to get your own suggestions on how to better organize modules.

Read the full article…

Leave a comment

about

I make high quality solutions at an alarming rate.

Always add a wildcard at beginning of a search term

There was a question on the SugarCRM forums today that asked if anyone has found an upgrade-safe way to customize searches to always add a % to the beginning of a search (http://forums.sugarcrm.com/f3/auto-wildcard-74128/). Being able to add a wildcard is useful if you are searching for something that may exist in the middle of a value and not necessarily at the beginning. For example, you know a lead you’ve been working on contains “Auto” in it, but you aren’t sure where. You can manually search for %Auto and it will return any lead that contains “Auto” anywhere in the name. (FYI – the search wildcard is always added to the end of a search)

Some would prefer that the search always act this way. In the past, a hack had to be done to include/SearchForm/SearchForm2.php. Since that time, SugarCRM has added a global config option. Simply add the following to your config_override.php:


'search_wildcard_infront' => true,

There are tons of undocumented or hard to find documentation on options such as this. If enough people would find it useful I’d be willing to take a shot at writing a parser that extracts nuggets such as this from the code.

6 Comments

about

I make high quality solutions at an alarming rate.

Manifest Copy Directive in a SugarCRM Module

The following is from SugarOutfitters, which is a marketplace for SugarCRM modules, addons, integrations, and more.

When it comes to developing solutions for SugarCRM there are many different ways to go about solving a particular challenge. In our Dev Tips series of blogs we will be highlighting various best practices for add-on development with the end goal of creating higher quality solutions while minimizing the chances of an end user experiencing an issue.

One way to create solutions for SugarCRM is by creating installable packages called modules. Modules contain all the required files along with a manifest.php which defines how the module should be installed.

Read the full article…

Leave a comment

about

I make high quality solutions at an alarming rate.

Large forms not saving in SugarCRM

I ran into an interesting issue for a client with project templates only saving the first 47 tasks. Viewing the ajax post using Firebug showed that every task was being sent over. Now it was time to debug the server side.

Within modules/Projects/SaveGrid.php I threw in some debug statements:

....
for ($i = 1; $i <= $_REQUEST['numRowsToSave']; $i++) {
// don't save any blank rows
if (isset($_REQUEST["duration_" . $i]) && ($_REQUEST["duration_" . $i] != "")) {
....
} else {
$GLOBALS['log']->fatal("Task ".$i." has no duration");
}
....

Sure enough, tasks 48 and up had no duration. So I dumped the request before the for loop to see how the parameters looked and it ends up there were 1001 parameters. None for anything related to task 48 and up. Before all of this I did a phpinfo() page to see what the environment configuration was. Previously I thought we were running into timeout or memory issues. At the bottom of the phpinfo page it showed that Suhosin was being used which is a somewhat popular project to deal with a number of known PHP flaws/security vulnerabilities (http://www.hardened-php.net/suhosin/).

Now 1001 is a weird number for someone to cutoff at so I assumed that was due to something within the app or a general programming error in Suhosin. So I searched for Suhosin 1000 parameters and found that there is a max_vars setting that many installs limit to 1000. The client then bumped this up to a much higher number (see http://anothersysadmin.wordpress.com/2012/02/16/php-5-3-max_input_var) and all tasks started to save.

6 Comments

about

I make high quality solutions at an alarming rate.

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

10 Comments

about

I make high quality solutions at an alarming rate.

Adding a panel via javascript in SugarCRM

I’m working on a new feature that requires a panel to be inserted on the edit screen in very specific cases. Since this is for SecuritySuite it must work for all modules; both current and future. Because of this, customizing each module’s view.edit.php is out of the question. The end goal is to allow users who are a member of multiple groups to choose which group(s) should be associated with any newly created record. This used to be the “Use Popup Select” feature but due to the new AjaxUI in SugarCRM this no longer works.

Right below the Assigned To panel and above the buttons is where I’d like to insert the panel:
Where the panel should go

Now here is where the problem comes in. Generally, when you need to show/hide a panel based on a field’s value all you do is create the panel in Studio and then either customize the view.edit.php and view.detail.php or detailviewdefs/editviewdefs.php to spit out javascript to hide the panel based on certain conditions AFTER the parent::process(). If it needs to be tightly coupled to a field’s value then you’d just spit out the appropriate javascript to show/hide all of the appropriate panels. In this case, we don’t necessarily know what modules will ever be created in a given install. Since there are thousands of installs using SecuritySuite on SugarCRM CE for the team functionality it needs to be as forgiving as possible.

The place that allows for this flexibility to customize is the main MVC view.edit.php (include/MVC/View/views/edit.php). Thankfully this module already customizes that file to allow for the custom layout for each team feature. This is not upgrade-safe, of course. There are many changes in SecuritySuite that are not upgrade-safe. It is a necessary evil to ensure that everything is secured properly. Any team solution that claims to be upgrade-safe is doing so at the expense of not truely being secure.

In this specific case the panel should only show if this is to be a newly created record and if the current user is a member of 2 or more groups. Otherwise, it just inherits the single group associated to the user (if the “Inherit from Created By User” is chosen).

On to the code. To insert the panel we are going to do so after the template has been rendered. So we will do this in the display function after the echo.

Yes, some of that code sucks. For example, the functions used to get the group count, etc should be static functions but it’s been around for years and would cripple some folks who use the library so now I’m kind of stuck. If you have suggestions, please fire away.

As of 6.5.0 SugarCRM began shipping with jQuery already included. I’m a fan of jQuery due to the ubiquity of it, ease of use, and ease of finding help. In this case I hand build the panel html then do a preg_replace to remove line breaks as javascript’s support for multiline strings is nil. Once the panel is all set some javascript is outputted with which uses jQuery’s append function to add the panel to the panel container.

Here is the finished result:
The finished product. A panel insert into SugarCRM

Have ideas on how to improve this? I’d love to hear them!

3 Comments

about

I make high quality solutions at an alarming rate.