Day 4 – Joining Tables and Filtering Results in DB-Toolkit

Posted by David Cramer on Nov 8, 2010

At times we will want to join tables together. DB-Toolkit makes this task simple. We are going to see here how to link two tables together so that our report represents the linked value. This will introduce a new fieldtype to play with and how to filter results from those links. This Tutorial will assume you have done Days 1 – 3 so I wont be explaining some settings that where explained in other tutorials, I’ll simple say what they need to be set as and move on to the new stuff.

Where going to make a meeting scheduler that links to the contacts table for attendees, then we’ll be able to filter out meetings with specific contacts. So lets jump in!

New Table

First thing to do is the creation of the meetings table. you can use the following structure.

CREATE TABLE `tutorials_meetings` (
  `MeetingID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Subject` varchar(255) NOT NULL,
  `Venue` varchar(255) NOT NULL,
  `Attendee` int(11) NOT NULL,
  `MeetingDate` datetime NOT NULL,
  PRIMARY KEY (`MeetingID`),
  KEY `Attendee` (`Attendee`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

This tutorial will only allow for a single attendee for now, but we’ll get to multiples at a later stage.

Once the table is created, lets go make a new interface against it.

Interface

Add a new Interface, Call it Meetings with a Description of Meetings Schedule. Keep Dashboard item unchecked and call the Menu Group Meetings and give permissions to Administrators.

Select the new table we created tutorials_meetings and move on to the Report Setup box.

Field Types

MeetingID: Keep this on Auto and set it to Hidden Indexed.
Subject: Text Input>Single Text Field, Shown Indexed and set it as a required field and hide the FieldType config by clicking the Setup Button on the right corner.
Venue: set this the same as Subject.
Attendee: Now this is where it get different to what we’ve done before and as such here’s a picture of how it should be setup. Set the FieldType to Join > Join Table. Set it to Shown Indexed and Flag it Required. Now on to the FieldType Config Box.

Setting up the join

Table: This selects the table you want to join to. in this case we want to join it to a Contact, so select the tutorials_contacts table.
Reference Field: This is the field that gets joined. This is usually the id field or your primary. Select ContactID.
Value Field: This is the field that we see. Select Firstname. click the green + icon to the right. this will add another value field. in that one select Lastname. so we can see both the Firstname and Lastname of our attendee.
Select Type: this is the method of selecting. lets use dropdown for now.
Single Select: leave this unchecked as it allows us to filter multiple links.

Ok that’s it, ignore the Advanced Button for now as that just alters the Join type. Default is Left Join which is fine for this task. Click Setup Button to hide the config pane.

MeetingDate: Set this type to Date Input > Date and Time, Shown Indexed and Flag it Required and Sortable. Set the Format in the fieldtype config to “Y-m-d @ H:i a” without the “” and minimize the panel.

Scroll down to the Sort Field Panel and set the Sort as DateAdded Descending.

Now click Save and we’re done.

Capture

joined table dropdown

Now that you have a meetings scheduler, go view your interface and capture some meetings. you’ll see that when you add an entry, you are presented with a drop down of all your contacts, listed by First name and Last name.

Capture a few meetings with various contacts and dates.

Filtering

Once you have a few meetings scheduled, click on the Filters button next to the Add Entry. you’ll see the filters panel. The fields in here are those with the Indexed setting, both shown and hidden. In the Contacts Filter you’ll see a dropdown with all the contacts, simply check the contacts you want to filter by, select a date rage to the right and click Apply Filters.