Visual Match-Off Import Walkthrough

This walkthrough explains how to perform a Visual Match-Off Import to reconcile and settle outstanding documents that still appear in the Receivables/Payables Report or the Voucher Download Model, even though corresponding payment or receipt entries already exist in the ledger.


📌 When to Use Visual Match-Off

In day-to-day business operations, transactions frequently remain pending in your accounts reports:

  • SI – Sales Invoice
  • PB – Purchase Bill
  • AD – Advance Payment (Vendor advance / Customer payment)
  • OA – On-Account Receipt (Customer receipt / Vendor refund)

Why do these documents remain pending?

When payments or receipts are recorded on-account or as lump sums without linking them to specific invoice numbers at the time of entry, both the invoices and the payments continue to show as open lines in your AR/AP statements. Over time, statements can accumulate dozens or hundreds of open rows, making account reconciliation confusing and time-consuming.

Visual Match-Off allows you to clear these offsetting debits and credits in bulk by generating a balanced system Journal Voucher (JV).


🚀 How the FIFO Model Simplifies Your Task

ERP Crystal provides two ways to generate your match-off file: the Automated FIFO Model and the Standard Blank Model.

â„šī¸
Why the FIFO Model is a Game-Changer: Without the FIFO model, reconciling an active party’s account requires manually downloading the Receivables/Payables report, searching for unallocated receipts, matching them line-by-line against historical invoices, calculating the split amount for boundary invoices so the batch balances to zero, and copying and pasting 9 columns per row into Excel. This manual process can easily take 30–60 minutes per party and is prone to calculation and copy-paste errors.

With the FIFO (First-In, First-Out) Model, the entire process is automated in seconds:

  1. Automatic Chronological Matching: The system scans all open documents for the selected party and pairs the oldest open payments and receipts against the oldest open bills and invoices.
  2. Automatic Boundary & Split Calculation: The algorithm works out the net outstanding balance, identifies the exact cutoff boundary invoice, and splits the boundary amount automatically so that the downloaded file balances to exactly Net = 0.00.
  3. Zero Manual Copy-Pasting: All required identifiers (Doc Type, Ref Doc, Ref No, Ref Date, Doc No, Doc Date, Amount, Net, and Party Id) are pre-populated directly from the ERP database with 100% accuracy.
  4. 1-Click Settlement: You simply download the pre-filled Excel model, save it, and upload it back. The ERP immediately creates a Visual Match-Off Journal Voucher (JV Type: 'V'), dropping all matched historical documents and leaving only the latest unpaid invoices that total up to the true net outstanding.

đŸ› ī¸ Step-by-Step Walkthrough

Step 1: Access Visual Match-Off

  1. Navigate to Finance from the main navigation menu.
  2. Under the Transactions column, select Visual Match-off.

Visual Match-off Menu


Step 2: Choose Your Download Model

You can choose between two methods depending on your reconciliation needs:


Method A: Automated FIFO Model (Recommended)

Use this method when you want to clear historical open items for a specific customer or vendor automatically based on chronological priority.

  1. On the Visual Match Off screen, check the Download FIFO Model checkbox.
  2. A party selection dropdown and informational guide will appear.

Visual Match Off - FIFO Model

  1. In the Select Party field, search and select the desired Customer or Supplier (type at least 3 characters to search).
  2. Review the on-screen FIFO rules:
    • Download Model: The system works out the net outstanding and creates a download model to adjust/remove pending documents, leaving only the latest entries that total up to the net outstanding.
    • Upload & Match Off: When you upload this system-generated Excel file, a Visual Match Off JV will be created and all the previous documents will get dropped automatically.
  3. Click the green Download button at the bottom of the card.
  4. The system generates and downloads VisualMatchOff_Model.xlsx, pre-filled with the offsetting entries balanced to Net = 0.00.

System Notifications on FIFO Download:

  • If the party has no open transactions, the system will notify: “No outstanding transactions found for the selected party.”
  • If the party’s net outstanding is negative or no positive boundary can be determined, the system will notify: “No positive FIFO boundary found for the selected party.”

Method B: Standard / Blank Model (Manual Matching)

Use this method if you need to perform selective, non-chronological matching or reconcile specific individual entries manually.

  1. Ensure the Download FIFO Model checkbox remains unchecked.

