How to generate a Consolidated Purchase Order Docu...

Magistyo_Raharj · 08-31-2023 09:42 PM

Hello, AppSheet Community!

I am working on an app that involves generating purchase order documents for suppliers. Here's how my data is structured:

Order List: This table contains data about the supplier, order date, and our company information.
Related Order List Detailed: This table holds details about items and the amount for each order.
Supplier, Items, and Recipe: These are separate tables where each item can be associated with multiple recipes, but each recipe has only one supplier.

Other important details:

I use PDF generation with an HTML template for generating order documents.
The price is directly associated with each material in the recipe and they are static.

My goal is to generate a consolidated document for each supplier that lists all the items I intend to order from them, along with a total price. Considering the structure of my data, I am unsure about the best way to sum up the pricing for each order, especially since the materials for a given recipe can come from different suppliers.

Any advice or guidance on how I can achieve this would be greatly appreciated!

Thank you in advance for your assistance.

HTML Template:

<body>
    <p class="startifend">&lt;&lt;Start: FILTER(data_supplier,in([supplier_id],[supplier]))&gt;&gt;</p>
      <div class="container company-data">
        <div class="company text">
          <h1 class="title">&lt;&lt;[_THISROW-1].[order_list_id]&gt;&gt;</h1>
        </div>
        <div class="timboel-logo"> &lt;&lt;LOOKUP("1RLad602hAgB6aD_E_c1Ck7_QDuxEe2Xy", "Logo PT Timboel","_ID", "File")&gt;&gt;
          <!--<img src="" 
                  alt="Logo PT. Timboel" 
                  width="100">-->
        </div>
      </div>
      <table class="header">
        <thead>
          <tr>
            <th>Vendor</th>
            <th>Customer</th>
            <!-- <th>Vendor Bank</th> -->
            <th colspan="3">Notes</th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td>
              <b>&lt;&lt;[nama]&gt;&gt;</b>
            </td>
            <td>
              <b>PT. Timboel</b>
            </td>
            <!-- <td>
              <b>&lt;&lt;[bank_id].[bank_des]&gt;&gt;</b>
            </td> -->
            <td>
              <b>Date</b>
            </td>
            <td>&lt;&lt;[tanggal]&gt;&gt;</td>
          </tr>
          <tr>
            <td rowspan="6">&lt;&lt;[supplier_id]&gt;&gt;  <br> 
              &lt;&lt;[alamat_1]&gt;&gt; <br> 
              &lt;&lt;[alamat_2]&gt;&gt; <br>
              &lt;&lt;[alamat_3]&gt;&gt; <br>
              &lt;&lt;[no_telefon]&gt;&gt; <br>
              &lt;&lt;[email]&gt;&gt; <br>
            </td>
            <td rowspan="6"> PII removed by staff
            </td>
            <!-- <td rowspan="5"> &lt;&lt;[bank_id].[atas_nama]&gt;&gt; <br> &lt;&lt;[bank_id].[nomor_account]&gt;&gt; <br> &lt;&lt;[bank_id].[nama]&gt;&gt; <br> &lt;&lt;[bank_id].[alamat]&gt;&gt; <br> &lt;&lt;[bank_id].[no_telfon]&gt;&gt; <br> 
            </td>-->
            <td class="deadline">
              <b >Deadline</b>
            </td>
            <td class="deadline">&lt;&lt;[_THISROW-1].[deadline]&gt;&gt;</td>
          </tr>
         <!-- <tr>
            <td>
              <b>Packing</b>
              <br>
            </td>
            <td>&lt;&lt;[_THISROW-1].[jenis_packing]&gt;&gt;</td>
          </tr> -->
          <tr>
            <td>
              <b>Mentah Deadline</b>
              <br>
            </td>
            <td>&lt;&lt;[_THISROW-1].[deadline_mentah]&gt;&gt;</td>
          </tr>
          <tr>
            <td>
              <b>Rangkai Deadline</b>
              <br>
            </td>
            <td>&lt;&lt;[_THISROW-1].[deadline_rangkai]&gt;&gt;</td>
          </tr>
          <tr>
            <td>
              <b>Finishing Deadline</b>
              <br>
            </td>
            <td>&lt;&lt;[_THISROW-1].[deadline_finishing]&gt;&gt;</td>
          </tr>
          <tr>
            <td rowspan="3">
              <b>Comments <br>
              </b>
            </td>
            <td rowspan="3" style="width: 30%;">&lt;&lt;[_THISROW-1].[komentar]&gt;&gt;</td>
          </tr>
          <tr></tr>
          <tr></tr>
        </tbody>
      </table>
      <table class="order_list_id-items">
        <thead>
          <tr>
            <th class="idcolumn">Item ID</th>
            <th>Photo</th>
            <th class="idcolumn">Item Name</th>
            <th>Material</th>
            <th class="smallcolumn">Finishing</th>
            <th class="smallcolumn">Size (cm)</th>
            <th >QTY</th>
            <!--<th>Mentah</th>-->
            <!--<th>Rangkai</th>-->
            <!--<th>Finishing</th>-->
            <th>Total</th>
            <th style="width: 4cm;">Notes</th>
          </tr>
        </thead>
        <tbody>
          <p class="startifend">&lt;&lt;Start: ORDERBY(
                      FILTER(
                        "order_list_detailed", 
                        AND(
                          [order_list_id] = [_THISROW].[order_list_id], 
                          IN([_THISROW-1].[supplier_id], [Supplier List]),
                          [quantity_bikin]>0
                        )
                      ), 
                      [barang_id].[Finishing]
                    )&gt;&gt;</p>
          <tr>
            <td class="idcolumn">&lt;&lt;[barang_id]&gt;&gt;</td>
            <td>&lt;&lt;[barang_id].[foto]&gt;&gt;</td>
            <td class="idcolumn">&lt;&lt;[barang_id].[nama_barang]&gt;&gt;</td>
            <td>&lt;&lt;[barang_id].[kategori]&gt;&gt;</td>
            <td class="smallcolumn">&lt;&lt;IF(SUM(
                SELECT(
                  Resep bukan packing[Value Rupiah],
                  AND(
                    [barang_id] = [_THISROW-1].[barang_id],
                    [supplier_id] = [_THISROW-2].[supplier_id],
                    [kategori] = "finishing"
                  )
                ))=0,"Unfinish",[barang_id].[Finishing])&gt;&gt;</td>
            <td class="smallcolumn">&lt;&lt;[ukuran]&gt;&gt;</td>
            <td>&lt;&lt;[quantity_bikin]&gt;&gt;</td>
   
            <td>
                            &lt;&lt;[quantity_bikin]*
                (SUM(
                    SELECT(
                        Resep bukan packing[Value Rupiah],
                        AND(
                            [barang_id] = [_THISROW-1].[barang_id],
                            [supplier_id] = [_THISROW-2].[supplier_id],
                            [kategori] = "finishing"
                        )
                    )
                ) +
                SUM(
                    SELECT(
                        Resep bukan packing[Value Rupiah],
                        AND(
                            [barang_id] = [_THISROW-1].[barang_id],
                            [supplier_id] = [_THISROW-2].[supplier_id],
                            [kategori] = "rangkai"
                        )
                    )
                ) +
                SUM(
                    SELECT(
                        Resep bukan packing[Value Rupiah],
                        AND(
                            [barang_id] = [_THISROW-1].[barang_id],
                            [supplier_id] = [_THISROW-2].[supplier_id],
                            [kategori] = "mentah"
                        )
                    )
                ))&gt;&gt;
            </td>
            <td style="width: 4cm;">&lt;&lt;[komentar]&gt;&gt;</td>
          </tr>
          <p class="startifend">&lt;&lt;End&gt;&gt;</p>
        </tbody>
      </table>
          <div class="post-table-info">
      <table class="items-totals">
       <!-- <tr>
          <th>Total CBM</th>
          <td colspan="2">&lt;&lt;[Total CBM]&gt;&gt; m&#179;</td>
        </tr> -->
        <tr>
          <th>Total QTY</th>
          <td colspan="2">&lt;&lt;SUM(SELECT(order_list_detailed[quantity_bikin],AND(IN([_THISROW-1].[supplier_id],[Supplier List]),
          [order_list_id] = [_THISROW].[order_list_id]),FALSE))&gt;&gt; Pcs</td>
        </tr>
      </table>
      <table class="items-totals">
       <!-- <tr>
          <th>Subtotal</th>
          <td colspan="2">&lt;&lt;[Subtotal]&gt;&gt;</td>
        </tr>
        <tr>
          <th>Discount (&lt;&lt;[discount_percent]*100&gt;&gt; %)</th>
          <td colspan="2">&lt;&lt;[Diskon Persen Amount]&gt;&gt;</td>
          <td></td>
        </tr>
        <tr>
          <th>Discount Amount</th>
          <td colspan="2">&lt;&lt;[discount_amount]&gt;&gt;</td>
          <td></td>
        </tr> -->
   
          <th>Deposit (30%)</th>
          <td>&lt;&lt;[Deposit]&gt;&gt;</td>
          <td>&lt;&lt;[Tanggal DP]&gt;&gt;</td>
        </tr>
        <tr>
          <th>Shipping Cost</th>
          <td colspan="2">&lt;&lt;[Shipping]&gt;&gt;</td>
          <td></td>
        </tr>
        <tr>
          <th>Balance</th>
          <td>&lt;&lt;[Balance]+[Shipping]&gt;&gt;</td>
          <td>&lt;&lt;[Tanggal Lunas]&gt;&gt;</td>
        </tr> -->
      </table>
    </div>
     <p class="startifend">&lt;&lt;End&gt;&gt;</p>
   
   
  </body>

