Updating other records during Save

[laa]

Progress version 9.1C

I have a question about how to best handle something using ADM2. What I have is a SmartWindow with a SDO, SDV and SDB on it. When I save a record, I need to update other records from the same table. Should I do a submitRow in one of the *TransactionValidate procedures in the SDO? If so, which one? If not, then where?

I would appreciate any help that you could provide!

Anne

 

[Norman Biggar]

I would suggest putting your additional code into the endTransactionValidate procedure of your SDO

In general

preTV - Data needs to be verified against data before processing
Example - Verify that an item entered in an orderline exists in the item table

beginTV - Data in another table in the database needs to be tightly linked with the update
Example - Lock order record while orderlines for that order are updated

endTV - Data needs to refer to updated records to make modifications elsewhere in the database
Example - Update Customer balance based on orders just processed

postTV - Some action needs to be taken on completion of the update but does not require the transaction itself
Example - Send an e-mail on completion of the update

 

[laa]

I was pretty much thinking that my logic should be in the EndTransactionValidate procedure. That being said, I am getting some unexpected results. When I add a record that causes a second record to be updated, both of these things are happening but a second record is also being created. (e.g. A record exists for Contract #1 (revision 0) which expires on 12/31. A second record is created for Contract #1 (revision 1) which is effective on 2/7. This should update the revision 0 record and change its expiry date to 2/6. This happens but while it is happening another copy of the revision 1 record is created (revision 2).)

This is my logic in the endTransactionValidate procedure:

FOR EACH RowObjUpd WHERE RowObjUpd.RowMod NE "D":U
AND LOOKUP("EffectiveDate":U,RowObjUpd.ChangedFields) NE 0
NO-LOCK:
FIND FIRST rowObject WHERE rowObject.ContNumber EQ RowObjUpd.ContNumber
AND rowObject.RevNumber EQ RowObjUpd.RevNumber - 1
NO-LOCK NO-ERROR.
IF AVAILABLE rowObject THEN DO:
IF rowObject.EffectiveDate GE rowObjUpd.EffectiveDate THEN DO:
RUN addMessage("Effective Date falls before previous revision",
"EffectiveDate":U,
"SQHeader":U).
RETURN.
END.
DYNAMIC-FUNCTION("submitRow":U,
rowObject.RowIdent,
"ExpiryDate" + CHR(1) + STRING(RowObjUpd.EffectiveDate - 1)).
END.
END.

Does this make sense?

Anne

 

[laa]

And in a completely unrelated topic -- Why do messages at ProgressTalk get stripped on leading spaces? I went through the bother of formatting my code sample to be readable and then the message stripped out all of the spacing.

Anne

 

[Norman Biggar]

Originally posted by laa
And in a completely unrelated topic -- Why do messages at ProgressTalk get stripped on leading spaces? I went through the bother of formatting my code sample to be readable and then the message stripped out all of the spacing.

Anne

Try using the code option (# symbol on the menu)

Here is a line.
    This one's indented.

 

[laa]

Thanks for the pointer. Here's another attempt at the code then:

[size=1]FOR EACH RowObjUpd WHERE RowObjUpd.RowMod NE "D":U
                                    AND LOOKUP("EffectiveDate":U,RowObjUpd.ChangedFields)[/size]
[size=1]                                                    NE 0[/size]
[size=1]     NO-LOCK:
  FIND FIRST rowObject WHERE rowObject.ContNumber EQ RowObjUpd.ContNumber
                                     AND rowObject.RevNumber EQ RowObjUpd.RevNumber - 1
      NO-LOCK NO-ERROR.
  IF AVAILABLE rowObject THEN DO:
    IF rowObject.EffectiveDate GE rowObjUpd.EffectiveDate THEN DO:
      RUN addMessage("Effective Date falls before previous revision",
                     "EffectiveDate":U,
                     "SQHeader":U).
      RETURN.
    END.
    DYNAMIC-FUNCTION("submitRow":U,
                     rowObject.RowIdent,
                     "ExpiryDate" + CHR(1) + STRING(RowObjUpd.EffectiveDate - 1)).
  END.
END.[/size]

 

[Norman Biggar]

If you run without the endTransactionValidate code, do you still get the extra record? i.e. Contract 1 Revision 2

I can't see anywhere in your sample code that would increment the revision number.

 

[laa]

If I run the logic without the endTransactionValidate procedure I do not receive the duplicate record. This only started happening since I added the logic to check the Effective Date.

As far as the incrementing of the Revision Number goes, this happens in the preTransactionValidate procedure:

[font=Courier New][size=1]FOR EACH RowObjUpd WHERE LOOKUP(RowObjUpd.RowMod, "A,C":U) NE 0:
  FIND LAST SQHeader WHERE SQHeader.SQNumber EQ RowObjUpd.SQNumber
      NO-LOCK NO-ERROR.
  RowObjUpd.RevNumber = IF NOT AVAILABLE SQHeader THEN 0
                        ELSE SQHeader.RevNumber + 1.
END.
[/size][/font]