First, let's log in to the Buglist Websheet as a Websheet administrator. You can get to the Websheet via the following URL or APEX Application Builder:

http://<server:port>/apex/ws?p=110:home

The differences in this URL from others we have seen in APEX are as follows:

You will first see the Websheet standard login page as shown in Figure 8-12. On this page, the credentials fields are displayed along with a link back to the APEX 4.0 development environment (in case you need it). Log in to the application as the Websheet administrator. This is the user to which we granted the Administration role in the Websheet Access Control List.

Figure 8.12. Websheet login screen

On the home page of the Buglist Websheet application, as shown in Figure 8-13, you can see the main menu, Page controls, and Annotation controls. Users will build their applications using these controls. Most of them are pretty self-explanatory, but they are not available to all users. Remember that there are three distinct roles that can be granted to a user in the Access Control List. Table 8-1 shows which controls are available based on role.

Figure 8.13. Websheet home page

Now that we are in the development environment, we can start actually building the Buglist application. We'll start just like we did in Chapter 3 by using the buglist.csv file as the source for our data, and we'll do this using a Websheet Data Grid. A Data Grid in a Websheet is nothing more than an interface to a table that allows you to define the interface and table "From Scratch" or as a "Copy and Paste." For non-developers, the Copy and Paste method will certainly be more attractive. To start the process, click the Data Grid icon on the home page, choose the "Copy and Paste" option, and navigate to the next step in the process. Although the title of the Data Grid Properties page says "Upload Spreadsheet," you can't actually do that. You have to open the buglist.csv file, copy the contents, and paste them into the appropriate section of the page as shown in Figure 8-14. You also have to give the Data Grid a name and an alias and decide if the column names are contained in row 1. If they're not, you can add them later.

Figure 8.14. Create a new Data Grid

Pressing the "Upload" button completes the creation of the Data Grid and displays it, as shown in Figure 8-15.

Figure 8.15. Websheet Data Grid

You may think this is a standard, interactive report, but you'd be wrong (sort of). There are some similarities to an Interactive Report in that the search bar and edit row functionality work just like an Interactive Report and all of the report manipulation features are there as well. The following is a list of features that are unique to a Websheet Data Grid:

Clicking on a text-based column header produces the controls shown in Figure 8-16, while Figure 8-17 shows the controls associated with a date-based column. The values below the search field in the text-based column are pulled directly from the selected column. For date-based fields, the Websheet builds several date-related search values automatically. The user can perform a search on a column simply by clicking one of the auto-generated values. Hiding and showing columns is a feature that would require developer intervention in a standard APEX application. The Control Break icon is used to create a control break in the report using the values in the currently selected column. If you use any of these controls, you will notice the familiar report filters are displayed between the report search bar and the Data Grid.

Figure 8.16. Text-based column controls

Figure 8.17. Date-based column controls

One of the best features of a Websheet Data Grid is in-line editing. A user can simply click on a value in any column and the column value transforms into an editable field. Figure 8-18 shows the Description field being edited. In this example, the edit icon (the pencil) is displayed while in-line editing is enabled. Having two paths to the Edit feature may be confusing for some users, so you may consider using one or the other. By default, all columns can be edited in-line, but this feature can be disabled by changing the Display Type column property to "Read Only," as we will see shortly.

Figure 8.18. In-line editing in a Websheet Data Grid

In order to edit the column properties, you must use the new "Manage" button on the Data Grid's search bar. The Properties tool, for example, is used to change the name and description of the Data Grid. The Toggle Checkboxes control enables and disables checkboxes on each row of the Data Grid. Once enabled, they can be used in conjunction with several of the Row controls to limit actions to specific rows. The Delete Data Grid and Copy functions work as expected, one deletes the Data Grid while the other copies the current Data Grid to another name.

Figures 8-19 and 8-20 show not only the list of tools available to manage the Data Grid as a whole, but also the Row and Column tools. The reason for drilling down on the Row and Column tools is that they contain several options for managing Row and Column properties, where the other tools very self-explanatory.

