This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Log in to ask a question
Log in to ask a question

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

Posted on 08-31-2023 09:42 PM
Magistyo_Raharj
Bronze 1
Bronze 1
Reply posted on --/--/---- --:-- AM

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.

Show More
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>โ€‹
0 3 351
Topic Labels
0 Likes
3 REPLIES 3