Portal Filtered By String

[FredP]

I have seen this before, but have never done one. I would like to have a global.filter for a portal, and I would like it to work like a search filter.

 

IE: With nothing in the field, it shows all records.

 

Type S and it shows only records with an S in the title

Type Sa and it shows only records with Sa in the title.

all the way to Saturday, and the portal would show only records with Saturday in the title.

 

 

Does someone have an example of this sort of portal they could share?

 

thanks

Fred

[Ender]

Hey Fred,

 

Check my detailed portal filtering description here:

 

http://fmforums.com/forum/showpost.php?post/183594/

[FredP]

Unfortunately, i cannot make sense of that. I guess its a bit over my head. Are there any more simplistic explanations?

 

Fred

[Weetbicks]

The locator key field required is the most complex bit, so will leave that till later.

 

For now, You have a table (for the same of example we'll make it Home) , and another table which is the portal (Contacts).

 

You'll need 2 fields on Home:

Filter Entry

Filter Key

 

Filter entry is the search field you type your "S", "Sa" into etc...

 

Filter Key is a text calculation, as follows:

 

if ( isEmpty ( Filter Entry ) ; "All" ; Filter Entry )

 

Ensure on this calc that "do not evaluate if all referenced fields empty" is UNTICKED.

 

Okay thats the home table setup, next you need to add a field to Contacts, we will call this field "Locator Key"

 

Locator key is a calcualtion, and it is what the filter key looks thru to find matching contacts (thus displaying them in the portal).

 

Here is what the calculation is going to look like:

 

"All" & "¶" &

FilterKeyBuilder ( Day )

 

..

 

First line puts "All" , so that every single contact, regardless of anything, will display in the portal if you leave the filter entry field empty (see the filter key calc... it will relate All -> All in the relationship..)

 

Second line is a ficticious custom function I have named FilterKeyBuilder. The reaosn I have made it into a custom function, is because to build a complex locator key like you have mentioned, is best done using a custom function that incorporates recursion.

 

---

 

If you have access to FileMaker 8 Advanced or later, you are able to create this custom function (very much recommended)...

 

Name: FilterKeyBuilder

Parameters: Text

Calc:

 

Substitute(

Case ( Length ( text ) > 1 ;

FilterKeyBuilder ( Left ( text ; Length ( text ) - 1 ) ) & "¶" & text ;

text

)

; "¶ " ;""

)

 

 

----

 

It will build a list of values from the text passed. For example, FilterKeyBuilder ( "Saturday" ) will spit out:

 

S

Sa

Sat

Satu

Satur

Saturd

Saturda

Saturday

 

, put this in your locator key and you get all this + the "All" first line

 

Note: Locator Key field must be stored (indexed).

 

-----

 

Okay, putting it all together, you'll have a relationship from Home to Contacts using:

 

Filter Key = Locator Key

 

---

 

thats all thats required. Some examples:

 

1. Leave filter entry field empty - Filter Key field evaluates to "All" and thus matches with all contacts

 

2. Type "Sat" into the filter field - Filter Key field evalutes to "Sat" , and matches with all contact records whose locator key contains "Sat" . This would be all contacts whose "Day" field begins with "Sat". Since only one day beigns with Sat, this is obviously all contacts whose Day field contains Saturday.

 

---

 

Alternatively you can use more than just one field in the locator key, pass whatver you want, ie:

 

"All" & "¶" &

FilterKeyBuilder ( First Name ) & "¶" &

FilterKeyBuilder ( Last Name ) & "¶" &

FilterKeyBuilder ( Day )

 

Now you can type "John" into the filter entry field, and find all contacts whose name is John....

[FredP]

OK Very Cool. I got that working. Thank you.

 

I think I have seen something about this now, it seems I may need a little different filter key builder.

 

If i have 2 records, one with "Snow Comes" as the data, and one as "Snow Melts" as the data, for some reason, if i search for Snow, it only finds one of them.

 

Odd?

 

Fred

[FredP]

Well, I bet this isnt the best way to do it, but its working for now,until someone shares a better way, but I am simply substituting any spaces with pilcrows before the word explosion.

 

"All" & "¶" & FilterKeyBuilder ( Substitute ( Data & " " & Name; " "; "¶"))

 

And then the with field name of "Example Name" and a data field with "Data goes here." I get the following exploded view:

All

D

Da

Dat

Data

Data

 

Data

g

Data

go

Data

goe

Data

goes

Data

goes

 

Data

goes

h

Data

goes

he

Data

goes

her

Data

goes

here

Data

goes

here.

Data

goes

here.

 

Data

goes

here.

E

Data

goes

here.

Ex

Data

goes

here.

Exa

Data

goes

here.

Exam

Data

goes

here.

Examp

Data

goes

here.

Exampl

Data

goes

here.

Example

Data

goes

here.

Example

 

Data

goes

here.

Example

N

Data

goes

here.

Example

Na

Data

goes

here.

Example

Nam

Data

goes

here.

Example

Name

 

which isnt great, but it seems to work for now.