Skip navigation
All Places > Developer > Blog > 2014 > March
2014

Post originally written by sugarraagaard.

 

 

1. Introduction

 

So you have yourself in a bit of a bind, you need to get an existing API to do something different. Maybe you need more information on an Account record, or perhaps your custom module needs some custom handling to go along with it. This cookbook entry is going to give you a few examples on how to override the default behavior of the REST API.

 

2. Before we get started

 

Before we get started you need to have some basic idea of how to interact with the SugarCRM API from a client standpoint and The School of REST – Part 1 is a great place to start. After that you should probably learn how to add a new API so you could use that instead and for that I will point you to Adding a rest endpoint.With that taken care of let's get started on modifying an existing API.

 

3. Api Helper Classes

 

In order to facilitate different modules having different needs we added the SugarApiHelper class. You can find this class in data/SugarBeanApiHelper.php this class is used by all modules and is responsible for both formatting any bean before it is displayed to the client and also handling populating the bean from the API's requests to save/update.

 

To create a new ApiHelper for the class Contacts put a file in either custom/module/Contacts/ContactsApiHelper.php and then name the class in there CustomContactsApiHelper and be sure to extend the ContactsApiHelper. An important thing to note here is that you use the module name in front of ApiHelper, so it is Contacts instead of just Contact. For your own custom modules you can just put that ApiHelper class in your custom module's base directory. Some of our shipped modules have ApiHelpers, and others don't so if you are creating a custom api helper be sure to check if the base api helper exists and extend that if it does, otherwise just extend the core SugarBeanApiHelper class.

 

4. Overriding FormatForApi()

 

For our cookbook entry here we are going to build a method to display some information about the biggest open opportunity related to this contact.

 

