Stonefield Query: A Complete Guide for Sage 300 ERP Users
Overview
Stonefield Query is a report-writing and query tool designed to work with Sage 300 ERP (formerly Accpac). It lets non‑technical users build, run, and export custom reports and queries directly against Sage 300 data without writing SQL. This guide explains core features, installation basics, creating queries and reports, common use cases, performance tips, and troubleshooting.
Key features
- Drag‑and‑drop query builder: visually select tables, fields, joins, and filters.
- Report designer: create printable layouts with grouping, subtotals, headers/footers, and formatting.
- Export options: export results to Excel, CSV, PDF, HTML, and XML.
- Security integration: respects Sage 300 user permissions and data security.
- Calculated fields & expressions: create formulas, date math, and conditional fields without SQL.
- Scheduled reports: automate report generation and delivery (where enabled).
- Prebuilt templates: common Sage 300 reports and sample queries to accelerate development.
Installation & setup
- System requirements: verify Stonefield Query version compatibility with your Sage 300 ERP release and Windows/SQL setup.
- Install Stonefield Query: run the installer on a machine with Sage 300 libraries or a report server; follow prompts to register with Sage 300.
- Database connection: configure connection to your Sage 300 company database (support for Pervasive, SQL Server, or other supported engines depending on Sage 300 setup).
- Security: ensure users have appropriate Sage 300 permissions; enable Stonefield Query access in Sage 300’s Administrative Services if required.
- Client deployment: install client components for report designers and end users who will run queries.
Creating your first query
- Launch Query Builder: open Stonefield Query and select your Sage 300 company.
- Select tables: add the primary module table (e.g., AR Invoice, AP Voucher, IC Items). The builder typically suggests joins to related tables.
- Choose fields: drag fields you need (dates, amounts, item codes, customer names).
- Add filters: restrict data with criteria (e.g., Invoice Date between X and Y, Balance > 0). Use parameter prompts to allow user input at runtime.
- Define sorts & groups: order results and set grouping levels for subtotals.
- Run & preview: execute the query to validate results. Tweak fields, joins, and filters as needed.
Designing reports
- Layout: switch to the report designer to place fields, labels, and images (logo).
- Grouping & summaries: add group headers/footers and insert sum, count, average, min/max functions.
- Conditional formatting: highlight rows or cells when conditions are met (e.g., overdue invoices).
- Calculated fields: create expressions for derived values (e.g., extended price = QuantityUnit Price).
- Headers/footers & pagination: include page numbers, report date, and user/company info.
- Preview & export: test layout, then export to preferred format.
Common use cases
- Accounts receivable aged trial balance and aging detail.
- Inventory valuation, slow‑moving SKU reports, and sales by item.
- AP payment runs and vendor analysis.
- GL account detail, trial balance, and period comparisons.
- Sales order backlog and sales performance by salesperson or territory.
Performance best practices
- Limit returned rows: use filters and parameters to avoid pulling entire tables.
- Select only needed fields: fewer columns improve query speed.
- Use database indexes: ensure join/filter fields are indexed in the Sage 300 database.
- Preaggregate when possible: perform summary calculations in SQL or at database level for large datasets.
- Run heavy reports on a report server: schedule during off‑peak hours.
- Avoid complex calculated fields where DB can compute: delegate heavy calculations to the database engine.
Exporting & scheduling
- Export to Excel for further analysis, PDF for distribution, or CSV for integrations.
- Use built‑in scheduling (if licensed) or Windows Task Scheduler with command‑line tools to automate report runs and email delivery.
Troubleshooting common issues
- Missing or incorrect data: check joins, filters, and company database selection.
- Permission errors: verify Sage 300 user permissions and Stonefield Query integration settings.
- Slow queries: add filters, check indexes, or run on the database server.
- Version incompatibility: confirm Stonefield Query release supports your Sage 300 version.
- Export formatting problems: verify report layout settings and export options; test different formats.
Security & compliance notes
Stonefield Query respects Sage 300 data security, but ensure only authorized users have access to sensitive reports. For compliance, control distribution, use secured file shares or encrypted email for sensitive exports, and routinely review report access.
Maintenance & best practices
- Keep Stonefield Query and Sage 300 patched and on supported releases.
- Maintain a library of tested report templates and document their intended use and data sources.
- Archive older reports and schedules to reduce maintenance overhead.
- Train business users on parameterized queries to reduce ad‑hoc load on production systems.
Resources
- Stonefield Query documentation and knowledgebase (vendor site).
- Sage 300 support and community forums for integration specifics.
- Internal data dictionary for your Sage 300 implementation (table and field definitions).
If you’d like, I can create a sample query for a specific report (e.g., AR aging, inventory valuation) and provide step‑by‑step instructions and field mappings.