Automatically Generate Upgrade Scripts

Snippet of PeopleCode that takes a Project and creates an upgrade script for all the Records in that Project. Does 90%+ of the work for you.

Requires a Prompt to select the Project, a button to put this code under, and a HTML Area to output the results to.

If None(NS_MN_TEST.PROJECTNAME.Value) Then
   MessageBox(0, "", 0, 0, "Enter a Project Name");
Else
   
   Local string &title, &result, &fields;
   Local integer &f;
   
   Local Record &recPSPROJECTDEFN = CreateRecord(Record.PSPROJECTDEFN);
   &recPSPROJECTDEFN.PROJECTNAME.Value = NS_MN_TEST.PROJECTNAME.Value;
   &recPSPROJECTDEFN.SelectByKey();
   
   Local Record &recPSOPRDEFN = CreateRecord(Record.PSOPRDEFN);
   &recPSOPRDEFN.OPRID.Value = &recPSPROJECTDEFN.LASTUPDOPRID.Value;
   &recPSOPRDEFN.SelectByKey();
   
   &title = &title | "/*" | Char(13);
   &title = &title | "     WORK UNIT:     " | &recPSPROJECTDEFN.PROJECTNAME.Value | Char(13);
   &title = &title | "     DEVELOPER:     " | &recPSOPRDEFN.OPRDEFNDESC.Value | Char(13);
   &title = &title | "     DATE:          " | String(%Date) | Char(13);
   &title = &title | "     DESCRIPTION:   " | &recPSPROJECTDEFN.PROJECTDESCR.Value | Char(13);
   &title = &title | "     INSTRUCTIONS:  EXECUTE IN THE TARGET" | Char(13);
   &title = &title | "     DEPENDENCIES:  " | Char(13);
   &title = &title | "*/" | Char(13) | Char(13);
   
   &title = &title | "-- List of Records that have data converted by this script:" | Char(13);
   
   
   Local SQL &sql = CreateSQL("SELECT OBJECTVALUE1 FROM PSPROJECTITEM A, PSRECDEFN B WHERE A.PROJECTNAME = :1 AND A.OBJECTTYPE = 0 AND A.OBJECTID2 = 0 AND B.RECNAME = A.OBJECTVALUE1 AND B.RECTYPE = 0", NS_MN_TEST.PROJECTNAME.Value);
   
   Local string &recName;
   While &sql.Fetch(&recName)
      
      If Left(&recName, 3) = "NS_" Then
         
         &title = &title | "--    " | &recName | Char(13);
         
         &result = &result | Char(13) | "-- " | &recName | Char(13);
         &result = &result | "Delete From ps_" | &recName | ";" | Char(13);
         Local Record &rec = CreateRecord(@("record." | &recName));
         &result = &result | "Insert Into ps_" | &recName | "(" | Char(13);
         For &f = 1 To &rec.FieldCount
            If &f = 1 Then
               &fields = "  " | &rec.GetField(&f).Name | Char(13);
            Else
               &fields = &fields | ", " | &rec.GetField(&f).Name | Char(13);
            End-If;
         End-For;
         &result = &result | &fields | ")" | Char(13);
         &result = &result | "Select " | Char(13) | &fields | "From ps_" | &recName | "@nsconv.unsw.edu.au;" | Char(13);
         
      End-If;
      
   End-While;
   
   &title = &title | Char(13) | "SPOOL ON;" | Char(13);
   &title = &title | "SET ECHO ON;" | Char(13);
   &title = &title | "SPOOL 'c:\temp\" | Substring(&recPSPROJECTDEFN.PROJECTNAME.Value, 4, 7) | "_data_conversion.log' REPLACE;" | Char(13);
   
   
   NS_MN_TEST.HTMLAREA1.Value = &title | Char(13) | Char(13) | &result | Char(13) | Char(13) | "commit;" | Char(13) | Char(13) | "SPOOL OFF;";
   
End-If;
Paste your text here.


Searching Through Views

If you make a change on a Record or View you should check its impact. App Designer doesn't search through SQL definitions but you can do that yourself in SQL:

select SQLID from PSSQLTEXTDEFN
where sqltext like '%SOME_TABLE%'

Posting CommGen to Report Manager

As delivered Communication Generation (CommGen) produces the final, merged output of letters on the Server. There is no way to configure it to send to Report Manager or Process Monitor.

However, there is an easy fix.

Each Process Instance has its own directory made at run-time, and all files in that directory are published to Process Monitor and Report Manager. We just need to make a simple tweak to create the Merged File Output in the process's Process Instance directory instead of the file destination directory as defined on the Run Control (by the way it would pay to modify the run control accordingly as well; hide the now unused File Output field etc.).

Application Package SCC_COMMGEN:Model:CommGen


/* This method merges all the communications that have been generated so far */
method MergeCommunications

...

   rem &sFileDestination = &rsRunCtlParms(1).SCC_CG_RUNCTL.FILE_PATH.Value;
   &sFileDestination = "log_output/AE_SCC_COMMGEN_" | &ProcessInstance;

Just change the one line, as above. This will have the merged file go to Report Manager and Process Monitor instead of a directory on the server. I suspect this issue may be a left over from version 7, where users could run on the client and have it appear on their local drive.