Work Instruction: Using OData Feed in Excel from Epicor BAQ

Purpose: To provide a step-by-step guide on how to retrieve data from an Epicor Business Activity Query (BAQ) and display it in an Excel spreadsheet using OData feed.

Scope: This work instruction applies to users who need to access Epicor BAQ data in Excel.

Pre-requisites:

  • Epicor system access
  • Excel software installed on the user's computer
  • Knowledge of basic Excel functions

Step-by-Step Instructions:

  1. Open Epicor BAQ:
    • Log into the Epicor system with valid credentials.
    • Navigate to the Business Activity Query (BAQ) that contains the desired data.
  2. Copy OData URL:
    • In the Epicor BAQ, note down the actual BAQ name without any additional characters or formatting.
  3. Open Excel and Create a New Workbook:
    • Open Microsoft Excel and create a new workbook.
    • Ensure that the workbook is set to the correct data type (e.g., CSV or XLSX).
  4. Go to Data > From Other Sources > Connect:
    • In Excel, go to the "Data" tab in the top navigation menu.
    • Click on "From Other Sources" and select "Connect".
  5. Select OData Feed:
    • In the "Connect to a data source" dialog box, select "OData Feed" as the data source type.
    • Enter the copied URL from step 2 in the "URL" field. Substitute the actual BAQ name for "BAQ_Name".

Example:

https://erp.versalift.com/E10Prod/api/v1/BaqSvc/your_actual_BAQ_name/
  1. Authenticate with Epicor System:
    • Select the Basic Authentication method and enter your Epicor username and password in the corresponding fields.
  2. Map Fields (Optional):
    • If the desired fields are not automatically mapped, use the "Map Data" feature to manually map the fields from the OData feed to Excel columns.

Example:

Field NameColumn Header
OrderNumOrder ID
NameCustomer Name
OrderDateOrder Date
  1. Load Data:
    • Click "OK" to load the data from the OData feed into Excel.
  2. Review and Edit Data (Optional):
    • Review the loaded data for accuracy and completeness.
    • Make any necessary edits or updates.

Troubleshooting:

  • If you encounter issues with authentication, ensure that your Epicor system credentials are correct.
  • Verify that the OData URL is correctly formatted and points to the desired BAQ.
  • Check for any network connectivity issues or firewall restrictions.

Best Practices:

  • Use the "Refresh" button in Excel to periodically update data from the OData feed.
  • Regularly review and edit data for accuracy and completeness.
  • Consider using macros or VBA programming to automate tasks or improve data processing efficiency.