Non-db Field via SQL Query

[HartleyH]

I’m relatively new to SuiteCRM and trying to figure out if something is possible and if so, how to do it. In the system I’m developing, I have added a module called “Hospitals” similar to Accounts that is related to Calls. I’ve been asked to add a field to the Calls module called “Significant Contact”. Users will check that based on whatever criteria the project staff comes up with. All that is simple enough.

The next piece, however, is to display on the Hospital detail view the date of the most recent significant contact for that hospital. Basically, I need to get the max(date_start) from the calls table where calls_cstm.significant_contact_c = 1 for the particular hospital. I’ve found some code that looks similar to what I want to do, which is create a non-db field (i.e. most_recent_sig_contact) with the SQL necessary to get the appropriate value. What I’m not entirely sure of is where this code goes in my custom module and what, exactly, it needs to look like.

If anyone can provide an example of this or give me some pointers to this sort of thing in the docs, I would be much obliged.

[govind]

Hello,
I think you should make a code in view.detail.php file. First get your expected result from query you used in your above comment.Here is some code sample which may help you.

1. view.detail.php in custom directory of your custom module.

In display function.

// make a code to get your result
$query = “Your query should go here”;
$date = $db->fetchByAssoc($db->query($query));
$maxDate = $date[‘date start’];

2. Then Assign this field like below
   $this->dv->ss->assign(‘maxDate’, $maxDate);
   // Assign this before parent display call.

3. Use this in detailviewdefs.php in custom
   =>
   array(
   ‘name’ => ‘your nondb field’,
   ‘label’ => ‘LBL_Lable’,
   ‘customCode’ => ‘{$maxDate}’
   ),

Hope this will you.
Thanks.

[HartleyH]

Thanks. I think I’m getting close and this is very helpful.

One question I have is about the view.detail.php. That file did not already exist. I assumed I need to make one. I copied one from another module putting it in modules/hosp_Hospital/view/view.detail.php. Is that the right place for it? Then I edited it, removing everything except the __construct() and display() functions and changed the names to use “hosp_Hospital” in place of the names that were there. I added your four lines to the display function, with the only other line in that function being “parent::display()” at the end. Does that sound right?

The field is being displayed with the label I assigned but it’s not being populated, so clearly I’ve done something wrong. It isn’t clear to me that the view.detail.php file that I created is actually being run or loaded. Do I need to do something so that it will be?

This will probably be obvious to others but isn’t to me: Should I create a field first, and then assign it this value or just modify the code without creating a field through any other means?

[govind]

Hello there,

One question I have is about the view.detail.php. That file did not already exist. I assumed I need to make one. I copied one from another module putting it in modules/hosp_Hospital/view/view.detail.php. Is that the right place for it?

– Yes because this is your own module so this can be right place but in SuiteCRM standard method is to put this in custom dir. but that’s ok.As I assumed your module name is “hosp_Hospital” I have make a code sample for it. Just copy and paste into your view.detail.php and put your sql query into right place.

require_once(‘include/MVC/View/views/view.detail.php’);

class hosp_HospitalViewDetail extends ViewDetail {

function display() {
    global $db;

    // make a code to get your result
    $query = "Your query should go here";
    $runQ = $db->query($query);
    $date = $db->fetchByAssoc($runQ);
    $maxDate = $date;
    $this->dv->ss->assign('maxDate', $maxDate);
    parent::display();
}

}

And about non-db field if you have not created then please first create.rest please follow my recent post.

If still getting any doubts you can share your view.detail.php and detailviewdefs.php file.So I will make changes in that.

Thanks.

[HartleyH]

Once again, thank you. The code you just posted is pretty close to what I had. The only significant difference is that I hadn’t included the “require_once” line. The bigger error, however, turns out to be where I put the file. I had this in a sub-directory called “view” but it was supposed to be “views” (plural). Once I fixed that, the code actually ran. Thank you very much for your time and explanation.