×

BirdDog DataLink

BirdDog DataLink allows you to import customers, shiptos, items, item locations, prices, quantity on hand, sales orders, and more, into BirdDog Enterprise; orders placed within BirdDog will then be exported to a comma-separated value file that can be used to easily import those orders into your otherwise unsupported ERP. Accomplishing this does require some setup as well as the use of several different BirdDog features. The steps below can be boiled down to this:

  • Install Software
  • Create BirdDog Database
  • Create required Codes
  • Setup Email Server
  • Create a File Sync directory
  • Create master file export from ERP in the appropriate format
  • Create order import to ERP in the appropriate format
  • Setup Jobs
  • Test the system

Install Software

The following software will need to be running:

  • Enterprise for Windows - Use this to set up and maintain your jobs.
  • Job Master - This software automates the imports, which we get into setting up later.
  • Web Software - Certain required import elements can only be set up in the web software, so this will also need to be installed. This website will not be accessible outside of the machine that you set it up on unless you set it up to do so - consult your IT department for further assistance.

Create BirdDog Database

Once the software is set up, you'll need to create a new BirdDog Engine Database and add some required elements on the website.

  • BD Engine Database Setup: This part is remarkably easy, although it does require access to your installation of Microsoft SQL Server Management Studio (SMS).
    • Create a new blank database
    • Open the BlankBDStarter.SQL script for the version you are installing from our buildserver in SMS
    • Make the minor changes to the script included in its instructions
    • Run the SQL script we will provide you against that database

Create Required Codes

  • The following features need to be created on the website for later imports. You may want to create many other options, but these are the ones required for the database to function properly.
    • Customer Type Code - This is required for new customer imports and is the customer type that is used to assign general ledger accounts and group customers on sales reports.
    • Sales Rep or Salesperson - this is required for new customer imports. More information on Sales Reps can be found here.
    • Ship Via Code - This is required for new customer imports and is the default shipping method on new orders. These are set up in the web via System Management | Freight | Ship Vias or in Enterprise for Windows via Maintain | Freight | Shipping Methods.
    • Tax Codes or Tax Schedules - The system only requires one, but you may want to generate all of the codes that are appropriate to your business model.
    • Terms Code - This is required for new customer imports and is the identifier for the default payment terms on new orders.
    • Warehouse Location - Our script automatically creates a default location with the code 01, but you will want to create the ones in your ERP to match.
    • Field Definition Codes - Any user-defined fields that exist in the current ERP must also be generated in BirdDog. These can be created in one of two ways:
      • Using the Web software - Individual fields must be created one at a time
        • Navigate to System Management | System | Fields and click on Add New Field
        • Under the Group dropdown, select an appropriate option from the dropdown list. Options are:
        • The Field should match the Field Name of the existing user-defined field you have in your ERP.
        • Select the appropriate Data Type from the dropdown list; as noted above, the only valid options here are String, Floating (which is Numeric in some ERPs), and Date.
        • Data Length should also match.
        • Save
        • When you Run Connectors, this field will be pushed up to Service Pro automatically.
      • Using Imports - bulk generation of fields
        • If you have a large number of UDFs that need to be created and don't want to do these one at a time, you can import them in bulk using a comma-separated value (CSV) file.
        • On the website, navigate to System Management | System | Imports.
        • Click on Import Field Definitions
        • Download the Sample and populate using the on-page documentation and examples in the CSV
        • Drag and drop the completed CSV or click to upload
        • Click on Import
        • Job Master will import all of the field definitions into the BirdDog database and when you Run Connectors, those fields will be pushed up to Service Pro automatically.
  • There are other codes that you may wish to populate to match your ERP, but these are the ones that are required for imports to function properly.

Create a File Sync Directory

