Introducing dialog for implementing custom validation fix using Database Consistency Checks feature

Hi,

In this post, we will see implementing custom validation fixes using a dialog when we run database consistency check feature.

• Created new tables SampleTable and SampleTrans and loaded some data into it.
• Developed a class by name CGConsistencyCheck and included a validation fix where if due date of record in table SampleTrans is latest when compared to the due date on its corresponding record of table SampleTable then info message will be shown with the corrected record details.
• Also added a dialog where it takes the due date as input value and operates on SampleTrans table records where the due date is later than this value.
• class CGSysConsistencyCheck methods
• Method - dialog
• Method - getFromDialog
• Method - hasDialog
• Method - pack and unpack
• Method - run 
  public void run()
      {
          SampleTable             sampleTable;
          SampleTrans             sampleTrans;
          Query                   query = new Query();
          QueryBuildDataSource    sampleTableDS, sampleTransDS;
          QueryRun                queryRun;
       
          sampleTableDS = query.addDataSource(tableNum(SampleTable));
          sampleTransDS = sampleTableDS.addDataSource(tableNum(SampleTrans));
          sampleTransDS.addLink(fieldNum(SampleTable, Id), fieldNum(SampleTrans, RefId));
          sampleTransDS.joinMode(JoinMode::InnerJoin);
          sampleTransDS.relations(true);
   
          if (dueDate != dateNull())
          {
              SysQuery::findOrCreateRange(sampleTransDS, fieldNum(SampleTrans, DueDate)).value(strFmt('(DueDate > %1)', Date2StrXpp(dueDate)));
          }       
   
          queryRun = new QueryRun(query);
          while (queryRun.next())
          {
              sampleTable = queryRun.get(tableNum(SampleTable));
              sampleTrans = queryRun.get(tableNum(SampleTrans));
   
              if (sampleTrans.DueDate > sampleTable.DueDate)
              {
                  if (this.checkFix() == CheckFix::Check)
                  {
                      checkFailed(strfmt("Due date on Transaction %1 exceeds due date when compared
  to due date on its respective header %2", sampleTrans.TransID, sampleTable.Id));
                      this.updateNotCorrected();
                  }
                 
                  if (this.checkFix() == CheckFix::Fix)
                  {
                      ttsbegin;
                      sampleTrans.selectForUpdate(true);
                      sampleTrans.DueDate = sampleTable.DueDate;
                      sampleTrans.update();
                      ttscommit;
   
                      info(strfmt("Due date on Transaction %1 matched with due date when compared to due date on its respective header %2", sampleTrans.TransID, sampleTable.Id));
   
                      this.updateCorrected();
                  }
              }
          }
      }
• Execute Consistency check by selecting the node Custom, Fix error as an option for Check/Fix dialog and select button dialog and set the due date value as "9/1/2019".   

Output: Info message Info message and Message details.

Regards,

Chaitanya Golla