Question

Printable Generation - Detail inside a detail

02:41 Apr 26, 2023

Hi All,

The requirement is to generate a document where data is stored in detail inside a detail and it should be grouped based on the value in the parent detail.

Scenario Explanation,

We have created a custom section named "Quotes" and a detail inside it named "Parts, Design, Labor Components", this detail has a string column known as "CEID".

And we have detail named "Parts Transformation : Design Paid Upfront" and "Design Transformation : Design Paid Upfront" inside this main detail,

And our final printable document should be in the format like as below, As the CEID group should be common (pointing the main detail) and the parts and design records associated to it should be shown as table (pointing to child details). This printable should generate a single document for all the list of the CEID's and their associated parts and design.

In OOTB printables, its unable to bring the printables in this format. Is there a way to implement this logic in creatio ?

Regards,

Adharsh S

1 comments

Kate Karpik

03:52 Apr 29, 2023

Hello,

As far as I understand you need to pull the "Design Transformation : Design Paid Upfront" detail data from the "Parts, Design, Labor Components" detail into Quotes printable. Unfortunately, that would not work since it is not possible to print out the table in the table.

As a workaround to display data in a report from the nested second-level detail data as a hierarchy, you can use an object as a database view. The view can be created using a Common Table Expression (CTE). For example:

WITH  cte AS (
    select
             cast(l1.Name as NVARCHAR(MAX)) as Name,
             l1.Id,
             0 as hierarhy,
             l1.Id as Level1,
             m.Id as MainId
       from Main as m
       join Level1 as l1 on m.Id = l1.MainId
 
    UNION ALL
 
     select
             cast('  -  ' + l2.Name as NVARCHAR(MAX)) as Name,
             l2.Id, 
             1 as hierarhy,
             l2.Level1Id as Level1,
             m.MainId as MainId
       from cte as m
       join Level2 as l2 on m.Id = l2.Level1Id
)
 
SELECT *
FROM   cte
order by Level1, hierarchy

In this example,

Main is the main object, Level1 is the detail that is linked to Main, and Level2 is the detail that is linked to Level1.

As a result, an object can be obtained that joins two tables and displayed the data. Therefore, when creating the necessary columns in the view, they can be used to build the table and output the information row by row.

Best regards,

Kate

Show all comments