Skip to main content

Importing Invoices and Commissions

If you'd like to use the template we've created, you can download it here: Invoice Import Template revised.xlsx

If you work as a commission manager for a sales agency, you know how tedious it can be to record and keep track of invoices and commissions. With MarketTime, you can simplify this process by importing data directly, which saves you time and effort.

This article will guide you through using the "Import Commission" tool in MarketTime to import invoices and commissions from manufacturers.

 

Before You Start

Here are a few things to keep in mind before you import invoices and commissions:

  • You must be an admin user with Read/Write permission for Order Payments > Import Commissions
  • Commissions can be imported to one manufacturer at a time
  • The commission import process must be completed in one session, which means that you will not be able to start the process, leave, and then come back to resume the process

Preparing Your File for Import

What template should I use?

It's up to you! If the commission file you intend to import contains the required columns, you can use any template you prefer—or no template. Most users of the commission import tool upload the file they receive from the manufacturer or vendor. You can reformat or remove columns in these files if you want to, but it's not required.

Download Template

If you'd like to use the template we've created, you can download it here: Invoice Import Template revised.xlsx

File Requirements

  1. File type must be in .XLS, .XLSX, or .CSV format.
  2. File must only contain one worksheet.
  3. File must contain these required columns:
    1. PO#
    2. Invoice Number
    3. Invoice Date
    4. Invoice Amount
    5. Commission Amount
  4. We also recommend including these columns for any new direct orders:
    1. Ship To Retailer Name
    2. Ship To Zipcode

Other Tips Before Uploading

Here are a few more tips before you import a commission file or statement:

Tip ✔️ ❌
Delete extra worksheets or consolidate all invoices and commissions into one sheet because the import tool only supports importing one sheet from the file. Keep: One sheet Remove: Multiple sheets
Remove any extra rows that contain totals, sums, or counts for the items on the sheet. Keep: One header row plus one row per invoice Remove: Total rows at the bottom of the sheet
Remove any special formatting or formulas. Keep: Values Only Remove: Formulas
Remove extra columns (optional, not required). Keep: Required columns Remove: Extra columns
Check for bad formatting. Keep: Properly formatted dates Change: Improperly formatted dates

How to Import Commissions

To import commissions, follow these four steps:

  1. Upload Your File
  2. Map Columns
  3. Preview Invoices and Resolve Errors
  4. Finish Importing Commissions

Upload Your File

  1. Login to https://apps.markettime.com/login
  2. Navigate to Order Payments > Import Commissions
  3. Select the manufacturer from the Select Manufacturer dropdown
  4. Select your commission check

    • If you have already added your commission check: Click the Select Check dropdown and select your check from the list
    • If you have not already added your commission check: Add it using the Add Check form on the right side of the screen or follow these steps to add your commission check
    • If you do not have a commission check yet: Skip this step
  5. Click Import Invoice & Commission
  6. Click Choose
  7. Locate the file on your computer

Map Columns

After you have uploaded your file, you will see a screen that looks like this:

  • Columns that were automatically mapped from your file will have a green checkmark (✔️)
  • Columns that could not be mapped automatically will have a red x (❌) to the right

Once you are on this screen, follow these steps

  1. Look in the Database Column to Match column on the screen
  2. Verify the auto-mapped columns were mapped correctly
  3. For any column with a red x (❌), click the dropdown
  4. Select the matching column from the list
  5. Repeat for all columns you want to import
  6. Skip any columns you don't want to import
  7. Click Next

Preview Invoices and Resolve Errors

Once you've mapped columns, you will see the "Preview Table" screen:

To review invoices and resolve errors, follow these steps:

  1. Toggle to the right (so it turns blue) the Only Show Invoices with Errors/Warnings to view only the invoices with errors or warnings
  2. Review the errors and warnings and resolve them for each invoice (a list of messages is provided below)
  3. To edit a value, click into the cell:
  4. Once you have resolved the warnings and errors, click the Order Date column header to sort the list by order date
  5. Look for any invoices that were auto-matched to old orders
    • If you want to make a new direct order: Toggle to the right the Create Direct Order button
    • If you want to match to an existing PO already in MarketTime:
      1. Click into the PO Number cell and delete a few characters
      2. Click out of the cell
      3. After the "Error: Order not marked as Direct or No orders exist with the given PO Number message appears", click Search to find the correct PO number
  6. Once you're done resolving warnings and errors, click Next

