Customizing the query used for a subpanel

bsoremsugar —  October 8, 2012 — 12 Comments

Sometimes you need to do a bit of magic to to pull together the right query to get the subpanel data you need. I’ve seen the question come up on the forums, and found a nice solution proposed by poster erop. There was a small issue of it not being upgrade-safe, so I took that solution and tweaked it for that.

First off, there’s the entry in the subpaneldefs, which has specific parts it in called out in the comments to indicate that we’ll be using a custom function to get the data we need.

Now comes the function itself, which thanks to the Ext Framework can be dropped in any .php file in the custom/Extension/application/Ext/Utils/ directory. Note the one thing you’ll want to do is get a reference to the current $bean object, which is easy to access thru the global scope as noted in the example below.

If you are on a version of Sugar prior to 6.3, you will need to drop the function in the custom/include/custom_utils.php file and change the above file reference to this file location as well.

With a Quick Rebuild and Repair, you’ll be up and going with your custom query.

12 responses to Customizing the query used for a subpanel

  1. 

    Nice solution, i want to create a subpanel for related Emails, i don’t know how to to this, if i use title_key ‘LBL_EMAILS_SUBPANEL_TITLE’ it doesn’t work.

  2. 

    The GitHub code for your subpanel definition has a filename of custom/Extension/application/Ext/Utils/ directory and your description of the file says custom/application/Ext/Utils/ directory. Notice the missing “Extension/” subfolder.

    Thanks for the post and the help. I was struggling with this issue for a while before finding this post.

  3. 

    Another thing that might help others…..I used the Reports module to create the necessary query to put into the $return_array. My query happened to be a bit more complex as I was pulling only certain grandchildren relating to my custom module. I created the report and chose “Show Query” to get the necessary SQL. Sugar rocks.

  4. 

    How I can add a groupby clause?

  5. 

    I need to use “WHERE” clause in sub-select, like that
    “…NOT IN ( SELECT meeting_id FROM meetings_leads WHERE column = value)…”
    but I getting error, because “WHERE” word removing from query. Any ideas?

  6. 
    Janusz Witrykus March 9, 2013 at 6:10 am

    How to add field from related table to be displayed?
    $return_array['select']=’SELECT meetings.id, related ‘;

    doesn’t work

  7. 

    A couple of questions:
    1. Will this work in Sugar 7? (If so, where do I find the correct files?)
    2. If I am joining tables, how does that syntax go?
    Thanks for any help.

Trackbacks and Pingbacks:

  1. Calls do not display on Accounts in certain circumstances - March 27, 2013

    [...] [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s