Sometimes one doesn't have to exit an Excel spreadsheet to get the necessary data. All Sage 300 (formerly Accpac) tables can be accessed and analysed directly from within Excel. Yes, yes - Excel! I'm talking here about MS Query that can be called upon from the Data menu of Excel. Provided you know the ODBC connections to your company's data and credentials to access it you're good to go and query the data you need, all fields of any table, with the availability of selections and filters. Of course knowing SQL language can be handy and can save hours of work, as in the below example.
So, let's say you need to import certain A/R Invoices from one company to another, and you want to limit it to a specific date range and only for some customers. Doing export/import batch by batch can consume significant amounts of time when it comes to more than 10 batches, not to mention the manual ‘cherry picking' your customers' invoices - that can really turn out as a nightmare.
This complexity is caused by the fact that multiple sheets are interrelated by common sets of references together in one import Excel file. So, for example, one import file of A/R Invoices consists of at least three following spreadsheets (optional fields will require additional sheet): Invoice Header; Invoice Details; Payment Schedule. These sheets correspond to each other by Batch number, Entry number and other references, therefore excluding certain transactions from the workbook can be a tedious task, resulting in a failure of import in the case that some records were deleted incorrectly.
What can be helpful here is to run a query on the Sage 300 tables (such as ARIBH) directly from Excel: Data/Get External Data/from other sources/MS Query, or using shortcut Alt+DDN and specifying data source name and database from within the login screen. When this done, you'll be prompted by the next screen - choose the Table or set of fields from various tables to build the query. Then select the data by certain criteria, such as batch range or ‘Date Posted' range and sorting it in necessary order.
The last step of the Wizard is deciding whether you want to dump the data on a spreadsheet or keep editing it in MS Query. By selecting Query you have obviously more things to do with data, such as further slicing and dicing it before final cast on the sheet, almost like baking a cake.
After all is done and you're in the query window, two sections show up - tables section and records section. Now, remember I've mentioned the SQL language - here's the example of how you can really save time: instead of picking fields and specifying criteria in the query window, go right to SQL scripting by pressing the SQL button and script a query. It's easy - try this;
SELECT ARIBD.CNTBTCH, ARIBD.CNTITEM, ARIBD.CNTLINE, ARIBD.AUDTORG, ARIBD.IDINVC, ARIBD.IDITEM, ARIBD.TEXTDESC, ARIBD.UNITMEAS, ARIBD.QTYINVC, ARIBD.AMTEXTN, ARIBD.TOTTAX, ARIBD.BASETAX1, ARIBD.BASETAX2, ARIBD.TAXSTTS1, ARIBD.TAXSTTS2, ARIBD.AMTTAX1, ARIBD.AMTTAX2, ARIBD.IDACCTREV, ARIBH.FISCPER
FROM SAMLTD.dbo.ARIBD ARIBD, SAMLTD.dbo.ARIBH ARIBH
WHERE ARIBD.CNTBTCH = ARIBH.CNTBTCH AND ARIBD.CNTITEM = ARIBH.CNTITEM AND ((ARIBH.IDCUST='CUS01') AND (ARIBH.CNTBTCH>$145) OR (ARIBH.IDCUST='SHAC01') AND (ARIBH.CNTBTCH>$145))
ORDER BY ARIBD.CNTBTCH, ARIBD.CNTITEM, ARIBD.CNTLINE
What you see in red font is the database name, so you can just change it to your company database ID, change the criteria to appropriate for your needs, such as batch number and customer name and you're good to go ahead and execute the query. If all steps are properly done, what you will then have at hand is an import spreadsheet of Invoice detail lines for the specified criteria.
Note: The above mentioned example works for MS SQL Database engine, the script will slightly change when querying Pervasive data.
If you have any questions or concerns, or would like further assistance with this TIP please contact us.