Figure 8.19. Data Grid Column tools

Figure 8.20. Data Grid Row tools

The Column Controls list is quite comprehensive. The ones that take the least explanation are the Add and Delete Column. Before discussing Column Properties, we need to look over the other three controls, as they are used in support of the Column Properties control.

The first control, shown in Figure 8-21, is used to create Lists of Values (LOV), which can be attached to any column, as we will see when we look at the Column Properties control. The interesting (or maybe disappointing) thing about this feature is that the LOV can only be based on a comma delimited list of static values. You cannot populate an LOV using a query. While this may be disappointing as a developer, the end user may have no SQL skills and allowing him to build dynamic LOVs may cause trouble.

Figure 8.21. Data Grid List of Values

Next on the list is Column Groups, which are nothing more than a named group of columns. The effect of creating a Column Group is not seen on a report or Data Grid as you might expect. What they do is enable you to hide and show a set of columns by clicking an icon on an "add" or "edit" record page. Like LOV's, you create the Column Group here, as shown in Figure 8-22, and you apply it to the appropriate columns using the Column Properties control. Even after you assign a Column Group, you will not see the effect of grouping columns until you navigate to an "add" or "edit" record page.

Figure 8.22. Data Grid Column Group

The Validation control allows you to create rules that govern the type of data entered in any column of a Data Grid. Figure 8-23 shows the Validation types that are available by default. Because Validations can evaluate both column values directly or compare column values to an expression, Validations can be very comprehensive. We created a simple validation on the Description field, which ensures that the column value is not null.

You can also create Validations that compare a column value to an Expression, which can basically be a text string. For example, if you wanted to restrict a field to a specific value (and you had not used a Select List), you can set the Validation Type to "Column Specified is contained in Expression," which will evaluate to true if the value of the column is contained anywhere within the expression. For example, if the Expression contained 'Open' and the column value was 'Open', the Validation would evaluate to true.

Given that Validations that compare column values to Expressions only really work with single values in the Expression, use Select Lists where specific values are required. When Validations are more generic (null, not null, numeric, etc.) then Validations that do not look at Expressions work well.

As you can see, not everything about Websheets is going to be end user–friendly. The way Validations work and the way the Types are defined can be confusing to a user who doesn't have much development experience.

Figure 8.23. Data Grid Validation Types

The Row Tools are also interesting, although some of them are a little impractical. Figure 8-24 shows the controls available in a Websheet to manage data at a row level. The simple ones, Add Row and Delete Row, do exactly what they say they do. The Delete Rows control is useful in that it allows the user to mark records for deletion and then remove them all in one shot. Deleting rows in this manner requires that the Check Boxes in the Websheet be toggled on.

Figure 8.24. Data Grid Row controls

The other Row tools give you SQL-like capabilities in that you can modify multiple rows with one command. The Set Column Values control is interesting, but not overly practical for our Buglist application. This feature, shown in Figure 8-25, allows you to set a value in one or more rows very much like a simple update statement. You don't get to set a where clause, but you can specify either all rows, selected rows, or empty rows.

Figure 8.25. Data Grid Set Column Values control

The Replace row control can be very beneficial since it works like a Search and Replace feature in a text editor. You simply identify the column you want to update, the value to search for, and the value to replace when the search evaluates to true. As with the Set Column Values control, you can approximate a where clause using the All Rows or Selected Rows options. Figure 8-26 shows the Replace control.

Figure 8.26. Data Grid Replace Values control

The last of the Row controls is the Fill feature. This enables you to fill columns that are set to NULL with the value from the row above where the NULL values start. This control does not take into account selected rows. It starts at the top of the table and scans down until it finds a NULL value in the selected column, backs up one row, takes the value from the previous row, and begins replacing NULL values with the new value in all subsequent. This does not mean that all NULL values will be replaced with a single value, but explaining the process is difficult so the following examples (Listings 8-2 and 8-3) have been created:

bug_id description
------ ----------------------------------
1      logo occasionally does not display
2
3
4      Buttons don't work in Firefox
5

