Technical Documentation - Bulk QuickBooks Invoicing

1. Overview

The Bulk QB Invoicing feature allows users to upload a CSV file containing multiple invoice lines and create QuickBooks Invoice records in Salesforce in bulk. The feature consolidates multiple CSV rows into a single invoice when they share the same Company, Customer, Invoice Date, and Terms, then queues all created invoices for QB API sync via the existing batch infrastructure.

Entry point: Quick Action (lightning__RecordAction, type ScreenAction) available on any record page.

2. Component Architecture

bulkQBInvoicing (orchestrator)
  ├── bulkQBInvoiceReview (editable data grid)
  │     └── lookupPill (inline search-and-select control, one per lookup cell)
  └── BulkQBInvoicingService (Apex — all server calls)
        └── CM_QB_Connector_Settings__mdt: BulkInvoiceFieldConfig (field schema source)

3. LWC Components

3.1 bulkQBInvoicing

File: lwc/bulkQBInvoicing/bulkQBInvoicing.js | .html Target: lightning__RecordAction (ScreenAction), lightning__RecordPage

Responsibility: Top-level orchestrator. Owns the two-step wizard state machine, all server calls, CSV parsing/validation, and row state management.

State Properties

Row Object Shape (validatedRows entries)

Each row object carried in validatedRows is a plain JavaScript object with these properties:

Lifecycle

connectedCallback → calls loadTemplateHeaders() to prefetch column names and populate requiredColumns before any user interaction.

renderedCallback → loads cmModalOverrides static resource CSS once via loadStyle.

Key Methods

loadTemplateHeaders() Calls generateTemplate Apex. Parses sMessage into requiredColumns array (comma-split). Also captures salesTermMapping (reserved for future use). Shows error toast if Apex call fails.

handleFileChange(event)

1. Guards: file must exist, size ≤ 5 MB.

2. Reads file using FileReader via readFileAsText().

3. Guards: content must be non-blank.

4. Counts data rows via countDataRows().

5. Calls validateHeaders Apex — checks server-side for missing columns.

6. Calls checkExactHeaderOrder() — JS-only check for column count mismatch and column position errors (BOM-aware on position 0).

7. On all guards passing, advances to Step 2 and calls runServerValidation({ useUiRows: false }).

handleDownloadTemplate() Calls generateTemplate Apex, reads base64 from response, creates a temporary <a> element with data:text/csv;base64,... href, clicks it programmatically, and removes it.

runServerValidation({ useUiRows }) Core validation driver called on file upload and on "Check My Changes":

• If useUiRows = true → rebuilds csvContent from the current in-memory validatedRows via buildCsvFromUiRows(), replacing the original file content.

• Calls validateData Apex.

• Maps response allRecords array to validatedRows, decorating each row with _rowId, _rowNum, _hasError, _errors, label maps from server.

• Rows with errors sort to the top in displayRows getter.

• Shows success or warning toast based on errorCount.

handleCellChange(event) Receives cellchange custom event from bulkQBInvoiceReview. Updates the matching row in validatedRows immutably. Sets _dirty = true. Does NOT clear _hasError — errors only clear on explicit revalidation.

handleLookupSearch(event) Calls searchLookup Apex with lookupType and searchTerm. Writes results back to the row's _<type>Results array. Uses row index from _rowId.

handleLookupSelect(event) Updates CustomerId / ProductId / QuickbooksCompanyId and the matching label field on the row. Clears the results dropdown array. Sets _dirty = true.

handleLookupClear(event) Nulls the Id field and label field. Sets _dirty = true.

importValidInvoices()

1. Rebuilds csvContent from validatedRows (always uses current UI state for import).

2. Calls createInvoiceRecordsFromCsv Apex.

3. On success: shows success toast with invoice/item counts, closes panel after 400 ms.

4. On failure: calls applyImportErrorsToRows(res) to re-mark rows with DML errors, shows error toast.

buildCsvFromUiRows() Reconstructs a canonical CSV string from validatedRows, using requiredColumns as the column order. Handles RFC 4180 quoting (commas, newlines, double-quotes inside values) via csvEscape().

