Login | Sign up | Toll-Free: 1-800-752-0455
 
 

Forum

 

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

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

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 gkornbluth - November 15, 2017 - edited: November 15, 2017

Thank Dave,

Is there a sample plugin that you could share?

Creating plugins is still a mystery to me.

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

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

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