How Do I import customers or invoices into BrokerForceŽ?

Importing vendor customer lists and invoices requires BrokerForce Enterprise and Internet synchronization services.  Pending that you subscribe to these services, this is a guide for how to import a batch of invoices or customers.  This process will reduce the labor for posting invoices and commission by 95%.

The same "Orders_Excel" tool is used to import customer lists and invoices.  Both processes begin with a comparison of customer lists based on the vendor's customer number (required).  Before importing your first batch from a vendor, you should forward a sample of that manufacturer's file format to support@brokerforce.com. Although the application can import a wide variety of formats, the file must conform to the guidelines in the How to return Invoices to BrokerForceŽ Users FAQ.  BrokerForce staff can map the format for you and synchronize that map to your data set.  When you next synchronize, the fields (columns) will already be mapped for you to import.

If you import a batch of invoices a second time, your accounting should not be adversely affected by this since duplicates are excluded by vendor by invoice number.  This may be done if information such as a vendor customer number(s) was missing in your data during a previous import. 

When line item details are imported, the invoice number will be repeated on each row of the spreadsheet being imported.  However, the invoice will only be imported once into the header portion of the order/invoice and then the details are added for each line item on that invoice.

Backorders are automatically calculated and carried forward.

Locate and validate the file

  1. Internet Synchronize
  2. click the <EDI> tab
  3. Select "Orders_Excel" from the <Import> drop list
  4. Select the appropriate vendor
  5. Click the <Open Folder> button at the right end of the <Source Path> control to navigate (browse) to the file to be imported.  Multiple files should be imported chronologically.
  6. Click the <Import> button
  7. You can map the invoice columns yourself if you are familiar with BrokerForce field names.
  8. For customer information, only ship to (name, address, phone) information is used .
  9. If line item details are included in your import, select only "detail" prefix items from the drop list above each detail field (column)  -- Example: for line item quantity use only DetailQuantity NOT Quantity, for the item price use DetailUnitPrice NOT OrderAmt or InvoiceAmt, for line item commission use DetailComm NOT CommPct, for a line item discount use DetailDiscount NOT Discount, for the line item status use DetailStatus NOT Status, for the first option level use DetailUnit, for the second option level use DetailPack, for the 3rd option level use DetailOpt3, for the 4th use DetailOpt4... for the item number use DetailVendorItem, for product description use DetailProductName.
  10. After reviewing that the information in the Vendor Orders Map form is in the correct column sequence and the number of columns has not changed, click the <Continue> button.
  11. A validation process will begin to test for data types and field lengths.  If a field (column) is not compliant, you will be prompted regarding the issue and to allow the program to correct it.  Answer <Yes> to allow the program to attempt to correct the issue.  If you do not want to continue to be prompted and know that this is the correct format, you can click <Cancel> on any of the validations to avoid additional prompts.

Match your customers to your vendor's

BrokerForce uses the vendor customer number to match your customers to your vendor's customers. In a window on the left, you will be shown a list of the customers from the incoming invoice headers that do not have a corresponding vendor customer number match in your data on the right.   Click on a customer name in the window on the left (1), then click <Find>(2).  The nearest match will be found in your list of customers for this vendor.  There are three possible conditions for any customers on the incoming invoices that are missing a corresponding vendor customer number in your data:

  1. The customer is shown in your list of customers but does not have the correct vendor's customer number.  If the <Find> process locates the correct customer or you can browse to the correct customer in your list of customers on the right, click <Update> (3) to update your customer number to that of the vendor.  That customer name will be removed form the list on the left.
  2. The customer is in your customer database for all vendors but is not listed as a customer of this vendor.  In this case, the <Find> process will not find the correct customer and you can not browse to the customer in your list of customers for this vendor.  To test if this customer is in your full list of customers, click the <New Record> (4) (arrow pointing to a star) at the bottom of your list of customers for this vendor.  Try selecting the customer from your full list of customers.  If you find the right one, save your change and click the <Update> (3)button to update their vendor customer number.  That customer's name will be removed from the list on the left.
  3. The customer in the list on the left is new to your company.  If so, click <Add> (5).  The <New Customer Dialog> will pop-up using the information available from the vendor's invoice header.  This information can be edited and the rep assignment can be made in this dialog box.  When you close the dialog, the customer information will be added to your full customer list and to this vendor's list of customers. The customer name will be removed from the list at left.
  4. Use the <Update> button at left (not shown) to change a drop ship invoice to the customer that you have selected in your list of customers at right.  This will force that invoice into the account that you have chosen in your list of customers at right.
  5. Click the <PO?> button to open the matching PO number for clarification of shipping information.  This may help you determine the assignment.

For combination Bill to and Ship to numbers: Only BrokerForce version 3.0.26 or greater supports this.

If you are only importing a list of customers, click <Cancel> after you have finished matching your customer lists.

Import the invoices

  1. Click the <Continue> button at the bottom left of the form
  2. A progress counter is shown at the bottom of the form.  This process may be lengthy depending on the number of invoices being imported and whether or not there are line item details with the invoices.  You do not need to attend this part of the process and should be able to estimate the time for the process to complete based on the counter.
  3. When the process completes, you will be prompted "Export a list of invoices not imported?".  You should answer <Yes> and review the comment at right on each row for the reason.  The two common reasons are "Missing Vendor Customer Number" or "Duplicate Invoice #".
  4. When prompted to "Export list of invoices imported?" you should answer <Yes> to see the status of those invoices.  This will show the order source (if available) and the status of the order "Invoiced", or "Back Order".
  5. The process will close when completed.

Post Commission Payments

  1. If the import file includes the vendor's payment number (check number), enter the vendor payment into BrokerForce before importing that batch of invoices.  Use the <Payments> tab on the vendor form to enter the payment.  Map the vendor's payment number column to the <VPmtNo> column in BrokerForce.
  2. If the file being imported includes the commission amount for the total invoice, map that column to the <VAmt> field in BrokerForce.
  3. If the payment information has not been provided, one can still apply a payment to a date range of invoices in seconds. To do this, enter the payment using the <Payments> tab on the vendor form, click the <Orders> list tab, enter the invoice date range that the payment covers in the <From> and <To> controls above and to the left of the orders list.  Select the payment number from the the <VPmtNo> drop list at the lower left of the <Orders> list.  If the available payment selected is greater than or equal to the anticipated amount for this date range of invoices, click the <VPmtNo> button to apply this payment to those invoices.  Each invoice will be stamped with this payment number and the anticipated amount to be paid.  Since BrokerForce assumes a pay-on-pay basis, the rep's commission shares will now be due and can be viewed using the "Commission Statement" report on the Representative form.

 

Return to FAQ's