checkExactHeaderOrder(csv, expected) Pure JS — parses first CSV line, strips BOM from position 0, compares column count and each column name in order against expected. Returns a human-readable error string or null.

decorateRow(row, rowNum, isInvalid, rowErrs) Attaches all underscore-prefixed metadata fields to a row. Strips Row N: prefix from error messages (Apex adds it; UI tooltip renders it separately).

applyImportErrorsToRows(res) After a failed import, maps errorsByRowNum from Apex response back onto validatedRows by matching _rowNum. Marks affected rows _hasError = true.

3.2 bulkQBInvoiceReview

File: lwc/bulkQBInvoiceReview/bulkQBInvoiceReview.js | .html Responsibility: Purely presentational editable data grid. Renders one row per item in the rows @api prop. Bubbles all events upward — owns no state of its own.

@api Props

Columns Rendered

Status icon | QB Company (lookupPill) | QB Customer (lookupPill) | QB Product (lookupPill) | Invoice Date (date input) | Terms Days (number input, min 0) | Qty (number input, min 1) | Unit Price (number input, min 0, step any)

Row Decoration (setter)

The set rows(value) setter enriches each row with:

• _rowClass: grid-data-row + row-invalid (if _hasError)

• _errorTitle: pipe-joined error string for tooltip

Events Dispatched

The component re-dispatches lookup events from child pills unchanged, letting bulkQBInvoicing handle all data mutations.

3.3 lookupPill

File: lwc/lookupPill/lookupPill.js | .html Responsibility: Reusable inline search-and-select control used in each lookup cell of the review grid.

@api Props

Behaviour

• When selectedId is null: shows a lightning-input type="search".

• When selectedId is set: hides the input and renders a pill with an ✕ button.

• Typing in the search input fires lookupsearch with bubbles: true, composed: true.

• Clicking a dropdown item fires lookupselect.

• Clicking ✕ on the pill fires lookupclear.

• The dropdown visibility is managed locally by showDropdown.

• The parent controls the dropdown options by updating the results @api prop.

4. Apex Service — BulkQBInvoicingService

File: classes/BulkQBInvoicingService.cls Sharing: with sharing

4.1 Public @AuraEnabled Methods

generateTemplate() — cacheable=true

Returns: JSON String

Reads field configuration from CM_QB_Connector_Settings__mdt (record BulkInvoiceFieldConfig) via getFieldConfig(). Extracts field names in order. Builds a two-line CSV string (header row + one blank row) with a UTF-8 BOM prepended for Excel compatibility. Base64-encodes the CSV via EncodingUtil.base64Encode. Returns:

{
  "isSuccess": true,
  "fileName": "QuickBooksInvoiceTemplate.csv",
  "sMessage": "QuickbooksCompanyId,CustomerId,...",
  "base64": "<base64 string>"
}

Error: Returns isSuccess: false with sMessage on exception.

validateHeaders(String csvContent) — cacheable=false

Returns: JSON String

Parses the first line of the CSV via parseCsv(), extracts uploaded column names, and compares against getTemplateHeaders(). Reports all missing column names. Does not check column order (that is done client-side in checkExactHeaderOrder). Returns:

{
  "isSuccess": true | false,
  "errors": ["Your file is missing required columns: X, Y."]
}

validateData(String csvContent) — cacheable=false

Returns: JSON String

Full row-level validation pipeline:

1. Parses all rows via parseCsv().

2. Loads field config via getFieldConfig().

3. Loads all active QB Companies, Customers, Products into Id-keyed maps (one query each, not per row).

4. Builds display label maps for the three lookup types (Company Name, Display Name, Product Name).

5. For each row, for each field config entry:

   • Required check.

   • Type check via validateDataType() (Decimal, Integer, Date MM/DD/YYYY or ISO, Lookup existence).

   • Negative number check for Decimal/Integer.

   • Date normalization to ISO (YYYY-MM-DD) for lightning-input type="date" compatibility.

   • Invalid field value is nulled in the clean row (sent back to LWC for display) so the user sees an empty, editable cell.