Visual Match Off - Standard / Non-FIFO Screen

  1. Click the green Download button to download the blank template VisualMatchOff_Model.xlsx.

  2. Open the downloaded Excel file. You will see two sheets:

    • VisualMatchOff_Model: The data sheet containing the 9 required columns: Doc Type, Ref Doc, Ref No, Ref Date, Doc No, Doc Date, Amount, Net, and Party Id.

    Visual Match Off Blank Excel Template

    • Instructions: Guidelines on data formats and character length requirements for each column.

    Excel Template Instructions

  3. Generate the Receivables or Payables Report:

    • Go to Finance > Reports > Receivables/Payables.

    Finance Reports Menu

    • Select the ArAp Type (Receivable or Payable), specify your Customer/Supplier, set the date range, and click Print.

    Receivables / Payables Report Filter

  4. Prepare your matching data:

    • Open the downloaded Excel template and the exported report side by side.

    Report and Template Side by Side

    • In the exported report, ensure all required columns (such as Ref No, Ref Date, Doc No, Doc Date) are visible. Unhide columns if needed.

    Unhide Columns in Report

    • Copy the relevant entries you want to match off and paste them into the template sheet.

    Copy Details to Template

    • Verify that your template contains the matched debit and credit rows and that the Net Total equals 0.00.

    Template Ready with Matched Entries


Step 3: Prepare the File for Upload

Before uploading, ensure the following rules are met:

  1. File Name: Save the file with the exact name: ImportVisualMatchOff.xlsx.
  2. Net Zero Balance: The sum of the Net column must be 0.00 (total debits must match total credits).
  3. Data Integrity: When using the manual method, copy values exactly as they appear in the report without altering document numbers or dates.
âš ī¸
Do not rename column headers or change the file name from ImportVisualMatchOff.xlsx. Any deviation in column layout or file naming will result in an upload rejection.

Step 4: Upload the File

  1. Navigate back to Finance > Visual Match-off.
  2. Click Select File (paperclip button) and select your prepared ImportVisualMatchOff.xlsx file.
  3. Click the purple Upload button.

The system performs automatic validations:

  • Financial Year Validation: The file can only be uploaded in the active financial year.
  • Structural & Logic Validations: Checks document roles (SI, PB, OA, AD), validates party active status, and enforces the net zero rule.

Step 5: Review Results and Confirmation

Successful Upload

When validation succeeds:

  • An automated Journal Voucher (JV Type: 'V', Notes: “SYSTEM JV FOR MATCH-OFF”) is created in the database.
  • A confirmation Excel file (VisualMatchOffImportXL.xlsx) is automatically generated and downloaded.
  • The success message displays the created JV number and date:

    “Visual Match Off upload is succeeded. JV No. [JV_NO] / Dated [DD-MMM-YY] is created.”

Visual Match Off Success JV Confirmation

Error Handling

If any row fails validation:

  • The system prevents JV creation and downloads a results file highlighting specific error codes:
    • Code A: Invalid Ref Doc (must be SI, PB, OA, or AD).
    • Code B: The sum of the Net column is not zero (netamount != 0).
    • Code C: Invalid or inactive Party Id.
    • Code D: Missing or zero document/reference number (000000).
  • Correct the indicated rows in your spreadsheet and upload again.

📊 Comparison: FIFO Model vs. Manual Model

Feature / AspectAutomated FIFO ModelStandard Manual Model
Primary Use CaseClearing accumulated historical open items for an accountSelective, ad-hoc, or non-chronological specific invoice matching
Party SelectionSelected directly on screen prior to downloadFiltered during report generation
Data PopulationAutomatic: ERP pre-fills all matching rowsManual: User copies and pastes rows from reports
Net Zero BalancingAutomatic: ERP calculates boundary split to equal 0.00Manual: User must calculate and ensure Net sums to 0.00
Time RequiredUnder 1 minute15 to 45 minutes
Human Error RiskZero (system generated)Moderate (copy-paste, rounding, or typo errors)

✅ Final Result in ERP

After a successful Visual Match-Off upload:

  1. Clean AR/AP Statements: All matched entries (SI, PB, AD, OA) are cleared from the Receivables/Payables Report.
  2. Accurate Net Outstanding: Only the remaining open invoices appear, adding up directly to the party’s current ledger balance.
  3. Clean Voucher Import: Cleared documents no longer clutter the Voucher Import Model (Download Module).
  4. Complete Audit Trail: An official system Journal Voucher (JV) is permanently recorded in the Journal Register for full auditability.