Import Carrier Statement Mapping Gears
When mapping a carrier statement, Gear Icons appear next to specific fields in the mapping screen. Each gear opens an advanced configuration menu that lets you manipulate raw data from your file before it is imported into Commission Tracker.
💡 Why Use This
Use the gear icon settings when:
- Policy numbers don't match — Strip carrier-added prefixes or suffixes so policy numbers align with your database.
- Dates are off by one month — Automatically adjust "Paid-To-Date" values to the correct billing month.
- The file lacks a final commission column — Build a formula to calculate Premium, Subscribers, or Commission from the available columns.
- Agents are paid flat dollar amounts — Define how flat-dollar agent splits are handled during the import.
🗺️ How to Access
Gear icons appear automatically during the column mapping step of an Import Carrier Statement session. Click any gear icon next to a mapped field to open its advanced settings.
General Map Settings
The main gear icon at the top of the mapping screen controls global logic for agent payouts.
- Dollar Amount Payouts — Define how agents receiving a flat dollar amount (rather than a percentage) are processed during the import.
Policy Number Manipulation
Carriers often include extra prefixes or suffixes on policy numbers that do not match your database.
- Character Removal — Automatically strip a specified number of characters from the Start or End of the Policy Number string during import.
Billing Date Adjustments
Some carriers provide a "Paid-To-Date" instead of a "Billing Date."
- Subtract One Month — Checking this option automatically subtracts one month from the date in the statement, aligning the payment with the correct billing cycle in your policy terms.
Financial Formulas (Premium, Subscribers, and Commission)
When the carrier file does not provide a final calculated value, use the Formula Input gears to build the calculation from available columns.
Premium and Subscribers
Apply custom formulas to calculate a new Premium or Subscriber value based on columns in your spreadsheet.
-
Premium Gear:
-
Subscribers Gear:
Commission Value
If the carrier statement provides gross commission but you need to calculate a net value or apply a multiplier, use the Commission Formula gear.
- Commission Gear:
Advanced Formula Interface
Clicking any financial gear opens a formula builder where you can combine columns using standard mathematical operators (+, -, *, /).
Quick Reference: When to Use Each Gear
| Feature | Best Use Case |
|---|---|
| Start/End Trim | Removing "POL-" prefixes or "-01" suffixes from carrier policy numbers. |
| Date Offset | Aligning "Paid Through" dates with your internal billing month tracking. |
| Formulas | Calculating Premium when only "Rate" and "Volume" columns are provided by the carrier. |
🔧 Troubleshooting
Policy numbers still aren't matching after trimming characters. Verify the exact number of characters to remove. Open the carrier file and count the prefix or suffix characters carefully — off by one will still cause mismatches. Also check whether the carrier uses leading zeros that may need to be handled separately.
The Billing Date is still one month off after enabling Subtract One Month. Confirm the date in the carrier file is actually a "Paid-To-Date" rather than a true "Billing Date." If the carrier has already provided the correct billing month, the offset will shift it in the wrong direction. Disable the setting and re-import.
My commission formula is calculating the wrong total. Double-check the column letters referenced in the formula builder against the actual columns in your file. Column assignments can shift if the carrier changes their file layout.
🔗 Related Topics
- Import Carrier Statements (Excel Statements) — Overview of the full ICS import process.
- Example — Import Carrier Statement — A walkthrough of a complete import.
- Errors — Import Carrier Statement (ICSE) — Resolve payments that failed to import.
Need help? Contact support@commission-tracker.com