HTML Template:<body> <p class="startifend"><<Start: FILTER(data_supplier,in([supplier_id],[supplier]))>></p> <div class="container company-data"> <div class="company text"> <h1 class="title"><<[_THISROW-1].[order_list_id]>></h1> </div> <div class="timboel-logo"> <<LOOKUP("1RLad602hAgB6aD_E_c1Ck7_QDuxEe2Xy", "Logo PT Timboel","_ID", "File")>>  </div> </div> <table class="header"> <thead> <tr> <th>Vendor</th> <th>Customer</th>  <th colspan="3">Notes</th> </tr> </thead> <tbody> <tr> <td> <b><<[nama]>></b> </td> <td> <b>PT. Timboel</b> </td>  <td> <b>Date</b> </td> <td><<[tanggal]>></td> </tr> <tr> <td rowspan="6"><<[supplier_id]>> <br> <<[alamat_1]>> <br> <<[alamat_2]>> <br> <<[alamat_3]>> <br> <<[no_telefon]>> <br> <<[email]>> <br> </td> <td rowspan="6"> PII removed by staff </td>  <td class="deadline"> <b >Deadline</b> </td> <td class="deadline"><<[_THISROW-1].[deadline]>></td> </tr>  <tr> <td> <b>Mentah Deadline</b> <br> </td> <td><<[_THISROW-1].[deadline_mentah]>></td> </tr> <tr> <td> <b>Rangkai Deadline</b> <br> </td> <td><<[_THISROW-1].[deadline_rangkai]>></td> </tr> <tr> <td> <b>Finishing Deadline</b> <br> </td> <td><<[_THISROW-1].[deadline_finishing]>></td> </tr> <tr> <td rowspan="3"> <b>Comments <br> </b> </td> <td rowspan="3" style="width: 30%;"><<[_THISROW-1].[komentar]>></td> </tr> <tr></tr> <tr></tr> </tbody> </table> <table class="order_list_id-items"> <thead> <tr> <th class="idcolumn">Item ID</th> <th>Photo</th> <th class="idcolumn">Item Name</th> <th>Material</th> <th class="smallcolumn">Finishing</th> <th class="smallcolumn">Size (cm)</th> <th >QTY</th>    <th>Total</th> <th style="width: 4cm;">Notes</th> </tr> </thead> <tbody> <p class="startifend"><<Start: ORDERBY( FILTER( "order_list_detailed", AND( [order_list_id] = [_THISROW].[order_list_id], IN([_THISROW-1].[supplier_id], [Supplier List]), [quantity_bikin]>0 ) ), [barang_id].[Finishing] )>></p> <tr> <td class="idcolumn"><<[barang_id]>></td> <td><<[barang_id].[foto]>></td> <td class="idcolumn"><<[barang_id].[nama_barang]>></td> <td><<[barang_id].[kategori]>></td> <td class="smallcolumn"><<IF(SUM( SELECT( Resep bukan packing[Value Rupiah], AND( [barang_id] = [_THISROW-1].[barang_id], [supplier_id] = [_THISROW-2].[supplier_id], [kategori] = "finishing" ) ))=0,"Unfinish",[barang_id].[Finishing])>></td> <td class="smallcolumn"><<[ukuran]>></td> <td><<[quantity_bikin]>></td> <td> <<[quantity_bikin]* (SUM( SELECT( Resep bukan packing[Value Rupiah], AND( [barang_id] = [_THISROW-1].[barang_id], [supplier_id] = [_THISROW-2].[supplier_id], [kategori] = "finishing" ) ) ) + SUM( SELECT( Resep bukan packing[Value Rupiah], AND( [barang_id] = [_THISROW-1].[barang_id], [supplier_id] = [_THISROW-2].[supplier_id], [kategori] = "rangkai" ) ) ) + SUM( SELECT( Resep bukan packing[Value Rupiah], AND( [barang_id] = [_THISROW-1].[barang_id], [supplier_id] = [_THISROW-2].[supplier_id], [kategori] = "mentah" ) ) ))>> </td> <td style="width: 4cm;"><<[komentar]>></td> </tr> <p class="startifend"><<End>></p> </tbody> </table> <div class="post-table-info"> <table class="items-totals">  <tr> <th>Total QTY</th> <td colspan="2"><<SUM(SELECT(order_list_detailed[quantity_bikin],AND(IN([_THISROW-1].[supplier_id],[Supplier List]), [order_list_id] = [_THISROW].[order_list_id]),FALSE))>> Pcs</td> </tr> </table> <table class="items-totals">  <th>Deposit (30%)</th> <td><<[Deposit]>></td> <td><<[Tanggal DP]>></td> </tr> <tr> <th>Shipping Cost</th> <td colspan="2"><<[Shipping]>></td> <td></td> </tr> <tr> <th>Balance</th> <td><<[Balance]+[Shipping]>></td> <td><<[Tanggal Lunas]>></td> </tr> --> </table> </div> <p class="startifend"><<End>></p> </body>

