sorting records by createdBy field

Hi Dave,

Thank you!

Your suggestion worked perfectly.

I've added some additional fields to the plugin. Here's what I ultimately came up with for anyone else that it might help:

<?php
/*
Plugin Name: Update createdBy_* fields
Description: Create custom createBy_ fields in a target table for sorting and searching.
Version: 1.00
CMS Version Required: 3.10

On record save - update ALL createBy_first_name fields with first_name values and ALL createBy_last_name fields with last_name values from accounts table

NOTE: A createBy_first_name text field and a createBy_last_name text field need to be created in the target table for this plugin to work (check capitalization)
 
NOTE: To add a third field to be updated to the plugin:

1) To the 'Set Variables' code, add
$sourceField3   = "your_field"; 
$fieldToUpdate3 = "createdBy_your_field";

2) To the 'Error Checking' code, add:
if (!isset($GLOBALS['schema'][$fieldToUpdate3])) { return; } // skip if field doesn't exist in target table

3) In the 'Update Variables' code, change the SET t. code to: 
SET t.`$fieldToUpdate` = a.`$sourceField`,
`$fieldToUpdate2` = a.`$sourceField2`,
`$fieldToUpdate3` = a.`$sourceField3` ";


4) Add a createdBy_your_field text field to your target table

*/ 

addAction('record_postsave', function($tableName) {
  
   $sourceField   = "first_name"; // from accounts table
   $fieldToUpdate = "createdBy_first_name";
   $sourceField2   = "last_name"; // from accounts table
   $fieldToUpdate2 = "createdBy_last_name";
 // Error Checking
 if (!isset($GLOBALS['schema'][$fieldToUpdate])) { return; } // skip if field doesn't exist in current table
 if (!isset($GLOBALS['schema'][$fieldToUpdate2])) { return; } // skip if field doesn't exist in current table
 
//Update Values
 $tablePrefix   = $GLOBALS['TABLE_PREFIX'];
  
  $query = "UPDATE {$tablePrefix}$tableName t
         LEFT JOIN {$tablePrefix}accounts a ON t.createdByUserNum = a.num
               SET t.`$fieldToUpdate` = a.`$sourceField`,
              `$fieldToUpdate2` = a.`$sourceField2`";
               
  mysql_do($query);
});

Best,

Jerry Kornbluth