I've Moved

This blog has moved.

In light of Capital IT finishing up, and no more BAS to do, I've moved this blog to:

ThePeopleSoftExperience.wordpress.com

Looks nicer and the search seems to work better too. This blog will stay around indefinitely but it won't be getting any new posts.

Programmer Interrupted

http://i.imgur.com/3uyRWGJ.jpg

Component Field Find & Replace

Sometimes you just want to find a field and replace its value no matter where it is in the Component.

class General;
   
   method ComponentFieldFindReplace(&rs As Rowset, &fieldName As string, &find As string, &replace As string)
end-class;



/* Searches *entire* Component Buffer for a given Field Name, and optional value, 
   and replaces with the given value. 

   &rs:        The RowSet that the search will begin at, and cascade down from. Generally use GetLevel0().
   &fieldName: The name of the Field whose value you want to replace.
   &find:      (optional) The value to match
   &replace:   The value to update the Field.Value with.

   Example: &clsGPUtilities.ComponentFieldFindReplace(GetLevel0(), "YOUR_FIELD", "", "X");
*/
method ComponentFieldFindReplace
   /+ &rs as Rowset, +/
   /+ &fieldName as String, +/
   /+ &find as String, +/
   /+ &replace as String +/
   
   Local integer &i, &r, &f, &c;
   
   For &i = 1 To &rs.RowCount
      
      For &r = 1 To &rs(&i).RecordCount
         For &f = 1 To &rs(&i).GetRecord(&r).FieldCount
            If &rs(&i).GetRecord(&r).GetField(&f).Name = &fieldName Then
               If &find = "" Or
                     &find = &rs(&i).GetRecord(&r).GetField(&f).Value Then
                  &rs(&i).GetRecord(&r).GetField(&f).Value = &replace;
               End-If;
            End-If;
         End-For;
      End-For;
      
      /* Do same for each child RowSet of this Row. */
      For &c = 1 To &rs(&i).ChildCount
         %This.ComponentFieldFindReplace(&rs(&i).GetRowset(&c), &fieldName, &find, &replace);
      End-For;
      
   End-For;
   
end-method;

Tetris SQL

This is a DB2 solution. Oracle will be different for the 'daterange' WITH clause (subquery factoring)

-- Two step process:
-- Delete rows in Target that don't exist in Results, 
-- Insert rows from Result that don't exist in Target.

-- Tricky bit: 
-- Don't overwrite any manually added rows in the Target.
-- Instead block *around* them; Tetris.


-- DELETE
delete from TARGET_TBL d
where d.source = 'ee'
and 
    (processing_range.start_dt between d.start_dt and d.end_dt
    or processing_range.end_dt between d.start_dt and d.end_dt
    or d.start_dt between processing_range.start_dt and processing_range.end_dt)
and not exists
    (select 1
    from RESULTS_TBL a
    where a.pin_num = d.pin_num
    and a.start_dt = d.start_Dt
    and a.end_dt = d.end_dt)



-- INSERT
with daterange(level,dt) as (
    select 1, current date - 1 year
    from sysibm.sysdummy1
    union all select level + 1, dt + 1 day
    from daterange
    where level < 1000 and dt < current date + 1 year
)

select '1. Result' as thing, pin_num, start_dt, end_dt, 'ee' as source from RESULTS_TBL
union
select '2. Existing' as thing, pin_num, start_dt, end_dt, source from TARGET_TBL

union
select 
  '3. Answer' as thing
, a.pin_num

-- For PI/EA
, greatest(a.start_dt, 
    (select coalesce(max(s.end_dt) + 1 day, a.start_dt)
    from TARGET_TBL s
    where s.pin_num = a.pin_num
    and s.end_dt between a.start_dt and d.dt)) as start_dt

-- For PI/EA
, least(a.end_dt, 
    (select coalesce(min(s.start_dt) - 1 day, a.end_dt)
    from TARGET_TBL s
    where s.pin_num = a.pin_num
    and s.start_dt between d.dt and s.end_dt)) as end_dt

, 'ee' as source
from 
  RESULTS_TBL a
