Archive | How To RSS feed for this section

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!

EDIT: Adam Jakab has done some nice work to generalize this so that the Importer takes advantage of any indices for a module. Check it out: http://dev.alfazeta.com/blog/item/41-sugarcrm-import-update-records-by-matching-user-supplied-indices-without-supplying-id-in-csv

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.

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

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.

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!