The report you mentioned appears to use AP_CheckHistoryHeader and AP_CheckHistoryDetail as the source tables so you should check those.

For future info - the AP_PaymentTransactionHistory file is based on how many months/days (depends on version and license) of history you choose to keep in your AP Setup Menu/AP Options.  999 days was the previous limit, before that changed in the most recent versions.  The AP_CheckHistory files are FOREVER unless you manually purge them.

Thank you that is a good idea.

Yes in one location in AP Options it lists to keep invoices for 999 days and then in the history tab it says 20 years. Rather confusing.

The 999 days is for the tables (OpenInvoice + TransactionPaymentHistory) used to track balance owed (which doesn't really matter after balance = 0, other than running a back-dated Aging report), and is not intended to be used as transaction history.

Thanks. We are trying to export data and although this does not appear to be the best practice this is how they are handling the older data. Its too bad there was not an easy way to extract all data between two dates for all modules.

Does that mean I would have to use a file for check, ACH and so on or does all the data live in check history?

Thank you! The data file is so huge I am constantly locking up just trying to place constraints on the data.

How are you trying to export the data?

Crystal Reports?

Excel?

VI?

If Crystal Reports, instead of creating an external report with multiple tables joined, try to leverage the report on the menu and just create a new report setting and modify the new report setting to export cleanly to excel in column and row format.

If Excel, query one table at a time to a table in Excel and then join the tables in Excel.  If on a recent version of Excel with PowerQuery, this is very easily done.

The goal here is to avoid querying multiple tables joined together in a single query where one or more tables in the query contain a significant number of records.  Using the existing report on the menu joins the data into the Wrk table so Crystal Reports only sees one table whereas querying one table at a time in Excel eliminates the join occurring in the ODBC driver and instead lets Excel handle the join.

You could also use Access with passthrough queries but don't do joins in the passthrough queries.  Create one passthrough query for each table and with any filter criteria for each table in the query.  Then use another query to join the data.