How to "Filter" a Portal Relationship?

[Shadowfax]

Ello everyone, slight trouble building this "simple" find feature.

I have a Master file with a portal that shows records from the Contacts file. There's also several blank fields on the Master labeled after the contact-fields I'd like to search ('Last Name', 'First Name', etc).

How can I enter data into these filter fields, and have the portal automatically show the correct records? Obviously through a relationship, but how?

Now in other database programs (such as MS-Access, or anything with SQL), it'd be just "WHERE LastName = gblFilterLastName OR FirstName = gblFilterFirstName...". Very simple. Is there some FileMaker alternative or trick I should know about (since v6 doesn't have an OR relationship)?

Now I could have it run a Find, but getting the info to Master would be trouble. Marking all the results (through ReplaceContents) would make sense, but this is a shared database, and everyone would see each other's finds. Stepping through each record and compiling a list in a global Master field would work too, but that takes five seconds over a network (which is an eternity). I know: lame ideas -- which is why I'm here today.

Any help would be greatly appreciated! :help:

Edited September 22, 2006 by Guest
Fixed the Subject

[Ender]

Sounds like you should just have one global Name field on the parent side, matching on a calc field, where the calc is First Name & " " & Last Name.

[comment]

I think you will need two calculation fields: in the Master table, cNames =

"Last:" & gblFilterLastName & ¶ & "First:" & gblFilterFirstName

In the Child table, cNames =

"Last:" & LastName & ¶ & "First:" & FirstName

Base your relationship on these two fields.

[Shadowfax]

Sounds like you should just have one global Name field on the parent side, matching on a calc field, where the calc is First Name & " " & Last Name.

Not a bad idea! Except I have six filter fields. Last name, first name, biz title, category, and related show. Your combination would work for two fields, but six has 720 combinations. So sad! :

I think you will need two calculation fields: in the Master table, cNames =

"Last:" & gblFilterLastName & ¶ & "First:" & gblFilterFirstName

In the Child table, cNames =

"Last:" & LastName & ¶ & "First:" & FirstName

Base your relationship on these two fields.

Hahahaha, oh boy.... first off, let me apologize for my obvious mistake. When I wrote that earlier this morning, it all made sense. But now that I'm awake, not so much! :

Your solution is the perfect OR solution. But this is not a simple OR command, I was totally offkey with that thought. It's more of a "If( not IsEmpty(gblFilterFirstName), FirstName = gblFilterFirstName, 1) AND If( not IsEmpty(gblFilterLastName), LastName = gblFilterLastName, 1)" relationship.

So sorry for the confusion, I'll make sure to have my breakfast and soda before posting a question again. :wired:

[comment]

