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

#1539 - Sorting of line items

Closed General Question created by WSiedler Verified Purchase 4 years ago

We are using SuiteCRM Version 7.8.10 with MailMergeReports 2.5.12 for creating documents in modules quotes and invoices in German language. In some cases, the sequence of line items is not the same as in SuiteCRM dialogue. It is very important for us that MMR uses the same sequence as it is in SuiteCRM.

This is the sequence in SuiteCRM dialogue: Error_20_1.jpg

This is the sequence in MMR document. The third item of Suite CRM is the first item of MMR. Fourth item SuiteCRM = Second item MMR. First item SuiteCRM = Third item MMR. Second item SuiteCMR = Fourth item MMR: Error_20_2.jpg

Please provide a correction so that the same sequence is used in SuiteCRM and MMR.

Regards, Wolf

  1. dharma member avatar

    Izertis Provider

    4 years ago

    Hi,

    MMR does not control the order in which the data is presented, it is not a bug. There can be multiple circumstances to the order in which the data needs to be presented. To be able to sort the data you have to do it by code, extending the class DHA_DocumentTemplatesCalculatedFields.

    You have two examples in custom/modules/Opportunities/DHA_DocumentTemplatesCalculatedFields.php (using calculated fields): - function OrderRows (event) - Order main module rows example - function BeforeMergeBlock (event) - Order rows example for submodule data

    For this specific case, if you want to sort by line number, follow these steps: - Create a file called 'DHA_DocumentTemplatesCalculatedFields.php' in the '/custom/modules/AOS_Quotes' folder of SuiteCRM. - If necessary, assign the required permissions and owner to that file so that it does not fail when called from the server. - Copy the following code into the file and save:

    <?php
    if(!defined(&#039;sugarEntry&#039;) || !sugarEntry) die(&#039;Not A Valid Entry Point&#039;);
    
    require_once(&#039;modules/DHA_PlantillasDocumentos/DHA_DocumentTemplatesCalculatedFields_base.php&#039;);
    
    class CustomAOS_Quotes_DocumentTemplatesCalculatedFields extends DHA_DocumentTemplatesCalculatedFields {
    
       ///////////////////////////////////////////////////////////////////////////////////////////////////
       function __construct($module, $bean) {
          parent::__construct($module, $bean);
       }
    
       ///////////////////////////////////////////////////////////////////////////////////////////////////
       function BeforeMergeBlock() {
          global $db;
    
          // Order products_quotes submodule
    
          if ($this->inMainModule() && !empty($this->Generate_Document_Instance->datos)) {
             foreach ($this->Generate_Document_Instance->datos as $main_key => $main_data) {
                if (isset($main_data[&#039;aos_products_quotes&#039;]) && is_array($main_data[&#039;aos_products_quotes&#039;]) && count($main_data[&#039;aos_products_quotes&#039;]) > 1) {
                   $data_array = $main_data[&#039;aos_products_quotes&#039;];
                   $data_array_ids = array();
                   $data_array_keys = array();
                   $data_array_ordered_keys = array();
    
                   foreach ($data_array as $key => $data) {
                      $data_array_ids[] = $data[&#039;id&#039;];
                      $data_array_keys[$data[&#039;id&#039;]] = $key;
                   }
    
                   $ids_condition = implode ("&#039;,&#039;", $data_array_ids);
                   $ids_condition = "&#039;".$ids_condition."&#039;";
                   $sql = "select id from aos_products_quotes where id in ({$ids_condition}) order by number desc ";
                   $dataset = $db->query($sql);
                   $i = 0;
                   while ($row = $db->fetchByAssoc($dataset)) {
                      $data_array_ordered_keys[$row[&#039;id&#039;]] = $i;
                      $i += 1;
                   }
    
                   $this->Generate_Document_Instance->datos[$main_key][&#039;aos_products_quotes&#039;] = array();  // empty array
                   foreach ($data_array_ordered_keys as $id => $data) {
                      $this->Generate_Document_Instance->datos[$main_key][&#039;aos_products_quotes&#039;][] = $data_array[$data_array_keys[$id]];
                   }
                   unset($data_array);
                }
             }
          }
       }
    
    }
    
    ?>
    

    Regards

  2. WSiedler member avatar

    WSiedler Verified Purchase

    4 years ago

    Dear Izertis team,

    Thank you! It works now, but is certainly not that easy to discover for a beginning user. I recommend adding this to some sort of FAQ.

    I had to apply one minor adjustment: Your code sorted the report's product lines in the right sequence, but reversed order. Changing string "desc" in the SQL query line to "asc" changed that in the desired way.

    Regards, Wolf

  3. Madmart member avatar

    Madmart

    a year ago

    There is no need to hit the database again, very inefficient. what you really want to do is just sort the $data_array variable and add that back to the document generator instance

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...
Rating
  • "It has improved our processes so much! Admin cut in half! We send a webform to clients to sign up straight into CRM then generate and print their paperwork to sign."

    Read More Reviews