Skip to main content

InvoiceTransAddProduct EDBL

Reference for the InvoiceTransAddProduct EDBL import in Encompass: bulk-add product lines to existing or new invoices with pricing, promotions, charges and payments. Needs InvoiceEdit permission; v22.01+.

B
Written by Bailey Naas-Thornton

Minimum Version Required: 22.01

Pre-Conditions

  • You must have the InvoiceEdit permission to use this routine.

  • Products must already exist in the system before importing.

  • Customers must already exist in the system before importing.

  • If you plan to use LocationID_Translation, the locations must already exist.

  • If you plan to use PromotionID, the promotions must already exist.

  • If you plan to use PaymentTypeID, the payment types must already exist.

Steps

The InvoiceTransAddProduct routine allows you to add products (line items) to invoices. It can either add products to existing invoices or create new invoices when needed.

This routine will:

Match imported data to existing invoices using various methods:

  • Direct match by InvoiceID (if provided)

  • Match by CustomerID + PONum combination

  • Match by CustomerID + InvoiceNum combination

  • Match by CustomerID + TranslationInvoiceNum

Create new invoices if needed (when in Add or Update+Add mode) when no matching invoice is found.

Add or update invoice transactions (line items) with product and pricing information.

Handle currency conversion if specified.

Calculate pricing based on unit price, full price, and discounts.

Apply promotions if specified.

The behavior changes based on the import action selected:

  • Add Only: Only creates new invoice transactions, skips existing ones.

  • Update Only: Only updates existing invoice transactions, doesn't create new ones.

  • Update or Add: Updates existing transactions when found, creates new ones when not found.

Fields And Definitions

Invoice Identification Fields:

  • InvoiceID - The internal ID of an existing invoice to update (optional).

  • InvoiceNum - The invoice number displayed to users.

  • TranslationInvoiceNum - External system's invoice ID for cross-reference.

  • PONum - Purchase order number from customer.

Customer and Location Information:

  • CustomerID - Internal ID of the customer for this invoice.

  • CustomerID_Translation - External system's customer ID for matching.

  • LocationID - Internal ID of the location for this invoice (defaults to user's location).

  • LocationID_Translation - External system's location ID for matching.

Date and Scheduling:

  • Date - The invoice date (defaults to next business day).

  • DueDate - Due date for payment.

  • LoadSheetID - Links invoice to a specific load sheet for delivery.

  • RouteID - Internal ID of the delivery route.

  • RouteNum - Route number for delivery.

Product Information:

  • ProductID - Internal ID of the product being added.

  • ProductID_Translation - External system's product ID for matching.

  • ProductName - Name of the product (overrides default if provided).

  • NumUnits - Number of individual units of the product.

  • NumUnitsCases - Number of cases of the product (converts to units automatically).

Pricing Information:

  • FullPrice - Original price before discounts (per unit).

  • UnitPrice - Price after discounts (per unit).

  • Discount - Primary discount amount (per unit).

  • Discount2 - Secondary discount amount (per unit).

  • Deposit - Container deposit amount (per unit).

  • AutoCalculateMethod - Controls how pricing is calculated.

  • PromoOverridden - Indicates if promotion pricing is overridden.

Promotion Information:

  • PromotionID - Internal ID of the primary promotion applied.

  • PromotionID2 - Internal ID of the secondary promotion applied.

  • Participation - Primary manufacturer participation amount.

  • Participation2 - Secondary manufacturer participation amount.

Invoice Charges:

  • InvoiceCharge1 to InvoiceCharge5 - Additional charge amounts.

  • InvoiceChargeType1 to InvoiceChargeType5 - Types of additional charges.

Payment Information:

  • Receipt - Payment amount received.

  • PaymentTypeID - Internal ID of the payment method.

  • PaymentTypeID_Translation - External system's payment type ID.

Additional Fields:

  • Memo - Notes on the invoice.

  • Locked - Whether the invoice should be locked against changes.

  • DeliveryUserName - Username of the delivery person.

  • EntrySource - Source system for the entry.

  • AllocationID - Links to inventory allocation.

  • JobID - Links to a job.

  • TransSource - Source of the transaction.

  • TransNote - Note for this transaction.

  • ClearAllTrans - If set to 1, zeroes quantities for products not in import.

  • ReasonID - Reason code (for returns, etc.).

Currency Handling:

  • CurrencyID - Internal ID of the currency used.

  • CurrencyID_Translation - External system's currency ID.

  • ExchangeRate - Exchange rate for currency conversion.

Purchase Transaction Linkage:

  • PurchaseTransID - Links to a purchase transaction.

  • PurchaseTransIDLocked - Controls whether the purchase transaction link can be changed.

Best Practices

Matching Existing Invoices:

  • Provide either InvoiceID, PONum, InvoiceNum, or TranslationInvoiceNum to match to existing invoices.

  • For best results, ensure CustomerID is always included.

Creating New Invoices:

  • When creating new invoices, provide Date, CustomerID, and either RouteID or RouteNum.

  • Including a PONum or InvoiceNum helps with future identification.

Product Quantities:

  • You can provide either NumUnits or NumUnitsCases - the system will convert cases to units automatically.

  • Always ensure ProductID or ProductID_Translation is correctly mapped.

Pricing Options:

  • You need to provide either FullPrice and Discount, or just UnitPrice.

  • If you provide UnitPrice and FullPrice, Discount will be calculated automatically.

Bulk Updates:

  • Use ClearAllTrans=1 to set quantity to zero for line items that aren't in your import file.

  • This is useful for importing the complete state of an invoice.

Performance:

  • Import related records in batches of 5,000 or fewer for optimal performance.

  • For large imports, consider scheduling during off-peak hours.

Currency Handling:

  • If using foreign currency, always provide both CurrencyID and ExchangeRate.

  • All price values are stored in your system's base currency but displayed in the invoice's currency.

Common Issues:

  • If products aren't appearing, check that the ProductID exists in your system.

  • If invoices are created but no line items appear, verify that both CustomerID and ProductID are valid.

  • For discounts to apply correctly, both PromotionID and Discount fields must be properly set.

Did this answer your question?