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

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

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

Hi Jerry, 

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

Dave Edis - Senior Developer
interactivetools.com