Prepare the Query Requirements
Before opening the designer, decide exactly what the query should answer and who will use the results. For example, a purchasing manager may need open purchase orders by supplier, while a controller may need posted transactions for a selected accounting period. These goals require different data sources and filters.
Write down the expected output, the relevant date range, required fields, and any conditions that determine which records belong in the result. Confirm that your Epicor account has access to the relevant tables and that the query complies with your organization's data-access rules.
When working with Epicor ERP, also identify the relevant functional area before selecting tables. Understanding the purpose of individual Epicor ERP Modules can help you locate the records associated with purchasing, accounts payable, inventory, sales, or the general ledger.
Step-by-Step: Create a BAQ in Epicor
The exact labels and available options vary by Epicor version, but the typical process follows these steps in the BAQ Designer or equivalent query tool.
- Open the designer: Navigate to Business Activity Query Designer using the menu available in your environment.
- Create a new query: Assign a unique query ID and a clear description, following your organization's naming conventions.
- Select the primary table: Choose the table that contains the main records required by the business question, such as purchase-order headers or invoice details.
- Add related tables: Include additional tables when fields from suppliers, order lines, customers, or transactions are needed. Define the appropriate relationships between them.
- Select output columns: Add the fields users need to see, such as supplier ID, order number, transaction date, amount, or status.
- Configure criteria: Apply filters for dates, statuses, company, supplier, or other relevant fields. Use the correct comparison operators and data types.
- Test and save: Run the query, inspect the output, correct any relationship or filter issues, and save it for authorized use.
For instance, an open-purchase-order BAQ might start with purchase-order header and detail tables, connect them using the appropriate keys, and filter for the relevant company and open status. The resulting columns could show order number, supplier, order date, line description, and remaining quantity or value where those fields are available.
Join Tables, Apply Filters, and Validate Results
Table relationships determine how records appear together. A header table usually stores information about an overall transaction, while a detail table stores its individual lines. Joining them through the correct keys helps associate each line with its parent transaction. If a query joins several one-to-many tables, the output may contain repeated header values; users should account for this when interpreting totals.
Filters define which records qualify. A date condition might limit results to transactions within a reporting period, while a status condition could isolate open orders. Check whether dates refer to creation, posting, due date, or another event. In finance queries, that distinction can affect period-end analysis and reconciliation.
Validation should include both record-level and total-level checks. Compare selected rows with the corresponding Epicor screens, verify that expected records appear, and reconcile financial amounts to an established report or ledger balance where appropriate. If a total differs, review join relationships, company filters, transaction statuses, and date criteria before using the result for decisions.
Use BAQs for Finance, Purchasing, and ERP Integrations
BAQs can support practical tasks such as identifying overdue supplier invoices, listing purchase orders awaiting receipt, reviewing customer transactions, and preparing operational extracts. Procurement teams can use query results to improve spend visibility and approval follow-up. Resources such as How to Create a Purchase Order System and PO Document Guide: What's Included & How to Create One provide additional context on purchase-order controls and the information used throughout procure-to-pay workflows.
When the query supports tax reporting, verify that the underlying transactions contain the required tax codes, jurisdictions, exemptions, and account mappings. Clear chart-of-accounts design, as explained in How to Structure Tax Accounts in Your COA, can help teams organize tax balances for review and audit preparation.
Organizations that extend epicor through integrations should also establish which system owns each field and how often the data is refreshed. Epicor Integration Finance covers the relationship between Epicor and connected finance workflows. For supported integrations, the Hyperbots Platform can be configured around company-specific ERP connections, workflows, roles, and general ledger structures.
Maintain and Extend BAQs Responsibly
Give each BAQ a descriptive name, document its purpose, and identify an owner who can review it when business requirements change. Test modifications before using the query for recurring management reporting, and confirm that access permissions remain appropriate. If a BAQ feeds a dashboard or downstream process, validate that dependent outputs still behave as expected after changes.
For finance workflows that use AI, Process Specific Capabilities support task-focused automation, while Ready to Deploy Capabilities include pre-trained agents and ERP connectors. Self Learning Capabilities can use human feedback to refine supported workflows, and Human in the Loop approaches provide review and approval for exceptions. These capabilities complement reporting and query design rather than replacing the need for correct source data.
As organizations consider broader ERP integration and finance transformation, Keep Your GL Codes Aligned in Any ERP System offers context on maintaining consistent account mappings. For an overview of how finance AI agents can extend Epicor processes, How Hyperbots AI Agents 10x Epicor Finance Operations discusses automation across finance workflows.
Summary
Creating a BAQ in Epicor involves defining a business question, selecting the correct tables, connecting related records, choosing output fields, applying filters, and validating the results. Clear requirements, accurate joins, appropriate permissions, and reconciliation checks help ensure that the query supports dependable operational and financial decisions. Once tested and documented, a BAQ can provide a reusable view of ERP data for day-to-day analysis and reporting.