DB-Toolkit

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.

Posted by David Cramer on Nov 5, 2010

Minimum Version for this Tutorial: 0.1.7

Sometimes you will want to have a report list of all entries and and a separate capture form. in this case you would have 2 interfaces, the report and the form.

We will learn about Passback Values, Redirection and Form Layouts. We will take Tutorial from Day 1 and split it into two interfaces that achieve this. So lets get started.

The interface we’re going to build: add contact.

Duplicating the Interfaces

Since we’re going to be interfacing the same table twice (the report and the capture) there is a quick way of getting this done. In the DB Toolkit Menu group, click on “Interfaces”. This will take us to the complete list of interfaces we’ve created. In this list you’ll see the group “Contacts” we created in Day 1, in that group is the interface “Contact Manager”. Mouse over this entry and you’ll see the Sub Menu with the options “Edit, View, Duplicate and Delete”. Simply click on Duplicate to create a copy of the interface. Directly below it you’ll see a new entry. this is our new interface that we will turn into our Capture Form.

Mouse over the duplicate entry and click on the sub menu item Edit.

Making the Form

New setting for capture form

Lets set the new interface as the image to the left.

Title: Add Contact

Interface Description: Capture a New Contact

Set as Dashboard Item: leave unchecked

Menu Group: Leave it as Contacts

Effective Permission: Administrator

This will essentially give our new interface a unique name. Now its time to give it different function.

Click on the “Form Layout” Tab, we are able to overide this default form and create our own form layout using a grid based model. So lets start by clicking on the

Form Layout

By default, DB Toolkit generates the input form according to the FieldType selection in the order of the fields from the field setup tab. In this section I’ll be using terminology explained in Form Layout Tab, so read that up to get familiar with it, or just wing it.

The form layout tab

The First thing we want to do is click on the Sync Fields button, this will fetch our fields that we can use in our form. Once we have our fields we want to add a Row to our form, so click on the Insert Row button.

We’re going to have a 2 column form here, the text based details to the left and an image to the right. So On the Row Editing Options, Click on the Add Button to divide the Row into 2 columns. Lets off balance the form a little by making the left column wider than the right, click on the Row Config button in the Row Editing Options, and set the balance in the dialog to 60% and 40% and click save. Great!

Now lets get rid of the fields we don’t want. We are not going to need to capture ContactID, DateAdded and AddedBy as they are auto fields and as such don’t have an input, so click the little X on those Fields to remove them. (this is not really necessary since leaving a field in the available field box effectively ignores them as well, but its cleaner to remove them as its less complicated.) Now we can start building our form, this is done via Drag and Drop so its fairly simple. Drag and drop the fields Firsname, Lastname, PhoneNumber and EmailAddress to the Right Column and Drag and Drop the Photo Field to the Left. And that’s the building of the form part. Now there are 2 more steps to do and that’s set the interface into Form Mode ans setup the redirects.

Settings Tab

in the Settings Tab

Redirect interface

Head on to the Settings Tab, I’m not going to get into much detail here. Simply check the Form Mode Checkbox in the General Settings Panel. This sets the interface render mode to Form. To the right is a panel called Redirects with 2 tabs Public and Admin. Click on the Admin tab in this panel we should see 2 interfaces there , Contacts Manager which we created in Day 1 and the Duplicate (which is highlighted as its the one we’re editing), Select the Original Contacts Manager.

What we have done here is tell the form to redirect to the Contacts Manager interface on submit. This way we will see the new entry in the report list after submission. Indecently, Setting no redirect would have the form create the entry and load it up in the same form for editing.

Now hit the Save button at the bottom of the page as we are done!

At the tom of the Interfaces page you should see “Interface Add Contact Updated”. Click the Add Contact link to view your capture form. You will also see that in your Contacts Menu to the right, you now have a new link “Add Contact” Now go and test your capture form and play.

Posted by David Cramer on Nov 3, 2010

Minimum Version for this Tutorial: 0.1.7

In the first of our db-toolkit tutorials, I’ll show you how to build a Contacts Book which consists of a report with an add item button.

This will be fairly straight forward and wont show you all the settings and all that since it uses manly the defaults, but it will show you basic concepts on how to configure a table to be a form and report. So without hesitation lets jump in.

The interface we’re going to build: contact manager.

The Table

Select the query below and use it to create the table for this tutorial. In this case its a simple contact book, nothing special. Note that it does not need the prefix as the plugin finds all tables in the database not just prefixed tables. This is to allow us the freedom to use any table.