Returns:

{
  "isSuccess": true | false,
  "allRecords": [...sanitized rows...],
  "errorRowNums": [1, 3],
  "errorsByRowNum": { "1": ["Row 1: ..."], "3": ["Row 3: ..."] },
  "errors": [[...row 1 errors...], [...row 3 errors...]],
  "parsedRecords": [...valid rows only...],
  "errorRecords": [...invalid rows only...],
  "companyLabels": { "<id>": "Company Name" },
  "customerLabels": { "<id>": "Display Name" },
  "productLabels": { "<id>": "Product Name" }
}

createInvoiceRecordsFromCsv(String csvContent) — cacheable=false

Returns: Map<String, Object>

End-to-end import pipeline with all-or-nothing transactional semantics (explicit savepoint + rollback):

Step 1 — Parse & validate Parses CSV, validates headers, loads lookup maps.

Step 2 — Grouping Groups rows by composite key: CompanyId::CustomerId::InvoiceDate::Terms. Each unique combination becomes one InvoiceGroup holding all its source rows.

Step 3 — Build Invoice records For each InvoiceGroup:

• Sets cm_finance__Quickbooks_Company__c, cm_finance__Quickbooks_Customer__c, cm_finance__Invoice_Date__c, cm_finance__Terms__c.

• Computes cm_finance__Total_Amount__c = Σ (Qty × UnitPrice) across all rows in the group.

• Copies billing address fields (Country, Street, City, State, Postal Code, Email) from the QB Customer record.

• Copies cm_finance__Parent_Record_ID__c and cm_finance__Quickbooks_Customer_QB_Id__c from the Customer.

• Computes cm_finance__Due_Date__c = InvoiceDate + Terms (days) via calculateDueDate().

• Sets cm_finance__Process_via_Batch__c = true and cm_finance__Status__c = 'Awaiting QB Sync'.

Step 4 — Insert Invoices (partial error capture) Uses Database.create (partial=true behavior). Any DML failures are mapped back to the source row numbers in mapErrorsByRowNum. If any invoice insert fails → rolls back entire savepoint.

Step 5 — Build Invoice Item records For each row in each group, creates one cm_finance__Quickbooks_Invoice_Item__c:

• Sets Invoice__c to the inserted Invoice Id.

• Sets cm_finance__Quickbooks_Product__c, Quantity__c, Unit_Price__c.

• Sets cm_finance__Rate__c = Qty × UnitPrice.

• Sets cm_finance__Invoice_Line_Id__c (sequential integer per Invoice, tracked in mapInvoiceLineCounter).

• Sets cm_finance__Quickbooks_Product_QB_Id__c from the product map.

• Sets cm_finance__Composite_Unique_Key__c = lineId::InvoiceId.

Step 6 — Insert Items (partial error capture) Same pattern as Step 4. If any item insert fails → rolls back to the savepoint (undoing Invoice inserts too).

Returns:

{
  "isSuccess": true | false,
  "createdInvoiceCount": 5,
  "createdInvoiceItemCount": 12,
  "errors": [],
  "errorsByRowNum": {},
  "errorRowNums": []
}

On failure, errorsByRowNum and errorRowNums let the LWC highlight exactly which rows caused the DML error.

searchLookup(String sLookupType, String sSearchTerm) — cacheable=true

Returns: List<LookupOption>

Executes a LIKE search on the label field for the given type:

Returns up to 20 results ordered by label. Each result is a LookupOption { id, label } with both @AuraEnabled.

4.2 Private Helper Methods

4.3 Inner Classes

FieldConfig

InvoiceGroup (private)

LookupOption

5. Configuration — BulkInvoiceFieldConfig Custom Metadata

Object: CM_QB_Connector_Settings__mdt Record: BulkInvoiceFieldConfig Field: Value__c — JSON string

Controls which columns appear in the CSV template and how each is validated. Changing this record changes the template, validation, and column order for all users without a code deploy.