Marc_Dillon

Can you share some examples of the data, and a mockup of how you want the PDF output to look from those data examples?

Magistyo_Raharj

Sample Order List

order_list_id	order_list_bot	invoice_id	customer_id	tanggal	deadline	jenis_packing	komentar	deadline_finishing
OL-C5C3D944-PTT	4ddd8fd2	PI-0ADC7ED1-PTT	PTT-3BCC2BA7	14/04/2023	26/05/2023	Standar export, pasahan halus, tanpa lubang	Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis	10/08/1996

order_list_idorder_list_botinvoice_idcustomer_idtanggaldeadlinejenis_packingkomentardeadline_finishingOL-C5C3D944-PTT4ddd8fd2PI-0ADC7ED1-PTTPTT-3BCC2BA714/04/202326/05/2023Standar export, pasahan halus, tanpa lubangLorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis10/08/1996

Sample Order List Detailed

barang_id

order_list_id

quantity_stock

quantity_bikin

komentar

pakai_stock

tanggal_mentah

tanggal_rangkai

tanggal_finishing

tanggal_packing

selesai_mentah

selesai_rangkai

selesai_finishing

selesai_packing

WTH-71841252-NS

OL-C5C3D944-PTT

0

1

FALSE

14/04/2023

1

AHB-A5DAFBED-BST

