Iterating Through a Connected Query Opened for Editing

This example demonstrates usage of the connected query ObjectRowset property. The program opens an instance from an existing connected query definition. The program retrieves the rowset from the ObjectRowset property, and changes some of its properties. It saves the rowset back to ObjectRowset property, and then saves the modified connected query definition back to the database.

import PT_CONQRS:CONQRSMGR;

Local PT_CONQRS:CONQRSMGR &cConQrsInst;
Local string &sOprId, &sCONQRSNAME, &str, &sQryParentName, &sQryChildName, ⇒
&sFldNamePar, &sFldNameChild;
Local Rowset &rsObjectRowset, &rsObjectFields;
Local Record &rMapObjRec, &rFldObjRec;
Local number &i, &j, &seqNum, &fldNum;

Function ShowException(&errString As Exception);
End-Function;

/* While working with connected queries, it is recommended to use a try- */
/* catch block                                                           */

try
   /* Create and open an object. System tries to find a connected query  */
   /* with private ownership first for current user. If not found, it    */
   /* uses a public ownership                                            */
   &sCONQRSNAME = "MyConQuery";
   &cConQrsInst = create PT_CONQRS:CONQRSMGR("", &sCONQRSNAME);
   &res = &cConQrsInst.Open(&cConQrsInst.Const.InitExisting);
   &str = &cConQrsInst.ErrString;
   If &str <> "" Then
      Error &cConQrsInst.ErrString;
   End-If;
   /* Validate the previously opened connected query object              */
   If Not &cConQrsInst.Validate() Then
      &str = &cConQrsInst.ErrString;
      If &str <> "" Then
         Error &cConQrsInst.ErrString;
      End-If;
   End-If;
   /* Rowset retrieved from a connected query has the following          */
   /* structure: ObjectRowset = CreateRowset(Record.PSCONQRSMAPVW,       */
   /* CreateRowset(Record.PSCONQRSFLDRLVW));                             */
   &rsObjectRowset = &cConQrsInst.ObjectRowset;
   /* The rowset contains the internal connected query structure. The    */
   /* user can iterate the rowset, retrieve and modify member query      */
   /* names, list the related fields, list object properties, etc.       */
   For &i = 1 To &rsObjectRowset.ActiveRowCount
      &rMapObjRec = &rsObjectRowset(&i).PSCONQRSMAPVW;
      &sQryParentName = &rMapRec.GetField(Field.QRYNAMEPARENT).Value;
      &sQryChildName = &rMapRec.GetField(Field.QRYNAMECHILD).Value;
      &seqNum = &rsObjectRowset(&i).PSCONQRSMAPVW.SEQNUM.Value;
      &rsObjectFields = &rsObjectRowset(&i).GetRowset(Scroll.PSCONQRSFLDRLVW);
      For &j = 1 To &rsObjectFields.ActiveRowCount
         &rFldObjRec = &rsObjectFields(&j).PSCONQRSFLDRLVW;
         &fldNum = &rFldObjRec.SELCOUNT.Value;
         &sFldNamePar = &rFldObjRec.QRYFLDNAMEPAR.Value;
         &sFldNameChild = &rFldObjRec.QRYFLDNAMECHILD.Value;;
      End-For;
   End-For;
   /* The preceding for loop shows how to navigate a connected query's   */
   /* internal structure. It allows a report developer to change a       */
   /* connected query definition by modifying existing query members or  */
   /* by adding new ones -- queries or related fields.                   */
   /* Need to set the connected query's rowset to the retrieved rowset   */
   /* after any changes are made to the retrieved rowset.                */
   &cConQrsInst.ObjectRowset = &rsObjectRowset;

   &Errstr = &cConQrsInst.ErrString;
   If &Errstr <> "" Then
      Error &cConQrsInst.ErrString;
   End-If;
   /* Set the connected query properties. Set the object's status to     */
   /* active, so it will be available for reporting use                  */
   &cConQrsInst.Description = "My Connected Query";
   &cConQrsInst.Details = "Includes QryA, QryA1, and QryC";
   &cConQrsInst.Status = "A";
   /* Save the connected query instance                                  */
   If Not &cConQrsInst.Save() Then
      If &cConQrsInst.ErrString <> "" Then
         Error &cConQrsInst.ErrString;
      End-If;
   End-If;
catch Exception &Err
   ShowException(&Err);
end-try;