Monday, August 6, 2007

Adding new fields... to an existing record or a new one?

At the very start of every PeopleSoft project we're confronted to the task of defining the Development Standards. One of the recurring discussion points is what to do when we need a new field in a record.

a) Create a sibling record (same primary key), adding the new field as an attribute to that record.

b) Add the new field in the original record.

At first, the a) option seems the cleanest one as it does not modify standard records and it sounds like it will easier to maintain through upgrades and fixes.

However, this option also has its limitations:
  • Querying the table now would involve joining two tables. This leads to additional complexity and performance issues (particularly if customizations have been made by adding sibling records to multiple records in a query).
  • When creating a query, you may need to filter on both the standard record and the sibling one. In that case, you won't be able to use an index on both filters, as the fields are placed in different tables.
  • Adding a sibling records means you need to use an additional scroll area in components showing both the original record and the standard one. In some situations, this may put in a situation where the 4 allowed scroll levels (0, and 1 to 3) are not enough.
Mainly due to performance reasons, my preferred option is b). In order to keep a clearer track for upgrades and fixes, what we normally do is to place new fields in a subrecord defined explicitly for customized fields. In such way, it is always easy to identify the customized fields in a record.

What is your experience on record customization? Which of the two options you prefer?
a la/s

12 comments:

  1. AnonymousNovember 11, 2008 at 4:22 AM

    Well said.

    ReplyDelete
  2. Jim MarionJuly 5, 2009 at 5:14 PM

    @Javier, I can certainly understand your perspective. I agree that the sibling record pattern adds to the complexity of the data structures. I think it is important to point out, however, that modifying delivered records may have an impact on Cobol stored statements, SQR's, and AppEngine SQL. Consider the case where a modified record is used in "INSERT INTO" SQL statements in Cobol, SQR's, or AppEngine SQL statements. Adding a field to this record will also require updating Cobol stored statements, SQR's, or AppEngine SQL statements.

    When using option b, it is critical that the developer conduct a thorough analysis of the record definition's usage. Option a does not require this level of analysis. Option a is safer from this perspective.

    Either approach is acceptable. A developer (or architect) must way the benefits and costs of each alternative.

    Besides the two alternatives you present, I also advocate a modified version of the sibling pattern. Rather than use another scroll (to avoid having 2 data records at the same level), I actually use a Derived/Work record and PeopleCode to place data at the same level as the delivered record. I outline this technique in a book I'm writing that will hit the market sometime in 2010 (I'll post details on my blog). Of course, this adds complexity to the design and maintenance of the component from a developer's perspective, but from a user's perspective, it may produce a result that more closely matches the user's design.

    I'm just writing up some documentation on the "sibling" pattern and thought it might be helpful to add some justification for using the "sibling" patterns.

    There are a lot of customers that are modification shy for fear of upgrade rework. I would rather see a happy and productive user than a fearful developer. Failure to customize applications generally leads to dissatisfaction among users and developers alike.

    Thank you for sharing your thoughts and pushing for customizations that make users more productive.

    ReplyDelete
  3. FredMarch 12, 2010 at 6:25 PM

    I usually go with the custom sibling route mostly to avoid the possibility of SQL errors from objects within and outside of Peoplesoft that reference the delivered table.

    In reading Jim's response regarding the modified version of the sibling pattern.... I was wondering if it took into account Correction mode.

    In other words, when using a derived field and peoplecode to sync values with the custom sibiling table that contains EFFDT, do you have a clean way make use of the delivered js correction functionality that stops a user from changing historic data?

    I went this route before and added peoplecode functionality to mimmick the correction error if the user tried to change historic data within that derived field but it seemed like it could be done by tricking the system.

    Thoughts?

    ReplyDelete
  4. Jim MarionMarch 13, 2010 at 7:04 PM

    @Fred, good point about correction mode. If you are modifying a component, then you will likely know the business logic around that component and correction mode, etc. Then you can use the PeopleCode system variable %Mode to determine if you are in correct history mode, and then respond accordingly.

    Actually, now that I think about it Fred, yes, the "modified sibling" approach would consider correct history because I don't actually write data to the database until SavePostChange. As you know SavePostChange doesn't happen until all edits are complete. Therefore, it would not be possible to execute SavePostChange from a component if a user tries to edit a history row in UpdateDisplay mode.

    ReplyDelete
  5. Javier DelgadoMarch 29, 2010 at 8:57 AM

    Thanks Jim & Fred for your feedback. I found Jim derived/work approach quite interesting, as it's a design pattern I haven't explored before.

    ReplyDelete
  6. AnonymousJune 23, 2010 at 10:14 PM

    @Jim, I like your approach but I'm wondering about what this does to Record level auditing. If you wanted to Audit these new custom fields would you have to manually do so in the PostSave peoplecode? Or is there a way for the normal Tools auditing to work?

    ReplyDelete
  7. AnonymousJuly 22, 2010 at 5:33 AM

    When using the derived/work record approach, what is the best way to handle row deletes? The RowDelete event doesn't offer you the ability to SQL delete the data from the physical table. Just curious if there are any suggestions for this...

    ReplyDelete
  8. AnonymousJuly 22, 2010 at 9:05 PM

    In the PostSaveChange for the row I would do something like this:

    If GetRow().IsDeleted Then
    /* Do Your Delete Stuff */
    &sibRecord.Delete();
    End-if;

    ReplyDelete
  9. AnonymousFebruary 24, 2016 at 5:48 AM

    Hi all,

    Can i insert one new field to 20 existing records at a time in App designer,Peoplecode ?. Let me know the ans if any one have idea on that. TIA

    ReplyDelete
  10. UnknownFebruary 24, 2016 at 8:50 AM

    Yes, sure. You can use the Bulk Operations functionality in Application Designer:

    Tools > Bulk Operations > Insert > Field into Records

    I hope this helps.

    ReplyDelete
  11. AnonymousFebruary 25, 2016 at 2:59 PM

    Hai Javier Delgado..... Thanks for your help.I am using 8.48 Peopletool version But the option BULK OPERATION is not there in 8.48 peopletools version. Can you please tell me the navigation in 8.48 version?????. TIA.

    ReplyDelete
  12. UnknownFebruary 25, 2016 at 3:27 PM

    Hi

    It should be there, as it's available since release 8.40. Please note that your user needs to be associated to the System Administrator role in order to have access to this menu.

    Thanks

    ReplyDelete