OL-C5C3D944-PTT

0

1

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis

FALSE

14/04/2023

1

SBS-2D531B2A-WC

OL-C5C3D944-PTT

0

1

FALSE

14/04/2023

1

barang_idorder_list_idquantity_stockquantity_bikinkomentarpakai_stocktanggal_mentahtanggal_rangkaitanggal_finishingtanggal_packingselesai_mentahselesai_rangkaiselesai_finishingselesai_packingWTH-71841252-NSOL-C5C3D944-PTT01 FALSE14/04/202314/04/202314/04/202314/04/20231111AHB-A5DAFBED-BSTOL-C5C3D944-PTT01Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quisFALSE14/04/202314/04/202314/04/202314/04/20231111SBS-2D531B2A-WCOL-C5C3D944-PTT01 FALSE14/04/202314/04/202314/04/202314/04/20231111

Sample Item Recipe

tanggal	barang_id	material_id	supplier_id	kategori	quantity	keterangan
19/04/2023	WTH-71841252-NS	CA-PEKNV45C-T	T-A1D117C2	Mentah	48
19/04/2023	WTH-71841252-NS	TL-34F767EC-T	T-A1D117C2	Rangkai	230
06/05/2023	WTH-71841252-NS	WA-DF514547-TT	TT-430D1B8F	Finishing	1.5
06/05/2023	WTH-71841252-NS	A-D8B1FB64-SGI	SGI-3C2F3ABC	Finishing	75
06/05/2023	WTH-71841252-NS	O-F2490673-SGI	SGI-3C2F3ABC	Finishing	15
06/05/2023	WTH-71841252-NS	A-793EAE63-SGI	SGI-3C2F3ABC	Finishing	0.3
06/05/2023	WTH-71841252-NS	G/C-585254FA-RK	RK-BE4D07B0	Finishing	1
06/05/2023	WTH-71841252-NS	G/C-281BF5F4-RK	RK-BE4D07B0	Finishing	1
06/05/2023	WTH-71841252-NS	FDG-0AF4AB39-RK	RK-BE4D07B0	Finishing	3
06/05/2023	WTH-71841252-NS	GSK-E24F372D-RK	RK-BE4D07B0	Finishing	1
06/05/2023	WTH-71841252-NS	CBU-55BC76DB-RK	RK-BE4D07B0	Finishing	1
06/05/2023	WTH-71841252-NS	TNT-0AE17666-RK	RK-BE4D07B0	Finishing	0.5
06/05/2023	WTH-71841252-NS	CP-A2B945D3-RK	RK-BE4D07B0	Finishing	0.2
06/05/2023	WTH-71841252-NS	BO-CF651D09-PT	PT-083ARFG45	Finishing	25
06/05/2023	WTH-71841252-NS	SG-9C47FCE3-PT	PT-083ARFG45	Finishing	25
06/05/2023	WTH-71841252-NS	PA--70DC7640-KL	KL-61FC8A48	Finishing	48
06/05/2023	WTH-71841252-NS	TKL-A4BB7325-PT	PT-083ARFG45	Finishing	20
19/04/2023	AHB-A5DAFBED-BST	CA-PEKNV45C-T	T-A1D117C2	Mentah	8.7
19/04/2023	AHB-A5DAFBED-BST	TL-34F767EC-T	T-A1D117C2	Rangkai	52
19/04/2023	AHB-A5DAFBED-BST	TF-575DBF68-RHO	RHO-D29A4A18	Finishing	3.2
19/04/2023	AHB-A5DAFBED-BST	L-1C89B15B-PT	PT-083ARFG45	Finishing	5
19/04/2023	AHB-A5DAFBED-BST	SG-9C47FCE3-PT	PT-083ARFG45	Finishing	1
19/04/2023	AHB-A5DAFBED-BST	BO-CF651D09-PT	PT-083ARFG45	Finishing	1.5
19/04/2023	SBS-2D531B2A-WC	RS/-F948D1A2-CCP	CCP-4312DE21	Mentah	1.5
19/04/2023	SBS-2D531B2A-WC	KS3-512D919C-CMD	CMD-A347B5ED	Mentah	13
19/04/2023	SBS-2D531B2A-WC	G/C-281BF5F4-RK	RK-BE4D07B0	Mentah	2
19/04/2023	SBS-2D531B2A-WC	G/C-585254FA-RK	RK-BE4D07B0	Mentah	3
19/04/2023	SBS-2D531B2A-WC	GSK-E24F372D-RK	RK-BE4D07B0	Mentah	1
19/04/2023	SBS-2D531B2A-WC	KPM-0489BA9F-TKS	TKS-F602EDE2	Mentah	1
19/04/2023	SBS-2D531B2A-WC	KPS-386F97CE-UKL	UKL-C601602D	Mentah	10
19/04/2023	SBS-2D531B2A-WC	WI/L-4E92F26A-RK	RK-BE4D07B0	Mentah	1
19/04/2023	SBS-2D531B2A-WC	KP-BC0023F8-RK	RK-BE4D07B0	Mentah	0.2
19/04/2023	SBS-2D531B2A-WC	FDG-0AF4AB39-RK	RK-BE4D07B0	Mentah	2
19/04/2023	SBS-2D531B2A-WC	AD-7621C904-RK	RK-BE4D07B0	Mentah	0.5
19/04/2023	SBS-2D531B2A-WC	B-44B77D10-PSY	PSY-6DFC57D7	Mentah	1
19/04/2023	SBS-2D531B2A-WC	PS1-7068C2FE-PCL	PCL-C216AA1D	Mentah	1
19/04/2023	SBS-2D531B2A-WC	SG-9C47FCE3-PT	PT-083ARFG45	Mentah	20
19/04/2023	SBS-2D531B2A-WC	BO-CF651D09-PT	PT-083ARFG45	Mentah	20
19/04/2023	SBS-2D531B2A-WC	TKL-A4BB7325-PT	PT-083ARFG45	Mentah	132
19/04/2023	SBS-2D531B2A-WC	PH/-783E21D5-PCL	PCL-C216AA1D	Mentah	2.5
19/04/2023	SBS-2D531B2A-WC	C-FDD30072-MC	MC-37FFFBD7	Finishing	39.12
06/05/2023	SBS-2D531B2A-WC	PS1-71B4877D-CMD	CMD-A347B5ED	Mentah	0.25

