sorting records by createdBy field

7 posts by 2 authors in: Forums > CMS Builder
Last Post: November 24, 2017   (RSS)

By gkornbluth - November 6, 2017 - edited: November 15, 2017

Hi all,

I’m trying to sort the records in a multi-record table that does not contain a last_name field (submissions) by the createdBy.last_name field in the account record of the person who created that record.

Although I can echo the createdBy.last_name in the body of the viewer, when I try to use 'orderBy' => 'createdBy.last_name ASC', in the load records call, that doesn’t work.

Any suggestions?

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Dave - November 14, 2017

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.

Dave Edis - Senior Developer

interactivetools.com

By Dave - November 22, 2017

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.

Dave Edis - Senior Developer

interactivetools.com

By gkornbluth - November 22, 2017 - edited: November 22, 2017

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Dave - November 23, 2017

Hi Jerry, 

Yes, that's right.  Let me know how it goes! 

Dave Edis - Senior Developer

interactivetools.com

By gkornbluth - November 24, 2017 - edited: November 24, 2017

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php