I've actually created some pretty complicated WebFOCUS reports but just haven't done anything with this logic and am having an old timer momemt I guess - so I come begging.

Here is a sample file:

Item Action
1 INSERT
1 UPDATE
1 OLD
2 UPDATE
2 OLD
2 UPDATE
2 OLD
3 UPDATE
3 OLD
3 UPDATE
3 OLD
4 UPDATE
4 OLD
5 UPDATE
5 OLD
6 INSERT
6 UPDATE
6 OLD

I want to apply the following logic to get results:
Within each group of items if:
a. if the Action is INSERT - print the UPDATE and the OLD records associated with that item
b. if the Action is UPDATE - and there are 2 UPDATE's associated with that item - print the second one, otherwise print the only one
c. if the Action is OLD - and there are 2 OLD's associated with that item - print the first one, otherwise print the only one

I can do this in Access but just don't understand WebFOCUS's record movement logic very well. I thought about and have investigated the lst fst functions but don't see that working since this doesn't appear to follow that logic. Any ideas and suggestions to help me get moving in the right direction will be greatly appreciated. I'm trying to make sense of an interface audit file for the hospital I work at. Thanks!

I think if you put the data into a grid using ACROSS, you can apply your logic a lot easier.

Here's something I cooked up. I added an extra column of data to have something to report on. I don't know if this will work for you, but it might help...

FILEDEF ACTIVITY DISK activity.txt
-RUN

-WRITE ACTIVITY 1 INSERT DSFSDF
-WRITE ACTIVITY 1 UPDATE FDSD
-WRITE ACTIVITY 1 OLD    GFFG
-WRITE ACTIVITY 2 UPDATE DSFF
-WRITE ACTIVITY 2 OLD    EFSDFD
-WRITE ACTIVITY 2 UPDATE DFFDS
-WRITE ACTIVITY 2 OLD    IUYUYI
-WRITE ACTIVITY 3 UPDATE WEWEEW
-WRITE ACTIVITY 3 OLD    KJK
-WRITE ACTIVITY 3 UPDATE WWREE
-WRITE ACTIVITY 3 OLD    JHJHJH
-WRITE ACTIVITY 4 UPDATE SDFSDV
-WRITE ACTIVITY 4 OLD    QWWQQD
-WRITE ACTIVITY 5 UPDATE VNVBV
-WRITE ACTIVITY 5 OLD    AASS
-WRITE ACTIVITY 6 INSERT SDFDSF
-WRITE ACTIVITY 6 UPDATE UOIUI
-WRITE ACTIVITY 6 OLD    SASEF

FILEDEF MASTER DISK activity.mas
-RUN

-WRITE MASTER FILENAME=ACTIVITY, SUFFIX=FIX
-WRITE MASTER SEGNAME=ACTIVITY
-WRITE MASTER FIELDNAME=ACT_ITEM,   FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=FILL1,      FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=ACT_ACTION, FORMAT=A6, ACTUAL=A6, $
-WRITE MASTER FIELDNAME=FILL2,      FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=ACT_DATA,   FORMAT=A6, ACTUAL=A6, $
-RUN

TABLE FILE ACTIVITY
PRINT
ACT_DATA
BY ACT_ITEM
BY ACT_ACTION
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

DEFINE FILE H001
CNT/I5 = IF ACT_ITEM EQ LAST ACT_ITEM AND ACT_ACTION EQ LAST ACT_ACTION THEN CNT + 1 ELSE 1;
ACT_ACTIONX/A11 = ACT_ACTION || EDIT(CNT);
END
-RUN

TABLE FILE H001
SUM
ACT_DATA
BY ACT_ITEM
ACROSS ACT_ACTIONX
END
-RUN

I have a several questions.

1. Are you keying the logic based on the first item in the group?
2. If the action is INSERT, do you print that record or not.
3. If the action is UPDATE and there are also OLDs in that item, do you print them too or just the second UPDATE.
4. Same as 3 except use OLD and first.

As for the logic, WebFOCUS in a DEFINE (which is where you would put all this logic) reads one record after another. In your case, it is imperative that the records are sorted by item on input. You can then tag each row for processing by doing IF-THEN-ELSE logic as Francis has demonstrated in his example. Note that he makes use of the word LAST which allows you to compare the contents of the current record with the previous one.

So, if you could give us a little more detail....

Answers to your questions:
1: The logic IS based on the the first ITEM in the group - as the interface creates the records in one of the following groupings for every ITEM:
a: Insert, Update, Old
b: Update, Old
c: Update, Old, Update

2: If the action is INSERT I don't want the INSERT record - just the Update and Old records associated with that ITEM