bug_id description
------ ----------------------------------
1      logo occasionally does not display
2      logo occasionally does not display
3      logo occasionally does not display
4      Buttons don't work in Firefox
5      Buttons don't work in Firefox

As you can see, the Fill process takes the last value prior to a NULL and uses that to fill the subsequent rows. When it encounters the next, NOT NULL value, it becomes the fill value for any additional rows that have NULL in the selected column. This process is not governed by what you can see on the page when you execute the Fill process, so this one can get away from you if don't know where all the NULL values exist.

Another very powerful feature of Data Grids is the ability to store Annotations (files, notes, links, and tags) in any row. The benefit of this feature is that a user could, for example, report a bug and attach either a screenshot or even a video capture of the error. They can also supply notes that allow them much more latitude in describing the error. The Data Grid does not include these objects by default, but the user can easily add them. Figure 8-27 shows the Edit screen for a row on a Data Grid.

Figure 8.27. Data Grid Annotations

As the user attaches objects to the Data Grid row, they are represented via additional sections in the Data Grid edit screen. Clicking on an attached file name or a link, as shown in Figure 8-28, immediately opens that object. Notes and Tags are displayed in line and can be viewed without navigating to another object or screen.

Figure 8.28. Data Grid Annotation view

As stated previously, Annotations are not included in Data Grids by default, but they can easily be displayed using the Select Column control on the Data Grid page. Figure 8-29 shows that the four columns for files, notes, links, and tags are actually available in any Data Grid, but are not automatically selected. We selected all four Annotation fields and added them to the Data Grid.

Figure 8.29. Data Grid Annotation field attributes

As soon as they are applied, the new columns appear and if any of the "new" columns contain data, icons show up to alert the user, as shown in Figure 8-30. All of the Annotation icons display pop-up text to help identify the contents and the user can click on any of them to go directly to the content (a file viewer for files, another browser window for a link, etc). This means that a Websheet provides limited document management capabilities.

Figure 8.30. Data Grid with Annotations

Now that we know how to use Data Grids, arguably the most important feature of a Websheet, we can start using the Buglist application like a user would. Up to this point, we have been interacting with the Websheet as an administrator, so let's change roles to a Contributor. The Contributor user is David so we are logged in to the Websheet as this user. First, we need to navigate back to the Buglist Websheet home page as shown in Figure 8-31. The first thing you should note is that there are only two options in the main toolbar (Page and Data). The Administration option is gone due to the role of the current user. Also of interest is that the Data Grid we just created is not visible on the home page, which is standard behavior. Data Grids are objects that can either be accessed directly (via the Data option on the main toolbar) or by being added to a page.

In Figure 8-31, you can see that the content of the Welcome section has been modified. This section was added for us when we created the Websheet. By using the Edit link in this section, we change the title to "Buglist" and added the text, "This is the Buglist Websheet" into the Content text area.

Figure 8.31. Buglist Websheet home page

To make the Buglist Websheet provide the same functionality as the original application, we have to start building pages. The home page was added automatically when the Websheet was created, so we can use that as a starting place and begin adding content. If you want a new page, you click on (you guessed it) New Page. For this example, we'll add some content to the home page using the New Section control. The Create Section process, shown in Figure 8-32, requires that you decide what type of information the section will contain.

Figure 8.32. Websheet section types

Each section type has specific features. The following is a summary of what each type does:

So, let's add the Buglist Data Grid we created to the home page so we can see the current list of bugs immediately when the application opens. This is done simply by choosing "Data" as the section type, selecting the appropriate Data Grid, and setting the title. The Create Data Section is shown in Figure 8-33. The Style selector, which has three settings, controls the look of the Data Grid but does not affect how it works.

Figure 8.33. Adding a Data Section to a Websheet

