Adding a Data Table to phpBMS

So you want to add a data table into phpBMS? This could be to add functionality to phpBMS or to view / manipulate legacy table data inside phpBMS. The steps below detail the process of adding a table into phpBMS. We will also be adding tutorial table for "work orders" into the system.

For most projects, make sure you are working inside a custom module. For information on creating a custom module see ModuleDevelopment

Why is Working in a Custom Module Important?

There are a couple of reasons why working in a custom module is important. This separates any code you intend to write form the phpBMS core. If a new version of phpBMS is released, your code and supporting data will not be overridden by the update. Secondly, if you ever want to distribute your code to other systems, or release to the public, this will make it easy to do so.

Unless you are planning to submit a patch with your table additions to the phpBMS core, make all of the following additions/changes to a custom module.

For the tutorial, we created a "work orders" module.

Creating the Table

If you already have an existing table (let's say from a legacy application) you will need to copy the table into the phpBMS MySQL database. If you are creating a new data table from scratch, you will need to run a SQL command through a MySQL client program.

For the tutorial add a work order table with the SQL command

CREATE TABLE `phpbms`.`workorders` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoiceid` INTEGER UNSIGNED NOT NULL,
  `inactive` TINYINT UNSIGNED DEFAULT 0
  `completed` TINYINT UNSIGNED DEFAULT 0
  `startdate` DATE,
  `enddate` DATE,
  `details` TEXT,
  `createdby` INTEGER UNSIGNED,
  `creationdate` DATETIME,
  `modifiedby` INTEGER UNSIGNED,
  `modifieddate` TIMESTAMP,
  PRIMARY KEY(`id`)
)
ENGINE = MYISAM;

Most tables in phpBMS have four common fields: createdby, creationdate, modifiedby, and modifieddate. If you are creating a new table, remember to put these fields in your table. If you are adding an existing table into phpBMS, you may or may not want to add these fields into your table.

If the new table is going to be part of a distributed module, we will need to make sure to run the create table SQL statement sometime during the install process.

Creating the Table Definition

With a table created, and in the phpBMS MySQL database, we now need to add the table definition inside phpBMS. Make sure you are logged with administrative privileges, and go to the "table definitions" menu item under the settings menu. From the table definitions search screen, click the new button. This is where you define the link between the MySQL Table and phpBMS. Important fields include:

• type - For most additions, type of "table" is appropriate. If you plan on *only* searching/displaying results choose type view.
• module - Make sure you enter your custom module in here.

For the tutorial, we added the following information:

Display Name: Work Orders

Primary Table Name: workorders

search/display SQL FROM clause:
((workorders INNER JOIN invoices on workorders.invoiceid=invoices.id) INNER JOIN clients on invoices.clientid=clients.id)

Type: table

Module: Work Orders

Add Record File: modules/workorders/workorders_addedit.php

Add Access: EVERYONE

Edit Record File: modules/workorders/workorders_addedit.php

Edit Access: EVERYONE

Search Access: EVERYONE

Advanced Search Access: Administrators

View SQL Access: Administrators

Delete Button Name: Inactivate

Default Search SQL WHERE Clause: workorders.id!=-1

Default Sort Order SQL ORDER BY Clause: workorders.id

Make sure to save the record before continuing.

Next, define the appearance and behaviors of the search screen. This is a four part process, with each step corresponding to a tab at the top of the table definition record. Each of these steps are required for the table definition's search screen to work properly.

Defining Search Screen Displayed Columns

The "columns" tab lets you define which columns will be displayed on the search screen, included how it is formatted, in which order, and if you want any footer calculations (for totals or averages etc...)

You need to have at least one column defined, and at least one column whose column size is 99%

In the tutorial, notice that when the table definition record was created the search/display SQL FROM clause included a double inner join, linking in both the invoice and client tables with the work order table. This means we can display information form any of these three tables as columns. Add a column for the work order id:

Name: ID
Field: workorders.id
Text Align: Left

The rest of the fields, use the defaults.

Add a column for client, finished, start date, end date, and client company name. Make sure to set the company name column's columns size to 99%

Defining Search Screen Button Options

Use the "options" tab to define what buttons and functions are displayed on the search screen. This includes whether users can see the new, edit, print buttons, whether they can highlight (select) individual records. This screen also links in any extra functions you have defined that can affect multiple records.

For the tutorial, we will be granting access to select, add new, and edit records. Make sure the pre-defined radio button is set, choose new from the function drop down, and then click the "Add Option" button. Do the same for edit and and select options.

The functions that define how to delete, inactivate, or perform any additional functions you want are defined in a specific location. phpBMS looks for these funtions in the module's include directory, and have a name that corresponds with the table definitions primary table name, combined with "_search_include.php"

In the case of the tutorial, we will to make sure that we create a file:

modules/workorders/include/workorders_search_include.php

We recommend copying and then modifying an existing search_include file from the base module (such as the menu_search_include.php) for a start on how to code these funtions.

The php file is not required for the table's search screen to function, only if you want to implement deletion/inactivation/other functions.

Defining Search Screen Quick Searches

Use the "quick search" option to define searches that appear in the "find" drop down on the search screen. You need to have at least one quick search item defined.

For the tutorial, we will add a single item:

Name: All Records
Access: EVERYONE
Search: workorders.id!=-1

Defining Search Screen Search Fields

Use the "Search Fields" tab to define the fields listed in the "where" drop down on the search screen. At least one field must be defined.

For the tutorial, we will be adding a simple field search:

Name: id
Type: Field
field name: workorders.id

Testing Your Search Screen

After creating the table definition an filling in the four tabs of search screen information you can test the search screen to see if all of the information you entered worked correctly by referencing the search screen directly.

Check the main table definition screen for your record, and note the id of the table definition you created.

Navigate back to the table definitions search screen. In the browsers location URL, change the passed id variable to the id of the table definition that has been newly created. If you entered the information correctly, you should now be able to search and see your table data inside phpBMS. Congratulations!

Note that you have not needed to create any PHP code to link the table into phpBMS

Adding the Table to the Menu

Now that the table is registered with phpBMS, and it's search screen is working properly, you want to hook it up to the menu system so that your users can access it.

From the settings menu, choose the "menu" option. Here you can add menu items to the phpBMS menu system.

For the tutorial, click the new button and enter the following information:

 Name: Work Orders
 Type: Table Definition Search
 Display Order: 50
 Access: EVERYONE
 Table Definition: Work Orders
 Parent: Sales

Creating the Add / Edit Screens

At this point, you table should be registered with phpBMS, attached the menu, and searching and displaying sorts. It is not a requirement that you add screens for adding or updating records. However if you want to add this functionality, you will need to have PHP knowledge to contiue.

Most tables inside phpBMS have an add/edit file (and corresponding PHP include file). They are referenced by the system by defining the add and edit record files in the table definition. For almost all tables inside phpBMS, they reference the same file.

We recommend copying an existing table's add/edit files as a starting point for your table. Something simple like the base module's menu_addedit.php and include/menu_addedit_include.php are good starting points.

For the tutorial, we copy the menu's add/edit files from the base folder to our work orders module folder, and rename the files workorders_addedit.php and include/workorders_adedit_include.php repectivley. We then modify the include file's SQL statements to reflect the workorder's fields and then format main php page to display the work orders fields.

phpBMS has many php functions that help display information, such as a date picker, time picker, auto fille, choice list, table list. It also has functions that help define a field as required, and what formating it accepts (integer, currency, etc..) The functions are defined in the /include/fields.php file.