Typical questions cross table and module boundaries
Historical requests rarely arrive as table names. They sound like:
- Show every invoice and payment for this customer in fiscal 2018.
- Which serial number did we ship under this order?
- Give the auditor all vendor payments above a threshold with supporting documents.
- Find the note attached to a disputed order.
- Explain the GL entries behind a transaction.
A useful history solution translates those questions into records without requiring the user to remember GP internals.
Option 1: keep using GP
GP windows and SmartList are familiar and retain the original application behavior. This works during a temporary parallel period. It keeps the full server, SQL, security, licensing and specialist-support footprint alive.
Option 2: restore SQL and write queries
Direct SQL is flexible for specialists. It requires a restored database and accurate handling of GP's work/open/history split, joins, date conventions, padded strings, document codes and multicurrency values. It does not give ordinary users a document-oriented interface by itself.
Option 3: use exported spreadsheets
Spreadsheets are easy to distribute for a known question. They become difficult when history is large, related records live in separate files or a new filter requires repeating the export. They also need a manifest if the recipient must understand completeness and provenance.
Option 4: query a historical archive
A purpose-built archive can support several access levels from the same reconciled data:
- ✓Global search across names, document numbers, descriptions and record notes
- ✓Business views for customers, vendors, transactions, items and accounts
- ✓Drill-down from a document to lines, applications, distributions and notes
- ✓Visual query builder for filters, fields, grouping and totals
- ✓Read-only SQL for analysts and technical partners
- ✓Direct access from Excel, Power BI, DBeaver and Python
What makes a query trustworthy
Show the user which date, company, status and document types are being filtered. Preserve the original GP field names as provenance. Save the query definition separately from its results. Exports should carry row counts, totals and the source archive hash.
For SQL access, open the archive read-only and enforce query-only mode. Add timeouts and result limits so an accidental Cartesian join does not exhaust the workstation.
Design saved questions around real work
Before decommissioning, collect recurring requests from each team and save them:
- Controller: trial balance and transaction detail by fiscal period
- Accounts receivable: customer invoices, applications and write-offs
- Accounts payable: vendor invoices, checks and payment applications
- Operations: sales orders, purchase orders, items, lots and serials
- Auditor: populations with supporting documents and control totals
Ask the person who will inherit the process to run each query while GP is still available. That is a practical usability test and a coverage test.