To add a api helper for the Contacts module first we check for an existing helper. Out of the box helpers will be located in the modules directory and will be named the module name with ApiHelper.php on the end. So if we look at modules/Contacts/ we will see a ContactsApiHelper.php file (note: It's not ContactApiHelper.php we use the plural here). Since there is a module specific helper already when we create our custom helper we will need to be sure to extend our CustomContactsApiHelper class from the ContactsApiHelper instead of from SugarBeanApiHelper. The method we are going to override here is formatForApi(). This method is responsible for converting a bean object into an array suitable for outputting over the API, it is called from all over the API, single record views, listing records, showing lists of related records, everywhere.  One of the things it does for example is that it reformats all of the dates and times into a standardized ISO-8601 format to make it simple for the other side to parse. To get this started we will make it simple we are just overriding the formatForApi() method, calling the parent method in there and then adding some filler data to the top_opp section and returning the data. This will be an easy way for us to check and make sure our api helper is being called. If you try this and it doesn't work the first thing to do is run a quick repair and rebuild to have it rebuild the file path cache and then the second thing is to make sure you are using the plural version and you name your class correctly.

 

ContactsApiHelper.php

<?php
require_once('modules/Contacts/ContactsApiHelper.php');

// Since the ContactsApiHelper exists, we'll extend it
// If it didn't we would just extend the SugarBeanApiHelper
class CustomContactsApiHelper extends ContactsApiHelper
{
// Mimic the SugarBeanApiHelper->formatForApi() class
public function formatForApi(SugarBean $bean, array $fieldList = array(), array $options = array())
    {
// First off, call the parent class
$data = parent::formatForApi($bean, $fieldList, $options);

// Make sure they requested the top_opp field, or no field restriction at all
if (empty($fieldList) || in_array('top_opp', $fieldList) ) {
// Let's just put some filler data in here for now
$data['top_opp'] = 'Rest in Peas Burger';
        }

// formatForApi returns the bean formatted as a hash in API format
return $data;
    }
}

 

Here is what we get back in our initial response. Notice that we added a fields parameter to the list in order to make sure we fetched the top_opp field.

 

first_response.json

# GET rest/v10/Contacts?fields=id,name,top_opp&max_num=2
{
"next_offset": 2,
"records": [
        {
"id": "f2d4042e-6d9e-88af-3c00-53271512e333",
"name": "Tabitha Johansson",
"date_modified": "2014-03-17T15:29:16+00:00",
"_acl": {
"fields": {}
            },
"top_opp": "Rest in Peas Burger",
"_module": "Contacts"
        },
        {
"id": "f23e19e3-a59e-8985-4202-5327156a36a9",
"name": "Kuchi Kopi",
"date_modified": "2014-03-17T15:29:16+00:00",
"_acl": {
"fields": {}
            },
"top_opp": "Rest in Peas Burger",
"_module": "Contacts"
        }
    ]
}

 

To fill in the rest of this function we turn to our good friend SugarQuery. We build a seed bean, pass that to SugarQuery so it knows how to translate the fields then we join it to the current contact. Finally we restrict it down to the top opportunity. With that we grab the bean using fetchFromQuery() so we don't have to manually populate the bean, run that bean through the Opportunities formatForApi() to keep things nice and consistent and copy the API ready format over to the output data.

 

ContactsApiHelper.php

<?php
require_once('modules/Contacts/ContactsApiHelper.php');

// Since the ContactsApiHelper exists, we'll extend it
// If it didn't we would just extend the SugarBeanApiHelper
class CustomContactsApiHelper extends ContactsApiHelper
{
// Mimic the SugarBeanApiHelper->formatForApi() class
public function formatForApi(SugarBean $bean, array $fieldList = array(), array $options = array())
    {
// First off, call the parent class
$data = parent::formatForApi($bean, $fieldList, $options);

// Make sure they requested the top_opp field, or no field restriction at all
if (empty($fieldList) || in_array('top_opp', $fieldList) ) {
// Build a list of the fields we care about
static $oppFields = array(
'id',
'name',
'currency_id',
'amount',
'date_closed',
            );

// Make sure we don't populate this some other way
if (!isset($bean->top_opp) && !empty($bean->id)) {
// Get a seed for Opportunities
$oppSeed = BeanFactory::newBean('Opportunities');
$q = new SugarQuery();
// Set the from bean
$q->from($oppSeed);
// Join in using the 'contacts' link field
$contactJoin = $q->join('contacts');
// Limit our join to just the contact we care about
$q->where()->equals($contactJoin->joinName().'.id', $bean->id);
$q->where()->notIn('sales_stage', array('Closed Lost', 'Closed Won'));
// Sort by the "usdollar" amount, the normal amount field could be in
// different currencies and so the sorting will be all wrong.
$q->orderBy('amount_usdollar DESC');
// Just get the top opportunity
$q->limit(1);

// Use fetchFromQuery and pass in the fields we care about
$beans = $oppSeed->fetchFromQuery($q, $oppFields);

if (count($beans) > 0) {
// Even though we had a limit of 1, fetchFromQuery still returns an array
// They are indexed by ID, so let's just fetch the first and only one
$bean->top_opp = array_pop($beans);
                } else {
// Flag it so we know we tried to set it, there just wasn't anything there
$bean->top_opp = null;
                }
            }

$data['top_opp'] = array();
if ($bean->top_opp != null) {
// getHelper will get us the helper we want
$data['top_opp'] = ApiHelper::getHelper($GLOBALS['service'], $bean->top_opp)
->formatForApi($bean->top_opp, $oppFields);
            }
        }

// formatForApi returns the bean formatted as a hash in API format
return $data;
    }
}

 

Now you see that we get the top opportunity on every Contact. Also note how the numbers and the dates are formatted the exact same way as everything else in the API thanks to our call to formatForApi()

 

second_response.json

# GET rest/v10/Contacts?fields=id,name,top_opp&max_num=2
{
"next_offset": 2,
"records": [
        {
"id": "f23e19e3-a59e-8985-4202-5327156a36a9",
"name": "Kuchi Kopi",
"date_modified": "2014-03-18T16:45:29+00:00",
"_acl": {
"fields": {}
            },
"top_opp": {
"id": "f116eb68-1f2d-d6be-6d07-532715473850",
"name": "Make glowing burger",
"amount": "12171.000000",
"currency_id": "-99",
"_acl": {
"fields": {}
                }
            },
"_module": "Contacts"
        },
        {
"id": "f2d4042e-6d9e-88af-3c00-53271512e333",
"name": "Tabitha Johansson",
"date_modified": "2014-03-18T16:26:44+00:00",
"_acl": {
"fields": {}
            },
"top_opp": {
"id": "83ef4ea5-56d8-7e30-e592-532715a73955",
"name": "Build Custom Piano Burger",
"amount": "12279.000000",
"currency_id": "-99",
"_acl": {
"fields": {}
                }
            },
"_module": "Contacts"
        }
    ]
}

 

5. Overriding populateFromApi()

 

Typical relate fields in SugarCRM do not respond to changes on the parent record. This is an intentional design decision to prevent users from accidentally changing parent records. For "top_opp" however we want it to be different, we want to support changing that record directly from the related Contact. This is where populateFromApi() comes in. The symmetric method for formatForApi() this method takes API formatted data and populates it into a bean. By overriding this method in the same class we defined above we will be able to force feed the data into the top Opportunity and save it manually. To do this we just call the parent method, make sure there are no errors and that they actually submitted some "top_opp" data. After we have verified that we should populate a bean, we build a bean, retrieve it based on the passed in ID and then run it through the Opportunities ApiHelper to populate that data. If there are no errors there we manually save the Opportunity and return true so that the Contact is saved by whatever called it.

 

ContactsApiHelper.php

<?php
require_once('modules/Contacts/ContactsApiHelper.php');

// Since the ContactsApiHelper exists, we'll extend it
// If it didn't we would just extend the SugarBeanApiHelper
class CustomContactsApiHelper extends ContactsApiHelper
{
// Mimic the SugarBeanApiHelper->formatForApi() class
public function formatForApi(SugarBean $bean, array $fieldList = array(), array $options = array())
    {
// Same as before, snipped for display purposes.
    }

// Mimic SugarBeanApiHelper->populateFromApi()
public function populateFromApi(SugarBean $bean, array $submittedData, array $options = array() )
    {
$errors = parent::populateFromApi($bean, $submittedData, $options);
if ($errors !== true || empty($submittedData['top_opp']['id'])) {
// There were errors in the original, don't bother saving the opp
// Or they aren't trying to save the opp, so just pass back the parent
return $errors;
        }

// Do a full retrieve so the logic hooks on save have everything they need
$opp = BeanFactory::getBean('Opportunities', $submittedData['top_opp']['id']);
// Load up the ApiHelper class for Opportunities and have it populate from
// this subsection of data.
$oppErrors = ApiHelper::getHelper($GLOBALS['service'], $opp)
->populateFromApi($opp, $submittedData['top_opp']);
if ( $oppErrors !== true ) {
// Errors populating the opportunity
return $oppErrors;
        }
// Typically the save happens after this is all done, but since that function doesn't
// know about this extra opportunity, we'll just save it here.
$opp->save();

// Checked both the parent errors, and the opp errors, they are both true so return true
return true;
    }   
}

 

Testing it out we see that we can change the name from the related Contact. Amounts and expected closed dates could also be set in PRO editions, but ENT has some special logic around those so we can't populate them externally as easily.

 

third_response.json

# PUT /rest/v10/Contacts/f23e19e3-a59e-8985-4202-5327156a36a9?fields=id,name,top_opp
# Contents of the PUT request
{
"top_opp": {
"id": "f116eb68-1f2d-d6be-6d07-532715473850",
"name": "Make super glowing burger"
  }
}
# Returned
{
"id": "f23e19e3-a59e-8985-4202-5327156a36a9",
"name": "Kuchi Kopi",
"date_modified": "2014-03-19T10:04:51-06:00",
"_acl": {
"fields": {}
    },
"top_opp": {
"id": "f116eb68-1f2d-d6be-6d07-532715473850",
"name": "Make super glowing burger",
"amount": "12171.000000",
"currency_id": "-99",
"date_closed": "2014-09-23",
"_acl": {
"fields": {}
        }
    },
"_module": "Contacts"
}

 

6. Adding hooks to fetchFromQuery()

 

Eagle eyed readers may have spotted the fact that if you run a query in fetchFromApi() that means on a list you will run that query once per row, for efficiency sake that's not exactly the best thing to do. We will use this as a good example on how to modify list views to run alternative queries. Please note that in the future normal retrieve() calls map run through this same set of code, so be prepared for that. We've seen fetchFromQuery() run in the previous example of fetchFromApi(), so we see that it takes in a query object and returns an array of populated beans. The good news with this function is that it was designed to be hooked into using both the before_fetch_query and after_fetch_query logic hooks. The before hook is perfect for modifying the query to have it return different data for a set of fields, or return extra data that you can get at from the after hook to populate into the bean. While we could have done it using both a before and after hook I decided to make it easy and just run it as a separate query per list and run it all through the after_fetch_query hook.

 

To get started here we need to add a logic hook file into custom/modules/Contacts/logic_hooks.php that looks like this:

 

logic_hooks.php

<?php
// If you are making this module loadable, put it in an extension file instead
// These are needed if you are hand-building a logic hook file
$hook_version = 1;
$hook_array = Array();
// Setup a hook into the after_fetch_query hook
$hook_array['after_fetch_query'][] = array(
90, // Priorty, what order does this hook run in
'DemoProcessQuery', // What do we want to name this hook
'custom/modules/Contacts/DemoHook.php', // What filename contains the hook class
'DemoHook', // What is the demo hook class name
'processQuery', // What function to call in the class
);

 

Now if we look at the actual code of the custom/modules/Contacts/DemoHook.php that we referenced in the logic_hooks.php file we will notice that we skip out early if we aren't in the requested list of fields, or if there are no beans in the list. If we are clear to proceed we go ahead and build up the SugarQuery in much the same way as the previous example, except this time instead of passing in a single contact we pass in an array of contacts to match on and instead of limiting to the first result we group by the contact id and order by the amount_usdollar, this should give us the top opportunity per contact id. This will only work on MySQL, but I figured it was the easiest way to demo this. From there we run fetchFromQuery() on the Opportunities themselves and then using the raw rows that were returned we re-associate them with the parent contact.

 

DemoHook.php

<?php
class DemoHook
{
// Called from our definition in the logic_hooks file
public function processQuery($bean, $event, $args)
    {
if (!empty($args['fields']) && !in_array('top_opp',$args['fields'])) {
// They don't want the top_opp, don't process the query
return;
        }
if (empty($args['beans'])) {
// There are no results to this query, don't need to process it
return;
        }

// Same fields as formatForApi
static $oppFields = array(
'id',
'name',
'currency_id',
'amount',
'date_closed',
        );

// The beans array is keyed by the bean id
$beanIds = array_keys($args['beans']);

// Get a seed for Opportunities
$oppSeed = BeanFactory::newBean('Opportunities');
$q = new SugarQuery();
// Set the from bean
$q->from($oppSeed);
// Join in using the 'contacts' link field
$contactJoin = $q->join('contacts');
// Make sure to select the contact ID, so we can sort it out later
$q->select(array(array($contactJoin->joinName().'.id','contacts__id')));
// Fetch them by the bean id's
$q->where()->in($contactJoin->joinName().'.id', $beanIds);
$q->where()->notIn('sales_stage', array('Closed Lost', 'Closed Won'));
// Sort by the "usdollar" amount, the normal amount field could be in
// different currencies and so the sorting will be all wrong.
$q->orderBy('amount_usdollar DESC');
// Just get the top opportunity
$q->groupBy($contactJoin->joinName().'.id');

// Use fetchFromQuery and pass in the fields we care about
$opps = $oppSeed->fetchFromQuery($q, $oppFields, array('returnRawRows' => true));

// Raw rows are returned keyed by _rows, but we don't want to try and process
// that as a bean, so let's set them aside for later
$rows = $opps['_rows'];
unset($opps['_rows']);

// Loop through the opportunities and with the help of the raw rows link them to their contacts
foreach ($opps as $oppId => $opp) {
$contactId = $rows[$oppId]['contacts__id'];
$args['beans'][$contactId]->top_opp = $opp;
        }
    }
}

 

Don't need to put in a request/response here because with all of that added work everything looks the same just now we don't run a bunch of queries on a list view, only one.

 

7. So you really have to override an endpoint.

 

Some days all of the logic hooks in the world aren't enough. I've tried to give you a lot of very simple and supported methods to override our API but if they just aren't enough for you then you can always override the endpoint. Be cautious and consult an expert before overriding an endpoint, take a stroll and think about it, and just know that we may add functionality to an endpoint that you aren't expecting so you may break things on a normal update.

 

Didn't scare you off and you still want to add an endpoint? Okay then, here you go. Overriding an endpoint is very similar to creating a new endpoint so start by following the directions there. Don't worry about your path conflicting with another path in the base app, the REST lookup code prefers endpoints registered in the custom directory, you will notice that two identical endpoints registered for the same path will appear in /help, but the one with the highest score wins. If you want to live in the danger zone and override an endpoint so that the REST API will match a less-specific path you can add bonus score by putting a value in the option extraScore in your registerApiRest() array for that endpoint. In our example here we will register a new endpoint living on top of the out of the box /Contacts/:record endpoint. Let's create this class in the file custom/modules/Contacts/clients/base/api/FavoriteBurgerApi.php making sure that the filename matches the class name. In our override class we will also extend the ModuleApi because that is what normally handles these requests. In our overridden retrieveRecord() method we call the parent method so it can continue doing the things it normally does and after that is done we just go in and manipulate the returned data before we send it back. Be sure to run a quick repair and rebuild and check the /help to make sure the REST API picked up your newly created endpoint, it helps to make the short help something unique so it's easier to spot.

 

FavoriteBurgerApi.php

<?php

require_once('clients/base/api/ModuleApi.php');

class FavoriteBurgerApi extends ModuleApi
{
static public $favoriteBurgers = array(
"Poutine on the Ritz Burger",
"Mesclun Around Burger",
"The Don't Get Creme Fraiche With Me Burger",
"Onion-tended Consequences Burger",
"Bruschetta Bout It Burger",
"MediterrAin't Misbehavin' Burger",
"I'm Gonna Get You Succotash Burger",
"Every Breath You Tikka Masala Burger",
    );

public function registerApiRest()
    {
return array(
'retrieve' => array(
'reqType' => 'GET',
'path' => array('Contacts','?'),
'pathVars' => array('module','record'),
'method' => 'retrieveRecord',
'shortHelp' => 'Returns a single record, with their favorite burger attached',
'longHelp' => 'include/api/help/module_record_get_help.html',
            ),
        );
    }

public function retrieveRecord($api, $args)
    {
// Have the moduleApi do the hard work
$data = parent::retrieveRecord($api, $args);

// People don't remember what burger they tried so just give them a random favorite
$burgerNum = rand(1,count(self::$favoriteBurgers)) - 1;
$data['favorite_burger'] = self::$favoriteBurgers[$burgerNum];

// Return the modified data
return $data;
    }
}

 

As you can see in the sample request we have now added a "favorite_burger" field to the returned data.

 

fourth_response.json

# GET /rest/v10/Contacts/f23e19e3-a59e-8985-4202-5327156a36a9?fields=id,name
{
"id": "f23e19e3-a59e-8985-4202-5327156a36a9",
"name": "Kuchi Kopi",
"date_modified": "2014-03-19T10:04:51-06:00",
"_acl": {
"fields": {}
    },
"_module": "Contacts",
"favorite_burger": "Bruschetta Bout It Burger"
}

 

8. And that's all

 

While there are many more ways to manipulate the data inside of Sugar, these are the new ways to manipulate the data directly relating to the REST API that all non-backwards compatible code runs through in newer versions of Sugar. I hope this gives you all a good idea on where you can start getting your customizations in for your individual implementations of SugarCRM.

This article is aimed at beginning to intermediate SugarCRM developers who want to create custom filters in SugarCRM version 7.

This does not cover the custom filters that users can create for themselves.  Those are stored on a per-user basis and are not available to all users.  Instead, this document will cover creating filters that all users of the system will have access to.

This article assumes some knowledge of SQL, PHP, Unix, and general web stuff.

 

About Filters

 

In Sugar 7 parlance a filter is used to control what rows of data are displayed to the user in a given view.  Sugar has a number of core filters which come with Sugar out of the box.  When you go to a page with a layout that makes use of the filter view and click on the “Filter” pulldown menu you’ll see them displayed as options:

When you choose one of those filters you’ll notice that the rows that show up in the list view change.  The filter supplies certain characteristics the data must meet before the row is displayed in the view.

Filters can be extremely useful when you want to limit what the user sees and not just present every row in the table to the user.

 

How to create a very basic filter

The core filters are very convenient since you don’t have to do anything to make them show up. However, it’s not unusual to have a need for a more customized filter that sets criteria specific to your individual needs.  No core filter will be able to supply that.  Fortunately, SugarCRM 7 provides a way to  create such a filter without having to descend into the core functionality of the product and hack something into place.

So let’s dive in and create a very simple filter.

For the purposes of this document I have gone into Studio and created a module called “Random Custom Stuff” with a module name of “flt_RandomCustom”.  I know that’s a weird name, but it’s how we do things where I work, so I went with it.  I also added a custom field to the module to hold a random number.  This created both the “flt_randomcustom” and “flt_randomcustom_cstm” tables (among others) in the database.  That will be more interesting later.

 

When I go to

http://{my instance}/#flt_RandomCustom

 

I see the following:

You see all the rows for this module displayed because the default filter is “All Random Custom Stuff” which simply shows everything.  We’ll change that by creating a new filter.

 

Let’s create a filter to only show rows which have a name of “Blorg” and call it the “Blorg Only” filter.  Let’s say that you installed SugarCRM in

/var/www/sugarcrm

 

The first thing to do would be to create a directory for your new filter.  Like other components such as layouts and views, you create a custom filter by adding a directory and files under the

 

"custom/" directory in the SugarCRM instance.  We want this filter to be available only to this module so we’ll create the directory:

/var/www/sugarcrm/custom/modules/flt_RandomCustom/clients/base/filters/blorgonly

At first glance that looks like a crazy path, but if you’ve done any other customization of SugarCRM 7 you’ll recognize that this fits with layouts, views, and other customizations.

 

In that directory create a file "blorgonly.php" which contains the following text:

<?php

 

$module_name = "flt_RandomCustom";

 

$viewdefs[$module_name]['base']['filter']['blorgonly'] = array(

 

    'create'               => false,

 

    'filters'              => array(

 

        array(

 

            'id'                => 'blorgonly',

 

            'name'              => 'LBL_BLORG_ONLY_FILTER',

 

            'filter_definition' => array(

 

                array(

 

                    'name' => 'Blorg',

 

                ),

 

            ),

 

            'editable'          => false,        ),    ),

 

);

It’s very important that the name of the PHP file match the name of the directory which needs to match the name of the filter.  You’ll notice in the code above that we specify the name of the filter in two places, once as the element of the “filter” viewdefs array, and again as the “id” of the filter.  Both are important.  For the “name” we’ve put in a placeholder for a label string.  Don’t worry, we’ll get to that in a minute.

The real action takes place in the “filter_definition” array.  You can see that we have a single element that says that the “name” has to be “Blorg”.  The “name” field of the array under “filter_definition” is referring to the name of the actual column in the underlying table (flt_randomcustom in this case).  And, just to make things confusing, the name of that column is “name”.

To make it actually work you have to go to the Admin menu, click on Repair, and then click on “Quick Repair and Rebuild”.

 

When you reload http://{my instance}/#flt_RandomCustom and click on the filter pulldown you should see something like this:

When you click on the new filter you should see only the row with “Blorg” as the name appear.  My friend, you’ve just created your first custom filter in SugarCRM 7.  Savor the moment.

Now, if you don’t want a reputation for shoddy work, you’re going to want to make that label look like something real.  To do that we have to set up a string for it.  To do that we create another PHP file:

/var/www/sugarcrm/custom/Extension/modules/flt_RandomCustom/Ext/Language/en_us.RCFilt.php

This filename has to start with “en_us.” and end with ".php" but it can be named anything within that.  The contents of that file should be something like:

<?php

 

$mod_strings['LBL_BLORG_ONLY_FILTER'] = 'Only Blorg Rows';

 

Once you have that file in place, do the “Quick Repair and Rebuild” again.  Reloading the page and clicking on the filter pulldown should now look like this:

And there you have it, a simple custom filter users can employ within a custom module.

 

Making more complex filters

Filters which restrict rows to those matching a single value are all fine and good, you say, but not much use in the real world.  Reality demands much more complex criteria for filters.  I completely agree.  Let’s see if we can construct a less trivial filter.

 

Constructing a complex filter is exactly the same as the simple filter above except that the "filter_definition" array contents are more complicated.

 

Here’s where knowledge of SQL comes in handy.  The way that the filter_definition array works is not unlike the WHERE clause of a SELECT statement.  Let’s consider this example:

'filter_definition' => array(

 

    array(

 

        'random_number_c' => '17',

 

        'date_entered' => '2014-01-22 13:21:01',

 

    ),

 

),

By default, things that are grouped together within arrays have an implied AND relationship.  In this case the filter will only show rows with a random number of 17 and the given date_entered value.  By the way, did you notice that the random number field ends with “_c”?  That’s right, you can specify custom fields in these filters as well.  They just get treated like the columns of the main table.

Okay, that’s slightly cooler, but how would you do an OR instead of an AND?

The filter mechanism has these command directives which start with $ that you can use.  In this case we want to make use of the “$or” directive:

'filter_definition' => array(

 

    array(

 

        '$or' => array(

 

            array('random_number_c' => '17'),

 

            array('random_number_c' => '827'),

 

        ),

 

    ),

 

),

This array groups together two conditions with the $or directive which means that rows with either value for that column should be shown.  You’ve probably noticed that the two conditions need to be wrapped in arrays of their own within the $or array.  That’s important.  Bad stuff happens if you forget to do that.  Also, don’t make the mistake of using double-quotes or PHP will try to interpret the $or as a variable and fail.

So now let’s try to construct a really complex filter.

Let’s say you need to create a filter that’s the equivalent of this WHERE clause:

WHERE

 

(

 

    date_entered = "2014-03-01 19:54:47"

 

    OR (

 

        random_number_c > 0

 

        AND random_number_c < 40

 

    )

 

)

 

AND (

 

    name LIKE “A%"

 

    OR name LIKE "B%"

 

    OR name LIKE "R%"

 

)

Wow.  Okay, so it’s kind of tough to just rattle off the PHP necessary to do this, so let’s break it down logically.  First, we have two big conditions AND’ed together which implies:

'filter_definition' => array(

 

    array(

 

        //The thing with ‘date_entered’ and other stuff

 

    ),

 

    array(

 

        //The thing with the three ‘name’ conditions

 

    ),

 

),

 

The second condition is a little more straightforward, so let’s handle that one first.  To reproduce the behavior with the LIKE’s above, SugarCRM filters have a $starts directive which tests for whether the value of a string starts with the given value.  So we would use it as follows:

'filter_definition' => array(

 

    array(

 

        //The thing with the ‘date_entered’ and other stuff

 

    ),

 

    array(

 

        '$or' => array(

 

            array(

 

                'name' => array(

 

                    '$starts' => 'A',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'B',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'R',

 

                ),

 

            ),

 

        ),

 

    ),

 

),

While that may look intimidating, actually it makes sense.  It’s just three $starts conditions OR’ed together.

Now let’s attack the first bit.  What if all we had to do was do the two tests for the random number?  It would look like this:

'filter_definition' => array(

 

    array(

 

        array(

 

            'random_number_c' => array(

 

                '$lt' => '40',

 

            ),

 

        ),

 

        array(

 

            'random_number_c' => array(

 

                '$gt' => '0',

 

            ),

 

        ),

 

    ),

 

    array(

 

        //The thing with the three ‘name’ conditions

 

    ),

 

),

That’s not too bad, right?  Now let’s OR in the date_entered test:

'filter_definition' => array(

 

    array(

 

        '$or' => array(

 

            array(

 

                '$and' => array(

 

                    array(

 

                        'random_number_c' => array(

 

                            '$lt' => '40'

 

                        ),

 

                    ),

 

                    array(

 

                        'random_number_c' => array(

 

                            '$gt' => '0'

 

                        ),

 

                    ),

 

                ),

 

            ),

 

            array(

 

                'date_entered' => '2014-03-01 19:54:47'

 

            ),

 

        ),

 

    ),

 

    array(

 

        //The thing with the three ‘name’ conditions

 

    ),

 

),

Okay, I admit it, that’s a little weird.  You have to explicitly use the $and directive because in this case it gets confused if you leave the AND implied as before.  So let’s put the whole filter together in all its glory:

'filter_definition' => array(

 

    array(

 

        '$or' => array(

 

            array(

 

                '$and' => array(

 

                    array(

 

                        'random_number_c' => array(

 

                            '$lt' => '40'

 

                        ),

 

                    ),

 

                    array(

 

                        'random_number_c' => array(

 

                            '$gt' => '0'

 

                        ),

 

                    ),

 

                ),

 

            ),

 

            array(

 

                'date_entered' => '2014-03-01 19:54:47'

 

            ),

 

        ),

 

    ),

 

    array(

 

        '$or' => array(

 

            array(

 

                'name' => array(

 

                    '$starts' => 'A',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'B',

 

                ),

 

            ),

 

            array(

 

                'name' => array(

 

                    '$starts' => 'R',

 

                ),

 

            ),

 

        ),

 

    ),

 

),

Sadly, the filter mechanism lacks some of the features of an SQL WHERE clause such as NOT, a true LIKE, and a few other things.  But you can handle most conditions with filters.

For a complete list of directives and additional documentation you can go to:

http://{your instance}/rest/v10/help

Search for "/<module>/filter" next to "GET" and click on it.

 

Making your filter the default filter

Normally with a layout that includes the filter pulldown the filter that shows all rows is the default.  This is usually fine, but sometimes it’s not ideal.  It’s pretty easy to set a custom filter as the default filter for a module.

To do this you need to create a "default/" directory in the same "filters/" directory as your other custom filters.  In this directory you create a "default.php" file.  But instead of the filter code you saw above, the file would instead contain something like this:

<?php

 

$module_name = 'flt_RandomCustom';

 

$viewdefs[$module_name]['base']['filter']['default'] = array(

 

    'default_filter' => 'blorgonly',

 

);

As I imagine you figured out, this will set the default filter to the blorgonly filter we created earlier.  To see this work you sometimes have to clear your browser cache completely after doing the repair and rebuild, but before reloading the page.

 

Debugging techniques

Remember the horrible complex filter we saw above?  It was as ugly to write as it is to read.  Developing it took a lot of iterations.  There aren’t a lot of tools which will help you figure things out, but there are a few.

 

Examining the API Call

When SugarCRM 7 tries to apply a filter it makes a GET call to the appropriate filter API.  If you open the JavaScript console on your browser (or Firebug, or whatever your tool of choice is) you can see the API call go by along with any errors.  It can sometimes be helpful to pull out the URL it calls and examine it to see what it might be trying to do.

Here’s the URL for the API call for the big ugly filter (with the various entities replaced with their original characters, of course):

http://{whatever+your+intance+is}/rest/v10/flt_RandomCustom/filter?fields=name,random_number_c,date_entered,my_favorite&max_num=20&order_by=date_modified:desc&filter[0][$or][0][$and][0][random_number_c][$lt]=40&filter[0][$or][0][$and][1][random_number_c][$gt]=0&filter[0][$or][1][date_entered]=2014-03-01+19:54:47&filter[1][$or][0][name][$starts]=A&filter[1][$or][1][name][$starts]=B&filter[1][$or][2][name][$starts]=R

Yeah, yikes.  But, you can ignore everything up to the first &filter parameter which leaves:

&filter[0][$or][0][$and][0][random_number_c][$lt]=40&filter[0][$or][0][$and][1][random_number_c][$gt]=0&filter[0][$or][1][date_entered]=2014-03-01+19:54:47&filter[1][$or][0][name][$starts]=A&filter[1][$or][1][name][$starts]=B&filter[1][$or][2][name][$starts]=R

That’s still not wonderful, but if you split it out by parameter, you get:

&filter[0][$or][0][$and][0][random_number_c][$lt]=40

 

&filter[0][$or][0][$and][1][random_number_c][$gt]=0

 

&filter[0][$or][1][date_entered]=2014-03-01+19:54:47

 

&filter[1][$or][0][name][$starts]=A

 

&filter[1][$or][1][name][$starts]=B

 

&filter[1][$or][2][name][$starts]=R

Which actually starts to make some sense, sort of.  Note, for example, how there are two main arrays, just like the arrays we have in our filter_definition array.

Watching how these parameters change as you change your filter code can shed light on how you need to arrange your arrays and directives.  It’s not a slam dunk by any means, but it can help when you’re stuck.  You’ll often see things grouped together in ways you didn’t expect which can point to a problem.

 

Actually calling the API

Sometimes it’s helpful to actually make the API call that SugarCRM makes so you can see everything in detail including any errors, warnings, and any returned rows.  You can also mess with the parameters directly to see what effect that has on the results.  Fortunately it’s not that hard to do.  Here’s what you need to do:

Bring up your favorite utility for making API calls.  For example, POSTman in Chrome, or RESTClient in Firefox are good choices.  For the purposes of this explanation we’ll use POSTman in Chrome, but the basic ideas are the same regardless of the tool you choose.

Bring up the JavaScript console in Chrome, select the “Network” tab, and reload your page with your filter selected.  You should see a bunch of calls go by.  Once the page is loaded, from the bottom up, hover over each call until you find a one resembling:

http://{your instance}/rest/v10/{your module name}/filter?...

That’s the API call that Sugar makes to apply the filter to the rows.

 

Click on that entry and make sure the “Headers” tab is selected.  This should show you, among many other things, the OAuth-Token.  Save that string.

Copy the “Request URL” into the url line for POSTman, set the method to GET, and add OAuth-Token to the header with a value of the string you just saved.

When you hit “Send” you should get a result with a return status of 200 and a “records” array appropriate for your filter.

 

This too isn’t necessarily a slam dunk in terms of debugging, but sometimes you’ll see errors or other things which may point to the problem.  Also you can manually change the parameters to experiment with different things to try to solve the problem.

Post originally written by sugarraagaard.

 

So you've seen what the REST API can do and you want more. In this recipe we'll be covering how to create your own REST endpoint.

 

Our sample endpoint is going to go beyond the filter API and give us a list of accounts ordered by the number of open cases.

 

1. Deciding on an endpoint location

 

This is just about the most important step in adding a new endpoint. The URL of an endpoint should be created using RESTful ideas and placed in the virtual directory tree in a way that will make it easy for developers too see the original intent of this API.

 

Since this endpoint is just going to be reading data let's make it a "GET" request.

 

Because we are dealing primarily with account data we'll throw it in "/Accounts".

 

To finish it off we'll name the endpoint "at_risk".

 

So with it all together our endpoint is "GET /Accounts/at_risk", now we could have our URL set to anything else but with a descriptive name and using the correct HTTP verb of GET it will help any other developers coming across calls to this endpoint to better understand what we are asking for.

 

2. Creating the endpoint class

 

The REST service looks in a lot of locations for endpoint classes:

  • clients/:platform/api/*
  • modules/:module/clients/:platform/api/*
  • custom/clients/:platform/api/*
  • custom/modules/:module/clients/:platform/api/*

 

Since we are adding a custom accounts endpoint we'll create a new class "AtRiskApi" and put it in the file "custom/modules/Accounts/clients/base/api/AtRiskApi.php". It is important to name the file so that it is the same as the class name except with .php at the end otherwise the REST service won't find our class.

 

To get this class so it is listening for a specific URL we need to add a function ->registerApiRest(). We are setting the path to array('Accounts', 'at_risk') and set the pathVars to array('', '') to represent "Accounts/at_risk" and not bring any part of the URL in to our array of arguments. If we wanted to match against a wildcard to look at the at risk profile for a single account record for example we could add a path of array('Accounts', '?', 'at_risk') and a pathVars of array('', 'id', '') which would let us listen for "Accounts/*/at_risk" and would take the second part of the path and populate the id element of our arguments with it.

 

Next we will actually add a function, setting the method in our register array to getAtRisk lets the REST API know to call that method in this class. We'll keep this method simple for now and just have it return 'burgers' just so we can tell it is working right away. These methods just need to return data and the REST API will take care of all the json encoding for you.

 

Finally we add a little line in the shortHelp giving a quick description of what this endpoint is for. We're leaving the longHelp out of this little demo but if you are building endpoints for real be sure to add some detailed documents there.

 

So, after everything is all said and done, here's what our little class looks like:

 

AtRiskApi.php

<?php

class AtRiskApi extends SugarApi
{
// This function is only called whenever the rest service cache file is deleted.
// This shoud return an array of arrays that define how different paths map to different functions
public function registerApiRest() {
return array(
'getAtRisk' => array(
// What type of HTTP request to match against, we support GET/PUT/POST/DELETE
'reqType' => 'GET',
// This is the path you are hoping to match, it also accepts wildcards of ? and <module>
'path' => array('Accounts', 'at_risk'),
// These take elements from the path and use them to populate $args
'pathVars' => array('', ''),
// This is the method name in this class that the url maps to
'method' => 'getAtRisk',
// The shortHelp is vital, without it you will not see your endpoint in the /help
'shortHelp' => 'Lists at risk accounts in the system',
// The longHelp points to an HTML file and will be there on /help for people to expand and show
'longHelp' => '',
            ),
        );
    }

function getAtRisk($api, $args)
    {
// Start off with something simple so we can verify the endpoint is registered.
return 'burgers';
    }
}

 

3. Taking it for a test drive

 

Let's do a GET request for /rest/v10/Accounts/at_riskcurl -X GET -H OAuth-Token:some-token http://localhost/burgers/rest/v10/Accounts/at_risk

 

And here is what we get back:

 

response.json

{
"error": "not_found",
"error_message": "Could not find record: at_risk in module: Accounts"
}

 

Hey, what gives? First things first let's check to see if it registered correctly by looking for the endpoint definition in /help, navigate over to /rest/v10/help in your browser and look for it. Not there? didn't think so.

 

We added the class and it didn't load. Since the REST API looks for so many files in so many directories we have to add some heavy duty caching in order to speed up the url lookup on every single request. In order for changes in endpoint definitions to show up we need to login as an admin and run quick repair and rebuild.

 

After quick repair, let's check /rest/v10/help again and you should see a line like this:



 

So let's try that request again.curl -X GET -H OAuth-Token:some-token http://localhost/burgers/rest/v10/Accounts/at_risk

 

Now we get back the correct response:

 

response.json

"burgers"

 

4. Fetching the data

 

While having a new URL that says "burgers" is pretty fancy I think we can accomplish more. While there are many ways to fetch and return this data I want to show you the preferred way to do it in Sugar 7.

 

First things first we need to start off by using SugarQuery. Let's get a seed bean going by fetching a new bean from the bean factory. We pass that through to the SugarQuery ->from() method to let SugarQuery know we will be querying off of the Accounts module. We'll limit our result set to just ID's by adding ->select('id') and then limit our rows to just the first five by adding ->limit(3). From here we can just have it return the results of the ->execute() call and see what that gets us.

 

Now our getAtRisk function looks like this:

 

getAtRisk.php

<?php
function getAtRisk($api, $args)
{
$seed = BeanFactory::newBean('Accounts');

$q = new SugarQuery();
// Set from to the bean first so SugarQuery can figure out joins and fields on the first try
$q->from($seed);
// Adding the ID field so we can validate the results from the select
$q->select('id');
$q->limit(5);

// Return the raw SQL results through the API
return $q->execute();
}

 

and when we make that same GET request to Accounts/at_risk we get back:

 

response.json

[
    {
"id": "160a729d-f808-a046-e901-5315ffa4aa2a"
    },
    {
"id": "2b0f91be-4392-8de5-896e-5315ffc814da"
    },
    {
"id": "2e890e9a-ca34-6b00-860a-5315ffdb0f71"
    }
]

 

Okay so now we have some simple SQL being run and are returning the result set. How about we add some more complex logic here so we actually fetch the results we want. To start things off let's join in the cases by adding this "$caseAlias = $q->join('cases')->joinName();". It's nice that we just need to use the link field to add a join and everything else is handled by SugarQuery. SugarQuery also understands that we have to go beyond it's abilities every once in a while, so we need to add a ->fieldRaw() call to fetch the count and then an ->orderByRaw() to properly sort them. We have to use the Raw versions of the functions because neither of those columns are defined in the field_defs for the modules. The ->groupBy() call just needs to group by the account ID so that is simple. Finally the ->where()->notIn() is there so we only fetch related cases that aren't resolved, no need to quote elements here because SugarQuery will handle that for us.

 

Added all together it looks like this:

 

GetAtRisk.php

<?php
function getAtRisk($api, $args)
{
$seed = BeanFactory::newBean('Accounts');

$q = new SugarQuery();
// Set from to the bean first so SugarQuery can figure out joins and fields on the first try
$q->from($seed);
// We need to save the alias of any join because they are auto-generated (and different if you are on some databases)
$caseAlias = $q->join('cases')->joinName();
// Adding the ID field so we can validate the results from the select
$q->select('id');
// fieldRaw will let us run raw SQL in the select, be sure to quote anything you are getting from users.
// The second argument is the field alias so we can tell what will be coming out of the other end of the query
$q->select->fieldRaw("COUNT(".$caseAlias.".id)","case_count");
// We need to use orderByRaw here instead of just orderBy because case_count isn't part of any vardefs
$q->orderByRaw('case_count');
$q->groupBy('id');
// the ->where() gives us a where object, there are a lot of operators that work on that (including ->and() and ->or())
$q->where()->notIn($caseAlias.'.status', array('Closed', 'Rejected'));
$q->limit(3);

return $q->execute();
}

 

Once again let's hit Accounts/at_risk and see what we get:

 

response.json

[
    {
"id": "f4225353-54a0-502e-409a-5315ffa656ab",
"case_count": "5"
    },
    {
"id": "c4024e67-dee3-553d-0040-5315ff8f2fd1",
"case_count": "5"
    },
    {
"id": "ab6b6784-38cc-077a-fda5-5315ff228fcf",
"case_count": "4"
    }
]

 

Looking good! Now we are getting the data we need how about we make it look nice for the javascript client that needs it?

 

5. Formatting the data

 

To format the data first we have to figure out what to format. Most endpoints accept the argument fields to specify which fields they want returned from the api and we'll keep up that tradition here with some simple parsing of the $args array.

 

Next up we want to convert the results of the query into beans so they can be properly formatted. Previously you would have to perform the PHP equivalent of banging two rocks together to make fire by manually fetching the rows and creating beans and populating them via ->populateFromRow(). Fortunately we are introducing a helper function in SugarBean named ->fetchFromQuery() to help automate and centralize this process, so we'll just call that here. We need to pass ->fetchFromQuery() the option of returnRawRows because we need to populate the case_count field manually because it doesn't exist in any of the field definitions.

 

With the beans returned from ->fetchFromQuery() we strip out the raw rows from the result set and then pass the remaining beans through to ->formatBeans() so that our returned results look the same as every single other API call. After we get the results back as an array from ->formatBeans() we loop through the results and jam the case_count in there.

 

So with all that, here's what our final method looks like:

 

getAtRisk.php

<?php
function getAtRisk($api, $args)
{
$seed = BeanFactory::newBean('Accounts');

$q = new SugarQuery();
// Set from to the bean first so SugarQuery can figure out joins and fields on the first try
$q->from($seed);
// We need to save the alias of any join because they are auto-generated (and different if you are on some databases)
$caseAlias = $q->join('cases')->joinName();
// Adding the ID field so we can validate the results from the select
$q->select('id');
// fieldRaw will let us run raw SQL in the select, be sure to quote anything are getting from users.
// The second argument is the field alias so we can tell what it will be coming out the other end of the query
$q->select->fieldRaw("COUNT(".$caseAlias.".id)","case_count");
// We need to use orderByRaw here instead of just orderBy because case_count isn't part of any vardefs
$q->orderByRaw('case_count');
$q->groupBy('id');
// the ->where() gives us a where object, there are a lot of operators that work on that (including ->and() and ->or())
$q->where()->notIn($caseAlias.'.status', array('Closed', 'Rejected'));
$q->limit(3);

// Let's parse the field array like formatBeans down below
if (empty($args['fields'])) {
$args['fields'] = array();
    } else if (!is_array($args['fields'])) {
$args['fields'] = explode(',', $args['fields']);
    }

// Run the new ->fetchFromQuery() call to get beans out of a query, get the raw rows for non-vardef fields
$accountBeans = $seed->fetchFromQuery($q, $args['fields'], array('returnRawRows' => true));

// The normal beans are in there by id, the raw rows are returned in their own element
// Let's strip that out so we don't try to apply sugarbean code to it.
$rows = $accountBeans['_rows'];
unset($accountBeans['_rows']);

// Part of SugarApi, this will format our list of beans like all of the rest of the API's
// Consistency is good
$accounts = $this->formatBeans($api, $args, $accountBeans);

// Since case_count isn't part of the vardefs, we have to populate it manually
foreach ($accounts as &$account) {
$account['case_count'] = (int)$rows[$account['id']]['case_count'];
    }

return $accounts;
}

 

And when we finally call the Accounts/at_risk, here is what we get:curl -X GET -H OAuth-Token:some-token http://localhost/burgers/rest/v10/Accounts/at_risk?fields=id,name,date_modified

 

response.json

[
    {
"id": "f4225353-54a0-502e-409a-5315ffa656ab",
"name": "A Fridge Too Far",
"date_modified": "2014-03-04T16:28:59+00:00",
"_acl": {
"fields": {}
        },
"_module": "Accounts",
"case_count": "5"
    },
    {
"id": "c4024e67-dee3-553d-0040-5315ff8f2fd1",
"name": "That's Improv-able",
"date_modified": "2014-03-04T16:28:59+00:00",
"_acl": {
"fields": {}
        },
"_module": "Accounts",
"case_count": "5"
    },
    {
"id": "ab6b6784-38cc-077a-fda5-5315ff228fcf",
"name": "Annie Get Your Gum",
"date_modified": "2014-03-04T16:28:59+00:00",
"_acl": {
"fields": {}
        },
"_module": "Accounts",
"case_count": "5"
    }
]

 

6. All done!

 

That's all, I hope this clears how to add your own endpoint to Sugar 7. Along with some helpful tips on how to use a combination of SugarQuery, ->fetchFromQuery() and ->formatBeans() to create easy and standardized code for returning data from the API. Add a comment if you have questions.

Post originally written by sugarmajed.

 

We are pleased to announce the return of UnCon!

 

Sugar is more than just a CRM – it is also a platform where developers can build all sorts of custom widgets and integrations to popular 3rd party applications and APIs. Join us at UnCon to learn how sweet it can be to build integrations and widgets in Sugar!

 

UnCon is a great chance for novice and skilled Sugar developers to hack side-by-side with our core engineers.

 

We'll be giving you a crash course in building integrations, and then we will have our developers join forces with you to make awesome happen in a two day hackathon!

 

This year the core engineering team will be hosting the event on Tuesday, April 29th and Wednesday, April, 30th.

 

You can attend UnCon with a General Conference pass to SugarCon for $799 — or skip the conference and attend only UnCon for $99.Click Here to Visit the official UnCon Website