Certificate of Compliance on Posted Sales Shipments

---

Scenario

A food producer needs to include a Certificate of Compliance (COC) whenever a posted sales shipment is emailed to a customer. The COC must contain shipment-specific details - customer name, product information, PO number, batch number, best-before date, and dispatch date - pulled automatically from the shipment and related tables at the time of sending.

Some columns are sourced from the shipment line itself, others are looked up from related tables (Sales Shipment Header, Item Ledger Entry), and several are constant values such as storage conditions, testing statements, and contact details that appear on every COC regardless of the data.

Step 1: Create the Attachment Generation Template

1. Use Tell Me (Alt+Q) and search for Attachment Generation Templates.

2. Click + New to open the Attachment Generation Template Card.

3. Fill in the header fields:

   Field	Value
   Code	COC-SHIPMENT
   Description	Certificate of Compliance - Sales Shipment
   Template Type	Data Source
   Source Table No.	Posted Sales Shipment Lines (table 111)
   Include Header Row	Yes
   Enabled	Yes

4. Optionally configure styling (e.g. Header Background Color = #2E75B6, Header Font Color = #FFFFFF, Font Family = Calibri, Arial, sans-serif).

Step 2: Configure Source Table Filters

Source Table Filters are static filters applied to the source table every time the template generates output. They narrow the dataset before any columns or runtime filters are evaluated.

In the Source Table Filters subpage on the Template Card, add a filter to exclude non-item lines:

Field No.	Field Name	Type	Value	Purpose
2	Type	CONST	Item	Excludes comment lines, G/L Account lines, and other non-item line types. Only lines where Type = "Item" are included.

How filters work: Each Source Table Filter adds a condition to the source table query. Records must satisfy all filter conditions to be included in the output. The CONST filter type matches an exact value. The FILTER type accepts Business Central filter expressions such as ranges (1000..2000), wildcards (*text*), or multiple values (CHEMICAL|FOOD).

Tip: If you need to restrict to specific item categories, add an additional filter on Item Category Code using the FILTER type (e.g. HONEY|WAX).

Step 3: Configure Columns

In the Columns subpage, define the data columns for the COC output. This example uses a mix of source fields, column relations to related tables, and constant values:

Column No.	Column Name	Constant Value	Relation Table	Relation Display Field	Notes
10	Customer		Sales Shipment Header (110)	Sell-to Customer Name (3)	Looked up from the shipment header via a column relation.
20	Product Name		-	-	Sourced from the shipment line's Description field.
30	Product Description		-	-	Sourced from the shipment line's Description 2 field.
40	PO Number		Sales Shipment Header (110)	External Document No. (100)	The customer's purchase order number, looked up from the shipment header.
50	Batch Number		Item Ledger Entry (32)	Lot No. (6501)	Looked up from the Item Ledger Entry via a column relation.
60	BBD		Item Ledger Entry (32)	Expiration Date (6503)	Best-before date, looked up from the Item Ledger Entry.
70	Dispatch Date		Sales Shipment Header (110)	Shipment Date (21)	The shipment date from the header.
80	Storage Condition	Storage Condition	-	-	Constant value - a placeholder or actual storage instruction.
90	Microbiological and Organoleptic Testing	Relevant testing results within specification – refer to PIF	-	-	Constant value - a standard compliance statement.
100	Contains Honeydew	No	-	-	Constant value.
110	Nutritional Information	Refer to PIF	-	-	Constant value - directs the reader to the Product Information File.
120	Shelf Life	1 Week	-	-	Constant value.
130	Contact	[email protected]	-	-	Constant value - company contact email.

Constant values are set using the Value Text field on the column. When Value Text is populated, the same text appears in every row of the output. This is useful for storage conditions, compliance statements, contact details, or any text that is the same regardless of the shipment data.

Source fields (Product Name, Product Description) are configured by setting the Source Field No. on the column to the appropriate field on the Posted Sales Shipment Line table.

Step 4: Configure Column Relations

Several columns pull data from related tables rather than directly from the shipment line. Use Column Relations to configure these lookups:

Sales Shipment Header relations (Customer, PO Number, Dispatch Date)

1. Select the Customer column (Column No. 10) and click the Relation Setup action.
2. On the Column Relation Setup card:
   • Set Relation Table No. to 110 (Sales Shipment Header).
   • Set Relation Display Field No. to 3 (Sell-to Customer Name).
   • In the Join Fields subpart, map the shipment line's Document No. field to the header's No. field.
3. Use the Save as Preset action to save this as a preset (e.g. SHIPMENT-HEADER).
4. For PO Number (Column No. 40), select the SHIPMENT-HEADER preset and change only the Relation Display Field No. to 100 (External Document No.).
5. For Dispatch Date (Column No. 70), select the SHIPMENT-HEADER preset and change only the Relation Display Field No. to 21 (Shipment Date).

Item Ledger Entry relations (Batch Number, BBD)

1. Select the Batch Number column (Column No. 50) and click the Relation Setup action.
2. On the Column Relation Setup card:
   • Set Relation Table No. to 32 (Item Ledger Entry).
   • Set Relation Display Field No. to 6501 (Lot No.).
   • In the Join Fields subpart, map the appropriate fields from the shipment line to the Item Ledger Entry (e.g. Document No., Line No.).
3. Use the Save as Preset action to save this as a preset (e.g. ITEM-LEDGER).
4. For BBD (Column No. 60), select the ITEM-LEDGER preset and change only the Relation Display Field No. to 6503 (Expiration Date).

Tip: Relation Presets save significant time when multiple columns reference the same related table. Define the preset once, then reuse it - only the display field changes per column.

Step 5: Create the Attachment Group

1. Search for Attachment Groups and create a new group:

   Field	Value
   Code	SHIP-COC
   Name	Sales Shipment with COC

2. Open the group via the Group Attachments action.

3. In the Attachment Generation section, add a new line:

   Field	Value
   Template Code	COC-SHIPMENT
   File Name	COC-{No.}-{Date}
   Output Format	PDF

   The {No.} token will be replaced with the shipment document number and {Date} with today's date at send time (e.g. COC-SSP00142-20260323.pdf).

Step 6: Configure Runtime Filters

Runtime Filters supply filter values at the time of sending so the generated attachment only includes data for the specific document being emailed.

1. Select the Attachment Generation line for COC-SHIPMENT and click the Runtime Filters action.

2. Add a runtime filter:

   Field No.	Field Name	Default Value
   3	Document No.	{No.}

   The {No.} token resolves to the posted sales shipment's document number at send time. This ensures the COC only contains lines belonging to the shipment being emailed - not all shipment lines in the system.

Step 7: Link the Attachment Group to a Report Selection

Choose one of the following approaches:

Option A - Customer-specific: Open the Customer Card → Related → Document Layouts. On the Shipment usage line, set Attachment Group Code to SHIP-COC.

Option B - Global (all customers): Search for Report Selection - Sales. On the Shipment usage line, set Attachment Group Code to SHIP-COC.

Step 8: Test the Output

Before going live, verify the COC output:

1. Open the Attachment Generation Template Card for COC-SHIPMENT.
2. Click Download PDF with Filters (or Download HTML with Filters for a quick preview).
3. Enter a known shipment document number when prompted.
4. Review the output - confirm that:
   • Only item lines appear (no comments).
   • The Customer, PO Number, and Dispatch Date are populated from the Sales Shipment Header.
   • Batch Number and BBD are populated from the Item Ledger Entry.
   • Constant columns (Storage Condition, Testing, Contact, etc.) display on every row.

Result

When a posted sales shipment is emailed, the customer receives:

1. The standard sales shipment document (the primary attachment).
2. A Certificate of Compliance PDF (COC-SSP00142-20260323.pdf) automatically generated by Elevate Attachments, containing customer details, product information, batch and best-before data from the Item Ledger Entry, and standard compliance statements - all pulled together automatically at send time.

No manual steps are required at send time - the COC is generated and attached automatically.

---