Technology Advisors is working on cleaning up our internal SugarCRM instance and I was tasked with finding a way to remove all orphaned records so they can be purged. An orphaned record is a record that is not related to a parent record. For example, a Call that is not related to an Account, Contact, Opportunity or Case. This is simple enough to do with a SQL statement in an on-premise instance, but I wanted a way to easily do this in an ondemand instance. I could have created a Scheduler that would mark orphaned records as orphaned or even delete them outright but I wanted to try something new so I created a Custom REST Endpoint that will return the ID of all orphaned Calls. I can then use Starfish ETL to delete or update the records as needed.

I was surprised by how easy it is to create a custom endpoint. All it takes is a single file you place in the right spot in the custom directory and a Quick Repair. The instructions for this are found on Sugar's SugarCRM Cookbook. See this page on creating a custom endpoint. In order to run the query, I created a SugarQuery. What is SugarQuery? SugarQuery is a SQL query builder for retrieving data directly from the database. It is used extensively within the core of the application. For instance, the FilterAPI uses it. It uses a bean, the beans relationships, and visibility models to build a SQL query that can be used to retrieve data. See this page on creating a SugarQuery.

The end result can be found here:

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 'path' => array('Calls', 'orphaned_calls'), // 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' => 'getOrphanedCalls', // The shortHelp is vital, without it you will not see your endpoint in the /help 'shortHelp' => 'Lists all orphaned Calls in the system', // The longHelp points to an HTML file and will be there on /help for people to expand and show 'longHelp' => 'Returns the IDs of all orphaned Calls in the system. Orphaned calls are calls with no related Lead, Contact or Parent ID.', ), ); } function getOrphanedCalls($api, $args) { $seed = BeanFactory::newBean('Calls'); $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->whereRaw("not exists (select * from calls_contacts cc where calls.id = cc.call_id) and not exists (select * from calls_leads cl where calls.id = cl.call_id) and (calls.parent_id is null or calls.parent_id = '')"); //$GLOBALS['log']->fatal("q: " . $q->compileSql()); // 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 $callBeans = $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 = $callBeans['_rows']; unset($callBeans['_rows']); //In this case, we saved the raw rows data in $rows, but we are not using it. //If we had needed some data from the raw row data, we could have used it now. // Part of SugarApi, this will format our list of beans like all of the rest of the API's // Consistency is good $calls = $this->formatBeans($api, $args, $callBeans); return $calls; } }
Posted in:

Looking for SugarCRM help?

We do training, customization, integration, and much more. Contact us today.