3: If the action is an UPDATE and there are just two records - an UPDATE and an OLD (meaning there isn't an INSERT record associated with this ITEM), I want both records (the UPDATE and the OLD) for that ITEM

4: If the action is an UPDATE and there are four records associated with the same ITEM (two UPDATES and two OLDS) I want the second UPDATE record and the first OLD record.

What happens is that the interface sometimes takes two passes on an item when it does and UPDATE - so the first UPDATE record will not have all of the new fields in it that were used to update the item and the second OLD record actually reflects the first pass of the update - so it really contains some fields that were just updated - and therefore isn't a true reflection of what existed before the interface ran against the file - it's a terribly kludgy interface.

Graysailor,

Have you tried to run my code? Take a look at the grid that's displayed - you will see INSERT00001 OLD00001 OLD00002 UPDATE00001 UPDATE00002 field names for every Item.

Put this in a HOLD file, then you can apply your selection criteria quite easily.

I've just run it and I'm reviewing the data. Thank you for such a fantastically quick response! This is why I love forum boards! At first glance it appears to have worked but it'll take me a minute to review.

Oh cool! I see what you've done now - this is going to work perfectly! What a great way to handle it. I had 'hovered' near this myself - my thought was to roll through all the records and assign each a number code based on the criteria and THEN do a select but this is a much cleaner method. Now I'll have to examine your code so I can make sure I know how it's working! Thanks a lot Francis (and you too Ginny) for your time - the University of Arkansas Medical Center also thanks you!

With many thanks to Francis for keying in the data, I cribbed his code and did it another way. His is much cleaner and please use it if it works for you. But this is an example to explain how the define logic works and what you can do with it. And as a side note, if you are doing this, make sure that you print your switches as you go with screening on 'SELSW' so that you can see if you are selecting or rejecting what you expect.

FILEDEF ACTIVITY DISK activity.txt
-RUN

-WRITE ACTIVITY 1 INSERT DSFSDF
-WRITE ACTIVITY 1 UPDATE FDSD
-WRITE ACTIVITY 1 OLD    GFFG
-WRITE ACTIVITY 2 UPDATE DSFF
-WRITE ACTIVITY 2 OLD    EFSDFD
-WRITE ACTIVITY 2 UPDATE DFFDS
-WRITE ACTIVITY 2 OLD    IUYUYI
-WRITE ACTIVITY 3 UPDATE WEWEEW
-WRITE ACTIVITY 3 OLD    KJK
-WRITE ACTIVITY 3 UPDATE WWREE
-WRITE ACTIVITY 3 OLD    JHJHJH
-WRITE ACTIVITY 4 UPDATE SDFSDV
-WRITE ACTIVITY 4 OLD    QWWQQD
-WRITE ACTIVITY 5 UPDATE VNVBV
-WRITE ACTIVITY 5 OLD    AASS
-WRITE ACTIVITY 6 INSERT SDFDSF
-WRITE ACTIVITY 6 UPDATE UOIUI
-WRITE ACTIVITY 6 OLD    SASEF

FILEDEF MASTER DISK activity.mas
-RUN

-WRITE MASTER FILENAME=ACTIVITY, SUFFIX=FIX
-WRITE MASTER SEGNAME=ACTIVITY
-WRITE MASTER FIELDNAME=ACT_ITEM,   FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=FILL1,      FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=ACT_ACTION, FORMAT=A6, ACTUAL=A6, $
-WRITE MASTER FIELDNAME=FILL2,      FORMAT=A1, ACTUAL=A1, $
-WRITE MASTER FIELDNAME=ACT_DATA,   FORMAT=A6, ACTUAL=A6, $
-RUN

SET ASNAMES=ON
TABLE FILE ACTIVITY
COUNT
ACT_DATA AS ACTS_CNT
BY ACT_ITEM
PRINT
ACT_ACTION
ACT_DATA
BY ACT_ITEM
-*BY ACT_ACTION
ON TABLE HOLD AS H001 FORMAT ALPHA
END

DEFINE FILE H001
INSSW/I1=IF ACT_ITEM NE LAST ACT_ITEM AND ACT_ACTION EQ 'INSERT' THEN 1 
    ELSE IF ACT_ITEM EQ LAST ACT_ITEM AND LAST INSSW EQ 1 THEN 1 ELSE 0;
INSSEL/I1=IF ACT_ITEM NE LAST ACT_ITEM AND INSSW EQ 1 THEN 0 
     ELSE IF ACT_ITEM EQ LAST ACT_ITEM AND INSSW EQ 1 THEN 1 ELSE 0;

UPDSW/I1=IF ACT_ITEM NE LAST ACT_ITEM AND ACT_ACTION EQ 'UPDATE' THEN 1 
    ELSE IF ACT_ITEM EQ LAST ACT_ITEM AND LAST UPDSW EQ 1 THEN 1 ELSE 0;
OLDSW/I1=IF ACT_ITEM EQ LAST ACT_ITEM AND ACT_ACTION EQ 'OLD' THEN OLDSW+1 
    ELSE IF ACT_ITEM NE LAST ACT_ITEM THEN 0
    ELSE 1;
UPDSEL/I1=IF UPDSW EQ 1 AND ACTS_CNT EQ 2 THEN 1
     ELSE IF UPDSW EQ 1 AND ACTS_CNT GT 2 AND OLDSW EQ 1 THEN 1
     ELSE 0;

SELSW/I1=IF INSSW EQ 1 THEN INSSEL ELSE IF UPDSW EQ 1 THEN UPDSEL ELSE 0;
END

TABLE FILE H001
PRINT ACTS_CNT
INSSW INSSEL UPDSW OLDSW UPDSEL SELSW
ACT_ACTION
ACT_DATA
BY ACT_ITEM
-* Comment this out while testing
WHERE SELSW EQ 1
END