Now that Enterprise for Windows is functioning and we're done with the setup, we'll want to look at setting up the import process. Once everything is setup and running, your process will be simple: populate the appropriate CSV file using the proper format, then drop that folder into the folder set up for it. Job Master will handle the rest ... unless there's an error, but we'll get to that in a bit.

  • Create a new folder on your server in an appropriate location named Imports. Under this Folder, you should have multiple sub-folders for:
    • Customers
    • Item Locations
    • Items
    • Orders
    • Orders Out
    • Ship Tos
  • All of these but Orders Out should have two additional sub-folders called Errors and Successful Imports. The naming of these folders is not set in stone - you can call them whatever you wish - but these are our suggested formats for ease of use.
  • These folders need to be accessible to Job Master as well as Enterprise for Windows, so using a path in the format {Server Name}\{Share Name}\BirdDog Software\File Sync\Imports is recommended.
  • Using the above, the general layout should look something like this:
    • The purpose of this is simple: you drop the new CSVs into the appropriate folder (Customers, Items, Item Locations, Orders, Ship Tos), and then when Job Master completes the import into the BirdDog database, it will move the CSV to either Errors (if something is wrong with that CSV) or to Successful Imports (if everything went right.)
    • Orders Out is a tiny bit different as this is where the CSVs are placed for order information exported from Service Pro.

Create Master File Exports from ERP

  • To access the proper format of these files, navigate to the following in Enterprise for Windows and download a sample. Note that the CSV format is essential for this system to function properly.
    • CUSTOMERS - Go to (web) System Management | System | Imports | Import Customers or (Enterprise for Windows) Utilities | Import | Accounts Receivable | Customers
    • ITEMS - This allows you to bring new items into or maintain existing ones in the BirdDog database: go to (web) System Management | System | Imports | Import Items or (Enterprise for Windows) Utilities | Import | Inventory | Items -
    • ITEM LOCATIONS - This allows you to maintain prices according to location, so if you have five warehouses that stock the same item but have slightly different costs and prices, this is where you'd adjust that. Go to (web) System Management | System | Imports | Import Item / Locations or (Enterprise for Windows) Utilities | Import | Inventory | Item Locations
    • SHIP TOs - This allows you to add multiple shipping addresses to a customer. Go to (web) System Management | System | Imports | Import Ship Tos or (Enterprise for Windows) Utilities | Import | Accounts Receivable | Ship Tos

Create Order Import to ERP

  • Similarly to the above, we need to create an import to push our data into BirdDog. To access the proper format of these files, navigate to the following in Enterprise for Windows and download a sample. Note that the CSV format is essential for this system to function properly.
    • ORDERS - Integrate | Sales Order(CSV)

Order Export Info from BirdDog