Default JSON:

{
  "fieldConfigurations": [
    { "fieldName": "QuickbooksCompanyId", "dataType": "Lookup",  "isRequired": true, "lookupType": "QuickbooksCompany"  },
    { "fieldName": "CustomerId",          "dataType": "Lookup",  "isRequired": true, "lookupType": "QuickbooksCustomer" },
    { "fieldName": "ProductId",           "dataType": "Lookup",  "isRequired": true, "lookupType": "QuickbooksProduct"  },
    { "fieldName": "InvoiceDate",         "dataType": "Date",    "isRequired": true, "lookupType": null                 },
    { "fieldName": "Terms (Days)",        "dataType": "Integer", "isRequired": true, "lookupType": null                 },
    { "fieldName": "Quantity",            "dataType": "Decimal", "isRequired": true, "lookupType": null                 },
    { "fieldName": "UnitPrice",           "dataType": "Decimal", "isRequired": true, "lookupType": null                 }
  ]
}

6. End-to-End User Flow

Step 1 — Upload
  connectedCallback
    └─► generateTemplate (Apex)
          └─► requiredColumns populated

  User clicks "Download Template"
    └─► generateTemplate (Apex) → base64 CSV download

  User uploads CSV
    └─► FileReader.readAsText
    └─► validateHeaders (Apex) — server missing-column check
    └─► checkExactHeaderOrder (JS) — column count + order check
    └─► [advance to Step 2]
    └─► validateData (Apex)
          ├─► parseCsv
          ├─► getFieldConfig (CMT)
          ├─► getQBCompanies / getQBCustomers / getQBProducts (3 SOQL)
          ├─► Per-row: required + type + lookup existence checks
          └─► Returns allRecords + errorRowNums + label maps
    └─► validatedRows decorated + sorted (errors first)

Step 2 — Review
  User edits cells (date/number inputs) → cellchange → handleCellChange
  User edits lookups → searchLookup (Apex) → handleLookupSearch
                     → handleLookupSelect / handleLookupClear

  User clicks "Check My Changes"
    └─► buildCsvFromUiRows (JS) → csvContent rebuilt
    └─► validateData (Apex) — full re-validation
    └─► validatedRows refreshed

  User clicks "Create Invoices"
    └─► errorCount must be 0
    └─► buildCsvFromUiRows (JS)
    └─► createInvoiceRecordsFromCsv (Apex)
          ├─► parseCsv
          ├─► header re-validation
          ├─► per-row validation (same as validateData)
          ├─► grouping by composite key
          ├─► Database.setSavepoint
          ├─► Invoice INSERT (partial DML)
          │     └─► DML error → rollback + return errors
          ├─► Invoice Item INSERT (partial DML)
          │     └─► DML error → rollback + return errors
          └─► isSuccess = true → toast + close panel

7. Invoice Consolidation Logic

Rows are consolidated into a single invoice when they share the same composite key:

CompanyId :: CustomerId :: InvoiceDate :: Terms(Days)

Each row within a group becomes one cm_finance__Quickbooks_Invoice_Item__c line on the shared invoice. The invoice's Total_Amount__c is the sum of all line amounts (Qty × UnitPrice) across all rows in the group. Invoice Line IDs are assigned sequentially per invoice starting at 1.

Example: If a CSV has 5 rows, 3 sharing the same Company/Customer/Date/Terms and 2 with different Terms:

8. Validation Rules Summary

Validation errors are row-scoped. A row with any error is excluded from the insert. If any row has an error at import time, the entire operation is blocked (no partial inserts).

9. DML Transaction Safety

createInvoiceRecordsFromCsv uses an explicit savepoint to guarantee atomicity:

1. Database.setSavepoint() before any DML.

2. Database.create (partial=false) for invoices — any failure triggers Database.rollback(sp) and immediate return.

3. Database.create (partial=false) for items — any failure triggers Database.rollback(sp) and immediate return.

4. No savepoint release is needed; Salesforce auto-commits if the transaction completes normally.

This means either all invoices and items are inserted, or none are.