sorting records by createdBy field

Hi Jerry, 

There's no easy way to do that.  One solution we've done in the past was to have a plugin that would set the value of a second field, eg: lastname and then sort on that.

Thank Dave,

Is there a sample plugin that you could share?

Creating plugins is still a mystery to me.

Best,

Jerry Kornbluth

Hi Jerry, 

I found one, but it didn't work for createdBy fields.  Try this: 

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

// On record save - update ALL createBy_username fields with username value from accounts
addAction('record_postsave', function($tableName) {
  $fieldToUpdate = "createdBy_username";
  $sourceField   = "username"; // from accounts table
  if (!isset($GLOBALS['schema'][$fieldToUpdate])) { return; } // skip if field doesn't exist in current table
  $tablePrefix   = $GLOBALS['TABLE_PREFIX'];
  
  $query = "UPDATE {$tablePrefix}$tableName t
         LEFT JOIN {$tablePrefix}accounts a ON t.createdByUserNum = a.num
               SET t.`$fieldToUpdate` = a.`$sourceField`";
  mysql_do($query);
});

Let me know if that works for you.

Thanks again Dave for keeping up with this.

So does this mean that in order for the plugin to work I need to create a text field called createBy_username in the table that I'm trying to sort, and then sorting on that new field?

After  I've tried it, I'll try to change the field that is called, and see if I can use the last name field in accounts.

Best,

Jerry

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