Wednesday, December 19, 2012
Tip of the Day: Moving Form Lookup Variables into Custom EEPs
Product: R:BASE eXtreme 9.5 (32/64)
Build..: 9.5.2.11212 or higher www.rupdates.com
Section: Conversion, Speed, Performance, Programming
When upgrading databases from older versions, it is important to take into
account if and how R:BASE may work differently. Applications created with
legacy logic can perhaps be improved upon in new releases.
For instance, in legacy versions of R:BASE, form variables were not calculated
automatically. A RECALC VARIABLES command was needed in an EEP to refresh the
variables and generate different results. In newer releases of R:BASE (7.0 and
higher), form variables are recalculated automatically when the cursor moves
from field to field.
Because of this logic change in R:BASE, the response time may be longer in
forms with many lookup variables which are based upon large tables.
To retain that same performance when using the form, the lookup variables can
be populated within an EEP.
In the following variables list, there are 11 lookups performed for
the "Client"
table based upon a provided client identification number. The variables were
meant to display a range read-only information about the client.
Form : OrderEntry
Main Table : Orders
1 : TEXT vClientFirstName = CFirstName IN Client WHERE ClientID = ClientID
2 : TEXT vClientLastName = CLastName IN Client WHERE ClientID = ClientID
3 : TEXT vClientCompany = CCompany IN Client WHERE ClientID = ClientID
4 : TEXT vClientAddress1 = CAddress1 IN Client WHERE ClientID = ClientID
5 : TEXT vClientAddress2 = CAddress2 IN Client WHERE ClientID = ClientID
6 : TEXT vClientCity = CCity IN Client WHERE ClientID = ClientID
7 : TEXT vClientState = CState IN Client WHERE ClientID = ClientID
8 : TEXT vClientZipCode = CZipCode IN Client WHERE ClientID = ClientID
9 : TEXT vClientPhone = CPhone IN Client WHERE ClientID = ClientID
10 : TEXT vClientFax = CFax IN Client WHERE ClientID = ClientID
11 : TEXT vClientEmail = CEmail IN Client WHERE ClientID = ClientID
Instead, the variable values can be populated using a SELECT command placed
within an "On Exit" Custom EEP, within the control where the client ID was
entered.
-- On Exit EEP
SET VAR vClientFirstName = NULL
SET VAR vClientLastName = NULL
SET VAR vClientCompany = NULL
SET VAR vClientAddress1 = NULL
SET VAR vClientAddress2 = NULL
SET VAR vClientCity = NULL
SET VAR vClientState = NULL
SET VAR vClientZipCode = NULL
SET VAR vClientPhone = NULL
SET VAR vClientFax = NULL
SET VAR vClientEmail = NULL
SELECT +
CFirstName, +
CLastName, +
CCompany, +
CAddress1, +
CAddress2, +
CCity, +
CState, +
CZipCode, +
CPhone, +
CFax, + +
CEmail +
INTO +
vClientFirstName INDIC iv1, +
vClientLastName INDIC iv1, +
vClientCompany INDIC iv1, +
vClientAddress1 INDIC iv1, +
vClientAddress2 INDIC iv1, +
vClientCity INDIC iv1, +
vClientState INDIC iv1, +
vClientZipCode INDIC iv1, +
vClientPhone INDIC iv1, +
vClientFax INDIC iv1, +
vClientEmail INDIC iv1 +
FROM Client WHERE ClientID = .vClientID
RECALC VARIABLES
RETURN
In the following set of variables lookup data within a "ThirdParty" table based
upon a third party payer ID, the variables were also meant to display a range
read-only information.
12 : TEXT vTPPContactName = TPPContactName IN ThirdParty WHERE
TPP_ID = TPP_ID
13 : TEXT vTPPCompany = TPPCompany IN ThirdParty WHERE TPP_ID = TPP_ID
14 : TEXT vTPPConract = TPPContract IN ThirdParty WHERE TPP_ID = TPP_ID
15 : TEXT vTPPCertificate = TPPCertificate IN ThirdParty WHERE
TPP_ID = TPP_ID
16 : TEXT vTPPNotes = TPPNotes IN ThirdParty WHERE TPP_ID = TPP_ID
These variable values can be populated using a similar SELECT command
placed within
an "On Exit" Custom EEP, within the control where the third party payer ID was
entered.
SET VAR vTPPContactName = NULL
SET VAR vTPPCompany = NULL
SET VAR vTPPConract = NULL
SET VAR vTPPCertificate = NULL
SET VAR vTPPNotes = NULL
SELECT +
TPPContactName, +
TPPCompany, +
TPPContract, +
TPPCertificate, +
TPPNotes ,+
INTO +
vTPPContactName INDIC iv1, +
vTPPCompany INDIC iv1, +
vTPPConract INDIC iv1, +
vTPPCertificate INDIC iv1, +
vTPPNotes INDIC iv1 +
FROM ThirdParty WHERE TPP_ID = .vTPP_ID
RECALC VARIABLES
RETURN
Other variables which perform calculations must remain in the Form
Variables. The
variables will continue to be updated if any changes are made to the variables
contained within the expressions.
17 : TEXT vDayOfWeek = (SGET((TDWK(DateContarct)),3,1))
18 : CURRENCY vSubTotal = (SUM(ExtPrice))
19 : CURRENCY vFreight = (.vSubTotal * .01)
20 : CURRENCY vSalesTax = (.vSubTotal * .07)
21 : CURRENCY vInvoiceTotal = (.vSubTotal + .vFreight + .vSalesTax)
Note:
Using such technique, make sure to pre-define all variables with
appropriate data
type as On Before Start EEP, and clear all necessary variables as On Close EEP.
-- Example
-- On Before Start EEP
SET VAR vClientFirstName TEXT = NULL
SET VAR vClientLastName TEXT = NULL
SET VAR vClientCompany TEXT = NULL
SET VAR vClientAddress1 TEXT = NULL
SET VAR vClientAddress2 TEXT = NULL
SET VAR vClientCity TEXT = NULL
SET VAR vClientState TEXT = NULL
SET VAR vClientZipCode TEXT = NULL
SET VAR vClientPhone TEXT = NULL
SET VAR vClientFax TEXT = NULL
SET VAR vClientEmail TEXT = NULL
SET VAR vTPPContactName TEXT = NULL
SET VAR vTPPCompany TEXT = NULL
SET VAR vTPPConract TEXT = NULL
SET VAR vTPPCertificate TEXT = NULL
SET VAR vTPPNotes TEXT = NULL
RETURN
-- On Close EEP
CLEAR VARIABLES iv%,vClient%,vTPP%
RETURN
You will also need to create two variables as Form Expression to capture the
values for ClientID and TPP_ID columns required for the SELECT statements as
stated above.
-- Example
nn : INTEGER vClientID = (ClientID)
nn : INTEGER vTPP_ID = (TPP_ID)
This topic has also been added to the "Increasing Performance in Forms" section
of the "Multi-User Guide"
From The Edge: http://www.razzak.com/fte
Stay productive, my friends!
Very Best R:egards,
Razzak.
www.rbase.com
www.facebook.com/rbase
www.Razzak.com
www.RazzakMemon.com
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