CREATE TABLE `tutorials_contacts` (
  `ContactID` int(11) NOT NULL AUTO_INCREMENT,
  `Firstname` varchar(45) NOT NULL,
  `Lastname` varchar(45) NOT NULL,
  `Photo` varchar(255) NOT NULL,
  `PhoneNumber` varchar(25) NOT NULL,
  `EmailAddress` varchar(100) NOT NULL,
  `DateAdded` date NOT NULL,
  `AddedBy` int(11) NOT NULL,
  PRIMARY KEY (`ContactID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1001 ;

Creating The Interface.

Add New Option

First thing to do is make sure DB-Toolkit is installed an up-to-date. Now click on the “Add New” option in the DB Toolkit panel at the bottom of the toolbar. This will take you to a new interface creation page.

Don’t be alarmed at all the settings and optiona available, we’ll go over them over the course of these tutorials soon enough.

Setup for new interface

Interface Title
This is the title of the interface. Lets call this Contacts Manager

Interface Description
This gives us a better idea of what the interface does, it also is the page name for the interface. Lets call this one Manage Company Contacts

Set as Dashboard Item
By checking this it will allow the interface to be usable on the Dashboard. lets leave that off for now.

Menu Group
Interfaces can be accessible as menu items in WP admin. Lets call this menu group Contacts.

Effective Permission
This sets what kind of user has access to this interface. in this case its for administrators.

The next step is to select the table you want to interface. in the table selection dropdown select the table you created from the above SQL “tutorials_contacts” once you select the table name you’ll see all the fields load up in the Report Setup panel below.

Report Setup: Configuring the Fields.

This is perhaps the most important step. a FieldType is more than a setting each field type is a mini plugin all on its own. it has the power to manipulate the report query, has its own headers, function library, footers and javascript files. They are very powerful. I’m going to use terminology found in the Field Panel documentation for this so check there for reference. should be pretty easy if you want to wing it.

Field Config Panel

Field Settings:

ContactID: Lets keep the FieldType as Auto. This will allow for the auto-increment to work. set the Field View as Hidden Not-Index the panel should fade out indicating it is hidden.

Firstname: Lets set this to Single Text Input Click on the fieldtype button, in the panel below, click on Text Input > Single Text Input the panel should fade away and the FieldType button should reflect the new type. Set the Field View as Shown Indexed and click the Required flag. Leave the FieldType config panel as is, click the setup button to minimize it.

Lastname: Set this the same as Firstname only click the Sortable button as well.

Photo: Set the FieldType to File > Image Upload. Set the Field View to Shown Not-Indexed. Now in the FieldType Config Panel set the Icon Size to 25 check the square crop , the View size to 85 and the full view to 150 and minimize the panel.

PhoneNumber: Lets set this to the same as Firstname. (We are working on a phone number FieldType but use single line input for now).

EmailAddress: Lets set this to the same as Firstname. (We are working on an Email Address FieldType but use single line input for now).

DateAdded: Set the FieldType to Date Input > Auto Timestamp, Set the Field View to Shown Indexed and you can ignore the FieldType Config for now by minimizing the panel.

AddedBy: Set this to Auto Values > User ID, Shown Indexed and ignore the FieldType Config.

Organise your Report

Now lets re-arrange the fields to be more logical for a report. Click on the Photo Field Name and drag it above Firstname All field types can be arranged for report view this way.

The next panel is Totals Lets ignore this for now and go on to Passback Field this is the field that is passed to and from interfaces. its commonly the primary field. in this case its the ContactID. lets leave it as it is for now  as this should be the default. You can add multiple return fields.

In the Sort Field Panel, this is the default sort field in the report. lets choose Lastname and set it to Asending so we can sort the Contact alphabetically by their lastname in ascending order.

Now click Save to create your first interface.

And thats it! you now have created your very first contact manager.
In your Interfaces list, you can see your new interface grouped under Contacts put your mouse over the Contacts Manager entry and click View so view your Interface.

Using your Interface

Now lets go and add a new Contact. You’ll notice after a page load that there is now a new menu group just below the Comments Group called Contacts. This is your new interface Group that you defined earlier when setting up the interface.

adding an entry

View your Interface and click on the Add Entry button on your interface toolbar. In the dialog window you get all the fields that you setup earlier with validation.

Fill in the Form with your own details to get the feel of how it works.

Play!

Go try out all the features in your new interface. (Export is buggy I know!) look at how to edit and view and entry as well as deleting.