Filtered Portals

Allowing users to select rows within portals allows portals to serve multiple functions: They can both display information to the user and, 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. Think of it somewhat as performing a find in List view.

For example, imagine a case in which 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 either to a List view, supported by Find mode to reduce the found set, or to a filtered portal.

Generally, filters enable users to dynamically specify match criteriaoften a status or type field of some kindand then view only portal rows that match those criteria. In the real estate example you've been following in this chapter, listings could be set as 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.11).

Figure 16.11. 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.11, a gFilter_ListingStatus will work well. When it's placed 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.12).

Figure 16.12. 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 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.

Multivalue and Filtered Portals

Consider a second scenario in which you might want to filter on two criteria. To accomplish this task, add a second criterion to your match relationship, as shown in Figure 16.13.

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

The relationship shown in Figure 16.13 filters on match values for both status and date fields. A filter acts on both fields. Notice the images/U2265.jpg border=0> 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/2006.

Multivalue 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 criteria. 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 earlier 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, Multivalue Or Filtered Portals

Multivalue 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

Corbin Daniels

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 in which you have the following fields in a contact table:

• gMatchFieldA text field stored globally where users type whatever portion of text by which they'd like to match.
• NameFirstThe 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

NameFieldzzz, calculation = [NameField & "zzz"]

and then create a calculation in which gMatchField <= NameFieldzzz, a portal using this sample comparison will display all names that are comparatively less than "Coleenzzz." This gets you only halfway 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 in which 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.14.

Figure 16.14. Notice that relationships can accept multiple And criteria

Consider the NameFieldaaa field. When empty, it returns zeroa value that no text string will be less than. 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.15 demonstrates the case in which both LastName and FirstName fields can be used.

Figure 16.15. 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 was one often used with prior versions of FileMaker Pro, before comparative operators were introduced into relationship definitions, and enables you to assemble a true Or condition for a relationship by making use of multi-key matching. It is an alternative to the earlier approach, but it has the added benefit of giving you the ability to add additional or custom values (like "all") into your match criteria. The technique still applies, and using a custom function to explode match values is even easier to utilize.

Consider the same example from the preceding section, in which 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 a contact record's NameFirst field or NameLast field, or the domain from a ContactEmail field. In Figure 16.16, notice that matches are valid for all three data columns.

Figure 16.16. 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 task, use exactly the same multi-key technique. Rather than concatenating 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 that 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 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.