I was looking for an answerbook on epicweb that I had gotten started with that gave coding examples when I found this in Tech Tips. There are lots of other
good tips, but I had never seen this best practices before.
Techtip is under Support > My EpicWeb > Search Tips. I enter Epicor Ent Ed and pick module BPM.
I also use OpenEdge Development: ABL Essentials and OpenEdge Development: ABL Reference. I think you have to join the progress community site to get access
to them.
Greg
BOOK: Epicor General Information
PAGE: 674SYD
SUMMARY: BPM good practices for ABL coding
DESCRIPTION:
BPM good practices for ABL coding
It has been observed that some on-site development done in BPMs and the Configurator are not following best practice.
The following is a guide to the best practices when writing 4GL code within BPMs and the Configurator. Failure to follow these guidelines can lead to poor performance, unnecessary record locking and unnecessary support calls.
RESOLUTION:
The first rule below is the most important. This can help avoid lock-wait timeouts and deadlocks in your system.
!!!!THE MOST IMPORTANT RULE OF ALL!!!!
!!!!THE MOST IMPORTANT RULE OF ALL!!!!
GOLDEN RULE: You must put a lock clause on every table access.
!!!!THE MOST IMPORTANT RULE OF ALL!!!!
!!!!THE MOST IMPORTANT RULE OF ALL!!!!
If just reading the database you must have a no-lock clause on the record access. Failure to do so opens the door for database locking problems.
If updating the database, an exclusive-lock should be used and its scope should be minimised.
E.g. if you need read access to the Company table throughout the BPM or OnLeave event and you also need to update the Company table conditionally then you do not want the Company table to be locked for the whole duration of the trigger. To avoid this you use
two buffers...
Example of no-lock clause:
For each OrderHed no-lock where <criteria>:
/* no writing to the OrderHed table done here */
End.
Example of an exclusive-lock clause:
For each OrderHed exclusive-lock where <criteria>:
OrderHed.CheckBox01 = true.
/* other actions here */
End.
STABILITY RULE (with Vantage 8/Epicor 9.04): Do not use exclusive-lock when using lib\UpdateTableBuffer.p UpdateTableBuffer inherently uses a lock clause inside the program. Using exclusive-lock here can cause locking issues.
for each OrderHed no-lock
where ... :
run lib\UpdateTableBuffer.p (
input buffer OrderHed:handle,
input "Number01",
input 12345
).
end.
The next rules are for performance improvements and are in the order of importance.
PERFORMANCE RULE 1: Always use Indexed fields if possible
When writing queries, make sure you use indexed fields where possible. The available indexes can be found in the Data Dictionary Viewer (under System Management). Make sure that you use as many fields from left to right as possible.
For example, if writing a query to find all Orders against a certain PONum you could write a query as follows:
For each OrderHed no-lock where OrderHed.PONum = 'xxx':
/* do require actions here */
End.
The above is not using an index as it requires extra fields. A better Index to use would be the "OpenPONum" index (see Data Dictionary). Using indexes will ensure that the query will have efficient performance as the database grows. If you do not use indexes,
the BPM will become slower over time, especially on high transaction volume tables.
For each OrderHed no-lock
where OrderHed.Company = Cur-Comp
and OrderHed.OpenOrder = true
and OrderHed.PONum = 'xxx':
/* do require actions here */
End.
PERFORMANCE RULE 2: Do not use lib\UpdateTableBuffer.p with Epicor 9.05.
When Epicor added Extended User Definable tables to 9.05 it added a technical requirement that the E9 software has a full compiler installed. So this removed the need to use the UpdateTableBuffer program in order to update database records. You can get a significant
improvement in performance by using a For statement with field assignments.
Instead of:
for each OrderHed no-lock
where ... :
run lib\UpdateTableBuffer.p (
input buffer OrderHed:handle,
input "Number01",
input 12345.678
).
end.
Use the following for statement with an exclusive-lock.
for first OrderHed exclusive-lock
where ...:
OrderHed.Number01 = 12345.687 .
end.
PERFORMANCE RULE 3: Minimise the number of queries.
Lowering the number of queries executed will improve performance. This is true for MS-SQL and Progress databases.
Example: Instead of...
for each QuoteHed no-lock
where QuoteHed.Company = CUR-COMP
and QuoteHed.QuoteNum = aQuoteNum:
for each Customer no-lock
where Customer.Company = QuoteHed.Company
and Customer.CustNum = QuoteHed.CustNum:
/* do require actions here */
End.
End.
Use the following code instead:
for each QuoteHed no-lock
where QuoteHed.Company = CUR-COMP
and QuoteHed.QuoteNum = aQuoteNum,
each Customer no-lock
where Customer.Company = QuoteHed.Company
and Customer.CustNum = QuoteHed.CustNum:
/* do require actions here */
End.
This executes a single query to bring back the required records.
PERFORMANCE RULE 4: Avoid Find statements.
In a MS-SQL environment you want to avoid the use of Find statements. They can be a cause of poor performance when using an MS-SQL database. They are fine to use in a Progress db environment.
Another benefit is the code is visually easier to read.
Instead of using a Find statement, e.g.:
find first OrderHed where ... no-lock no-error.
if available OrderHed then do:
/* do require actions here */
end.
Use a For statement, e.g.:
for first OrderHed no-lock where ... :
/* do require actions here */
end.
Using this For statement and putting the field usage inside the for block avoids any need for an if-available check.
If you need to use the For statement like a Find (to check if something is not available) then you can use, e.g.:
for first OrderHed no-lock where .... : end.
If NOT available OrderHed then do:
/* do require actions here */
End.
If you use the NO-ERROR clause on a FIND or the "find" version of a FOR then you must qualify access with IF AVAILABLE.
E.g., the following FOR statement may fail to find a record. If this happens the access to OrderHed will cause an error.
for first OrderHed where .... no-lock: end.
OrderHed.CheckBox01 = true.
Instead make sure the record is available before you use it...
for first OrderHed where .... no-lock: end.
if available OrderHed
OrderHed.CheckBox01 = true.
An alternative to this is to use the findtbl.i program.
See Answerbook 682SYD for details on this
PERFORMANCE RULE 5: List the Fields you need access to in the FOR statement by using a "FIELDS" statement.
Performance improvements can be obtained by not pulling back all fields in a table. To do this you use the "fields" clause with a For statement.
!!WARNING!!: If you miss out a field in the fields clause, it will not pick it up at Validation/Compile time. It will only be picked up at Runtime, i.e., only visible in the Appserver Log.
Suggested process is:
Create query WITHOUT fields clause, and test
Once you're sure it works, add the fields clause and test again
Example: Instead of...
find first OrderHed
where OrderHed.Company = CUR-COMP no-lock no-error.
if OrderHed.CheckBox01 then do:
/* do require actions here */
end.
Use the following code for better results:
for first OrderHed
FIELDS (Company CheckBox01) no-lock
where OrderHed.Company = CUR-COMP
and OrderHed.CheckBox01:
/* do require actions here */
end.
PERFORMANCE RULE 6: Use an Implicit Fields statements for nested queries if the fields are not required.
If you use the "fields" key word without any parameters, it uses only the fields required to make the join. This can increase efficiency of the query statement.
E.g. if you wanted to run some update unposted AP Invoice line descriptions, you could do the following.
Example query:
for each APInvGrp no-lock
where APInvGrp.PostErrorLog = ''
and APInvGrp.Company = CUR-COMP,
each APInvHed no-lock
where APInvHed.Company = APInvGrp.Company
and APInvHed.GroupID = APInvGrp.GroupID
and Posted = false,
each APInvDtl exclusive-lock
where APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
and APInvDtl.Description = 'Text to Change' :
APInvDtl.Description = 'Text changed!'.
end.
This would bring back all the fields in APInvGrp (34 fields), APInvHed (266 fields), and APInvDtl (157 fields).
This is much more efficient by doing the following:
for each APInvGrp FIELDS() no-lock
where APInvGrp.PostErrorLog = ''
and APInvGrp.Company = CUR-COMP,
each APInvHed FIELDS() no-lock
where APInvHed.Company = APInvGrp.Company
and APInvHed.GroupID = APInvGrp.GroupID
and Posted = false,
each APInvDtl FIELDS() exclusive-lock
where APInvDtl.Company = APInvHed.Company
and APInvDtl.VendorNum = APInvHed.VendorNum
and APInvDtl.InvoiceNum = APInvHed.InvoiceNum
and APInvDtl.Description = 'Text to Change' :
APInvDtl.Description = 'Text changed!'.
end.
Only the fields included in the query are returned: APInvGrp (3 fields), APInvHed (4 fields), and APInvDtl (4 fields). Which is much more efficient.
This would not work if you also wanted APInvDtl.Character01 and/or APInvHed.Character02 as they are not in the join statement.
Some other good practices that do not relate to performance or stability.
GUIDELINE 1: Comment your code.
All code should have some commenting in it. Each block that is not-self explaining should have comments. You should also put a comment at the end of a for loop to identify the matching start position. Here is an example of code with comments:
for first Customer no-lock
where Customer.Company = QuoteHed.Company
and Customer.CustNum = aCustNum:
/* this loop finds all "escalated quotes" */
for first QuoteHed no-lock
where QuoteHed.Company = CUR-COMP
and QuoteHed.CustNum = Customer.CustNum
and QuoteHed.CheckBox01 = true:
/* do require actions here */
end. /* for first QuoteHed */
end. /* for first Customer */
GUIDELINE 2: Indent your code correctly.
Indentation should be used for all loops and do statements.
This helps a person reading your code understand where parts stop and start.
Example of bad indentation:
for first Customer no-lock where Customer.Company = QuoteHed.Company and Customer.CustNum = aCustNum:
if Customer.CheckBox01 = true then do:
for first QuoteHed no-lock where QuoteHed.Company = CUR-COMP and QuoteHed.CustNum = Customer.CustNum and QuoteHed.CheckBox01 = true:
end.
Customer.CheckBox01 = false.
end.
end.
Example of good indentation:
for first Customer no-lock
where Customer.Company = QuoteHed.Company
and Customer.CustNum = aCustNum:
/* this loop finds all "escalated quotes" */
if Customer.CheckBox01 = true then do:
for first QuoteHed no-lock
where QuoteHed.Company = CUR-COMP
and QuoteHed.CustNum = Customer.CustNum
and QuoteHed.CheckBox01 = true:
/* do require actions here */
end. /* for first QuoteHed */
Customer.CheckBox01 = false.
end. /* if Customer.CheckBox01 = true */
end. /* for first Customer */
GUIDELINE 3: Do not hard-code Company and Plant codes.
Using hard-coded values can make code fragile to change. Rather than hard code Company codes it pays to use the variable CUR-COMP which provides the current company code. Likewise there is also CUR-PLANT for the user's current Plant.
In the Configurator you need to use the getCurrentCompany() function. If using this value in many places you can either call the function or store the value returned by the function in a local variable. e.g.
define variable curCompany like Company.Company no-undo.
curCompany = getCurrentCompany().
Note: it is safer (but less efficient) to use the getCurrentCompany() function. If you use a variable, you need to ensure that the "curCompany" variable is unique to the Configurator. If you do not, then you will get an error when trying to approve/compile
the Configurator as there will be multiple definitions of the "curCompany" variable.
To avoid this, use the Configurator "input name" in the variable name. E.g., if the Input Name is PAINT, then use:
define variable coyPAINT like Company.Company no-undo.
coyPAINT = getCurrentCompany().
PRODUCT:
Epicor 9.05
MODULE:
BPM / Custom Coding / ABL / 4GL / Configurator
KEYWORDS:
customization, customisation, ABL, 4GL, code example, good practices, best practices, guidelines, performance, stability, business process management, BPM, product configurator,
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
On Behalf Of Jose Gomez
Sent: Wednesday, October 23, 2013 8:21 PM
To: Vantage
Subject: Re: [Vantage] BPM Coding Language
There is the Experience and Customization guide on epic web that can get you starded. Good luck, there is also the ICE tools book online at epicweb that can help.
The programming language for BPMs is ABL or 4GL
Quis custodiet ipsos custodes?
On Wed, Oct 23, 2013 at 6:38 PM, <jseeman@...> wrote:
Hello,
I am trying to get started with using BPMs and I am new to the programming side of Epicor. What I am trying to establish is what programming language is used if you wish to write custom code? In addition, does anyone have a "Getting Started"
guide or "Basics Programming" guide that I can reference.
CONFIDENTIALITY NOTICE
The information contained in this communication, including attachments, is privileged and confidential. It is intended only for the exclusive use of the addressee. If the reader of this message is not the intended recipient, or the employee or agent responsible
for delivering it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us at 727-578-6280 and immediately
delete the communication.
"This (document/presentation) may contain technical data as defined in the International Traffic In Arms Regulations (ITAR) 22 CFR 120.10. Export of this material is restricted by the Arms Export Control Act (22 U.S.C. 2751 et seq.) and may not be exported
to foreign persons without prior approval form the U.S. Department of State."
