Linked tables in CMSB

4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 15, 2010   (RSS)

By garyhoffmann - March 15, 2010

Hello,
I'm wondering if something I would like to be able to do can be done from within CMSB.

I would like to create linked tables. I would need this both at the CMSB admin level and the website display level.

What I mean can best be described by an example.

Assume I have a company that specializes in trainings. For these trainings, they have trainers. Each training may be able to be done on multiple dates.

What I would like to be able to do is create a table for the trainer (TrainerTable).

1. Bob Smith - Bob's bio...
2. Gary Anderson - Gary's bio...
3. Fred Jones - Fred's bio...

Then, I'd like to have a table for the course descriptions (CourseDescriptions):

1. Course 1 - Course description...
2. Course 2 - Course description...
3. Course 3 - Course description...

Finally, I'd like to have a table that ties these together by date (OfferedClasses):

1. June 10th, Course 1, Trainer 2
2. June 15th, Course 2, Trainer 1
3. July 1st, Course 3, Trainer 3
4. July 12th, Course 2, Trainer 2
5. July 27th, Course 1, Trainer 3
6. August 5th, Course 3, Trainer 1

So, while maintaining the OfferedClasses table, I would like the admin to have a drop-down list of Courses and Trainers while creating the offered classes.

Then, on the site, I would want to display the OfferedClasses in a table fashion with links from the Course name to the Course Description and from the Trainer name to the Trainer's bio.

Is this possible in CMS Builder without custom code? I know I can do anything at the website level I need with custom code, it's more the admin part that I wonder about.

Thanks in advance.
Gary.

Re: [garyhoffmann] Linked tables in CMSB

By gkornbluth - March 15, 2010

Hi Gary,

This is by far not a complete solution, but you can easily create drop down lists with data pulled from other tables.

The easiest way might be to set up your drop down lists is to use the advanced "get options from database" function when creating your list field(s).

You get to choose the source table and fields for each list from your existing tables (so you'd have to create the multi-record "Trainers" and "Descriptions" tables first).

When the admin creates a record in the "offered classes" table, he or she would then choose the appropriate values from the lists. Those values would be from the records in the other tables.

You'd then echo those "list" fields in your viewer as required.

If you need more info, let me know.

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

Re: [garyhoffmann] Linked tables in CMSB

By gkornbluth - March 15, 2010

If you're going to need to insert multiple fields from the selected record you might want to look at this excerpt from the latest version of my CMSB Cookbook www.thecmsbcookbook.com

It's based on a long thread and a plugin that was developed by Chris from Interactive tools.

POPULATING PAGES FROM A MASTER “ADDRESS BOOK” OR “PRODUCT LIST”
Let’s say you have a number of address book entries that contain things like name, address, phone, travel directions, map URL, etc. Or you have a number of products with detailed information about them.

Instead of retyping all of your information each time you want to display it on a detail page, you can pull that information directly from your master information list. Chris from Interactive Tools created a “relatedRecordLookupFunctions” plugin to add the required functionality this and suggested the following approach, I modified the table and field names for this “Address Book” example:

FOR MULTI-VALUE LIST FIELDS (THE CODE REQUIRED FOR SINGLE VALUE LIST FIELDS FOLOWS)

Step 1: Set up your address book section. for this example call it “Venue Address Book”.

This is a standard multi-record section with the necessary information fields. Since this example is an address book of venues for an events listing, I’ve set up fields for Venue Name, Venue Address, Venue Phone, Venue Contact E-mail, Venue URL, Venue URL link text (I like to keep these separate to add flexibility for my clients but I set the default text to WEB SITE or CLICK HERE FOR WEB SITE), Venue Travel Directions, and Venue Map URL (either from Mapquest or Google maps).

Step 2: In the section where you want this information to appear you’ll add a list type field called “Venue” with the following parameters.

Field Label: Venue
Field Name: venue
Field Type: list

Field Options:
Display As: pulldown or checkbox (multi-value)
List Options: Get options from database (advanced)
Section Tablename: venue_address_book
Use this field for option values: num
Use this field for option labels: venue_name


Step 3: Install the Related Record Lookup Functions Plugin which you can download from:

www.thecmsbcookbook.com/downloads/relatedRecordLookupFunctions.php

Upload the file to your server in the /cmsAdmin/plugins directory, then log into CMSB, go to Admin > Plugins, and click Activate on it.

Step 3: To set up your viewer to display the fields required you'll need to add some code to the top of your page. You probably already have the code in black in your PHP source code. Just add the code in red:

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php

require_once "/your_path_to/cmsAdmin/lib/viewer_functions.php";

list($e_blast_events_noticeRecords, $e_blast_events_noticeMetaData) = getRecords(array(
'tableName' => 'e_blast_events_notice',
'where' => whereRecordNumberInUrl(1),
'limit' => '1',
));


beta_lookupRelatedFields(array(
'table' => 'e_blast_events_notice',
'recordList' => &$e_blast_events_noticeRecords,
'fieldList' => array( 'venue' )

));
$e_blast_events_noticeRecord = @$e_blast_events_noticeRecords[0]; // get first record

// show error message if no matching record is found
if (!$e_blast_events_noticeRecord) {
header("HTTP/1.0 404 Not Found");
print "Record not found!";
exit;
}



?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

Step 4: Then in the body, where you want to display your address book fields:

<?php if (empty($e_blast_events_noticeRecord['venue'])): ?>
No Venue Information is available.
<?php else: ?>
<div align="left"> Venue Information: <br />

<?php foreach ($e_blast_events_noticeRecord['venue'] as $venue): ?>

<?php echo $venue['venue_name'] ?><br />
<?php echo $venue['venue_address'] ?><br />
<?php echo $venue['venue_contact_e_mail'] ?> <br />
<?php echo $venue['venue_phone'] ?><br />
Directions: <br /><?php echo $venue['venue_travel_directions'] ?> <br />
<a href="<?php echo $venue['venue_map'] ?>">CLICK HERE FOR A MAP</a> <br />
<a href="<?php echo $venue['venue_url'] ?>"><?php echo $venue(venue_url_link_text); ?></a><br/>

<?php endforeach ?>
</div>
<?php endif ?>

You can display the fields separately but you’ll need a foreach loop for each field or set of fields that you want to display.

SINGLE VALUE LIST FIELDS
According to Chris, "The process works with single value lists too, but the interface is slightly different. The plugin replaces the field with the associated record, instead of a list of records. For this reason, you wouldn't use foreach to loop over the records, but instead you could say: (if venue was a single value list field)"

$venue = $e_blast_events_noticeRecord['venue'];
echo $venue['venue_name'];


or even:

echo $e_blast_events_noticeRecord['venue']['venue_name'];

Of course, as always, styling is up to you.

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