To describe the drill-down technique in more detail, we'll use another example from the Action Items Filtering database. The database tracks information about projects -- a project description, start date and project manager -- as well as separate action item documents pertaining to each project. We'd like to give the user a "private" view showing just her projects -- the ones she's assigned to as project manager. She should also be able to click on a link next to any one of the project names and get a listing of action items for that project. In this way, she is "drilling down" to the action items by way of the projects.

Unfortunately, private views are not supported for Web clients. Of course, a standard view of action items categorized by project manager and project would provide the drill-down capability, but it would show all projects. With our approach, the user is only seeing her data.

We'll describe the technique using two examples. In the first, we simply present the user with a list of his projects, like this:

We accomplish this using the following form:

This form is used only for display purposes -- no data is entered here. So, we launch the form from the Home form using a URL that ends in ?ReadForm rather than ?OpenForm, so that we don't get a Submit button at the bottom.

The three fields ProjectNamesIDs, ProjectIDs, and ProjectNames give us the project documents to list for the user. The formula for ProjectNamesIDs looks up the projects for that user in a hidden view categorized by project manager:

@DbLookup ("Notes" : "NoCache" ; @DbName; "(ProjectsByManager)"; @UserName; 4)

The fourth column of the (ProjectsByManager) view contains the names and IDs in one column. This way, we need to do only one @DbLookup to get both the project document IDs and names. This can save considerably on performance, particularly in large databases and where you are looking up several values. Here is the hidden view:

Now, we can parse out the names from the IDs in the ProjectNames and ProjectIDs fields. Here is the formula for ProjectNames:

@Left(ProjectNamesIDs; "~")

Now, we want to display this list of projects as links. We want to display the project names, but use the project document IDs to open the project when the user clicks on it. YourProjects is a multi-value computed-for-display field on the form, with separate entries set to display on separate lines (in the Options tab of the field properties). Here is the field formula:

"<A HREF=/" + ThisDBW + "/AllByDocID/" + ProjectIDs + ">" + ProjectNames + "</A>"

The HTML anchor tag (hotlink) opens a URL when the user clicks on a project name. The URL is the project document ID within the AllByDocID view (a hidden view sorting all documents in the database by their document ID).

This one line of code produces not one anchor-tagged project name, but a whole list of them, because the list processing feature of the Notes formula language automatically applies this formula to every item in the list. Because we defined ProjectNames to contain multiple values with each displaying on a separate line, our list of links will display as if it were a view, as it did in the screen capture.

Drilling down to the Action Items
We've defined a field that displays a list of projects, with each project name as a link to the project document. But we also want to let the user drill down to see a list of action items for any project. We could certainly provide a link within the project document to do this, but we may also want to have an link next to each project name in the list, which takes the user directly to the action items for the project, as shown below:

The user can click on the Action Items link -- although you could easily use an icon rather than the text "Action Items" -- next to any project. If you click on the "Action Items" link to the right of "Refinance Your Mortgage," you see this:

You can then open any of the three action items for that project.

The way we create the Action Items links is by using a second @DbLookup. Here is the "Web Drill Down 2" form in design mode:

The new field is ProjectActionItems, and is now in a two-column table next to YourProjects. It is also computed-for-display -- we need to look up data, but it is not saved anywhere. Here is its formula:

"<A HREF=/" + ThisDBW + "/ProjectActionItemsByProjectID/" + ProjectIDs + ">Action Items</A>"

As in the YourProjects formula, this one also takes the user to the project document, but it does so via the hidden view (ProjectActionItemsByProjectID), which uses a form formula to open an alternate form, one which lists action items for the project. So when you open a document using that view, you don't see the regular project information, only links to action items for that project.

If you haven't seen form formulas before, you define them in the view properties for the view. Here is the form formula for the hidden view:

The alternate form is called ProjectActionItem, and looks like this:

The ProjectActionItems field is another list of links, and contains this formula:

ItemNames := @DbLookup ("Notes": "NoCache" ; @DbName; "(ActionItemsByProject)"; ProjectName; 2);
ItemIDs := @DbLookup ("Notes": "NoCache" ; @DbName; "(ActionItemsByProject)"; ProjectName; 3);
"<A HREF=/" + ThisDBW + "/AllByDocID/" + ItemIDs + ">" + ItemNames + "</A>"

This is similar to our lists of projects on the earlier form. We look up the action items for this project. We look up both the names and document IDs -- notice we did not use our performance-improving technique of a single @DbLookup here -- and display those action items in a list.

We have used several new hidden views and alternate forms to implement this technique. Remember that every view is another index in your database, so from a performance perspective, views are expensive. As long as we try to use a single hidden view in as many places as possible, we should be able to design applications that are both powerful in their data filtering capabilities and that perform well.

Now, try Technique #3: Readers Fields.

Copyright 1998 Iris Associates, Inc. All rights reserved.