When we created the Buglist Data Grid, we saw an object that looked like an interactive report, but it had lots of added functionality. When you finish adding Buglist Data Grid to the home page, as shown in Figure 8-34, you will see the data, but the Data Grid functionality is not available. The "Manage" button is not here, the headings are not clickable. This is basically just a report that can be used to search for and view data. To get back to the fully functional Data Grid, you have to click on the "Data" option in the main toolbar and click on the Data Grid you want to use.

Figure 8.34. Buglist Websheet home page

Another nice feature we had in the original Buglist application was a page containing graphical representations of our data. So let's add a graph, but place it on a new page. Clicking on the Add Page control opens the Create Page process, as shown in Figure 8-35, which allows us to name the page and create an initial section. If you would rather add all the sections manually, just make the section title blank, as we have done, and the page will be built with no sections. We also need to select the Parent page so that the Navigation control, which we will build shortly, will know how to get to this page.

Figure 8.35. Websheet Create Page process

We can now add a chart to the new Buglist Charts page by adding a new section and selecting Chart as the type. For this example, we chose to build a Column chart. In Figure 8-36, you can see that you have two choices for the source (or query) on which to base the chart. You must use a report or Data Grid that was previously created. Since we only have a Data Grid called Buglist, we chose that as the source. The Section Title will default to the name of the report or Data Grid, but you can change that as necessary.

Figure 8.36. Websheet Chart section creation

Since you are using either a report or Data Grid as the source and both of those objects can have saved filters, you have the option of using either the default report or Data Grid settings or the saved filters. We did not create any filters on the Buglist Data Grid, so our only option was to select the Primary Report (Primary Default) setting, as shown in Figure 8-37.

Figure 8.37. Websheet Chart Source settings

The final step in creating the column chart allows you to identify the things like axis labels, the type of functions to apply (sum, count, etc.), and the columns to which the functions are applied. For this example, we want to produce a count of bugs reported by person. Figure 8-38 shows the settings that will provide the desired result. The Chart Label identifies which will be used to produce the x-axis labels (the name of the person who reported the bug). The Axis Title for Label can be anything you want, but it should be something relevant to the chart.

The Chart Value field will list all of the numeric columns in the source query and allow you to perform functions on those values. In our Data Grid, the only numeric column is the bug_id, which is irrelevant to this chart, and since we are doing a COUNT, we don't need to identify a Chart Value. You can apply a sort on the Chart Value, the Chart Label, or use the default sort that was applied to the Data Grid. We left ours at the default sort.

Figure 8.38. Websheet Chart Label settings

Following the verification step, the chart is produced in the new section, as shown in Figure 8-39. The chart has what we wanted on it, but there are a few things to note. First, not all of the Chart Labels (on the x-axis) are being displayed. This is default behavior as there is no way to fit all of the labels horizontally and there is no way to rotate the text of the labels. You can, however, see the label on each column by hovering the mouse as done in Figure 8-39. The units used to display the count on the y-axis is set to two decimals by default and you can't change that either. These may seem like shortcomings to a developer, but remember that this application is intended to be built by a user, so keeping it simple is the focus.

Figure 8.39. Display Column Labels by hovering the mouse over a Column

So we have a home page, a Data Grid, a column chart, and a custom authentication scheme. To put it all together, we only need to add one more thing: a Navigation Section. To do this, we'll have to add a new section to the home page between the Welcome and Buglist sections. Figure 8-40 shows the Navigation Section creation process. As noted earlier, you can add Navigation Sections for either sections or pages. Since our home page is not too long and we have a separate Chart page, we'll add a Page Navigation Section.

Figure 8.40. Websheet Navigation Section Type

You then just have to set the sequence of the Navigation Section so it fits where you want it and give it a title. You can also set a limit to the number of levels (shown as indentions on the Navigation tree) that the Navigation tree will display. If you go beyond this limit, users can always select a page individually using the Page selection on the main toolbar. Figure 8-41 shows the settings for our Page Navigation control.

Figure 8.41. Websheet Page Navigation settings

With all the pieces put together, the Buglist Websheet is a usable system. Figure 8-42 shows the finished product. Looking at it now, it would probably look better if the original Welcome section were removed so that the Navigation Section was at the top of the page.