So you have figured it out (it's a simple fix) or are you still asking?

[Ender]

Not a bad idea! Except I have six filter fields. Last name, first name, biz title, category, and related show. Your combination would work for two fields, but six has 720 combinations.

It's a little easier if you tell us what the goal is from the beginning.

I'm not sure where you get the 720 combinations. The method I suggested works for any number of fields, but to avoid false positives, you need matching that's tied to the right field, as in comment's suggestion.

[Shadowfax]

So you have figured it out (it's a simple fix) or are you still asking?

Nope, still asking. I've done a lot of complicated relationships before, but I'm scratching my head over this one.

It's a little easier if you tell us what the goal is from the beginning.

I'm not sure where you get the 720 combinations. The method I suggested works for any number of fields, but to avoid false positives, you need matching that's tied to the right field, as in comment's suggestion.

Alrighty, I'll try to communicate a little better.....

Goal: Filter a portal by relationship using six "filter-fields" that may or may not have info.

Your idea is good, but like you said, it doesn't always match things to the correct field. They sort of mix-and-match (and there's 720 ways to mix 6 fields... ignore me, math wiz ).

Perhaps I need to reword this: How does one get a relationship to be dynamic? Where it doesn't require data in all the filter fields, it sort of ignores blanks? On the front-end (Master), I could just treat blanks like an "All", but can I write a back-end (Contacts) calculation that will work?

Hope this makes sense. If there's any more info you need, just me know. :

[Shadowfax]

The method I suggested works for any number of fields, but to avoid false positives, you need matching that's tied to the right field, as in comment's suggestion.

Hmm... maybe you just gave me the answer. What if I used comment's suggestion, but used a " " in place of ¶?

In the Master table, cNames =

"Last:" & gblFilterLastName & " First:" & gblFilterFirstName

In the Child table, cNames =

"Last:" & LastName & " First:" & FirstName

Would FileMaker work with something like that? Don't have time at this particular moment, but I'll try it out later today. Wish me luck!

[Ender]

No, that wouldn't work. It needs a multi-line.

Actually that space in my suggestion should be a ¶ for Last Names to match.

[comment]

Wait a second, something doesn't make sense here. In my method above:

John & Smith matches:

- John Smith

- John Anybody

- Any Smith

_blank_ & Smith matches:

- John Smith

- Any Smith

John & _blank_ matches:

- John Smith

- John Anybody

Now I am not sure I understand what you mean by

It's more of a "If( not IsEmpty(gblFilterFirstNam e), FirstName = gblFilterFirstName, 1) AND If( not IsEmpty(gblFilterLastName ), LastName = gblFilterLastName, 1)" relationship.

It seems like if one the fields is blank, you would want it to match ANY value. But this is an OR relationship, so that would mean any blank field would match ALL records - regardless of the other fields. I think you need to clarify the requirements once again. Meanwhile, I'd suggest these two threads:

http://fmforums.com/forum/showtopic.php?tid/171912

http://fmforums.com/forum/showtopic.php?tid/172518/

[Shadowfax]

I think you need to clarify the requirements once again.

Haha, alright then... but first, understand that I'm not creating an OR relationship. That was totally offkey, feel free to ignore all that talk.

But I see that someone else had a question much like mine, forgive me for shamelessly copying his words (it was well written).

I want to create a multi-criteria portal and I'm having trouble with the logic.

I have 6 ordinary text fields in a DB. I want to use these fields based on their values to 'constrain' or 'filter' the portal.

IOW ... I want to enter a word in the 1st field and see all matching records in the portal. When I enter a word in the 2nd field, I'd like the portal to 'constrain' the matching records. And the same for the 3rd field, etc.

That's hard enough for me ... and I haven't managed it yet ... BUT!

I'd also like it, if a filter field is left blank, all records should match for that filter. That way, I could leave the 1st field blank and find matching records based on the filled 2nd and/or 3rd fields.

Can anyone give me an idea of how I should go about this?

I'm just looking for just that... portal filtering, plain and simple.

I know this'd be easy with newer FileMaker versions, they allow multi-key relationships. But I have a challenge, and need something complex out of a simpler relationship model.

I have a couple ideas on how to do it, but neither is very good (check out the attached file to see them in action).

Method 1: Find and Go Global

Run a Normal find, and save a list of all the results (at least, their IDs) in a Global text field.

The portal just looks up Master::gblContactList = Contacts::ContactID.

But when dealing with 1000's of records over networking-speeds, it takes waaaay too long to find, sort, and retrieve everything (five-seconds, which equals computer-eternity).

Method 2: The Simple Relationship

A great and fast solution, I can just use calculation fields for the relationship. Examples....

Master:

If( Length(FirstName & LastName) = 0, "All",

Trim(

Case( not IsEmpty(FirstName), "First:" & FirstName & " ") &

Case( not IsEmpty(LastName), "Last:" & LastName))

Contacts:

"First:" & FirstName & "¶" &

"Last:" & LastName & "¶" &

"First:" & FirstName & " Last:" & LastName & "¶" &

"All"

At least, that would work for two fields; it has 4 combinations. With six fields, there's a full 32 combinations; but can you imagine 2000 records each with a 32-line key? That would be a very very large index.

(In case you're wondering where 720 went, that was the "mix-and-match" scenario. But since FileMaker uses multi-line, not spacebar-marks, that could never happen anyway. So more math crap... ignore ).

Does any of this make sense? I'm probably barking up the wrong tree; I should just take a breathe and check out Google. Portal filtering is all I seek... if there's a better way of getting there, I'm all for it. Your ideas are most welcome.

FilterPortal.zip

[comment]

Actually, I think that with 6 fields you are looking at 64 (2^6) possible combinations (each field either specified or not). I don't think there's a way around it, large index or not. If you want your master file to be flexible, the child records have to be ready for any combination thrown at them.

If pushed, you could go with Ender's original suggestion, i.e. just concatenate the 6 fields using a "¶" as the separator. It will produce a few false positives - in some cases, this might be considered an advantage.

Edited September 24, 2006 by Guest

[Shadowfax]

Actually, I think that with 6 fields you are looking at 64 (2^6) possible combinations (each field either specified or not).

Haha, that's true... thanks for reminding me. :

I don't think there's a way around it, large index or not. If you want your master file to be flexible, the child records have to be ready for any combination thrown at them.

If I have to trade file-size for speed, then so be it. I've already made up the calc field (thank God for the Loop command, it's what built the expression).

If pushed, you could go with Ender's original suggestion, i.e. just concatenate the 6 fields using a "¶" as the separator. It will produce a few false positives - in some cases, this might be considered an advantage.

Yeah.... but I'd hate to enter "Car and Driver" company-name, and have everyone with a "Car and Driver" related-show appear too.

Alrighty then... after all that talk, and using a key with 64-lines, it doesn't seem to be working! It acts a little weird. For instance, if the filters are all blank, and I put '"All" & "¶" &' at the top of the child-key, it works; put "All" at the bottom of the expression, and it doesn't.

There isn't some internal limit on key sizes, is there? Please see the attached file, let me know if something's amiss.

Thanks in advanced.

FilterPortal_Attempt1.zip

[comment]

Yes, there is a limit on calculated keys. I don't remember what it is in version 6, but it is rather small (32k?).