Write the contents of a grid to a database table

From pmusers
Revision as of 04:51, 3 April 2019 by Amosbatto (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

This trigger example shows how to use executeQuery() to save the contents of a grid to a database table.

For example, there is a PM Table named "CLIENTS" with the following fields:

  • ID (integer(16), auto-increment, primary key),
  • CASE_NUMBER (integer(12)),
  • CLIENT_NAME (varchar),
  • NUMBER_CONTRACTS (integer(8)),
  • CONTRACT_AMOUNT (decimal)
  • BIRTHDAY (datetime)

Users will run create cases which enter a list of clients into a grid associated with a variable named "clientsList". This grid contains the following fields:

  • Text with ID "clientName",
  • Text with ID "numberContracts",
  • Text with ID "contractAmount",
  • Datetime with ID "birthday"

A trigger with the following code can be used to write the rows in the grid to the PMT_CLIENTS table:

//check if the grid variable was set if the user might have skipped the Dynaform holding the grid:  
if (isset(@=clientsList) and is_array(@=clientsList)) {
   //Delete all existing records in the database table for the current case and 
   //then write the new values from the grid to the database table:
   $caseNo = @@APP_NUMBER;
   //If writing to a PM Table, then remember to prepend "PMT_":
   executeQuery("DELETE FROM PMT_CLIENTS WHERE CASE_NUMBER=$caseNo"); 

   //Write each row in grid as a new record in the database table
   foreach (@=clientsList as $aRow) {
      //Remember to use addslashes() for security when writing to char, date, datetime fields
      //When writing to integer or decimal fields, remember convert with (int) and (float) for security 
      $client     = addslashes($aRow['clientName']);
      $nContracts = (int) $aRow['numberContracts'];
      $amount     = (float) $aRow['contractAmount'];
      birthday    = addslashes($aRow['birthday']);

      //char, date, datatime fields are strings and should be enclosed in '...'
      $sql = "INSERT INTO PMT_CLIENTS 
         (CASE_NUMBER, CLIENT_NAME, NUMBER_CONTRACTS, CONTRACT_AMOUNT, BIRTHDAY)
         VALUES ($caseNo, '$client', $nContracts, $amount, '$birthday')";
      //Note that it isn't necessary to include the ID field, because it is auto-increment
      //and the database will automatically add it when inserting a new row 
      executeQuery($sql);
   }
}

Set this trigger to fire after the Dynaform.