I am using Sage 300 CRE v18.1. Attempting to create a Custom Report Design that can print invoices by job, by vendor and will show the amounts paid and amounts remaining (especially retainage amounts) as of a cut off date for use in a Cash Flow Report (glorified Work-In-Process ["WIP"] statement that has columns to compare job cost amounts to AR amounts received) where the A/P report will match the General Ledger financial statements for a prior period for which financial statements are prepared. The standard A/P Invoices by job report does not cut off for a prior period and only shows current up-to-date balances. Had attempted to use the ASUM formula to obtain amount and retainage held and received from the A/P invoice distribution and A/P payment distribution records but the ASUM command causes an error message saying all fields must be from the same record (so the IF and Lookup commands cannot be added to the formula where the fields needed are from the Invoice Distribution or Invoice records) and where different job numbers appear in a invoice distribution the amounts are added each time. Need to be able to limit them to just the job amounts being sought so the job number and date paid needs to be added to the AP payment distribution record in order to make this work. I have been using a export of data and import to excel and then massaging the data to get the report I need and it is a time consuming process each month.

by: Edward A. | over a year ago | 8 Reports and Inquiries