In order to export order information from BirdDog, we will need to set up a query that will run later.

  • To generate this query, go to (web) System Management | System | Queries or (Enterprise for Windows) Processes | Query | Maintain Queries, then add. Make sure to name this query something that is easily remembered for this. In our example, we simply named it Export Orders.
  • Below is our example query. It can be easily modified according to your business needs.
    • select
      --top 10
      h.ord_type Ord_Type,dbo.removeleadingzeros(h.ord_no) Ord_No
      , dbo.removeleadingzeros(h.cus_no) Cus_No, c.cus_name Name
      ,h.Bill_To_Addr_1,h.Bill_To_Addr_2,h.Bill_To_Addr_3,h.Bill_To_Country
      ,h.cus_alt_adr_cd Ship_To_Code
      ,h.Ship_To_Addr_1,h.Ship_To_Addr_2,h.Ship_To_Addr_3,h.Ship_To_Country
      ,h.inv_no,CONVERT(VARCHAR(8),dbo.isodatetodate(ord_dt), 1) Ord_Date,datepart(year,dbo.isodatetodate(ord_dt)) Year,datepart(quarter,dbo.isodatetodate(ord_dt)) Quarter
      ,l.Item_No,l.Loc,l.Item_Desc_1, l.qty_ordered, l.unit_price, l.discount_pct, l.qty_ordered * l.unit_price * (100 - l.discount_pct)/100 Amount
      --,H.*
      ,OS.*
      --, L.*
      --, OLS.*
      ,h.Ord_No + '-' + CONVERT(VARCHAR(255),l.qty_ordered,121) + '-' + CONVERT(VARCHAR(255),ols.LastUpdate,121) ExportKey
      from OEORDHDR_SQL H (nolock)
      left join BDOrderSupport OS (nolock) on os.Ord_Type=h.ord_type and os.Ord_No=h.ord_no
      join OEORDLIN_SQL L (nolock) on h.ord_type=l.ord_type and h.ord_no=l.ord_no
      left join BDOrderLineSupport OLS (nolock) on OLS.Ord_Type=l.ord_type and OLS.Ord_No=l.ord_no and OLS.Line_No =l.line_no
      join ARCusFil_Sql C (nolock) on h.cus_no=c.cus_no
      left join BDCustomerSupport CS on cs.Cus_No=c.cus_no
      left join IMITMIDX_SQL IM (nolock) on l.item_no=im.item_no
      left join BDItems25 BDI (nolock) on bdi.Item_No =im.item_no
      left join IMINVLOC_SQL IL (nolock) on l.item_no=il.item_no and l.loc=il.loc
      left join BDItemLocationSupport ILS (nolock) on ILS.item_no=il.item_no and ILS.loc=il.loc
      left join IMLOCFIL_SQL Loc (nolock) on loc.loc=il.loc
      left join BDLocationSupport LS (nolock) on ls.loc=loc.loc
      --left join imlstrx_sql LST (nolock) on lst.
      where os.ServiceProOrderStatus='Complete'
          
  • Set Unique Column on the query to ExportKey
  • The typical implementation is for a work order from Service Pro to be exported one time after it is marked complete in Service Pro. This is enabled by the where clause on this query looking for the Service Pro order status being set to complete.

Setup Jobs

  • Automated jobs will need to be set up to handle this. To set them up, navigate to (web) System Management | Automation | Jobs or (Enterprise for Windows) Maintain | Jobs. Make sure to read the instructions:
    • Import Customers from Directory
    • Import Items from Directory
    • Import Item Locations from Directory
    • Import Ship Tos from Directory
    • Sales Orders Import from Directory
    • Export Query Results(CSV)
      • The File Name field is where you want your export folder to be written and should be annotated using this format: \\MYSERVER\Imports\Orders Out\Order Export
      • The Query ID referenced in the Service Data is more easily obtained when using the web query functionality (System Management | System | Queries) - when you Preview, copy the GUID from the URL.)
    • For troubleshooting purposes, it is essential that you populate the Email to Notify section of Service Data for each in order to resolve bad imports or general errors that occur.
    • While you're here, you'll also want to set up the Run Connectors job. This will communicate with Service Pro and push new data up from our database to it.

Test the System

At this point, your software is set up, and you should be ready to begin testing to confirm that all of the moving parts are functioning correctly. The steps to test are relatively obvious:

  • Generate a CSV Export for each option (New Customer, New Inventory, New Item Location, New Orders); drop those CSVs into the appropriate folder.
    • If you want to test the erroring out functionality, you can intentionally screw up a CSV by plugging in invalid data like a Warehouse Code that doesn't exist on an inventory import.
  • Generate a new order in BirdDog.
  • Monitor the folders for activity. When the jobs run, you should see the following behavior:
    • Imports that have valid data will be moved to the appropriate Successful Imports folder
    • Imports with invalid data will be moved to the appropriate Errors folder
  • When the Run Connectors job completes, two things will happen:
    • Data that you have imported (new customer, new item, etc.) will be pushed to Service Pro.
    • The new order you created in Service Pro will come down to the BirdDog database, and when that Export Query Results(CSV) job runs, a new CSV with your order data will be generated in the appropriate Orders Out folder.

Receive your free product tour!

Need Help? Have Questions?