The CMSB Cookbook

AUTOMATICALLY POPULATING FIELDS IN A TARGET TABLE USING THE CREATEDBY FUNCTIONALITY IN CMSB - Dec 9th, 2018


The createdBy functionality built into CMSB allows you to echo field values from the account record of the person who
created a record in another table.

I was trying to use that createdBy functionality to sort records by last name in a multi-record table that does not
contain a last_name field field.

When I tried to sort the records in the table using  'orderBy' => 'createdBy.last_name ASC', in the load records call,
that didn’t work.

Thankfully, Dave Edis came to the rescue once again.

He pointed out that although you can’t use the createdBy functionality for sorting directly, you can use a plugin to
populate real fields in your target table from those createBy values and then use the actual field data to sort your
records.

Based on what he suggested, here's the plugin that we came up with:

<?php

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);
});

The materials on this web site have been created for use with CMS Builder content management software. CMS Builder software is published and licensed for use by InteractiveTools.com. Please contact Interactive Tools for information on the downloading of the software or the purchasing of licenses.