tanggalbarang_idmaterial_idsupplier_idkategoriquantityketerangan19/04/2023WTH-71841252-NSCA-PEKNV45C-TT-A1D117C2Mentah48 19/04/2023WTH-71841252-NSTL-34F767EC-TT-A1D117C2Rangkai230 06/05/2023WTH-71841252-NSWA-DF514547-TTTT-430D1B8FFinishing1.5 06/05/2023WTH-71841252-NSA-D8B1FB64-SGISGI-3C2F3ABCFinishing75 06/05/2023WTH-71841252-NSO-F2490673-SGISGI-3C2F3ABCFinishing15 06/05/2023WTH-71841252-NSA-793EAE63-SGISGI-3C2F3ABCFinishing0.3 06/05/2023WTH-71841252-NSG/C-585254FA-RKRK-BE4D07B0Finishing1 06/05/2023WTH-71841252-NSG/C-281BF5F4-RKRK-BE4D07B0Finishing1 06/05/2023WTH-71841252-NSFDG-0AF4AB39-RKRK-BE4D07B0Finishing3 06/05/2023WTH-71841252-NSGSK-E24F372D-RKRK-BE4D07B0Finishing1 06/05/2023WTH-71841252-NSCBU-55BC76DB-RKRK-BE4D07B0Finishing1 06/05/2023WTH-71841252-NSTNT-0AE17666-RKRK-BE4D07B0Finishing0.5 06/05/2023WTH-71841252-NSCP-A2B945D3-RKRK-BE4D07B0Finishing0.2 06/05/2023WTH-71841252-NSBO-CF651D09-PTPT-083ARFG45Finishing25 06/05/2023WTH-71841252-NSSG-9C47FCE3-PTPT-083ARFG45Finishing25 06/05/2023WTH-71841252-NSPA--70DC7640-KLKL-61FC8A48Finishing48 06/05/2023WTH-71841252-NSTKL-A4BB7325-PTPT-083ARFG45Finishing20 19/04/2023AHB-A5DAFBED-BSTCA-PEKNV45C-TT-A1D117C2Mentah8.7 19/04/2023AHB-A5DAFBED-BSTTL-34F767EC-TT-A1D117C2Rangkai52 19/04/2023AHB-A5DAFBED-BSTTF-575DBF68-RHORHO-D29A4A18Finishing3.2 19/04/2023AHB-A5DAFBED-BSTL-1C89B15B-PTPT-083ARFG45Finishing5 19/04/2023AHB-A5DAFBED-BSTSG-9C47FCE3-PTPT-083ARFG45Finishing1 19/04/2023AHB-A5DAFBED-BSTBO-CF651D09-PTPT-083ARFG45Finishing1.5 19/04/2023SBS-2D531B2A-WCRS/-F948D1A2-CCPCCP-4312DE21Mentah1.5 19/04/2023SBS-2D531B2A-WCKS3-512D919C-CMDCMD-A347B5EDMentah13 19/04/2023SBS-2D531B2A-WCG/C-281BF5F4-RKRK-BE4D07B0Mentah2 19/04/2023SBS-2D531B2A-WCG/C-585254FA-RKRK-BE4D07B0Mentah3 19/04/2023SBS-2D531B2A-WCGSK-E24F372D-RKRK-BE4D07B0Mentah1 19/04/2023SBS-2D531B2A-WCKPM-0489BA9F-TKSTKS-F602EDE2Mentah1 19/04/2023SBS-2D531B2A-WCKPS-386F97CE-UKLUKL-C601602DMentah10 19/04/2023SBS-2D531B2A-WCWI/L-4E92F26A-RKRK-BE4D07B0Mentah1 19/04/2023SBS-2D531B2A-WCKP-BC0023F8-RKRK-BE4D07B0Mentah0.2 19/04/2023SBS-2D531B2A-WCFDG-0AF4AB39-RKRK-BE4D07B0Mentah2 19/04/2023SBS-2D531B2A-WCAD-7621C904-RKRK-BE4D07B0Mentah0.5 19/04/2023SBS-2D531B2A-WCB-44B77D10-PSYPSY-6DFC57D7Mentah1 19/04/2023SBS-2D531B2A-WCPS1-7068C2FE-PCLPCL-C216AA1DMentah1 19/04/2023SBS-2D531B2A-WCSG-9C47FCE3-PTPT-083ARFG45Mentah20 19/04/2023SBS-2D531B2A-WCBO-CF651D09-PTPT-083ARFG45Mentah20 19/04/2023SBS-2D531B2A-WCTKL-A4BB7325-PTPT-083ARFG45Mentah132 19/04/2023SBS-2D531B2A-WCPH/-783E21D5-PCLPCL-C216AA1DMentah2.5 19/04/2023SBS-2D531B2A-WCC-FDD30072-MCMC-37FFFBD7Finishing39.12 06/05/2023SBS-2D531B2A-WCPS1-71B4877D-CMDCMD-A347B5EDMentah0.25

