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:
Show More
<<Start: FILTER(data_supplier,in([supplier_id],[supplier]))>>
# <<[_THISROW-1].[order_list_id]>>
<<LOOKUP("1RLad602hAgB6aD_E_c1Ck7_QDuxEe2Xy", "Logo PT Timboel","_ID", "File")>>
<table>
<tr>
<th>
Vendor
</th>
<th>
Customer
</th>
<th colspan="3">
Notes
</th>
</tr>
<tr>
<td>
**<<[nama]>>**
</td>
<td>
**PT. Timboel**
</td>
<td>
**Date**
</td>
<td>
<<[tanggal]>>
</td>
</tr>
<tr>
<td rowspan="6">
<<[supplier_id]>>
<<[alamat_1]>>
<<[alamat_2]>>
<<[alamat_3]>>
<<[no_telefon]>>
<<[email]>>
</td>
<td rowspan="6">
PII removed by staff
</td>
<td>
**Deadline**
</td>
<td>
<<[_THISROW-1].[deadline]>>
</td>
</tr>
<tr>
<td>
**Mentah Deadline**
</td>
<td>
<<[_THISROW-1].[deadline_mentah]>>
</td>
</tr>
<tr>
<td>
**Rangkai Deadline**
</td>
<td>
<<[_THISROW-1].[deadline_rangkai]>>
</td>
</tr>
<tr>
<td>
**Finishing Deadline**
</td>
<td>
<<[_THISROW-1].[deadline_finishing]>>
</td>
</tr>
<tr>
<td rowspan="3">
<b>Comments
</b>
</td>
<td rowspan="3">
<<[_THISROW-1].[komentar]>>
</td>
</tr>
<tr>
</tr>
<tr>
</tr>
</table>
<<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]
)>>
<<End>>
| Item ID | Photo | Item Name | Material | Finishing | Size (cm) | QTY | Total | Notes |
| - | - | - | - | - | - | - | - | - |
| <<[barang_id]>> | <<[barang_id].[foto]>> | <<[barang_id].[nama_barang]>> | <<[barang_id].[kategori]>> | <<IF(SUM(<br> SELECT(<br> Resep bukan packing[Value Rupiah],<br> AND(<br> [barang_id] = [_THISROW-1].[barang_id],<br> [supplier_id] = [_THISROW-2].[supplier_id],<br> [kategori] = "finishing"<br> )<br> ))=0,"Unfinish",[barang_id].[Finishing])>> | <<[ukuran]>> | <<[quantity_bikin]>> | <br> <<[quantity_bikin]*<br> (SUM(<br> SELECT(<br> Resep bukan packing[Value Rupiah],<br> AND(<br> [barang_id] = [_THISROW-1].[barang_id],<br> [supplier_id] = [_THISROW-2].[supplier_id],<br> [kategori] = "finishing"<br> )<br> )<br> ) +<br> SUM(<br> SELECT(<br> Resep bukan packing[Value Rupiah],<br> AND(<br> [barang_id] = [_THISROW-1].[barang_id],<br> [supplier_id] = [_THISROW-2].[supplier_id],<br> [kategori] = "rangkai"<br> )<br> )<br> ) +<br> SUM(<br> SELECT(<br> Resep bukan packing[Value Rupiah],<br> AND(<br> [barang_id] = [_THISROW-1].[barang_id],<br> [supplier_id] = [_THISROW-2].[supplier_id],<br> [kategori] = "mentah"<br> )<br> )<br> ))>><br> | <<[komentar]>> |
| Total QTY | <<SUM(SELECT(order_list_detailed[quantity_bikin],AND(IN([_THISROW-1].[supplier_id],[Supplier List]),<br> [order_list_id] = [_THISROW].[order_list_id]),FALSE))>> Pcs |
| - | - |
-->
<table>
<tr>
<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>
<<End>>