Commission Import Warnings and Errors

  • Warnings: Invoices with warnings can be imported
  • Errors: Invoices with errors will not be imported

WARNINGS
Message Why This Shows Up Steps to Resolve
Order not marked as Direct or No orders exist with the given PO Number. The importer needed help finding a match for PO# + Ship To Address Name + Ship To Zip Code.
  • Click Search and use the Search PO Number tool to find the PO you want to match with this invoice
  • Or, toggle to ON (so it turns blue) the Create Direct Order button for this invoice
Retailer not found for the invoice The importer needed help finding an exact match between the Ship To Address Name and + the Ship To Zip Code.

For example:

  • The name is misspelled
  • The name in the file is the "Bill to Address Name" instead of the "Ship To Address Name"
  • The address name is the bill to address, and the zip code is the ship to zip code
  1. Click on the customer name cell
  2. Start typing to search for and select the customer
  3. If no customer is found, click Add to add that customer
Multiple orders (#) exist with the given PO Number
  • There are multiple duplicate PO Numbers in the system, including for different customers
  • If everything is the same, we will ask you to search for and select the correct PO# to tie to this invoice (you will make this judgment call).
  1. Click Search
  2. Use the Search PO Number tool to find and select the correct order

ERRORS
Message Why This Shows Up Steps to Resolve
Invalid or Empty PO Number The PO Number is missing from the import file or is an invalid format.
  1. Click the PO Number cell for that invoice
  2. Add the missing PO Number or remove any special formatting
Invoice has no commission amount The commission amount in the file is missing or is $0.
  1. Click the Commission Amount cell for that invoice
  2. Add the commission amount
Invoice has no salesperson to pay
  • The order is missing a "Salesperson to Pay" value
  • The ship-to-address for the customer does not have a salesperson assigned, and this is a new, direct order
  • Even if the bill-to address (i.e., the primary shipping location) has a salesperson assigned, if the ship-to address has no salesperson assigned, this error can appear
  1. Click the Salesperson Action dropdown
  2. Select whether you want to:
    1. Import Invoice As Is 
    2. Do Not Import

 

Finish Importing Commissions

After you have resolved the errors, follow these steps to finish the import commission process:

  1. Decide how you want to import duplicate direct order PO numbers
    • One Direct Order (With Multiple Invoices): When the commission statement contains multiple matching PO numbers and each PO number is marked as "Create Direct Order," a single order with multiple invoices will be created.
      • If the commission statement contains:
        • PO #1 with Invoice #1 ($100)
        • PO #1 with Invoice #2 (-$50)
      • After import, you will have:
        • PO#1 with Invoices #1 and #2 (Order Total Amount: $50)
    • Separate Direct Orders (One Per Invoice): When the commission statement contains multiple matching PO numbers marked as "Create Direct Order," separate direct orders will be created, each with a single invoice.
      • If the commission statement contains:
        • PO #1 with Invoice #1 ($100)
        • PO #1 with Invoice #2 (-$50)
      • After import, you will have:
        • PO#1 with Invoice #1 (Order Total Amount: $100)
        • PO#1 with Invoice #2 (Order Total Amount: -$50)
  2. Click Import Invoice/Commission
  3. If there are any errors, you will see an Import Error popup
  4. Click View Errors to download the list of errors
  5. Once you have reviewed the errors, you can attempt to re-import those invoices or enter them by hand

FAQs

Here are some frequently asked questions we receive about importing commissions:

When importing invoices and the vendor gives the account number, will this pull the correct customer with the account number?

Including the customer account number when you use the Commission Import Tool makes it easy for the system to find the right customer automatically. This can help, especially when your account has duplicate or similar customer names. To learn more about how the system auto-matches customers during commission import, you can visit this help article for details: Why does the import tool not find the customer when I want to create direct orders?

Is there a quick way to change formulas to values in Excel or copy and paste using values?

For tips and tricks on copying and pasting formulas as values in Excel, please check these articles:

What do I do when I see the warning about no salesperson to pay?

If you see a warning that says "Warning: Invoice has no salesperson to pay" when importing commission:

Check these items:

  • For orders already in MarketTime: Double-check that the order has a "Salesperson to Pay" value. If it doesn't, add one and then attempt the import again or manually enter the invoice.
  • For new, direct orders: Double-check that the customer has a salesperson assigned to the manufacturer on the order. If no salesperson is assigned, assign one to the customer and try to import the invoice again or manually enter it.

If you have checked both items above but still haven't paid the salesperson, please get in touch with support for assistance.

Is there a template with a format for Excel to use for importing commissions?

Yes! You can find that template on this page: Importing Invoices and Commissions.

If I import commission and later realize it was the wrong file. Do I have to delete the invoices individually?

You will need to delete the invoices individually to remove them. Otherwise, you can re-import the invoices with the correct information and replace them using these options at the end of the "Commission Import" process:

  • Ignore Existing & Import: Ignore invoices in your new import file that is already in the system, and import any new invoices from the file.
    • For example: If your file contains 10 pre-existing invoices and 10 new invoices, only the 10 new invoices will be imported.
  • Save/Add Existing & Import: Add the invoices in the file to any existing orders in the system.
    • For example, if your file contains 10 pre-existing invoices and 10 new invoices, all 20 invoices (existing and new) will be imported.
  • Replace Existing & Import: Replace the existing invoices already in the system with versions that match your new import file.
    • For example: If your file contains 10 pre-existing invoices and 10 new invoices, the 10 new invoices will get updated with the information in your file, and the 10 new invoices will be imported.

Can I import the commission percent instead of the commission amount?

Currently, the commission import tool only allows you to import the commission amount. This means you will need to calculate the amount manually in Excel or Google Sheets using the manufacturer commission rate they paid you on each order, and then import the final commission amount value for each invoice in the sheet.

How do I add a new customer when importing commissions?

If you are importing an invoice and commission for a new customer as a direct order, you can add that new customer during commission by following these steps:

  1. Ensure your import file includes a column for Customer Name
    1. Note: The column can be empty/blank if you do not have this information
  2. When you are on the Preview Table (step #3), look for any invoice errors that say "Error: Customer not found for the Invoice"
  3. Look in the Customer Name (In Your File) column for that invoice
  4. Click into the table cell that contains the customer name
  5. Search for the customer
  6. If you don't find a match, click Add
  7. Add the new customer
  8. The invoice will be automatically updated and linked to the new customer you created

Why does the import tool not find the customer when I want to create direct orders?

A customer name alone is insufficient to prevent "Customer Not Found for the Invoice" errors. The importer requires additional customer information above and beyond the name to auto-match to customers already in the system.

You will see fewer errors if you can include additional columns (on top of the required columns) in your import file. Here are some options for additional columns that the importer can use to choose customers already in the system automatically, so you don't have to fix each error manually during import:

Option Additional Columns to Include
(In Addition to Required Columns)
One
  1. Ship To Name
  2. Ship To Zip Code
Two
  1. Bill To Name
  2. Account Number
Three
  1. Bill To Name
  2. Ship To Account Number
Four
  1. Ship To Name
  2. Account Number
Five
  1. Ship To Name
  2. Ship To Account Number

These columns refer to these values from the order:

The five options above are additional columns to add to your file. The required columns for any import still include the following:

  1. PO#
  2. Invoice Number
  3. Invoice Date
  4. Invoice Amount
  5. Commission Amount

Getting these additional columns from the manufacturer can be challenging, if not impossible. We are continually working to improve the import commission tool, and one of the items on our to-do list is to find better ways to auto-select customers in MarketTime during import.

Last updated:
Was this article helpful?