texasstarbc

Custom Tables

This document describes the custom tables created specifically for Texas Star Nuts operations.

Overview

The extension includes 5 custom tables that handle specialized business requirements:

  1. TSN Brand Code (50004)
  2. TSN Global Group (50002)
  3. TSN Global Group Value (50003)
  4. TSN Item Customer (50015)
  5. TSN Warehouse Overview Buffer (50016)

1. TSN Brand Code (Table 50004)

Location: src/tables/TSNBrandCode.Table.al

Purpose

Manages brand codes for product categorization and private label tracking.

Key Fields

Field Type Description
Code Code[20] Unique brand identifier (Primary Key)
Description Text[100] Brand name/description
Private Label Boolean Indicates if this is a private label brand

Business Usage

2. TSN Global Group (Table 50002)

Location: src/tables/TSNGlobalGroup.Table.al

Purpose

Provides a flexible 5-tier classification system for items, customers, and vendors. Enables multi-dimensional categorization beyond standard Business Central dimensions.

Key Fields

Field Type Description
Code Code[20] Unique group identifier (Primary Key)
Description Text[100] Group name/description
Global Group No. Integer Group number (1-5) for system organization

Business Usage

Configuration

Example Use Cases

  1. Group 1: Product Category (Peanuts, Almonds, Cashews, Mixed Nuts)
  2. Group 2: Package Type (Bulk, Retail, Foodservice)
  3. Group 3: Organic Status (Organic, Conventional)
  4. Group 4: Processing Type (Raw, Roasted, Salted)
  5. Group 5: Market Segment (Retail, Wholesale, Export)

3. TSN Global Group Value (Table 50003)

Location: src/tables/TSNGlobalGroupValue.Table.al

Purpose

Stores the actual classification values for each global group. Provides the detailed breakdown of how items, customers, and vendors are categorized.

Key Fields

Field Type Description
Global Group Code Code[20] Link to TSN Global Group (Primary Key part 1)
Code Code[20] Unique value code within group (Primary Key part 2)
Description Text[100] Value description
Global Group No. Integer Group number for reference

Relationships

Business Usage

Example Records

Global Group Code: "CATEGORY"
Values:
  - Code: "PEANUT", Description: "Peanuts"
  - Code: "ALMOND", Description: "Almonds"
  - Code: "CASHEW", Description: "Cashews"

Global Group Code: "PACKAGE"
Values:
  - Code: "BULK", Description: "Bulk Packaging"
  - Code: "RETAIL", Description: "Retail Packaging"
  - Code: "FOODSVC", Description: "Foodservice Packaging"

4. TSN Item Customer (Table 50015)

Location: src/tables/TSNItemCustomer.Table.al

Purpose

Cross-reference table that links items to customers with customer-specific item numbers (SKUs). Enables customers to order using their own item numbering system.

Key Fields

Field Type Description
Item No. Code[20] Internal item number (Primary Key part 1)
Customer No. Code[20] Customer account number (Primary Key part 2)
Customer Item No. Code[50] Customer’s item number/SKU
Description Text[100] Item description for this customer

Business Usage

Workflow Example

  1. Customer ABC orders item “ABC-12345”
  2. Order entry looks up TSN Item Customer table
  3. Finds Customer Item No. “ABC-12345” maps to Item No. “NUT-ALMOND-1000”
  4. Order is created with correct internal item number
  5. Documents show both internal and customer item numbers

Key Business Benefits

5. TSN Warehouse Overview Buffer (Table 50016)

Location: src/tables/TSNWarehouseOverviewBuffer.Table.al

Purpose

Temporary buffer table for warehouse inventory analysis. Provides aggregated view of inventory by location, bin, lot, vendor, and expiration date with availability calculations.

Table Type

Temporary - Data exists only in memory during page/report execution

Key Fields

Field Type Description
Item No. Code[20] Item number (Key field)
Variant Code Code[10] Item variant
Location Code Code[10] Warehouse location
Bin Code Code[20] Bin within location
Lot No. Code[50] Lot number
Vendor No. Code[20] Supplier vendor number
Vendor Name Text[100] Vendor name
Expiration Date Date Lot expiration date
Quantity Decimal On-hand quantity
Quantity Base Decimal Base unit quantity
Reserved Quantity Decimal Reserved quantity
Available Quantity Decimal Available to promise (Calculated)

Calculated Fields

Business Usage

Data Population

Populated dynamically by querying:

Use Cases

1. FEFO Picking Analysis

Review lots by expiration date to ensure First Expiry, First Out compliance.

2. Vendor Inventory Analysis

Identify which vendor’s products are in stock and their quantities.

3. Bin Availability

Find which bins contain specific items for efficient picking.

4. Expiration Monitoring

Identify lots approaching expiration for proactive management.

5. Available to Promise

Real-time visibility of what can be committed to customer orders.

Performance Considerations

Summary

These custom tables provide the foundation for Texas Star Nuts’ specialized business requirements:

  1. TSN Brand Code - Product brand classification
  2. TSN Global Group - Flexible multi-dimensional grouping framework
  3. TSN Global Group Value - Detailed classification values
  4. TSN Item Customer - Customer-specific item number cross-referencing
  5. TSN Warehouse Overview Buffer - Real-time warehouse inventory analysis

Together, these tables enable sophisticated inventory management, customer-specific operations, and flexible product classification beyond standard Business Central capabilities.

See Also: