Recording a macro didn’t really help as the mechanism the UI uses to identify each row is the PARMSEQ field, which is an incrementing value representing the row number of the table.
For an automated import this is quite brittle as an additional entry could potentially reorder the rows causing the incorrect row to be updated.
When viewing the table we found an integer value (PARMNBR) was being stored, which we could only assume was some sort of reference to the record.
The PARMNMBR would be fine to use as the key to update, but you would need to know it’s value and this is a little concealed, ideally we’re looking to use the description as the key to update these values.
Investigation 1 (Fail)
Hunting around the tables….no tables would be found with this info.
Investigation 2 (RVPSPY)
We opened our friend, RVSPY with nesting turned on and found the screen was calling CT0002.
We then tried to querying the table, per the Object Model, but the table doesn’t exist.
So we tried querying the views. Success, it returned the values, but no unfortunately no description field.
This makes sense since all descriptions are stored in resource files, so the PARMNBR was actually the Resource ID.
To get the description we needed to using the RscGetString method to retrieve them (note that we’re querying the CT module and not the CP application.
|
1 |
mDBLinkCmpRW.Session.RscGetString("CT", CT.Fields("PARMNBR").Value) |
In the last couple of weeks we’ve been doing quite a bit of work to our Sage300 connector to add update functionality to existing transactions.
When working in a non-UI, automated process, one of the key issues when updating a transaction is the ability to locate a particular line (or child record) within a transaction; so that it can be updated, deleted or a new one inserted should one not exist.
We’ve had this sort of functionality within our software for quite some time (to locate particular lines when working with O/E Shipments, Invoices, P/O Receipts, etc.), but we needed to revisit our line search method to implement the new update functionality. Given the different methods to search for transactions and records in Sage300, we did more than think about whether we were using the best line search strategy.
This blog attempts to describe our investigations and those various search methods available within Sage300 to search for transactions, and to explore what each of these actually do under the hood.
Order Property & Indexes
One of the dysfunctions of Sage300 is that index use is explicit. Whenever Sage300 executes a query, rather than letting the database devise which index to use, the query generated by Sage300 expressly states which index the database must use. I can only blame Sage300’s early days of running on Btrieve for this, but the result is that you must know how the index will affect the underlying query.
To alter the index is simply a matter of altering the Order property of the view.
|
1 2 |
'Change the index to use fields: ITEM, DDTLTYPE, EXPDATE, LOCATION, LINETYPE, COMPLETE OeOrderDetailView.Order = 5 |
One of the key discoveries of our research is that the Order property of a view can drastically affect the functionality of the search operation.
Cursor Accessed Indexes
It is worthy to note that Sage300 employs two strategies for retrieving records: simple and cursor accessor.
Simple indexes simply specify a where clause with an SQL statement.
Cursor accessed indexes use a stored procedure (with a server side cursor for positioning) to setup the query and subsequent calls to the stored procedure to move the cursor and return the next record. The code below illustrates the initial setup of a cursor based index.
Please note the explicit field list has been removed and replaced with * for brevity.|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
declare @p1 int set @p1=180150055 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=-1 exec sp_cursoropen @p1 output,N' select * from "ARCUS" with (index(KEY_2) /*,updlock*/) where (("IDGRP">=''RTL '')) order by "IDGRP","IDCUST"',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 |
Sage300 then calls the stored procedure to return the next record from the query:
|
1 |
exec sp_cursorfetch 180150055,2,1,1 |
Whilst our testing indicates the index type is transparent to calling operation, the speed of cursor accessed indexes is significantly slower than simple indexes because of the SQL call to retrieve the next record.
Our investigation was a little inconclusive, and we’d be happy to hear from others on this point; but the use of cursor accessed indexes appears in Sage300 to be dynamic:
- A stored procedure/cursor would be used when an index was specified but the search did not include any index fields. This would only occur on non-composed views or the parent view of a composed view.
- Certain indexes always use cursors composed or otherwise. The API doesn’t provide any means to query if an index is cursor accessed or simple.
Read
The first access method is the most simple: the Read function is designed to return the single record pointed to by the values of the fields of the corresponding Order or index. The following code returns a single O/E Order, using the Order Number:
|
1 2 3 4 5 6 |
'Set the Index to use ORDNUMBER i.e. the 2nd index. OeOrderHeaderView.Order = 1 'Set the value of the ORDNUMBER field. OeOrderHeaderViewFields("ORDNUMBER").Value = "ORD000000002039" 'Retrieve it OeOrderHeaderView.Read |
SQL Trace
If we look at the SQL trace for the Read method, not only does it select just a single record with the top 1 directive; it also provides a hint to use the index corresponding to the Order selected.
|
1 2 3 |
select top 1 * from "OEORDH" with (index(KEY_1) /*,updlock*/) where "ORDNUMBER"='ORD000000002039 ' option(keep plan) |
DBSpy
DBSpy issues a similarly short statement involving a single Get operation.
|
1 2 |
LINK-100: 13:54:53.27 GET ( handle=OEORDH-0, keyno=1 [ $ F6 = 'ORD000000002039' ] ) LINK-100: 13:54:53.27 [ $ F6 = 'ORD000000002039' ] |
In terms of database access and speed, Read is certainly the most efficient since it only ever retrieves a single record based on an index.
Using Read to iterate multiple records
Read will only ever return the first matching record based off the values set to the fields used in the index. To iterate multiple records therefore it is not really a viable solution as it would be necessary to first set the key field values and then perform a read, and repeat.
Browse & Fetch
The Browse and Fetch operations combine to allow multiple records to be retrieved and iterated, and in contrast to the Read operation they allow you to search for records using non-indexed fields.
Specifying the Index of a Browse Operation
Like the Read function, the index used changes the way Browse and Fetch retrieve records. In the example below we’re retrieving the Customer record using the Customer’s Name, changing the index to 2 and specifying the single field in the ‘Put’ method used of that index.
|
1 2 3 4 5 6 |
ArCustomerView.Order = 2 ArCustomerView.Fields("IDGRP").PutWithoutVerification "RTL" ArCustomerView.Browse "NAMECUST = ""Ronald Black""", True While ArCustomerView.Fetch Debug.Print ArCustomerView.Fields("IDCUST").Value Wend |
SQL Trace
One would expect from the above the Browse operation would override any field set previously, however it does not. Instead, because the field is a part (or all) of the index the value of the field is incorporated into the query.
|
1 2 3 |
select * from "ARCUS" with (index(KEY_2) /*,updlock*/) where (("IDGRP">=''RTL '')) order by "IDGRP","IDCUST" |
Browse & Non Indexed Fields
Using Browse on non-indexed fields will result in Sage300 omitting them from the resulting query and instead using its own filtering mechanism executed client side in the Sage300 view code.
For example, a search of A/R Customer records using only the customer name will result in returning every single customer record back to Sage300 where each record is evaluated according to the Browse before the resultant dataset is return to the calling client.
TIP: We recommend to search using indexes where ever possible, or use the CS0120 view to execute a query directly on the database (where the database can perform more efficient filtering).
Fetch is not GoTop
As mentioned previously there are several functions (GoTop, GoNext & GoPrevious) which work in conjunction with Browse. The scant inline help provided in VBA & VB6 seems to indicate the Fetch and GoTop functions act equivalently on the fetch of the first record after a Browse.
They do not!
Whereas Fetch always incorporates index field values, GoTop behaves erratically where it includes the values from the Key fields most of the time and then omits values from the key fields at other obscure times (see below). Using the same Customer query as previously the resulting query using GoTop instead of fetch results in the following SQL:
|
1 2 3 4 |
select * from "ARCUS" with (index(KEY_2) /*,updlock*/) where (("IDGRP">='' '')) order by "IDGRP","IDCUST" |
TIP: We don’t have one… why does this happen?
Composed views and searching Child Views
Thus far the focus has been on searching either views not composed or the parent view of a composed view. There are some gotchas when querying composed views!
A view composition is Sage300’s implementation of record relationship and association similar to an Object Relational system such as Hibernate. A view within a composition defines the relationship to one or more other views and in turn how that view is related to other views, etc.
Iterating through a Child View
Once a set of views are composed and a record is retrieved on the parent view, all child records associated to the parent record and only those child records will be available to iterate.
The following example retrieves an O/E Order and Fetch iterates through each of the records of that particular order.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
OeOrderHeaderView.Order = 1 'Set the value of the ORDNUMBER field. OeOrderHeaderViewFields("ORDNUMBER").Value = "ORD000000002039" 'Retrieve it OeOrderHeaderView.Read OeOrderDtlVw.Browse "", True 'Iterate through each of the records of the order. While OeOrderDtlVw.Fetch Debug.Print OeOrderDtlVwFlds("ORDUNIQ").Value & "-" & _ OeOrderDtlVwFlds("LINENUM").Value Wend |
SQL Trace
The SQL trace shows something equally elegant, where on the Fetch operation of the Order Detail View simply retrieve every record for the Order.
|
1 2 3 4 |
select * from "OEORDD" where "ORDUNIQ" = 33217 AND (("LINENUM" >= 0)) order by "ORDUNIQ","LINENUM" option(fast 10) |
It is worth noting that each subsequent call to Fetch does not result in a corresponding SQL query, so Sage300 must hold an in-memory cache of the resulting dataset in this instance.
Searching the Child View
To search for a record(s) using the Browse operation simply requires a simple query and because the records being searched are only those related to the parent record it is not necessary to specify order header fields such as ORDUNIQ. For example, to search for all items within an order containing the character ‘3’.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
OeOrderHeaderView.Order = 1 'Set the value of the ORDNUMBER field. OeOrderHeaderViewFields("ORDNUMBER").Value = "ORD000000002039" 'Retrieve it OeOrderHeaderView.Read OeOrderDtlVw.Browse "ITEM like ""%3%""", True 'Iterate through each of the records of the order. While OeOrderDtlVw.Fetch Debug.Print OeOrderDtlVwFlds("ORDUNIQ").Value & "-" & _ OeOrderDtlVwFlds("LINENUM").Value Wend |
Beware the Dangling Reference!
If we attempted to re-iterate the same set of detail records using the same Browse operation the result would iterate only the last record of the previous iteration and the SQL Trace would show something odd also:
|
1 2 3 4 |
select * from "OEORDD" where "ORDUNIQ" = 33217 AND (("LINENUM" = 256)) order by "ORDUNIQ","LINENUM" option(fast 10) |
The query includes a filter to restrict to a single record where the LINENUM field is 256!
This is because the LINENUM field at the time the Fetch is set to the last value of the iteration (in this example it was 256) and Browse incorporates any values set to the index fields.
To reset the iteration, either set any key fields back to their default values or use GoTop (which ignores key field values).
Killing the View Composition
Up to this point the examples provided have used the default (0) index on the Child View. It is possible to set the Order property to use a different index on a composed child view.
It is important to note the view composition will hold only when the index’ first field(s) is the primary key of the parent view’s primary key/default index. Using an index where the Parent View’s primary key is not the first field in the view being searched will result in the query returning records not associated with the parent record.
For example, searching using indexes 0, 1, & 6 of the O/E Order Detail view would return only records of the composed order, whereas indexes 2, 3, 4 & 5 will not.
The following example searches a composed O/E OrderDetail view using index 2.
|
1 2 3 4 |
'Change the index to use fields: ITEM, EXPDATE, ORDUNIQ, DETAILNUM OeOrderDetailView.Order = 2 OeOrderDetailView.Browse "", True OeOrderDetailView.GoTop |
Generates the following SQL:
|
1 2 3 4 5 6 7 |
select * from "OEORDD" where (("DETAILNUM" >= -32767 AND "ORDUNIQ" = 33217 AND "EXPDATE" = 1000101 AND "ITEM" = 'A1-103/0') OR ("ORDUNIQ" > 33217 AND "EXPDATE" = 1000101 AND "ITEM" = 'A1-103/0') OR ("EXPDATE" > 1000101 AND "ITEM" = 'A1-103/0') OR ("ITEM" > 'A1-103/0')) order by "ITEM","EXPDATE","ORDUNIQ","DETAILNUM" option(fast 10) |
Irrespective of whether Browse or GoTop are used the resulting SQL generated by Sage300 is based on the order in which the fields of the index are listed. The 1st and 2nd parts of the where clause include the ORDUNIQ field and since the ORDUNIQ field is the 3rd field within the index the 3rd & 4th sub-clauses do not include ORDUNIQ, thus returning non-associated records.
TIP: Note this!
GoTop Revisited
In some Child Views on certain indexes we’ve found GoTop acts like Fetch and includes values from key fields in the query.
If for example, we search the O/E Order Detail View using index 6, the GoTop operation on second iteration includes the ITEM field value in the query (if it’s not explicitly cleared prior to GoTop).
The following Trace shows the SQL generated on second iteration of the above scenario.
|
1 2 3 4 5 6 7 8 9 10 |
exec sp_cursoropen @p1 output,N' select * from "OEORDD" with (index(KEY_6) /*,updlock*/) where "ORDUNIQ"=33217 and (("ITEM"=''A1-460/0'' and "DDTLTYPE"=0 and "EXPDATE"=1000101 and "LOCATION"='''' and "LINETYPE"=1 and "COMPLETE">=0) or("ITEM"=''A1-460/0'' and "DDTLTYPE"=0 and "EXPDATE"=1000101 and "LOCATION"='''' and "LINETYPE">1) or("ITEM"=''A1-460/0'' and "DDTLTYPE"=0 and "EXPDATE"=1000101 and "LOCATION">'''') or("ITEM"=''A1-460/0'' and "DDTLTYPE"=0 and "EXPDATE">1000101) or("ITEM"=''A1-460/0'' and "DDTLTYPE">0)or("ITEM">''A1-460/0'')) order by "ORDUNIQ","ITEM","DDTLTYPE","EXPDATE","LOCATION","LINETYPE","COMPLETE","LINENUM"',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 |
FilterSelect & FilterFetch
From our tests FilterSelect is shorthand for setting the Order property and the Browse operation, and FilterFetch is simply the corresponding fetch operation.
Conclusion
The trigger for this post was to educate ourselves on the different search methods available in Sage300 and their underlying functionality.
Our investigation has certainly educated us on some of the gotchas and caveats, which I know for certain, have tripped me up at one point or another. Internally we’ve also found a number of places where we can make existing searches more efficient, particularly those where we search for details within an existing transaction.
For example, prior to this post we were using a FilterSelect & FilterBrowse to locate a detail record within a transaction.
|
1 2 3 |
POReceiptDetailView.FilterSelect "RCPHSEQ = " & lRcpUniq & " AND RCPLREV = " & lReceiptLineSeq, True, 0, FILTERSELECT_FROMSTART If POReceiptDetailView.FilterFetch(False) Then |
We could re-engineer this to use GoTop and a manual record evaluation thereby avoiding the re-querying of the database on each line.
As always, we welcome your comments.
Introduction
Last week there was a post on the Sage300/Accpac VBA forum asking how to automate the Reversals function in Bank Services. In short the responses were: macro recording didn’t work; the view was clumsy and difficult; and the best method to automate would be to mimic the entry via SendKeys.
Given that macro recording is not supported by the bank reversals function, it is necessary to use RVSpy to understand the view calls. This post will describe the various tips and tricks we use when we have to use RVSpy, using the reversals example as per the forum post.
A copy of the final source code and the RVSpy log can be found at the bottom of the post.
Filter the view calls
By default RVSPY captures all calls which leads to an extremely verbose and sometimes incomprehensible log file. Filtering the view calls to remove all the metadata and get requests will significantly trim the log file to the calls which are most important.
- Filter view calls: Fields, Field, Attribs, Type, Get, Name, Presents, FieldName & FieldExists
- Turn off ‘Show Nesting’: Typically you are only interested in calls the UI makes, and not calls views made to other views.
In our example the size of the generated log went from 1.8MB unfiltered to 427KB filtered.
Recording the entry
Finder Fields
Try to enter the values into each field. Using the finder will significantly add to the log and can make it difficult to understand (see below, for exceptions).
Use comments
Before you do something major such as posting, or adding an entry, insert a comment into the log. It will help you later on to identify the event in the log file and will also provide you with a cutoff where major processing is performed by the view.
The oogily-boogily nature of the Bank Reversals View
If you look at the Bank Reversals view, you can easily understand the trepidation as it appears to have several purposes:
- As a history, where posted reversals can be queried and retrieved.
- The screen supports the ability to select multiple documents to reverse, so there are a range of fields to support the selection process.
- Posting of reversals where there are several fields with conflicting names such as BANK/PBANK and SRCEAPP/PSRCEAPP.
The other major issue is around the view protocol: ‘Flat, Ordered Revisions’.
- Flat Protocol: Flat is simply a list or table of records with no hierarchical element; a lot of the setup views such as Account Sets are flat.
- Ordered Revision: Ordered Revisions means that an internal list of changes are maintained in memory until a Process or Post command is given. In our example an Insert is performed prior to the Process. If processing multiple reversals, each reversal would be Inserted before the Process performs the reversals as a batch operation.
Understanding Your Log File
After recording is complete, you need to now understand the log file.
In our example the two major issues were the fields we needed to set, and the calls required to post the reversal.
To understand the fields which need to be set, search for ‘Put’. In our example the first put is to PBANK to set the bank code. One thing to note is that RVSpy uses field numbers as opposed to names, so you will need to translate these into names using the Accpac Object Model.
Continue searching and you will find Puts to PAYORID, TRANDATE, and REASON fields.
Next you will find Puts to two key fields SERIAL and LINENO.
These two fields were a little confusing at first but we know they had to do with the transaction being reversed. In both recordings we made we actually used the finder to select the transaction we were to reverse. Prior to the two Put statements we could see the finder searching BKTRANH & BKTRAND, thus the two values corresponded to the keys in these tables.
The final step was to search for the comment we made before we posted the reversal.
Three actions were performed:
- Inserting the record into the view.
- Switching the view mode to ‘Process Reversal Selections’.
- Issuing Process to process the reversal.
Finally
It may be necessary to record something more than once. You may enter something incorrectly, or you may need to repeat an action to understand each call; but with effective use, RVSpy is the best or only tool to understand those Sage300 views/screens which do not support macro recording.
Download Bank Services Reversals source codeThis blog uncovers the mystery of how to create A/R prepayments that are associated to their O/E documents and can be accessed via the drill down button on the A/R receipt entry screen. The logic described and presented in the blog applies equally to O/E orders, shipments and invoices.
Creating a prepayment with an O/E document is difficult for a few reasons:
- Macro recording does not work; you need to use RVSPY to understand view calls.
- Creating a prepayment requires manipulation of three separate, un-composed views and due to the lack of composition each view requires each field to be set individually.
There are three rough steps to creating a prepayment:
- Create an A/R receipt batch
- Create an O/E prepayment record.
- Update the O/E transaction header view.
Full source code is available for download at the bottom of this post.
A/R Receipt Batch
Firstly, it is necessary to either create a new A/R receipt batch or use a currently open batch. Creating the batch is relatively straight forward and really only requires you to set the bank account. Note that you do not need to compose each of the receipt views since you are not creating any receipt entries directly.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
'Open the A/R Receipt Batch View Dim ARReceiptBatch As AccpacView mDBLinkCmpRW.OpenView "AR0041", ARReceiptBatch 'Create a new batch ARReceiptBatch.Fields("CODEPYMTYP").PutWithoutVerification "CA" ARReceiptBatch.Fields("CNTBTCH").PutWithoutVerification 0 ARReceiptBatch.Init 'Set the bank to which the receipt will be entered. ARReceiptBatch.Fields("IDBANK").PutWithoutVerification sBank 'Set the bank currency...if you weren't setting this and were using the default 'bank account currency you can obtain it from the view. If sBankCur <> vbNullString Then ARReceiptBatch.Fields("CODECURN").Value = sBankCur Else sBankCur = ARReceiptBatch.Fields("CODECURN").Value End If 'Set a batch description ARReceiptBatch.Fields("BATCHDESC").Value = "Prepayment Batch from O/E" 'Update the batch ARReceiptBatch.Update |
Update the O/E Prepayment View
The second step is to create the O/E prepayment record. The prepayment record is responsible for creating A/R receipt entry and it also associates the receipt to the O/E order so that there is a drill down against the prepayment.
Each O/E transaction (Order, Shipment & Invoice) has a separate prepayment view where each is identical with the exception of the view id. Therefore the logic used here is applicable to each.
If you are familiar with Sage300 development, most of the views are nicely programmed where a lot of hard work is performed behind the scenes by the view. Unfortunately, the prepayment views are the complete opposite! They do not have any default values either from the customer or order, so it is necessary to explicitly set each field.
The code sample below illustrates this, where it’s necessary to set the Customer, Customer Name & Customer Currency all individually despite setting the Customer Id.
|
1 2 3 4 5 6 7 8 9 |
Dim OrdPrepayments As AccpacView mDBLinkCmpRW.OpenView "OE0530", OrdPrepayments OrdPrepayments.Init 'Associate the prepayment with order OrdPrepayments.Fields("ORDUNIQ") = OEOrdHeaderFields("ORDUNIQ").Value OrdPrepayments.Fields("CUSTOMER").Value = OEOrdHeaderFields("CUSTOMER").Value OrdPrepayments.Fields("CUSTDESC").Value = OEOrdHeaderFields("BILNAME").Value OrdPrepayments.Fields("CUSTCURN").Value = OEOrdHeaderFields("ORSOURCURR").Value |
Update O/E Header View
The final step is to update the prepayment fields on the O/E transaction header view, which update the Total Prepayments field on the order’s Totals Tab.
The attached code is a full working sample illustrating how to create and attach a prepayment and should work for Sage300 versions 5.6 and above.
Download Sage 300 OE Prepayment source code