Contents - Index


External Database Setup for Importing Orders

This is used to set up Lading Manager to be able to directly import an order from an external database (to create a BOL). Once all of the sections on this screen have been defined and tested, then you will be ready to import orders.

Database Connection
The first step is to create a connection to the external database. The database must be either Microsoft SQL Server (MSSQL), Oracle, or an ODBC driver. Provide the following fields:
  • Server Name: The name of the server (or IP address), or the name of the ODBC driver . A local MSSQL connection may be called, ".\SQLExpress". A local Oracle connection may be called, "localhost". Typically, this is the name of the server or instance.
  • Database Name: For MSSQL only, this is the name of the database.
  • Database Login User ID: The user i.d. that has the security to access the database.
  • Database Login Password: The password for the Database Login.

    OR -- you may provide your own database connection string. (This requires knowledge of Microsoft OLE DB connections.) Providing your own string allows you to connect to other databases besides SQL Server and Oracle.

    Use the Test Connection button to test your connection to the database.

    Database Query
    Lading Manager will access your order data through a query. Use the Fill with Example button to get started. This will start you with the following SQL statement:

      Select * from OrderFile
      Where OrderNumber = '{ORDERNUMBER}'

    This is meant only to provide a starting point; please rewrite this to work with your database tables.  The {ORDERNUMBER} coding will be replaced by your order number upon execution. This coding MUST BE PRESENT or Lading Manager will refuse to perform the import. 

    To test your query, type an order into the Order Number field and then click the Test the Query button. 

    Field Mapping
    Once your query works, you are ready to map your data fields to that of Lading Manager. In order to map the fields, you must have an understanding of Lading Manager's internal tables.

    To begin, click the Load Fields from Database Query button. This will provide you with a list of the field names extracted as the direct result of your query. Lading Manager will do its best to determine the type of data for each of your fields.

    For those fields that are to be imported to the bill of lading, you must ensure the Field Type is accurate and you must assign a corresponding Lading Manager field via the Set Field button.

    For the field that represents your order number, please select "SPCL Order Number" from the bottom of the list.

    Lading Manager Tables
    CarInfo: This is from the "Carrier Info." tab and will print in the Carrier Information section on the bill.

    Carriers: This is the carrier who will deliver your goods. The carrier information you provide will be entered into the Carriers table. 

    The "Carrier Number" field controls the number under which the carrier will be tracked. If you specify the carrier number and a record already exists in the Carriers table, it will be updated with the name and address data you provide. If you do not specify name and address data and a record exists in the table, then nothing will be updated and the existing carrier information will be used.

    If you do not specify a carrier number and carrier name and address information is provided, then Lading Manager will attempt to determine if a carrier record already exists for the information provided. If so, that carrier will be used. If not, then Lading Manager will create a carrier record for you and will make up a number for that carrier.

    Cons: This is the consignee (ship to) for this bill. The consignee information will be entered into the Cons table.

    The "Consignee Number" field controls the number under which the consignee will be tracked. If you specify the consignee number and a record already exists in the Cons table, it will be updated with the name and address data you provide. If you do not specify name and address data and a record exists in the table, then nothing will be updated and the existing consignee information will be used.

    If you do not specify a consignee number and consignee name and address information is provided, then Lading Manager will attempt to determine if a consignee record already exists for the information provided. If so, that consignee will be used. If not, then Lading Manager will create a consignee record for you and will make up a number for that consignee.

    CustOrderInfo: This is from the "Customer" tab and will print in the Customer Order Information section on the bill. This is an optional section and is not required.

    Lading: This is from the "Bill," "Bill 2," and "C.O.D." tabs.

    Origins: This is the origin (ship from) for this bill. The origin information will be entered into the Origins table.

    The "Origin Number" field controls the number under which the origin will be tracked. If you specify the origin number and a record already exists in the Origins table, it will be updated with the name and address data you provide. If you do not specify name and address data and a record exists in the table, then nothing will be updated and the existing origin information will be used.

    If you do not specify a origin number and origin name and address information is provided, then Lading Manager will attempt to determine if a origin record already exists for the information provided. If so, that origin will be used. If not, then Lading Manager will create a origin record for you and will make up a number for that origin.

    If you do not specify any origin data fields, then Lading Manager will use the origin as specifed in Defaults Maintenance.

    Field Type Notes
    The field types should be fairly self-explanatory. There are a few fields in Lading Manager that are defined as "bit." A "bit" is a logical field that is either true or false. These fields are:
  • Carrier Info Hazardous Materials Indicator
  • Customer Order Pallet/Slip Indicator
  • Consignee Customer Check Acceptable
  • Lading Carrier NMFC Indicator
  • Lading Customer Check Acceptable
  • Lading COD Enabled
  • Lading COD Prepaid Indicator (true = Prepaid, false = Collect)
  • Lading Consignee Free On Board Indicator
  • Lading Shipper Free On Board Indicator

    If you are mapping to one of these fields, and your field is not "bit," then Lading Manager will employ the following rules:
  • Y, T, 1, or X will be treated as True.
  • N, F, 0 (zero) or blank will be treated as False.

    If your field is character, only the first position of the character string would be used. So, "yes" would still work for True.

    Other Field Notes
    Freight Charge Terms: Set this to 0 for Collect, 1 for Prepaid, and 2 for 3rd Party.

    Pulling Multiple Orders from Alternate Field
    This is an alternate method of pulling multiple order based on a different query. The individual orders will still be extracted from the Database Query defined; this will allow you to pull a list of orders based on different selection criteria.

    Click the Fill with Example button to get started. That will give you the following query example:

      Select * from OrderFile
      Where AlternateFieldNameGoesHere = '{ALTERNATEFIELD}'

    This is meant only to provide a starting point; please rewrite this to work with your database tables.  The {ALTERNATEFIELD} coding will be replaced by your alternate field data upon execution. This coding MUST BE PRESENT or Lading Manager will refuse to perform the query. 

    You must also specify the name of the field (from this alternate query) that represents the order number. Enter that field name into the Order Number Field Name.

    To test your query, type an alternate field value into the Alternate Field Value field and then click the Test the Query button.