One to Many Relationship Tutorial in DB-Toolkit

In the last week alone I have had 8 requests to help with one to many relationships in DB-Toolkit. So I’ve decided to show how its possible to do this.

The initial idea was and still is (once its done) is to have the join fieldtype have multiselect options working properly, but until then this is my preferred method to do this kind or relationship.

In this tutorial I’m going to do feature based example. It will be a listing of camera’s and have a one to many relationship of features.

Before i start any application, I create my manager interfaces for the application. Manager interfaces are admin level screens for full management of data. something like the users page, where you can manage all the users details. By doing this, we get the chance to fully create the tables needed for the application and are able to build test data.

Lets start with the features interface: Co to the DB-Toolkit App Builder. If you have an app open, close it and create a new app called cameras.

Once your new app is created, we’ll create the “Features” manager. Click on “New Interface” to start with the creation of our features manager for managing the main features. 

Interface Tab

Set the Interface Title to: “Manage Features” and the description “Admin feature management” (the description is purely to let you know what the interface is for).
In the Navigation section, leave the Group blank as we wont be using that since this is a small app. Set the Menu Label to “Features”. leave the other settings on the tab alone as they are not needed.

Fields Setup Tab

On the select table entry, click “Add New” and call your table “App Feature” (I like using a singular term so the auto id is singular) and click “Create”

Once this is done you’ll get your first field added “app_featureID” as in the image below.

The app_featureID field is our Auto-Increment Field to keep each entry unique. this field is always set to ignore in management interfaces.

Add a new Field and call it “Feature” This is going to be the actual title we see when we select it for a listing. Once you see it added to your fields list, add another field and call it Description. This is to be a small blurb describing the feature.

Once you have created your two fields, its time to set them to the fieldtypes they are going to be.

In this case the Feature field will be a “Single Text Field” and the Description will be a “Text Area”.

On the Feature field, click the Ignore button to open the fieldtype selector menu and select  “Single Text Input” from the “Text” fields.

Once you click on the Single Text Field, the menu will change to a config panel. you can leave the settings in here as the are and hide it by clicking the “Setup” button to the bottom right. You have now set this fields fieldtype to single text field.

We want to have this field visible on the manager screen, so click on the little eye icon to set it as visible. It will highlight green when is visible.

For the Description Field, set it’s fieldtype to “Text Area” and also set it to visible. as we want to see it in the manager.

That’s it. You can now click “Save” all the other settings in other tabs are preset for manager screens, so we wont be changing anything else.

You should now have an interface in your application that looks similar to the image below.

Click on the View link in the interface listing and take a look at what we have created.

Click on the “Add Entry” button to create the first usable feature. I’m making one called “Interchangeable lens” with the description “Lenses can be changed to provide different focal lengths.” and click save. You now have a feature listed in your feature manager. Go an add a few more of your own that relates to cameras.

Once you have added 6 features, or as many as you wanted. your manager will look similar to the image bellow.

Now let’s go create the Camera Manager.

Go back to DB-Toolkit -> App Builder and click new Interface.

Call Your interface “Camera Manager” and description “Admin camera management”

Set the menu label to Cameras and go to the Field Setup tab.

Add a new table and call it “app camera” and click create.

Add 3 fields: Model, Make and Year. (just keeping it simple for now.)

Set the fieldtypes for all 3 fields to “single text field” and make all 3 visible. It should look like the image below.

Quickly go to the General Settings tab, scroll right down to the bottom and check the Disable Notifications checkbox.

click save.

Now you should have 2 interfaces in you app. click on view link in the Manage Cameras interface and add a camera.

Now you have features and a camera, but we want to be able to assign many features to a camera. The idea is that we need a table to keep all the features that the camera has. What we need is a camera feature interface. so let’s go make one.

Go to your app builder and click Create Interface. Call it “Camera features” and the description “Camera Feature Relation” there will be no menu label in this one.

In the Field Setup, add a new table called “app camera feature” and click create.

Add two fields “cameraid” and “featureid”

Set the cameraid fieldtype to “selected item filter”

The selected item filter allows the interface to capture and show only entries that match the id present we don’t need this field to be visible as it’s a control not a display field. I’ll show and explain it all a little later.

The featureid is going to be a Join fieldtype. so click on the ignore field button to change its fieldtype and select “Join Table” You’ll then get a config panel for the join that will look like the image bellow.

So what we are going to do is have this show us a dropdown of all the features when adding a new feature to a listing. this way we can select the feature we want and add it to the list. So in the Table selection, select the features table we created earlier. is should be “wp_app_feature” where wp is your wordpress prefix. once selected you’ll get the next part of the setup load up.

To try to explain what the settings are:

Reference Field: this is the field that is actually stored in the database. as a join we want to store the ID or at least a unique value. this is so we can reference a full entry from another table without getting the wrong entry. the ID will always be unique so go with that.
Value Field: this is the value we want to pull from the join table. Essentially the value we see to select. the little green + synbol adds another field if we want to add another display field. this is usefull if we join to a user table and want to show both the firstname and lastname in the selector or a list.
Join WHERE: this is a specific filter applied to the selector. it will only list items to select where the WHERE field is = the value you give it. an example of its use would be if the table you joining to had an enabled field, you can select the join WHERE to be that enabled field and set the = value to 1. then it will only allow you to select entries that have been enabled. Leaving this field blank lists all the entries. we’ll leave it blank.
Sort by: this is the field you want to sort the list by and in which direction.
Select Type: you have the options of:

• Autocomplete – this allows you to start typing and then it will give you matchin options from the table to select. )Great for joining to a table of lots of entries like countries)
• Dropdown – a simple dropdown list of the entries in the table.
• Checkbox – meant to be the rudimentary one to many type. its faulty and need work! consider it not working.
• RadioGroup – a radio selector allows you to select one option from the tables entries. use it only on small tables that have only a few entries.

Single Select checkbox: This sets the filter selection to be a single selector so you can only filter by a single entry at a time.

Leave the advanced button for now. well get to it later.

Right lets setup the join.

Reference Field: leave it as it is. app_featureID
Value Field: Set it to app_feature
Select Type: Dropdown

click setup button on the bottom right to hide this panel.

We want this field to be visible so set it visible by clicking the little eye icon so it’s highlighted in green.

General Settings Tab

We don’t want all the options available for the user on this interface so we’re going to remove a few options. well pretty much all of the.

go down to “New Item Title” and change it to “Add Feature” Set the “Items Per Page” to 0, we want to see all of them.

– toolbar settings section:

keep checked “show toolbar”  and uncheck everything down to list settings

scroll right to the bottom and check “disable notifications”

Form Layout Tab:

We want to have the form work in Ajax when adding features as its faster. So just check the Ajax Form checkbox. no need to build a layout as there is only 1 field.

And that’s it, you can now click save to create the Interface. You should now have 3 interfaces in your application:

Ok that was the easy part!

Now to great the actual relation part. The first thing we need is an edit interface. So in the “Camera Manager” interface, click the Duplicate link.

this will make a copy of the interface so that we can use its settings for the edit camera form. once duplicated you’ll have a new interface at the bottom of the list called “Camera Manager 4f3a12ecb2abd” the code will be different as its unique to everyone. Click on edit link for this interface.

Set the Interface Title to “Edit Camera” and the Description to “Edit Camera Form interface” remove the Menu Label as we don’t need that.

Go to the field setup tab.

What we are going to do is create a new “Virtual Field” and drag it below the “app cameraID” field. what we are doing is making a copy of the value in app_cameraID so we can give it a new fieldtype without effecting the actual value. In the Virtual Field Set the Master to app_cameraID it should already be set as it’s the first field. Then set the field to be visible by clicking the Eye.

Now set the Virtual Field FieldType to Include Interface. In the FieldType Config you’ll get a dropdown of all your interfaces in your system. This is very messy I know and I’ll be changing it soon to be a better easier to use selector. but for now please bear with me on it  The interface you want to select is the “Camera Features” interface we created just before this one. We are going to pull in the interface when this interface is loaded. you’ll see.

Lets just name the Field quick. Click on the little gear icon to the top right of virtual field and in the panel that opens, change the title to “Features” click the icon again to hide the panel.

Your settings should look like the image below

Now let’s go to the General Settings Tab.

General Settings Tab

First thing is to put the interface into Form Mode but selecting Form mode radio button.

Now scroll down to Notification and Button. Change the Edit Form Title to “Add Features to Camera

Now let’s go to the form layout tab to set up the form.

Form Layout

Click on the Sync Fields. This fetches out fields and places them in the “Available Fields” box to use.
Now click the “Insert Row” button to add a row to our form layout.

Click on the green + icon to the right of the new row to split it into 2 columns.

Now that we have a canvas for our form, we can drag our fields onto the form area. Drag all the fields to the left column and them move the virtual field (the one that’s just a code, as the title “Features” you gave it won’t be active until its saved) to the right. You can also adjust the column widths by clicking the black spanner and screwdriver icon to the right. set the widths to 30% and 70% so that the first column is smaller than the second. you can play with this later to make it perfect. Now at the top, set the form width to 650px and check Ajax Form.

Your form layout page should now look like the image below.

Now that our form layout is set, click on the Redirect tab to set redirecting.

Redirects Tab

On the Redirects Tab, select the interface subtab, expand your application entry by clicking the little gear icon to the right and select the interface “Camera Manager” This is so when we click save, it will take us back to the Camera Manager.

I think we’re done. Click Save to finish of the interface.

You should now have 4 Interfaces in your list.

Ok now that we have our edit interface we need to redirect out Camera Manager to it. So click Edit link in the Manage Cameras interface and go to the Redirects Tab.

Select the Interface SubTab, expand your Cameras Application and select the Edit Camera interface as your redirect to and Click Save.

Now the Final Step is to make sure that the Add Features to Camera Interface will accept the correct Passback Value. So click the Edit link on the “Camera features” interface and go to the Field Setup Tab. Click the Setup button on the bottom right corner of the “cameraid” field. and set the GET override to “app_cameraID” as this is the ID field of the camera listing. When you click on a Camera, a value is passed back to the next interface in this case it will be app_cameraID=x this will tell the next interface what you clicked on. The selected item filter looks for a specified value and uses that as a filter id. By default it will look for a value of its own name. so here it will be looking for cameraid= but it will be coming in as app_cameraID= so we set the override to look for the correct value.

Click Save and we’re done!

Now go to the Manage Cameras interface by clicking the View link in its listing. The camera you made earlier will now be a link. Click it to go to the add Features screen. You’ll see the settings for the camera to the left and the add features interface to the right. Simply click “Add feature” button and select a feature to add. the list will then show the features you’ve added.

I know its a little complicated to do but its currently the best way to do a many to one connection. I am working on the checkbox and a multi-select join but until then this is the way to do it.

BUGS
There is a bug with the Delete action, in that when you delete a feature it will refresh and all features will be gone. just reload the page and it will be fine! the refresh doesn’t pass back the passback value so it returns nothing. This is due to be fixed in the next update.

UPDATE: BUG fixed – get update to 0.3.2.009