This mockup contains red text representing specific data I'm trying to display. Essentially, I want to showcase the sum of the total. While I can accurately represent this when the quantity is 1, the value displayed is incorrect when the quantity is 2, as I haven't incorporated the quantity into the total calculation.

Sample PDF Mockup

Marc_Dillon

I think your examples contain a bit too much info, I'm lost in the details. Like you really included "lorem ipsum" sample text...

I don't even see any price values anywhere. I'd expect a column in the "Item Recipe" table with price values, but no?

All in all, I'm not really sure where you're having trouble. You say it's with summing up the total price, but I'm not sure what could be causing the issue.

Assume these tables:

Item_Recipe:

id	price
a	5
b	10

Order:

id

OL-C5C3D944-PTT

Order_Detail:

id	order_id	item_recipe_id	qty	total price (qty * item's price)
1	OL-C5C3D944-PTT	a	1	5
2	OL-C5C3D944-PTT	b	2	20

You see I'm calculating the total price in the individual order_detail records? Maybe that's what you're missing? Then your template would be:

<<START:[Related order_details]>>
order detail info...
<<END>>
Total: <<SUM([Related order_details][total_price])>>
Total Qty: <<SUM([related order_details][qty])>>

How to generate a Consolidated Purchase Order Document with Sum of Prices