Filtered Portals

Allowing users to select rows within portals allows portals to serve multiple functions: They can both display information to the user and also, through selections, act on that data somehow. However, in this chapter thus far we have dealt only with portals that are driven by fixed relationships. The data displayed remains constant and changes only when the data itself changes (when records are added to or deleted from a database.)

A portal filter extends your capabilities to allow you to dynamically alter or constrain the rows of data displayed in a portal.

For example, imagine a case where you have hundreds of customers in a database and a portal displaying all customers. Your portal actively shows only a modest fraction of all customers in your system and forces users to scroll quite a bit. (Most users don't have monitors that can support a portal hundreds of rows tall.) To solve this usability problem, you can turn to either a List view, supported by Find mode to reduce the found set, or to a filtered portal.

Generally, filters enable users to dynamically specify a match criteria ”often a status or type field of some kind ”and then view only portal rows that match that criteria. In the real estate example you've been following in this chapter, listings are "active," "sale pending," or "sold." Using a portal filter, you could allow users viewing a set of listings in a portal to specify a particular status and have the contents of the portal change to reflect their choice (see Figure 16.12).

Figure 16.12. In this example, choosing from the available value list alters the rows displayed in the portal.

To create a simple filtered portal, first create a globally stored text field for holding the value a user chooses. In the example shown in Figure 16.12, a gFilter_ListingStatus will work well. When placing it on a layout, we'd recommend attaching a value list with "active," "sale pending," and "sold" for ease of use.

The second element to this technique is modifying the relationship between agents and listings to take the filter into account. The relationship was originally set up with a cross-product operator so that all listings could be displayed. If you replace that with a match of gFilter_ListingStatus to ListingStatus , only listings that match the currently specified status are considered related records (see Figure 16.13).

Figure 16.13. The assumption this relationship makes is that gFilter_ListingStatus will change based on user preference.

NOTE

gFilter_ListingStatus is a global and is therefore unindexable; however, from the perspective of its table, it is possible to use this field to relate to another table. Only the "far" table being viewed in a relationship requires an indexed match field (in this case, ListingFiltered ).

By comparing the status field from the listing table with the gFilter_ListingStatus , you have created a relationship that changes based on the value a user selects in gFilter_ListingStatus . Keep in mind, if gFilter_ListingStatus is empty, so too will the portal be empty.

Multi-value "and" Filtered Portals

Consider a second scenario where you might want to filter on two criteria. To accomplish such, add a second criterion to your match relationship, as shown in Figure 16.14.

Figure 16.14. This example makes use of two match criteria.

The relationship shown in Figure 16.14 filters on match values for both status and date fields. A filter acts on both fields. Notice the operator on the date. You can use a combination of match fields and relationship operators to display a wide range of related records.

This enables you to create a filtered portal with a range of possible, mutually inclusive filters. You can allow someone to select, say, all active listings for dates greater than 4/1/2004.

Multi-value "or" Filtered Portals

A natural extension to the example you've been exploring is a case wherein you want the portal to match one or another criterion but not necessarily both. For example, what if you alter the value list attached to gFilter_ListingStatus to include "all" as an option? Here you will want listings for an agent displayed based on his status, unless the user selects All, in which case all listings for a given agent should be displayed.

You need to apply the technique for a multi-key as discussed previously. In the previous example, the multi-key was applied to the front end of the relationship. This time we apply it to the back end.

Create a calculation field in the listings table like so:

 listingStatus & "" & "all" 

Now change the listing side of the relationship from listingStatus to the new calculation field. This is the only change (beyond adding the "all" option to your filter value list) that you need to make to turn this into an or filter. This relationship resolves where values in the status list include one of the three statuses or "all" .

Ranged, Multi-value "or" Filtered Portals

Multi-value or filters can become quite powerful. Consider a new example: Imagine wanting to provide users with a filtered list of contact names from a pool of hundreds or even thousands of records in a contact table. Instead of creating a value list by which to filter, allow your users to type a few characters of text into a filter field. If a user enters co , your ( presumed ) contacts returned in the portal might be a list like this:

Coleen Neff

Conrad Moyer

Cordelia Henrich

If someone enters col , the list might return only Coleen Neff.

The way to approach this is to use the relationship operators for comparing ranges of text. Consider an example where you have the following fields in a contact table:

• gMatchField ” A text field stored globally where users type whatever portion of text by which they'd like to match.

• NameFirst ” The first name of your contact person.

You now need to compare the text co against some set of values to get the record for Coleen Neff to appear from the list.

Consider that a < c is a valid expression in FileMaker Pro. By relying on text string comparisons, you can create a relationship comparing your gMatchField to NameField . If you create a calculation like so:

 NameFieldzzz, calculation = [NameField & "zzz"] 

And then create a calculation where gMatchField <= NameFieldzzz , a portal using this example comparison will display all names that are comparatively less than "Coleenzzz."

This gets you only half of the way there. "Anthony" and "Beth" are, for example, comparatively less than "Coleenzzz." You need to create a second calculation:

 NameFieldaaa, calculation = [If (IsEmpty (gMatchField); "0"; gMatchField)] 

In cases where gMatchField has a value, say "co" , the comparisons now would be:

• "Coleen" >= "co" and "Coleen" <= "cozzz"

• "Conrad" >= "co" and "Conrad" <= "cozzz"

• "Cordelia" >= "co" and "Cordelia" <= "cozzz"

In this example, "Anthony" and "Beth" would both not be valid match conditions. Both are comparatively less than "co."

The relationship driving a portal of this nature is shown in Figure 16.15.

Figure 16.15. Notice that relationships can accept multiple "and" criteria.

Consider the NameFieldaaa field. When empty, it returns a value that no text string will be less than 0. This then means that when gMatchField is empty, all the records in your contact table will be valid matches and all records will show in the portal.

You can opt to add further fields to drive the relationship in the portal by adding additional pairs of gMatchField and its two comparison fields. The relationship shown in Figure 16.16 demonstrates the case where both LastName and FirstName fields can be used.

Figure 16.16. This relationship references two match fields. If a user types fractions of text in gMatch_firstname and gMatch_lastname , the portal filters on both values.

Exploded Key Filtered Portals

The last technique is one often used with prior versions of FileMaker Pro and enables you to assemble a true "or" condition for a relationship by making use of multi-key matching. The technique still applies, and using a custom function to explode match values is even easier to utilize.

There's a large caveat to using this technique: It dramatically increases the size of your database indices. However, if you want a single gMatchField to operate on multiple related fields, this is a solid approach.

Consider the same example from the preceding section, where you expect a user to type some fraction of text in a gMatchField text field and you want your filtered portal to display matches from either a contact record's NameFirst field, NameLast field, or the domain from a ContactEmail field. In Figure 16.17, notice that matches are valid for all three data columns .

Figure 16.17. Notice that the filter is being applied to three fields: first name, last name, and email address. The records returned match the filter in at least one case.

To accomplish this, use exactly the same multi-key technique. Rather than concatenate the values from one field, the match field concatenates the values from multiple fields. The calculation looks like this:

 Let ([      atPosition = Position (Email; "@"; 1; 1) + 1 ] ; Left (FirstName; 4) & "" & Left (FirstName; 3) & "" & Left (FirstName; 2) & "" & Left (FirstName; 1) & "" & Left (LastName; 4) & "" & Left (LastName; 3) & "" & Left (LastName; 2) & "" & Left (LastName; 1) & "" & Left (Email; 4) & "" & Left (Email; 3) & "" & Left (Email; 2) & "" & Left (Email; 1) & "" & Middle (Email; atPosition; 4) & "" & Middle (Email; atPosition; 3) & "" & Middle (Email; atPosition; 2) & "" & Middle (Email; atPosition; 1) & "" & FirstName & "" & LastName & "" & Email & "" & "all") 

This calculation, if you can't already guess, resolves a match based on the first four characters of a contact's first name, last name, email address start, or email address domain (the characters following the "@" symbol). Notice the end of the calculation includes the full text of all three fields, along with an "all" to support users wanting to see all contacts in their databases.

Remember that the far side of a relationship requires an indexed field. This means you cannot include related data in your multi-key.

This calculation shows explicitly the values you need to assemble for a multi-key match field, and it's valuable to know how to do it as a straight calculation field, but we'd suggest building a custom function for assembling match fields. Using the recursive capabilities of custom functions and the fact that they are reusable throughout a solution, you can write one custom "explode text" function to take the parameters of however many fields you want to feed them, and never again have to write a calculation like the one we've shown here.

To learn more about how to build custom functions, see Chapter 14, "Specialized Calculation Functions," p. 381 .