Figure 8.42. Completed Websheet Application

Although we have an application that works like the one we originally developed using standard APEX features, it can be changed by those who are using it, as opposed to just a few developers. Remember that all the work we did to create the Buglist Websheet was done as a Contributor. We only logged in as the administrator to create the Access Control List.

The most flexible content generator, and therefore the most difficult to use, is the Text Section. It's difficult because it is like an HTML development tool that allows you to build content and expects you to know the Websheet-specific syntax that is described very nicely in the Help system. To see the markup examples, simply edit one of the Text sections (the Welcome section, for example) and click the "Markup Syntax" in the middle-right of the screen. As you can see, using this content generator allows for coding that is somewhat contrary to the "Wiki-like" description we gave to Websheets at the beginning of the chapter. You can forego these features and just add text like you would on a Wiki, but this is APEX, which means you're only a few keystrokes away from the database —the features are there if you want to use them.

One of the more interesting types of Websheet Markup syntax is SQL. You can access data based on your privileges and your ability to write SQL. The markup is a little different than what a developer is used to and the output is better than you may expect. For example, you can enter a SQL statement in the Text section using the following syntax:

[[SQL: select * from buglist where priority = 'Critical' ]]

The double brackets ([[ ... ]]) along with a prefix (SQL in this case) are a Websheet-only convention. The output generated by this SQL statement is more than just column headers and some data. It is actually formatted to look like an APEX report, as shown in Figure 8-43, and it comes with a search field! Just another example of Websheets doing something good for the non-developer. Remember that when we created a Data Grid and added it to a page section, it looked like the report we just created. When we created the report with a SQL statement, no Data Grid was created.

Figure 8.43. Text Section based on SQL

Now let's say that you want to put a logo on the home page. You have to first get the logo file into the Websheet application then include it in a page using a Text Section. The logical place to put the logo is in the Welcome section on the home page. Previously, we used the bug.jpg logo file so that all we needed to know was where that file was located to identify it for upload. To make the logo file available click the Add File icon. The Add File process, as shown Figure 8-44, will upload and make the file available to any page. The uploaded file can now be seen where it was uploaded in the "Files" menu at the bottom right of the page.

Figure 8.44. Adding an Image file to a Websheet

To add the bug.jpg file to the home page, edit the first section as shown in Figure 8-45, adding the reference to the bug.jpg image file. If you're not sure about the syntax to add an image, click on the Markup Syntax link to see examples. Notice that we set the width=100 attribute on our image tag. By doing this, the image is reduced in size proportionally, which makes it fit better in the application. Since our image has a white background, it won't look as nice as it could because the backgrounds of all sections are grey. To remedy this situation, you could use images (and image formats) that support transparent backgrounds.

Figure 8.45. Adding an Image to a Text section

So let's take a look at the semi-finished product. It is "semi" because a Websheet is never really finished. Users will continue to add content as long as the application is relevant. Figure 8-46 shows the Buglist Websheet application with the logo in the top section, a Navigation Section, and a report based on a Data Grid. All of this was done with very little coding. By virtue of adding a file to the Buglist Websheet, a new option, Annotations, has been added to the main toolbar. Clicking this link will allow you to see files, tags, notes, and links that have been added to the application.

Figure 8.46. Websheet Application with Logo

The other Annotations you can add to your pages are Tags and Notes. Tags are used to describe the content of the page and operate like meta-tags in a standard HTML application. The benefit to using tags is that it makes your pages more searchable. Notes are just free text. Be aware that Notes and Tags are attached to the screen at the bottom right. There is no limit to the number of Notes and Tags you can create, so don't be surprised if pages get really long. Also, notes are deleted using the same mechanism as images and there is no warning that a Tag or Note is about to be deleted.

So there you have it. A working application that required very little coding (although we did steal the authentication mechanism from the original system) and provides the functionality to support the bug tracking community. Along with standard application features, we also displayed the ability for users to add new pages, notes, charts, and more.