Best way to filter portal? (Address manager)

[liltbrockie]

Hi guys..

I'm trying to make a portal that shows only matching records based on details someone has entered into address fields in an enquiry...

So we have;

Surname

Firstname

Address line 1

City

County

Postcode

..

I want the user to be able to enter any info they have into the available fields and the portal automatically filter accordingly

Am I going to have to make a long case statement along the lines of:

Case(

if(not isempty(address line1) and isempty("the other fields"); patterncount(address line 1; address line 1 related field );

if(not isempty(address line1) and not isempty(town) and isempty("the other fields"); patterncount(address line 1; address line 1 related field ) and patterncount(town; town related field)

etc etc...

so about 36 different case statements to account for all the different ways they could be search for an address...

is this the most efficient method?

[comment]

I don't understand your proposed method, even supposing it is feasible. What would such calculation achieve? Your profile shows version 10; portal filtering is a version 11 feature. In previous versions, the only way to filter a portal is by filtering the relationship.

Is there a reason not to perform a find instead?

[liltbrockie]

sorry ignore what it says in the side panel.. im on filemaker 11.

Imagine, if you will, a layout where there exists fields representing the address as described above (these fields are there for the sole purpose of performing the portal filtration). and there is a portal that displays the list of addresses for that particular customer id.

What i am trying to do is allow for the user to automatically view the matching records based on what they have entered in the search fields.. so as they type in these fields, the portal automatically refreshes..

I know how to make the portal refresh im just trying to work out if i need one huge long case statement as described in my previous post to get it to work or if there was a more efficient way of catching all the possible solutions

[comment]

I still don't see the purpose of this part:

and isempty("the other fields");

Assuming you want to AND the entered criteria, I believe the filter should look something like:

( IsEmpty ( Parent::Surname  ) or PatternCount ( Child::Surname ; Parent::Surname  ) )

and

( IsEmpty ( Parent::Firstname ) or PatternCount ( Child::Firstname ; Parent::Firstname ) )

and

( IsEmpty ( Parent::Address ) or PatternCount ( Child::Address ; Parent::Address ) )

and

...

---

Whether this is efficient or not depends on the number of related records * the number of fields involved; these are all unstored calculations that need to be performed "on-the-fly" for the entire related set.

Edited October 10, 2010 by Guest

[liltbrockie]

I still don't see the purpose of this part:

Assuming you want to AND the entered criteria, I believe the filter should look something like:

( IsEmpty ( Parent::Surname  ) or PatternCount ( Child::Surname ; Parent::Surname  ) )

and

( IsEmpty ( Parent::Firstname ) or PatternCount ( Child::Firstname ; Parent::Firstname ) )

and

( IsEmpty ( Parent::Address ) or PatternCount ( Child::Address ; Parent::Address ) )

and

...

---

Whether this is efficient or not depends on the number of related records * the number of fields involved; these are all unstored calculations that need to be performed "on-the-fly" for the entire related set.

Genius... thank you so much... i was over complicating things a bit there!

Thats just what I needed.. thanks again for your help.

[comment]

One more thing: if the search fields are not global, then any user setting up a filter will set it up for all other users, too.

[liltbrockie]

Well this is slow on my 50,000+ addresses!

Its just about bearable though... any quick and easy way to speed things up?

[comment]

any quick and easy way to speed things up?

I don't think so. With this amount of records, I'd move to either a scripted find or a filtered relationship.

Note that a "pattern count" type of find (i.e. *searchphrase*) cannot use the index, so it too may be somewhat slow. If you absolutely require this feature, consider storing an "exploded" version of each field, e.g. "Smith" produces:

Smith

Smit

mith

Smi

mit

ith

Sm

mi

it

th

S

m

i

t

h