Adding an FMP Spotlight Filter

We like to call it FileMaker Spotlight! The great thing about FOCUS is that it has some great filtering examples already built in, and it has a very structured way of adding filtering.

Filtering is also a great way to ensure optimal database performance. In FOCUS, we also provide a ‘reveal flag’ for the table. What this flag allows you to do is to reveal only those records that match your filter, by constraining data to match your criteria. If the reveal flag is turned off, match records will start showing up only when you type in some values into the filter field. This is similar to the behavior you see in Apple Directory. In the case of a few hundred records probably not necessary, but in the case of thousands of records, you might want to use this feature.

In the PROJECT table add the following calculation field:

CT_FILTER (CT = Calculation, Result TEXT)

With the following calculation:

BuildIndex ( NAME ; "Name" )

BuildIndex is a custom function and it takes 2
parameters.

BuildIndex (fieldContents; fieldname)

fieldContents & ¶ &

fieldName & " " & fieldContents
& ¶ &

IndexSpaces ( fieldContents ) & ¶ &

PermutateItem ( fieldName & " " ;
IndexSpaces ( fieldContents ) ; 1 )

The field that you want to have broken out for filtering and the name of the field – this is particularly useful when you want to add more than one field into your filtering. For example if we added a project STATUS field, then our calculation in CT_FILTER would look like this:

BuildIndex ( NAME ; "Name" ) &

BuildIndex ( STATUS ; "Status" )

Something else to note when filtering in this way is that this field will then hold the index, and as such you don’t need to index the NAME or STATUS field.

Now that you have a filter field, we’ll need to add fields in the FOCUS table and create a relationship that serves as our filtered portal of PROJECTS. The easiest way to accomplish this is to simply duplicate one of the other pair of fields and rename them to includethe name of the table (in this case PROJECT).

CT_FILTER_LOW_PROJECT

CT_FILTER_HIGH_PROJECT

The calculation for the low value is the following:

FilterLow ( ExtractTableName ( GetFieldName ( Self ) ) )

The calculation for the high value is the following:
Won’t go into too much detail here about this calculation – all you have to do though is change the table name and the rest is handled by FOCUS.

Let ( [

#_table
= "PROJECT" ;

#_index
= TableIndex ( #_table ) ;

#_filter
= UIN_FOCUS::GT_FILTER [ #_index ] ;

#_reveal
= GN_FILTER_FLAG_REVEAL [ #_index ] ;

#_field
= GT_FILTER_FIELD [ #_index ]

] ;

Case (

#_reveal = 1; // All records are showing

If (

IsEmpty ( #_field ) or #_field =
FilterFieldDefault;

#_filter & FilterLimitHigh;

If ( IsEmpty ( #_filter ); #_filter
& FilterLimitHigh ; #_field & " " & #_filter &
FilterLimitHigh )

);

#_reveal = 0; // No records are showing

If (

IsEmpty ( #_field ) or #_field =
FilterFieldDefault;

If ( IsEmpty ( #_filter );
""; #_filter & FilterLimitHigh );

If ( IsEmpty ( #_filter );
""; #_field & " " & #_filter & FilterLimitHigh)

)

)

)

Once you have your fields defined, you’ll want to add the table occurrence to the graph. In this case, you’ll be adding FIL_PROJECT and creating the following relationship between the PROJECT table and the FOCUS table.