3rd Party Ecommerce Connector

×

This integration system allows you to quickly import customers, items, item locations, sales orders and ship tos into a BirdDog database that can then be easily sent to Service Pro; orders coming down from Service Pro will then be exported to a comma-separated value file that can be used to easily import those orders into your 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
  • Create File Sync directory
  • Create master file export from ERP in appropriate format
  • Create order import to ERP in appropriate format
  • Setup Jobs
  • Test the system

Create 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.

  • On your server, create a new folder 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 your system. 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

In order to export order information, 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 remember for this. In our example, we simply named it Export Orders.
    • NOTE: This is much easier to do using the web software as the job we will be referencing later will ask for the GUID id of this query. So do it there. It'll save you a headache later.
  • 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.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.Ord_No + '-' + CONVERT(VARCHAR(255),ols.LastUpdate,121) ExportKey
      --,H.*
      --, l.*
      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 h.ord_type in ('O','I')
      --L means closed
      and h.status not in ('L')
      and h.ord_dt>=20190701

Setup Jobs

  • Automated jobs will need to be setup 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 to 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.)
    • 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 setup 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.
  • 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 apprpriate 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 imported.
    • The new order you created will come down to the BirdDog database and, when that Export Query Results(CSV) job runs, a new CSV with your order data will generate in the appropriate Orders Out folder.