There are few situations that almost every Accpac Developers face while working with Accpac Views, like…
PERFORMANCE, when you have to browse/read a filtered piece of information from an Accpac table/view that has a huge size of Data, yes! Accpac certainly allows us to filter the Data on the fields that are exposed by the view, but sometimes it is very slow specifically if the field that you are using in your filter is not indexed and the data is huge.
SQL QUERIES, often while writing business logic we wish if we could use a SQL Query and it would have been so simple and faster.
QUERYING MULTIPLE TABLES, with views if you have to deal with multiple tables (like if one has to list Orders of Customers belonging to specific Customer Type) we need to do a nested loop and all.
Considering these concerns of Developer community Sage has introduced a View “CS0120” CSQRY since v5.5 onwards, which we can use to pass our SQL queries.
Using this view you get results much faster, like we had a speed issue reported by a client where we were trying to list them all open orders of a customer in a List Control with checkbox, we had opened the view in reading mode and were fetching each row to add to this control it took approx 35 sec… for 6000 rows (why we did not use bound control and all is a different story), we changed the logic and pulled the result in “CS0120” view by passing appropriate query and it just took a couple of seconds for the same set of data to be populated in the List Control…
Here is the sample code of how to use CSQRY view, just in case you are not aware of how to use it…
Dim CSQry As AccpacCOMAPI.AccpacView
Dim sSql As String
‘Open view
mDBLinkCmpRW.OpenView “CS0120”, CSQry
Below is the query that can be passed to the CSQRY
sSql = “”
sSql = “select ICITEM.ItemNo from ICITEM where ICITEM. ItemNo in (‘A0001’, ‘A0002’, ‘A0003’)”
CSQry.Cancel
CSQry.Browse sSql, True
CSQry.InternalSet (256)
Do While CSQry.Fetch
‘< << Add your business logic here >>>
Loop
CSQry.Close
About Us
Greytrix is a one-stop solution provider for Sage ERP and Sage CRM needs. We provide complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development and implementation expertise.
Greytrix has some unique solutions of Sage 300 integration with Sage CRM, Salesforce.com and Magento eCommerce along with Sage 300 Migration from Sage 50 US, Sage 50 CA, Sage PRO, QuickBooks, Sage Business Vision and Sage Business Works. We also offer best-in-class Sage 300 customization and development services to Sage business partners, end users, and Sage PSG worldwide.
For more details on Sage 300 Services, please contact us at accpac@greytrix.com. We will be glad to assist you.