by Izertis

Create documents in docx, xlsx (MS Office Word and Excel), odt, ods (LibreOffice and OpenOffice) and pdf format without MS Office or others plugins installed from any module and subpanel using templates.

Includes a 30 day guarantee
Try it Now

By clicking you consent to be contacted by the developer

#832 - Filter subpanel records by condition - how to set?

Closed Feature created by kir 7 years ago

Hi Is it possible to filter subpanel records by condition (example: "status" of record wich is dropdown value)? Then sort selected records by some field And then generate document

Thanks

  1. dharma member avatar

    Izertis Provider

    7 years ago

    Please see custom/modules/Opportunities/DHA_DocumentTemplatesCalculatedFields.php example for Calculated Fields - ShowRow event to filter main module data - OrderRows event to order main module data - BeforeMergeBlock event to order and/or filter subpanel data.

    The BeforeMergeBlock event example is only to order data (Opportunities subpanel data in Accounts report), but you can apply any filter you need too, filtering $this->Generate_Document_Instance->datos['main_key_xxxxxxx']['opportunities'] array (in the example).

  2. kir member avatar

    kir

    7 years ago

    hi I tried but no success. Looks like BeforeMergeblock function not work for me... Main module is kir_kp, so custom class is Customkir_kp_DocumentTemplatesCalculatedFields subpanel module is kir_deal_items, so i renamed 'opportunities' into 'kir_deal_items' in BeforeMergeBlock example i want to filter subpanel data by field status_id = 'Active' then i tried several variants, but no success (even added condition directly to sql statement) file with class and function is placed to custom/modules/kir_kp folder (as main module folder) Can you explain how to make it work in my case? MMR 2.2 Thanks in advance

  3. kir member avatar

    kir

    7 years ago

    here is the text of the function

    function BeforeMergeBlock() { // Order rows example for submodule data. In this example we order opportunities related to account (in account report). // Please note that this event for this example must be placed in Accounts calculated fields class, not here // To order rows in MainModule, use OrderRows event (easier to use)

      global $db;
    
    
      if ($this->inMainModule() && !empty($this->Generate_Document_Instance->datos)) {
         foreach ($this->Generate_Document_Instance->datos as $main_key => $main_data) {
            if (isset($main_data['kir_deal_items']) && is_array($main_data['kir_deal_items']) && count($main_data['kir_deal_items']) > 1) {
               $data_array = $main_data['kir_deal_items'];
               $data_array_ids = array();
               $data_array_keys = array();
               $data_array_ordered_keys = array();
    
               foreach ($data_array as $key => $data) {
    

    if($data['status_id'] == 'Active'){ // my condition $data_array_ids[] = $data['id']; $data_array_keys[$data['id']] = $key; } }

               $ids_condition = implode ("','", $data_array_ids);
               $ids_condition = "'".$ids_condition."'";
               $sql = "select id from kir_deal_items where id in ({$ids_condition}) order by date_entered desc";
               $dataset = $db->query($sql);
               $i = 0;
               while ($row = $db->fetchByAssoc($dataset)) {
                  $data_array_ordered_keys[$row['id']] = $i;
                  $i += 1;                  
               }
    
               $this->Generate_Document_Instance->datos[$main_key]['kir_deal_items'] = array();  // empty array
               foreach ($data_array_ordered_keys as $id => $data) {
    
                  $this->Generate_Document_Instance->datos[$main_key]['kir_deal_items'][] = $data_array[$data_array_keys[$id]];
    
               }
               unset($data_array);
            }
         }
      }
    

    }

  4. dharma member avatar

    Izertis Provider

    7 years ago

    We will add the next filter example in next version of MMR (filter Contacts subpanel of Opportunities): https://gist.github.com/anonymous/df03390386ba6c89ebe4

    This example will be placed too in the BeforeMergeBlock event, in custom/modules/Opportunities/DHA_DocumentTemplatesCalculatedFields.php

    You will need to create the next file : "custom/modules/kir_kp/DHA_DocumentTemplatesCalculatedFields.php" Add the next require "require_once('modules/DHA_PlantillasDocumentos/DHA_DocumentTemplatesCalculatedFields_base.php');" Create a Calculated field class with the name "Customkir_kp_DocumentTemplatesCalculatedFields" Create a BeforeMergeBlock event Copy in this event the new example (uncoment first) Replace or remove the "plantilla_id" filter Replace "contacts" with "kir_deal_items" Change the filter condition for ...
    if ($data['status_id'] == 'Active') { $data_array_new[] = $data; } Ensure that filter condition is OK

  5. kir member avatar

    kir

    7 years ago

    hi I did as described, but filter not works. All subpanel records are in the report. What you mean create event BeforeMergeBlock? Function BeforeMergeBlock as in the opportunities example? Thanks

  6. dharma member avatar

    Izertis Provider

    7 years ago

    Yes, create Function BeforeMergeBlock as in the opportunities example

  7. kir member avatar

    kir

    7 years ago

    Hi MMR is updated to 2.4 but no success on sorting subpanel records in generated Document Used code from example from opportunities, changed query_table and field_name in SQL statement ORDER BY also changed opportunities into my child module_name kir_deeal_items File is placed in custom/main_module_folder No success it never goes inside condition if ($this->inMainModule() && !empty($this->Generate_Document_Instance->datos)) Any suggestions?

    • kir member avatar

      kir

      7 years ago

      created new case https://www.sugaroutfitters.com/support/mail-merge-reports/917 and corrected condition in BeforeMergeBlock pointed above

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
  • "The single biggest downfall of the community edition of SugarCRM or SuiteCRM is the lack of creating a report form a custom module for a single event...." - ian

    Read More Reviews