Adding a Data Table to phpBMS

There are times when you might like to to add functionality to phpBMS or to view / manipulate legacy table data inside phpBMS. The steps below detail the process of adding a table to phpBMS called "work orders" into the system as a tutorial.

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?

Working in a custom module separates any code you intend to write from the phpBMS core. If a new version of phpBMS is released, your code and supporting data will not be overwritten by the update. Secondly, a module is easier and cleaner should you wish to distribute your code to other systems, or release it to the public.

The alternative is to submit a patch with your table additions to the phpBMS core project.

For this tutorial, we will create 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 (this is an older style of table without UUID fields, so please refer to current table structures in phpBMS when creating your own module or table)

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 five common fields: UUID(not shown in the example above), createdby, creationdate, modifiedby, and modifieddate. If you are creating a new table, putting these fields in your table will facilitate more complete integration with all the features of phpBMS core. If you are adding an existing table into phpBMS just for display/sorting purposes, these fields are optional.

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 during the install process(modules/install/install.php).

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 Settings->Table Definition. Click the new button(+) to begin defining 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 name 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 that linked 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 size to 99%

Defining Search Screen Button Options

Use the "options" tab to define which buttons and functions are displayed on the search screen. This includes whether users can see the new, edit and print buttons; and whether they can 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, and edit records. Make sure the predefined radio button is set, choose new from the function drop down and click the "Add Option" button. Do the same for the edit and and select options.

The functions that define how to delete, inactivate, or perform any additional functions are defined in the module's include directory with a name that corresponds with the table definition's 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 modifying an existing search_include file from the base module (such as the menu_search_include.php) to begin coding these funtions.

The php file is not required for the table's search screen to function but is required if you want to implement delete, inactivate and 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 to be displayed by default.

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 and filling in the four tabs of search screen information you should test the search screen to see that it works properly.

Check the main table definition screen for your record and copy the uuid of the table definition you created.

Navigate back to the table definitions search screen. In the browsers location URL, change the id variable to the uuid of the table definition that you just copied. 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 its search screen is working properly, you will want to hook it up to the menu system so that 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

When you save the menu item, it should appear immediately under the main menu you selected. Test that it works.

Creating the Add / Edit Screens

At this point, your table should be registered with phpBMS, attached to the menu system, and is functionally 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 continue.

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 core, they reference the same file.

We recommend copying an existing table's add/edit files as a starting point for your table. 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 workorders module folder, and rename the files workorders_addedit.php and include/workorders_adedit_include.php respectively. We then modify the include file's SQL statements to reflect the workorder table fields and then format the form in workorders_addedit.php to display the work orders fields.

phpBMS has many php functions that help display information, such as a date picker, time picker, auto filler, choice list, table list. There are dynamic fields such as those used when selecting Clients and Products that do real-time lookups. You can also mark a field as required, and specify what formatting it accepts (integer, currency, etc.). The functions are defined in the /include/fields.php file and other places. grep -rli is your friend ;)