The Director’s Chair
		Time Saver
		New Feature
by Noreen Redden and Bruce Dillingham		Advanced Techniques
		The Resource Analyzer Challenge
		FOCUS 101
		Quick Tips
		User Group Meeting Schedule

MODIFY: Old Dog, New Trick Don’t you just wish you could use some of the features from a new release in your current release? Well, we have just the thing for you – the MODIFY SPA procedure. Many times we need to gather and store information in separate areas based on different selection criteria. The process of storing this information was thought to be impossible in MODIFY since it does not support stacks. Or does it? We have developed a way of doing just that by using MODIFY SPA to simulate MAINTAIN stacks. Look at Figure 1 for the complete code for the MODIFY. First, let’s discuss the definitions of MAINTAIN and MODIFY: MAINTAIN provides the power of set-based processing, enabling you to read, manipulate, and write groups of records at a time. You manipulate these sets of data using a data structure called a database stack, which is a simple temporary table. Columns in a database stack generally correspond to data source fields, and rows correspond to records, or path instances, in that data source. The database stack itself represents a data source path. MAINTAIN allows users to set up and identify multiple stacks. MODIFY SPA allows for multiple-record processing, which enables you to process multiple segment instances at one time. Each time the request retrieves an instance, it stores the instance values in a work area in memory called the Scratch Pad Area (SPA). The request continues to retrieve instances until it reaches a specified number. After the request has retrieved and stored the instances, the user can read the instance values from the SPA and process them. This is commonly accomplished by using FIDEL to display them all on one screen. The user can update these values and transmit the updated values back to the data source or SPA. Basically the difference between MODIFY and MAINTAIN is that in MODIFY only one stack is available. How can we make the best use of it? Let's look at the sample MODIFY in Figure 1 using a version of the CAR file. (Notice that only two keys are generated.) This stores all the cars for a country in the SPA. If you put your cursor on a particular BODYTYPE and press PF5, it looks up all cars of the same BODYTYPE and stores them after the last HELD CAR later in the SPA. Pressing PF5 again takes you back to the original screen. Figure 1: MODIFY SPA FOCEXEC for simulating MAINTAIN stacks SET PF12=RETURN SET ASNAMES = ON TABLE FILE CAR WRITE COUNTRY AS 'XCOUNTRY' BY COUNTRY WRITE RCOST DCOST SALES BY COUNTRY BY CAR BY MODEL BY BODYTYPE ON TABLE HOLD FORMAT FOCUS AS SPASTACK END -* SET PF KEYS SET PF5=RETURN SET PF7=RETURN SET PF8=RETURN CMS FILEDEF HOLDBOD DISK HOLDBOD DATA A MODIFY FILE SPASTACK COMPUTE HOLDCOUNT/I5 =; SCRENNINDEX/I5=; HOLDINDEX/I5 =; MAX_HELD/I5 =; PFKEY/A4 =; CURSORINDEX/I5 =; CURSORAT/A12=; CRTFORM "ENTER COUNTRY <:LAB.T.BODYTYPE(1)> " " <:LAB.T.BODYTYPE(2)> " " <:LAB.T.BODYTYPE(3)> " " <:LAB.T.BODYTYPE(4)> " " <:LAB.T.BODYTYPE(5)> " " <:LAB.T.BODYTYPE(6)> " "PF3 - EXIT PF2 -BACK TO TOP PF7 - BACKWARD PF8 - FORWARD " "PF5 -- BODYTYPES" COMPUTE OSCREEN/I5 = SCREENINDEX; IF PFKEY EQ 'PF05' GOTO GETMORE; -* PF7 MOVES BACKWARD (SCREENINDEX - 6) PF8 MVOES FORWARD (SCREENINDEX + 6). -* THE COMPUTE VERIFIES THAT SCREENINDEX CAN NEVER BE LESS THAN 0 OR GREATER -* THAN THE LAST VALUE IN THE SPA COMPUTE SCREENINDEX = IF PFKEY EQ 'PF07' THEN MIN(0,SCREENINDEX - 6) ELSE IF PFKEY EQ 'PF08' THEN MAX(MAX_HELD - 1,SCREENINDEX + 6) ELSE SCREENINDEX; IF PFKEY EQ 'ENTR' GOTO UPDATE; GOTO DISP1 ENDCASE CASE GETMORE -* CASE TO GET ALL OF A PARTICULAR TYPE OF BODYTYPE, REGARDLESS OF COUNTRY" -* PF5 FROM DISP1 -* CURSORINDEX CONTAINS THE LINE NUMBER (VALUE OF HOLDINDEX), -* FOR THE SELECTED BODYTYPE COMPUTE HOLDINDEX = CURSORINDEX; REPOSITION COUNTRY -* RETRIEVE THE SELECTED INSTANCE FROM THE SPA GETHOLD -* MAKE SURE THAT WE SET HOLDINDEX ONE LINE BELOW THE LAST VALUE WE -* COLLECTED IN CASE GETINITIAL COMPUTE HOLDINDEX =MAX_HELD + 1; TYPE ON HOLDBOD "HOLDCOUNT WAS 4 " " MAXHELD WAS 4 " "HOLDINDEX IS NOW SET TO MAX_HELD +1 => " -* LET'S SAVE THE BODYTYPE FROM THE SCREEN COMPUTE SAVEBOD/A12 = D.BODYTYPE; PERFORM FINDCOU -* REPEAT NOHOLD MEANS THAT HOLDINDEX AND HOLDCOUNT WILL NOT BE SET -*AUTOMATICALLY REPEAT * NOHOLD COMPUTE FOUNDBOD/A1 = ' '; -* LET'S SELECT ALL THE CAR'S FOR THAT BODYTYPE PERFORM FINDBOD IF FOUNDBOD EQ ' ' GOTO EXITREPEAT; -* PUT THEM IN THE SPA HOLD SEG.BODYTYPE COUNTRY ENDREPEAT COMPUTE MAX2/I5 = HOLDCOUNT; -* START THE DISPLAY (DISP2) ONLY FOR NEW INSTANCES COMPUTE SCREENINDEX = MAX_HELD; PERFORM DISP2 ENDCASE CASE DISP2 CRTFORM LINE 1 " DISPLAY OF ALL COUNTRY AND CAR FOR BODYTYPE In CASE GETINITIAL, we establish the size of the SPA by doing a REPEAT 100. This reserves enough space for 100 entries. After selecting England as the country, we perform a NEXT through the CAR segment and HOLD COUNTRY and the entire CAR segment, the contents of the SPA then appear as shown in Figure 2 below. Figure 2: All records with BODYTYPE=SEDAN collected 1 JAGUAR V12XKE AUTO CONVERTIBLE 8,878 7,427 0 2 JAGUAR XJ12L AUTO SEDAN 13,491 11,194 12000 3 JENSEN INTERCEPTOR III SEDAN 17,850 14,940 0 4 TRIUMPH TR7 HARDTOP 5,100 4,292 0 . . . . . . Unused SPA space . . . . . . . . 99 . . . . . 100 . . . . . The value of HOLDCOUNT is 4 and HOLDINDEX is 5. We save the value of HOLDCOUNT as MAX_HELD (4) so that we can determine where we want to place more data later in the code. When we select SEDAN from the second screen, we need to ensure the correct position in the SPA. We accomplish this by changing the value of HOLDINDEX to the next available line in the SPA. This is necessary because the default value of HOLDINDEX is 1 and will overwrite the previously stored values. Since HOLDCOUNT was 4 and MAX_HELD is 4, we can set HOLDINDEX to MAX_HELD +1 (5) and position HOLDINDEX to the next available SPA line. The selection of all records having a BODYTYPE of SEDAN is collected and placed in the SPA beginning at the OLDINDEX of 5 as shown in Figure 3. Figure 3: BODYTYPE=SEDAN records inserted in SPA at OLDINDEX 5 5 JENSEN INTERCEPTOR III SEDAN 17,850 14,940 0 6 PEUGEOT 504 4 DOOR SEDAN 5,610 4,631 0 7 ALFA ROMEO 2000 4 DOOR BERLINA SEDAN 5,925 4,915 4800 8 DATSUN B210 2 DOOR AUTO SEDAN 3,139 2,626 43000 9 TOYOTA COROLLA 4 DOOR DIX SEDAN 3,339 2,886 35030 10 AUDI 100 LS 2 DOOR AUTO SEDAN 5,970 5,063 7800 11 BMW 2002 2 DOOR SEDAN 5,940 5,800 8950 12 BMW 2002 2 DOOR AUTO SEDAN 6,355 6,000 8900 13 BMW 3.0 SI 4 DOOR SEDAN 13,752 10,000 14000 14 BMW 3.0 SI 4 DOOR AUTO SEDAN 14,123 11,000 18940 15 BMW 530I 4 DOOR SEDAN 9,097 8,300 14000 16 BMW 530I 4 DOOR AUTO SEDAN 9,495 8,400 15600 . . . . . . Unused SPA space . . . . . . . . 99 . . . . . 100 . . . . . As you can see, storing the value of the next available line in the SPA and then setting HOLDINDEX to that value effectively allows you to manage the SPA space. In effect we created two "STACKS" in this case. Looks like this old dog really can learn new tricks. For more on this, visit http://techsupport.ibi.com/app/css_web_tool/default.htm.