How to populate a PDF Template with data from a Custom Relate field in Line Items

šŸ‘Øā€šŸ’» Development Help
1

Iā€™m currently running SuiteCRM 7.5.1.

Alright, so I should preface that I have made modifications to the ā€˜Serviceā€™ Line Item feature in the Quotes module.

In mine, I changed the ā€˜Service Nameā€™ Textarea field to be a relate field that links to a custom module I created called ā€˜Policiesā€™.

After the painstaking work of modifying all the files required to get this to work, I was able to achieve this. Similar to the Products Line Items, I was hoping to be able to populate the line items with related data from those specific records for each line item. For your knowledge, there is a service_policy_id field in the AOS_Products_Quotes table in the DB that stores the IDā€™s for each record similarly to how the Products link works. If more information on what I changed is needed, I can certainly provide that.

Continuingā€¦

I then went to the PDF Templates module, I created a custom view.edit.php file in custom/modules/AOS_PDF_Templates/views.

I copied the original, but added the following code right after the Service and Currency options are populated around line 188.

//Policies
                $options_array = array(''=>'');
                $policies = new p_policies();
                foreach($policies->field_defs as $line_name => $line_arr){
                    if(!((isset($line_arr['dbType']) && strtolower($line_arr['dbType']) == 'id') || $line_arr['type'] == 'id' || $line_arr['type'] == 'link')){
                        if((!isset($line_arr['reportable']) || $line_arr['reportable']) && $line_arr['vname']  != 'LBL_NAME'){
                            $options_array['$'.$policies->table_name.'_'.$line_name] = translate($line_arr['vname'],$policies->module_dir);
                        }
                    }
                }

                $s_line_module_name = 'p_policies';
                $fmod_options_array[$s_line_module_name] = translate('LBL_LINE_ITEMS','AOS_Quotes').' : '.translate('LBL_POLICIES','p_policies');
                $insert_fields_js2 .="'$s_line_module_name':$options,\n";

So, I checked the Template again, and sure enough my new options were present from the custom module. I went ahead and popped them into the Template, hit save and then headed over the Quotes module.

In the quotes module, I clicked ā€˜Add Service Line Itemā€™, and clicked on my new relate field and entered a few line items from my Custom Policies Module. I then hit save and could see that the data was saved correctly on the DetailView. I clicked ā€˜Print PDFā€™ from the options. However, the policies fields I inserted into the Template did not get populated.

Iā€™m guessing Iā€™m missing a step somewhere, does anyone know where I would go to ensure that the related Policy Record data for each line item can be populated onto the form?

E.G.

I select my relate field and select policy name ā€˜RL51232ā€™, if I were to navigate to that record, I have a field called ā€˜LE_Monthsā€™, if I put ā€˜p_policies_le_monthsā€™ onto the PDF Template for line items, Iā€™m hoping to populate each line item with the respective Field data.

Hope this makes sense, and if anyone needs more information on my custom changes, please tell me.

1 Like
2

Here are some screenshots for reference:

Here is what I see in Quotes Module:

This is what I see on the PDF Templates Screen:

Here is what I see when I actually print the PDF:

3

SOLUTION (THIS IS NOT UPGRADE SAFE, YOU CAN MAKE IT UPGRADE SAFE THOUGH):

After some more tinkering here in the past hour, I came up with the solution:

The fields are actually populated from modules/AOS_PDF_Templates/generatePdf.php.

If you navigate down to the populate_service_lines function, youā€™ll notice that this where the field values are actually populated. So a number of changes have to occur here:

  1. You need to grab the field which is storing the ID of your relate field for each line item. So you can modify lines 48-55 like so:
    Youā€™ll notice, we now select service_policy_id, and we throw that into a variable called $lineItemsPolicies. Its important that you leave the ā€˜[$row[ā€˜idā€™]]ā€™ attached to the array keys for later on.
$lineItems = array();
    $sql = "SELECT pg.id, pg.product_id, pg.group_id, pg.service_policy_id FROM aos_products_quotes pg LEFT JOIN aos_line_item_groups lig ON pg.group_id = lig.id WHERE pg.parent_type = '".$module->object_name."' AND pg.parent_id = '".$module->id."' AND pg.deleted = 0 ORDER BY lig.number ASC, pg.number ASC";
    $res = $module->db->query($sql);
	while($row = $module->db->fetchByAssoc($res)){
            $lineItemsGroups[$row['group_id']][$row['id']]= $row['product_id'];
            $lineItems[$row['id']]= $row['product_id'];
            $lineItemsPolicies[$row['id']]= $row['service_policy_id'];
	}

  1. Now that we have the IDā€™s, we need to pass in this variable to the ā€˜populate_group_linesā€™ function, as well as pass the variable into the populate_service_lines function. I would expect this to be self-explanatory.

  2. Now that we have done that, we need to cycle through the field_defs, find the ā€œ$product_quote = new AOS_Products_Quotes();ā€ line inside the populate_service_lines function. Copy that text along with the foreach statement and put it right below. Youā€™ll only need to make a few adjustments. Here is my code, just replace the module name with yours:

$policy = new p_policies();
    foreach($policy->field_defs as $name => $arr){
        if(!((isset($arr['dbType']) && strtolower($arr['dbType']) == 'id') || $arr['type'] == 'id' || $arr['type'] == 'link')){

            $curNum = strpos($text,'$p_policies_'.$name);

            if($curNum)
            {
                if($curNum < $firstNum || $firstNum == 0)
                {
                    $firstValue = '$p_policies_'.$name;
                    $firstNum = $curNum;
                }
                if($curNum > $lastNum)
                {
                    $lastValue = '$p_policies_'.$name;
                    $lastNum = $curNum;
                }
            }
        }
    }
  1. The Last step, this where the data actually gets populated onto the PDF, at the very bottom of the page, youā€™ll see where they are counting the $lineItems variable. and then looping through each line item and populating the data. You simply need to add your module to the $obb variable and set the ID that you passed into this function as the value.
//Converting Line Items
        if(count($lineItems) != 0){
            foreach($lineItems as $id => $productId){
                if($productId == null || $productId == '0'){
                    $obb['AOS_Products_Quotes'] = $id;
                    $obb['p_policies'] = $lineItemsPolicies[$id]; 
                    //The line above is added, since the $productId will be blank or null, sitting inside the IF statement is fine. Notice how we are simply selecting
                    // which line item ID, and that tells us which policy ID is related, hence earlier why I said to leave the row ID in place.
                    $text .= templateParser::parse_template($linePart, $obb);
                }
            }
        }

Hope this helps someone!

1 Like
4

Hello,

Do you know how add line items to custom module?

Thankyou.

5

Do you mean how to add a relate field into the Services line items that relate to a custom module? Or how to create an entirely new set of line items to a custom module?

1 Like
6

Hello,

Thank you for answering the question.

I want to be able to add lines of products in custom modules, equal to the invoices, quotes and contracts.

Thanks for your help.

7

Hi,
From Line_item.js i have added 5 Fields into Service Line item where 3 from Product Line item and rest Custom created after some efforts i achieved viewing and saving them from database without issue now i want them to bring into PDF Templates i did some changes and tried to achieve it from your code Then its not working ā€¦ How can i bring all fields form products to service line items (quantity,descriptionā€¦).

Thanks