Back to Basics: Modifying a Subfile
by Kevin Vandever
[The code for this article is available for download.]
Subfiles are not just for displaying data. They are extremely useful when it is necessary to modify data in your database files. In fact, some of the most powerful subfile programs you will write are ones that contain update, add, and delete capabilities. I am going to discuss the technique I use to modify database files using subfiles. I will also explain why I use this technique and not others.
The method I am going to discuss lets me update, add, and delete from a master file. I am assuming some prior knowledge of subfile programming and am therefore not going to cover the basics in this article. For a primer on subfiles, check out "Back to Basics: Introduction to Subfiles."
Now that you're up to speed, I can share the secrets of subfile maintenance. As you go through this article, please refer to the program, SFLMNT, and to the DDS source, SFLMNTDF. Also, as you venture into your career, you can use this code as templates for any name type of master file, such as customer, salesman, or vendor. I have also included the DDS for the logical and physical files used in the program. They are SFL001LF and SFL001PF respectively.
The DDS keywords that are important to modifying a subfile are SFLNXTCHG and SFLRCDNBR. The SFLNXTCHG keyword is placed in the subfile record format and is used to mark a subfile record as changed, so that it will be picked up the next time your program attempts to read changed records. You do not need the SFLNXTCHG keyword for OS/400 to recognize that a user has made a change to a subfile record. OS/400 will mark the subfile record as changed whenever a user changes the data in that record. However, if you want to change data in a subfile record from within your program and to mark that record as changed, you will need to use the SFLNXTCHG keyword in your subfile record format and condition it on an indicator.
The SFLRCDNBR keyword allows OS/400 to know which page of data to display. If you have a subfile with 10 pages of data and you want to display page 7 of that subfile, SFLRCDNBR will let you do just that.
Now, jumping to the RPG program, let's talk about the READC operation. The READC operation will read changed records from a subfile. READC will read entries that have been changed by the user and those changed in the program and marked as changed, by setting on the appropriate indicator conditioning on the SFLNXTCHG keyword.
Putting It All Together
The DDS for formats SFL1 and SF1CTL should look much like that of a self-extending subfile, because that's what it is. You will notice that I have no ROLLDN keyword defined, so OS/400 will take care of the rolling down for me. Since SFLSIZ is greater than SFLPAG, the subfile will be allowed to expand if the user so desires. The paging down will be handled by OS/400, except where new records are added to the subfile, which will be done by the program.
You should also notice the aforementioned keywords SFLNXTCHG and SFLRCDNBR, entered in the subfile record format (SFL). SFLNXTCHG is conditioned with indicator 74. This is the indicator I will manipulate in my RPG program to mark subfile records that have been changed by my program and not the user. User changes are marked automatically by OS/400. The SFLRCDNBR keyword is used in conjunction with the relative record number field (RRN1). When you implement SFLRCDNBR and your program displays the subfile with the EXFMT keyword, OS/400 will figure out the page where the current relative record number sits and will display that page. This will be important because I want the user to go back where he left off when he makes a change to a particular subfile record.
In the DDS, I define the OPTION field as B, which stands for both input and output and allows the user to select specific options on his display. Each option will correspond to a specific action to perform in the program. The VALUES keyword tells OS/400 the valid options that can be entered. Any other option will give the user a warning error. Now, instead of simply displaying data in the subfile, the user will have the chance to do something with this data.
Looking at the RPG code, you will notice that I have defined two subfiles in the F-specs for display file. These two subfiles correspond to the two subfiles I defined in my DDS. In the main routine, when the user selects the Enter key with nothing in the position-to field, the PRCSFL subroutine will be executed. This routine will read the changed subfile records and process them accordingly. Let's check out how it's done.
As I stated earlier, control will pass to the PRCSFL subroutine when the user presses the Enter key with nothing entered in the position-to field. The PRCSFL subroutine contains a do loop that will read the changed records in SFL1. When a user enters one of the valid options in the OPTIONS field, including a blank (which can be done by the space bar or the field exit key), the READC operation will pick up that record and run it through the SELECT routine. Depending on what option was selected, the program will execute another subroutine. By selecting option 4, it will write records to another subfile. Selecting options 2 and 5 will provide the user with a detail screen of data related to that subfile record. In the case of this program, the detail screen will contain address information of the name listed on the subfile record. Selecting option 5 will allow the user to view the information, while option 2 will allow the user to modify the master file data. If option 4 was selected on one or more entries, a new subfile will be displayed, which will allow the user to view, and either cancel or delete, the records that were selected for deletion.
Getting back to the READC loop in the PRCSFL routine, you will notice that the user can select multiple records for processing. That is, he can select more than one record to be displayed, changed, or deleted. He can also select a mixture of the options to be processed. For example, if the user wanted to view subfile records 1, 3, and 5, to delete records 9 and 10, and to change address information on record 7, he could do so by placing the appropriate option next to the appropriate subfile record and pressing the Enter key.
The subfile entries would be processed in the order they exist in the subfile, and if any 4's were selected, a delete confirmation subfile would be displayed after all other processing. Since this is a self-extending subfile, the user could page down and select other records for processing without losing what was entered on the previous pages. The SFLRCDNBR keyword in my DDS will allow me to display the page that contains the last record selected for processing by the user. Once the Enter key is pressed, all the changed records will be processed. If the user were to select options, then position to somewhere else in the subfile, using the position-to field, he would lose what was previously selected.
You're Ready for Master File Maintenance
You've just seen a very useful template for a master file maintenance program. The user is allowed to view, change, and delete data from a database file. I use this type of subfile program when I am working with files that will have a limited number of additions and modifications. What is limited, you ask? Well, for me limited is something of master-file-like quality. Master files are not transactional files, which means that you won't have users pounding away at the keyboard adding data to them. If that were the case, my subfile program would not lend itself very well for that kind of work. For one, the user has to hit F6 every time he wants to add a record. This becomes very time consuming if one has hundreds or thousands of records to add to a file, as is sometimes the case with transactional type files like an order-entry-detail file.
Although it's simple in nature, this technique gives you the tools necessary to modify any field in a subfile record. What I will show you next time is a subfile technique that is not only able to keep up with the most skilled data-entry professional, it might even enhance those skills. This new technique wouldn't be appropriate for master file maintenance, but for heads-down keying, it is ideal. For now, download the code and give it a whirl. You might just have a new template for master file maintenance.
Contact the Editors
|Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.|