DOCUMENT #769
==========================================================================
=
LOOKUP AND EDIT DATA FROM A FORM
==========================================================================
=
PRODUCT: R:BASE VERSION: 4.5+ or Higher
==========================================================================
=
CATALOG: Forms, Reports & Labels AREA : FORMS
==========================================================================
=
It is easy to let users to lookup rows of data to edit from within a form.
Previous versions of R:BASE required a command file that took you in and out
of the edit form. Using a form-in-a-form lets your users stay in a field in
a form and enter different values for which the row or rows immediately
appear for editing. Edit the data, save changes and the cursor returns to
the same field on the form ready for entry of the next data value to lookup
and edit. You can easily modify your existing edit forms to do this.
It make it even easier for your users, add a pop-up menu to the field. Users
select the correct customer from a menu displaying the company name. Another
option is to provide users a search menu, letting them select from different
query options to find the row.
Create the form
1. Make a copy of the edit form. This copy is used as a "template".
2. Modify the copy. Remove all the field locations from the form. Remove
all tables except for the first table (automatically removes the field
locations for those tables). Remove all pages except the first page
(automatically removed when all field locations are removed.
3. On the Form settings screen, indicate this form can only be used with
Edit.
4. You now have a "template", a form that looks like the first page of the
original form, but with no fields located and no user-defined menu. the
form is a page of text only. The actual editing of data is done on the
original form which will be called from this template form.
5. Locate a variable in the same position as the field that is used as the
unique identifier for a row. For example, if you edit customer records and
identify customers by the Cust_Id column, locate a variable, VCust_Id, in
the same location on the form where the Cust_Id column is located on the
original form. This is the only field located on the form.
6. Customize the Field settings for the variable to make sure the user can
change the data in the field.
7. Add an On exit from field EEP, search.eep. The EEP can be created from
Forms create/modify by pressing Shift-F4 from the EEP name on the Field
settings screen.
*(SEARCH.EEP)
SET VAR vlast = (LASTKEY(0))
IF vlast = '[Esc]' THEN
RETURN
ENDIF
EDIT USING orig_frm WHERE Cust_Id = .VCust_Id
SET VARIABLE VCust_Id = NULL
RETURN
The EEP executes unless the user presses [Esc] from the template form. If
the user presses [Esc] the EEP does not execute and the user exits the
template form. The EDIT USING command brings up the original form using a
WHERE clause with the VCust_Id variable identifying the specified customer
.
After the data is edited and the original form exited, the variable
VCust_Id is reset to NULL so it will be blank on return to the template
form.
8. The command EDIT USING template brings up the template form. The origin
al
form is called by the EEP and displays the selected data for editing.
Add a pop-up menu
Use the new pop-up menu features of 4.5 Plus! to present a menu of company
names for the user to select from. The menu comes up automatically and
automatically executes the EEP.
1. Locate a second variable on the form. It is a variable for the purpose
of giving the cursor a place to land when exiting the template form.
Locate the variable, vdummy, as a single character text variable
immediately to the left of the VCust_ID variable. There should be no space
between them. The field order is vempid first, vdummy second.
2. Customize the Field settings for the variable VCust_ID. Add a pop-up
menu definition selecting the table and columns as desired. Make sure to
specify Yes to have it come up automatically and Yes to skip to the next
field. The same EEP, search.eep, runs On exit from field but needs to be
edited.
3. Edit the EEP code to add SKIP TO commands. So long as the user keeps
requesting new records, they stay in the VCust_ID field and the menu is
always displayed. When they press Esc to exit the template form, the
cursor is placed in the vdummy field.
*(SEARCH.EEP)
SET VAR vlast = (LASTKEY(0))
IF vlast = '[Esc]' THEN
SKIP TO vdummy
RETURN
ENDIF
EDIT USING orig_frm WHERE Cust_Id = .VCust_Id
SET VARIABLE VCust_Id = NULL
SKIP TO VCust_Id
RETURN
3. Customize the field settings for the vdummy field to add an EEP On entry
into field. The EEP automatically exits the user from the template form. It
uses the value of vlast as determined by search.eep.
*(LEAVE.EEP)
IF vlast = '[Esc]' THEN
PLAYBACK esc.pla
ENDIF
RETURN
4. Create the playback file, esc.pla, using RBEdit. Select New playback
file from the main RBEdit menu and enter the following:
[Esc][Enter]
Save the file and Exit. When executed from the EEP, the playback file
executes the keystrokes and automatically exits the template form.
Add a search routine
Instead of a pop-up menu, the EEP on exit from the VCust_ID can call a
search routine letting the user select from different search options.
Once the record is selected, the original form is called by the EEP and
displays the selected data for editing.
1. Create an ASCII menu file with the available search options for the user
to choose from. For example,
CustFind.MNU
POPUP
|Company Name|
|Last Name|
|Soundex Last Name|
|Customer I.D.#|
ENDC
2. Add code similar to the following to the beginning of the EEP called by
the VCust_ID field. This EEP is changed to execute On entry into field. In
addition, the LASTKEY function is moved to check if the user presses Esc at
the search pop-up menu. The SKIP TO command is changed to skip to the
vdummy field.
Depending on the selection from the search menu, the record to edit is
found using different criteria. Additional menus may be displayed if more
than one record matches the initial condition. The table data is retrieved
from must have the appropriate indexed columns for searching. In this
example, there is a computed column defined that uses the internal R:BASE
UDF, _SOUNDEX, to store the Soundex code. All the columns used for
retrieving data are indexed.
*( CustFind.CMD
* Customer cover routine
*
* Copyright (c) MicroRim, Inc. 1992. All rights reserved.
)
-- the WHILE loop code is the search routine
WHILE #PI > 0 THEN
SET V VCLstNm = NULL, VCCompany = NULL, VChoice TEXT = NULL, +
VCust_Id = NULL
CHOOSE VQueryMethod FROM CustFind.MNU AT CENTER CENTER
IF VQueryMethod = '[ESC]' THEN
SET VAR vlast = (LASTKEY(0))
SKIP TO vdummy
RETURN
ENDIF
IF VQueryMethod = 'Soundex Last Name' THEN
DIALOG 'Enter customer''s last name' VCLstNm VEndKey 1
IF VEndKey = '[ESC]' OR VCLstNm IS NULL THEN
GOTO ExitROut
ENDIF
SET VAR VSCLstNm = (UDF('_SOUNDEX',.VCLstNm))
-- a menu displays of all customer's with the specified last name
CHOOSE VChoice FROM #VALUES FOR +
((LJS(CDispFrstNm,10)) & (LJS(CDispLstNm,16)) & +
(LJS(CDispCompany,30)) & (LJS(CState,2)) & +
(CTXT(CustId))), CustId +
FROM Customer +
WHERE SCLstNm = .VSCLstNm +
ORDER BY CLstNm, CFrstNm AT CENTER CENTER
ENDIF
IF VQueryMethod = 'Last Name' THEN
DIALOG 'Enter customer''s last name' VCLstNm VEndKey 1
IF VEndKey = '[ESC]' OR VCLstNm IS NULL THEN
GOTO ExitROut
ENDIF
CHOOSE VChoice FROM #VALUES FOR DISTINCT +
((LJS(CDispFrstNm,10)) & (LJS(CDispLstNm,16)) & +
(LJS(CDispCompany,30)) & (LJS(CState,2)) & +
(CTXT(CustId))), CustId +
FROM Customer +
WHERE CLstNm LIKE .VCLstNm +
ORDER BY CLstNm, CFrstNm AT CENTER CENTER
ENDIF
IF VQueryMethod = 'Company Name' THEN
DIALOG 'Enter customer''s company name' VCCompany VEndKey 1
IF VEndKey = '[ESC]' OR VCCompany IS NULL THEN
GOTO ExitROut
ENDI
CHOOSE VChoice FROM #VALUES FOR +
((LJS(CDispFrstNm,10)) & (LJS(CDispLstNm,16)) & +
(LJS(CDispCompany,30)) & (LJS(CState,2)) & +
(CTXT(CustId))), CustId +
FROM Customer +
WHERE CCompany LIKE .VCCompany +
ORDER BY CCompany, CLstNm AT CENTER CENTER
ENDIF
IF VQueryMethod = 'Customer I.D.#' THEN
SET VAR VCust_Id TEXT
DIALOG 'Enter customer''s I.D.#' VCustId VEndKey 1
SET VAR VCust_Id INT
IF VEndKey = '[ESC]' OR VCust_Id IS NULL THEN
GOTO ExitROut
ENDIF
SELECT CustId INTO VCust_Id ICustId FROM Customer +
WHERE CustId = .VCust_Id
ENDIF
IF VChoice IS NOT NULL THEN
SET VAR VCust_Id TEXT = .VChoice
SET VAR VCust_Id INT
BREAK
ELSE
SET VAR VCust_Id = NULL
ENDI
ENDWHI
EDIT USING orig_frm WHERE Cust_Id = .VCust_Id
LABEL ExitROut
SKIP TO vdummy
RETURN
The entry EEP on the vdummy field is also modified. It is now used to reset
the variable VCust_Id to NULL and to skip back to the VCust_Id field. This
is needed because we changed the EEP on VCust_Id to an entry EEP.
*(LEAVE.EEP)
IF vlast = '[Esc]' THEN
PLAYBACK esc.pla
ELSE
SET VARIABLE VCust_Id = NULL
SKIP TO VCust_Id
ENDIF
RETURN