Open Source

Posted by David Cramer on Nov 24, 2010

This tutorial requires at least Version 0.1.13 due to additional field types and a minor bug-fix. Please update before attempting the tutorial.

Well the time is here. You have learned the basics on how to make interfaces on tables. So now lets put them all together and build a functional project management app.
In this tutorial, we’ll cover all the methods we learned in days 1-4.
some new stuff we’ll learn today:

• Cloned Fields : This allows us to chain together multiple FieldTypes. you’ll see how this works.
• Dashboard Items: We’ll place a task list on the dashboard
• Filter Locks: We’ll lock down filters to create a pre-filtered report.

This is going to be a mainly text based tutorial, so there wont be many screenshots, but there will be screens on the new stuff. So lets get going.

Tables

As always with new tables, here are the two tables you’ll need for this tutorial. the projects and the tasks.

CREATE TABLE `app_projects` (
`ProjectID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Title` varchar(255) NOT NULL,
`StartDate` date NOT NULL,
`EndDate` date NOT NULL,
`Description` text NOT NULL,
PRIMARY KEY (`ProjectID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

CREATE TABLE `app_tasks` (
`TaskID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ProjectID` int(11) NOT NULL,
`Title` varchar(255) NOT NULL,
`Start` datetime NOT NULL,
`End` datetime NOT NULL,
`UserID` int(11) NOT NULL,
`Priority` enum('Low','Normal','High') NOT NULL,
`Description` text NOT NULL,
`Status` int(11) NOT NULL,
PRIMARY KEY (`TaskID`),
KEY `ProjectID` (`ProjectID`,`UserID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=101 ;

Now that thouse are created, the first interface we’re going to build is the Projects Manager.

Projects Interface

go to DB-Toolkit -> Add New

Name the new interface Projects and the description Manage Projects, Give the menu group the name Projects and set the Permission to All. Leave Dashboard Unchecked.

Select Table, “app-projects” and move down to the Report Setup.

FieldTypes

ProjectID: set to Auto, Hidden-Indexed.
Title: Single Text Field, Shown-Indexed, Required
StartDate: Date Picker, Shown-Indexed, Required, Sortable
EndDate: Date Picker, Shown-Indexed, Sortable
Description, TextArea, Hidden-Indexed, Required

Scroll down to SortField and Select StartDate Ascending

Click Save.

Tasks Interface

FieldType config for ProjectID

Create a new Interface. Lets call this one Tasks, with the Description of Task Manager, Menu Group, Projects permission to All and leave Dashboard unchecked.

Select the “app_tasks” table and move on to the Fieldtypes.

FieldTypes

TaskID: Auto, hidden-indexed
ProjectID: Join->Join Table, Shown-Index, Required. In the FieldType Config, Link up the the field to app_projects, reference the ProjectID and show the Title. this will be a dropdown field.
Title: Single Text Field, Shown-Indexed, Required
Start: Auto Timestamp, Shown-Indexed, Sortable
End,  Date and Time, Shown-NotIndexed
AssignedTo: Join Table, Shown-Indexed, Required, Sortable. in The FieldType Config: Join to users table (usually wp_users) reference the ID and show the user_nicename field. type is dropdown.
Priority: Enum, Shown-Indexed, Required, Sortable
Description: Text Area, Hidden-Indexed, Required
Status: On-Off Toggle, Shown-Indexed

Sort field, Start Descending

Click Save.

Cloning

Here we introduce the concept of cloning. Open the interface Projects to Edit.

Scroll down to Report Setup, and click on Add Clone Field in the Advanced Field Types Box.

You’ll see a new Field Box appear just bellow the button, Drag the new field and place it between EndDate and Description. Click on the little gear to the right of the title to expose the Additional field settings. Name your field Duration. by tuping in the title field and close the panel by clicking the gear again.

settings for the cloned date difference panel

MasterField: This is the field we’re essentially cloning. this is the value that is passed back into the fieldtype handler. lets set it to ProjectID which should be already selected.
The rest is the same as a standard fieldtype panel.
Set the rest to Math->Date Difference,  Shown-Indexed. In the Field Config Panel, Select Start date and End date. order is not too important as it will only be calculating the time between the two dates.
open the FieldType Config for the Status Field and Check Enable Date Stamping and select End as the date stamp field. this will apply the current datestamp to that field when the status changes. Pretty Cool!

Head on over the the settings tab.

Now we can go an customise this interface to say Project instead of Item.
in the New Item Title, set it to Add Project.
Scroll down to Notifications & Buttons, Change all references from item to project. there purposes are pretty much self explanatory.

Click Save and done with this interface.

Open the Tasks Interface, and repeat the clone field steps to add a duration field to the tasks, you can also go and set the word Item to Tasks as you did with the projects while you’re there.

once you have done that, click save.

Dashboard

Go to your interfaces list and under Tasks, Click Duplicate to make a copy of the tasks interface. on the copy, click edit.

Change the name of the new interface to My Tasks with the Description of My Tasks. Check the Set as Dashboard item. and leave the Menu Group blank.
go down to the fieldtypes and change the UserID Field type to Auto Values->User ID and in the field type config panel, check Apply Hard Filter . this will set the report to show only entries for the logged in user id. in the Cloned Duration Field, remove it by click the red X next to the gear on the right as we dont need this.

Head on over the the List Template Tab.

Check Use Template and scroll down to the PreContent box and paste in the following html code:

<div style=””>
<div>
<ul>

In the Content box bellow that, use this code:

<li>{{Status}} <span>{{Title}}</span> <span>{{Start}}</span>
<div>Project: <strong>{{ProjectID}}</strong> | Priority: <strong>{{Priority}}</strong></div>
<div>{{Description}}</div>
</li>

scroll down to the PostContent Box ans paste in the following.

</ul>
</div>
</div>

That will set up the template for the dashboard, Click save and go view the new interface.

Click on the Show Filters button, in the filters panel, select unchecked in the Status Filter and click Lock Filters this will lock down this interface to only show unchecked tasks. Remember we set this interface to Apply Hard FIlter to the User ID? So this combination will now only show unchecked tasks for the currently logged in user. Pretty Neat!

Now click Edit on the interface and go to the Settings Tab.In general Settings, Check Hide Frame. Go down to ToolBar Settings and uncheck everything in that box. Scroll right down and Save.

and thats it! go to your dashboard, you’ll see your new tasks list.

Go play with your new project manager and comment below if you have any questions.

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.

Posted by David Cramer on Nov 6, 2010

Part of the beauty of DB-Toolkit, is being able to make your interfaces accessible to the public. You might ask why would i want to do that? Well, in this lesson, I’m going to show you a basic example of making our contacts interface public with a template output. The result is something similar to twitters following pane that shows icons of all the people you follow. While not really the same it will give us the same looking result. This tutorial will introduce the Template tab and Shortcodes. I’m going to asume you have already done Day 1 and Day 2 so I’m going to refer to things covered in those tutorials, you might want to go over them before continuing. So lets get right in!

The widget based interface we’re going to build

Duplication

Settings for our contact wall

Most of the time, when interfacing the same table multiple times, I find it easier to just duplicate the interface and work from there, that way most of the FieldType Allocation is already done. Lets duplicate the Contacts Manager interface as explained in Day 2 and click the Edit to start with your new interface interface.

Title: Contacts Photo Wall
Interface Description: Contacts Photo Wall. We copied the title here because since its not being used as an admin interface, it doesn’t need a distinct title and description. its really up to you on what you call it.
Set as Dashboard Item: leave unchecked, we’ll get to that in another lesson.
Menu Group: since this is a front end item, we don’t need it in a menu group. So make this blank. It will be placed in an ungrouped area in your interface list.
Effective Permissions: These permissions carry through to the front end, so well set it to all, so that the public can view it. If you where to set it to anything else, the interface wont show to the public, but will show if a user of the matching permission was logged in. Very handy!

Now scroll down till you get to the Photo field panel and click the Setup button on the bottom right corner of the panel. This opens the FieldType Config panel as seen below. The 3 sizes here are icon, view and full. Icon, is the size seen in a list view, the second view size is seen when you view a specified entry, and full is when the second view is clicked. Here we’ll set the size of our Icon to 45 and we’ll keep Square Crop checked. this will force our image to be cropped square.

setup the size of your icons.

Now that that’s done, we head on to the List Template. Scroll all the way up and click on the List Template tab.

Templates

The first thing you see on this tab is “Use Template” make sure its checked. when you check this, it tells the system not to push the data to a table, but rather wrap it up in the code we give it. See. List Templates in the documentation for the anatomy of List Templates.

Scroll down to the Content template box and use this code.

<div style="padding:2px; margin:1px; float:left; border:1px solid #efefef;" title="{{Firstname}} {{Lastname}}">{{Photo}}</div>

the curly braces {{}} are dynamic wrappers. when a field name is wrapped in these, it will get replaced by the content of that field on render. dynamic wrappers can only be used in Content and Footer template box. These two boxes are followed by a usable keys panel that gives reference the keys that can be used. Now scroll down to the Content Wrapper End template box and use this code to end off.

<div style="clear:left;"></div>

this will clear off our left floats.

Settings

we need to turn off a few things to make our interface show only the templated content, so click on Settings tab.

general settings

General Settings Panel I’m not going to mention any of the other settings, just the ones we’re interested in for now.

Hide Frame: Check this. this will make it so there is no framing panel and title around our interface.
Hide new item button: check this. This will disable adding of new entries on this interface.
Items per Page: This is the amount of items shown per page. if there are 16 entries and you show 8, there will then be 2 pages of entries. set this to 8 (or what ever you want for now) leaving this blank means no limit.

Now Scroll down to Toolbar Settings, and check Hide Toolbar, this will make it so the toolbar which holds the Add new button, Show filters etc.. not render. we want only the icons to show so this must go.

Scroll down to the next panel, List Settings and uncheck the Show Footer, we don’t want this to show, we only want to show the icons.

Now scroll right to the bottom and click Save.

our photo wall

You can now go and View your interface and it should look something like the image to the right, depending on how many you captured previously.

Short Code Embedding

We now need to embed the interface into our front end. We are going to do this on a Widget. First we need to get the Short code, so go to your interface list from the DB Toolkit -> Interfaces menu. Copy the Short Code for our Contacts Photo Wall interface. Now lets go to to the Widgets page by clicking on Appearance -> Widgets.

We use the simple Text widget for embedding interfaces, so drag the Text widget to your sidebar of choice where you want your wall to show and in the Title box, type in My Contacts and in the Text box past in the Short Code you copied earlier.

And that’s it! You have created a simple contacts photo wall. You can use either the Capture form we made in Day 2 or the report from Day 1 to add more contacts to the wall.