, daterange d
where d.dt between a.start_dt and a.end_dt
and not exists
    (select 1
    from TARGET_TBL x
    where x.pin_num = a.pin_num
    and d.dt between x.start_dt and x.end_dt)

Application Engine Log File

Handy little library to open Application Engine log files, based on whether running on client while developing, or on server.

class FuncLib
   method isTestingMode(&iProcessInst As integer) Returns boolean;
   method GetFile(&bTesting As boolean, &sFilename As string, &sMode As string) Returns File;
end-class;


/**********************************************************************
 * Returns whether app engine is in test mode (running locally).      *
 * -------------------------------------------------------------------*
 * Parameters  : &numProcessInst (process instance)               *
 * Returns   : True if running locally, otherwise false.              *
 **********************************************************************/
method isTestingMode
   /+ &iProcessInst as Integer +/
   /+ Returns Boolean +/
   Local boolean &bExists, &bTestMode;
   
   /* Check if process instance exists in a process scheduler table */
   SQLExec("SELECT 1 FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", &iProcessInst, &bExists);
   
   If Not &bExists Then
      /* If not, then its running in test mode. */
      &bTestMode = True;
   End-If;
   
   Return &bTestMode;
end-method;


/**********************************************************************
 * This method creates/opens a log file when running an app engine    *
 * in either 2 (testing) or n tier mode.                              *
 * -------------------------------------------------------------------*
 * Parameters                                                         *
 *   Parm 1:  Boolean - Testing mode?                                 *
 *   Parm 2:  File name                                               *
 *   Parm 3:  Mode: "W" will create a new file for writing,           *
 *                       "A" will append to an existing file.         *
 * Returns : File                                                     *
 **********************************************************************/
method GetFile
   /+ &bTesting as Boolean, +/
   /+ &sFilename as String, +/
   /+ &sMode as String +/
   /+ Returns File +/
   
   Local string &sPath;
   Local File &fFile;
   
   If &bTesting Then
      &sPath = "C:\TEMP\";
      &fFile = GetFile(&sPath | &sFilename, &sMode, %FilePath_Absolute);
   Else
      &sPath = "";
      &fFile = GetFile(&sFilename, &sMode);
   End-If;
   
   If &sMode = "W" Then
      MessageBox(0, "", 0, 0, "Creating file:  %1", &sPath | &sFilename);
   End-If;
   
   Return &fFile
end-method;

And to use:

import YOUR_PACKAGE:Utilities:FuncLib:*;

Component File &logFile;

&cbTestingMode = (create YOUR_PACKAGE:Utilities:FuncLib()).isTestingMode(YOUR_STATE_RECORD_AET.PROCESS_INSTANCE);
&logFile = (create YOUR_PACKAGE:Utilities:FuncLib()).GetFile(&cbTestingMode, "log.txt", "W");

PeopleCode to get a Sibling RowSet

For some reason this just took me far too long to figure out. Must be getting old. Writing down for reference.

Let's say you have a page with a Record structure like this:

Parent
   Sibling 1
   Sibling 2

And you want some code/event on Sibling 1 to change the data in Sibling 2.

The somewhat unusual scenario I had is that Sibling 2 is a kind of audit record for Sibling 1, so that every time data changed on Sibling 1 a new row was inserted into Sibling 2 with Oprid and DateTime etc.

Normally you could just have Sibling 2 as a child of Sibling 1 (and technically it is) but in my scenario Sibling 1 was already at Level 3. The data  was to be displayed. I had no room to move.


Place the following in something like SIBLING_1.Field1.SaveEdit()

Local Rowset &rsSIBLING_2 = GetRowset().ParentRow.GetRowset(Scroll.SIBLING_2);

You'll now have the sibling Record as a RowSet.

.ParentRow is the property of the RowSet you are looking for.

Make a Field Required using PeopleCode

Simple example of controlling whether a Field is required using PeopleCode instead of the Record definition properties.

Place this in the Record Field's SaveEdit PeopleCode.

If None(GetField().Value) Then
   GetField().SetCursorPos(%Page);
   GetField().Style = "PSERROR";
   Error MsgGet(15, 30, "Highlighted fields are required.");
Else
   GetField().Style = "PSEDITBOX";
End-If;