Great Plains Dexterity Dynamics eEnterprise Microsoft CRM Microsoft RMS Crystal Reports SQL .Net C# International
Phones
Toll Free: 1-866-528-0577
Office: 1-630-961-5918
Cell (consultant-on-duty): 1-630-854-0597
Email: welcome@albaspectrum.com.
Choose your naming convention:
Dexterity: Procedures and Functions, Forms and Windows, Variables and Types
SQL Stored Procedures
Dexterity data retrieving mechanism based on
Range start, Range End, Get First and Get Next clauses is similar, however a little bit slower to cursors in Transact SQL. Long ranges in Dexterity are good candidates for replacement by SQL stored procedures with update clause
For example, consider to replace following Dexterity code:
Range clear SOP_HDR_WORK.
Clear ‘SOP Type’ of table SOP_HDR_WORK.
Clear ‘SOP Number’ of table SOP_HDR_WORK.
Range start table SOP_HDR_WORK.
Get first table SOP_HDR_WORK.
While err<>EOF do
If ‘Salesperson ID’ of table SOP_HDR_WORK = “ERIC” then
Edit table SOP_HDR_WORK.
Set ‘Salesperson ID’ of table SOP_HDR_WORK to “BILL”.
Save table SOP_HDR_WORK.
End if.
Get next table SOP_HDR_WORK.
End while.
With the following SQL code
Update SOP10100 set SLPRSNID=”BILL” where SLPRSNID=”ERIC”
Bringing new data into table in Dexterity is based on change/edit table clauses, in SQL they are equivalent (by performance) to inserting one record at the time.
When having long cycle of change/edit table in Dexterity, consider replacement by SQL stored procedure with Insert Into clause.
Before using cursor, consider using update statement first. Use cursor only when there is no way for update statement. Cursors are very slow in comparison to update statement. The core elements of SQL performance are joins (nested loop, merge and hash joins in SQL 7.0) are deployed for regular (aggregated) SQL statements, such as update, select, insert into, but not used for cursors.
You can use ADO (or older Microsoft technologies) to access Dynamics/eEnterprise data. Another way is using Dexterity fragments with sanscript statements
Below is the sanscript example:
script_str = "local boolean err_val. local string vendorclass,accountdb_str,accountcr_str. "
script_str = script_str + "release table AS_IntegSetup. "
script_str = script_str + "set 'Setup Key' of table AS_IntegSetup to 1. "
script_str = script_str + "get table AS_IntegSetup. "
Advantage of Dexterity – better and more flexible security.
Advantage of ADO or other Microsoft technologies – better debugging facilities. As a rule – use Dex if you plan light Data access/modification with VBA and use VBA ADO or others when you plan heavy data access modification. In the heavy data access or modification consider, however either deploying SQL stored procedures or writing your project in Dexterity.
When you need to access third party tables in your customization, for example if you need to work with Fixed Assets or Project Accounting data, that are currently in FASQL.DIC and PA7378.DIC you can use two techniques:
The important difference is the inclusive ranges only in B-trieve/Pervasive SQL case and both inclusive and exclusive ranges for Microsoft SQL Server. Exclusive ranges are more flexible. For example, if you have index in Dexterity
Item Number
Vendor ID
Effective Date
And if you want all the items with Effective Date from 01/01/00 to 02/01/00 there is no way to get it using B-trieve inclusive range and this index, however exclusive index on Microsoft SQL platform will do the job.
In the case of B-trieve you will need another index, such as
Effective Date
Item Number
Vendor ID
The important drawback for Pervasive SQL/B-trieve in comparison to Microsoft SQL is absence of left joining for tables. You can do equal joins only. To find that, open table linking expert in Crystal, highlight any connection and right click you mouse, you will see the possible joining methods. In the case of Pervasive all will be deemed, except the top one – equal join. The illustration of the effect. Imagine you have Customer record in customer master and you sometimes have Salesperson ID for the customer and other times you don’t assign Salesperson at all. If you link Customer Master with Salesperson Master by Salesperson ID, Crystal Report will miss the records of customers, which do not have assigned salesperson (no way to link by empty field). In order to resolve this in B-trieve you need to create Salesperson details subreport and then link this subreport by salesperson ID with main report. Subreport can have empty data in it.
Another drawback for Pervasive SQL/B-trieve is the retriction for links to use key fields only (in the case of Microsoft SQL you can link any field with any other field of compatible type).
When designing Crystal report for MS SQL Server data, it is highly advisable to design Select statement with joins to all tables first. This will allow you to test performance using query tools. When this is done transfer the construction into Crystal
Easy way to call Crystal Report from your VBA code for any modified form:
Const RPT = "D:\Clients\Hospitality Group\Invoice Status.rpt"
Public crwApplication As CRPEAuto.Application
Public crwReport As CRPEAuto.Report
Private Sub Print_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)
If SalesTransactionInquiryZoo.Type = "Invoice" Then
If crwApplication Is Nothing Then
Set crwApplication = CreateObject("Crystal.CRPE.Application")
End If
Set crwReport = crwApplication.OpenReport(RPT)
crwReport.ParameterFields(1).SetCurrentValue (DocumentNo)
crwReport.Preview
End If
In some cases you want populate temp tables for your custom scrolling windows with SQL Stored Procedures. Good example – you want the aggregated monthly summaries for the accounts, complying specific wildcard, such as 1XX-2XXX-XX be displayed in your scrolling window. Dexterity in populating this table is very ineffective – it will come through all the records in GL10000 table. The better idea to deploy stored procedure, which will take the temp table as a parameter. Use function GetOSName. Below is the fragment of code:
{change and save will create temporary table in SQL}
change table AS_GL_Inquiry_Detail.
save table AS_GL_Inquiry_Detail.
{and then we transfer the table name as a parameter}
call sp_sbRecreateSBGLIND, L_Ret_Code, Table_GetOSName(table AS_GL_Inquiry_Detail))
Use reject records in fill script for the scrolling window when you don’t want the specific record from the range to be displayed. However it is good only if the percentage of the records rejected is relatively small. Otherwise consider creating additional key, which creates better range for this scrolling window. Be sure that scrolling bars for scrolling window navigation suppose that the whole range is displayed and they are confused and behave strange when large percent of records is rejected.
Use source code for understanding possible customization, good candidates – lookup buttons (select vendor, customer, item, etc.) and drill down buttons on your scrolling windows (drill down to specific document ).
Below is the fragment of code taken from Dynamics.DIC source code for Lookup button for SOP Document on SOP Entry window:
local 'Document Date' l_date.
open form SOP_Document_Lookup return to '(L) Temp Control Number'.
call OPEN of form SOP_Document_Lookup,
'SOP Sort By',
0, {return SOP Number}
WORK,
1, {lock the switch}
0, {SOP Type}
"", {SOP Number}
"", {Customer}
"", {Batch}
"", {TRX Source}
l_date,
0. {Master Number}
Now in your custom window you can use slightly modified code for Lookup button, which will return the SOP Document into your field. Just modify return to statement.
If you need to call custom functions in custom reports, use prefics rw_ in function name. The following function will return the batch comment string for SOP Document:
function returns string rc_batch_comment.
in string in_sop_number.
set 'SOP Type' of table SOP_HDR_WORK to 3.{Invoice only}
set 'SOP Number' of table SOP_HDR_WORK to in_sop_number.
get table SOP_HDR_WORK.
if err()=OKAY then
set 'Batch Source' of table Batch_Headers to "Sales Entry".
set 'Batch Number' of table Batch_Headers to 'Batch Number'
of table SOP_HDR_WORK.
get table Batch_Headers.
if err()=OKAY then
set rc_batch_comment to 'Batch Comment' of table Batch_Headers.
else
clear rc_batch_comment.
end if.
else
clear rc_batch_comment.
end if.
Then move SOP Invoice Blank form to your customization and if user wants to include batch comment – she/he can create local filed with the function call.