Match any/all of the following rule

[bradford]

Is it possible to create a relationship based on "Match any/all of the following rule"?

I love how "similars" work.

You know that cool feature with OS X Smart Folders? Or iTunes Smart Playlists, where you can set multiple criteria and choose "all" or "any"? I want to do that.

I have a grant file where each grant has restrictions—some you need to meet all, some any. I want to create a conditional value list to that table, based on said similars. I'm wondering if it's possible.

[Ender]

I don't know what "similars" means, but the rest sounds like portal filtering. There are plenty of discussions on this topic. You might check my step-by-step description in this recent thread:

http://fmforums.com/forum/showtopic.php?tid/171617/post/183594

[CyborgSam]

Multi-Key relationships do this. From FileMaker's Help:

You can increase the number of possible matching values by entering multiple values in the match field, separated by carriage returns. You can access related data by matching any single line of your match field, according to your relationship criteria. This is sometimes called a multi-key field or complex key field.

For example, you have a simple relationship joining records in TableA to TableB based on the contents of a single field in each table, and the match field in TableA contains the values:

red

green

blue

separated by carriage returns. FileMaker Pro will match any record in TableB where the corresponding match field contains the single value red, green, or blue. However, FileMaker Pro will not return records where the match field contains the value red green blue. The carriage returns tell FileMaker Pro to treat each line as a separate value.

[comment]

See the attached file.

MatchAnyOrAll.fp7.zip

[bradford]

You're a complete genius. This will take me a while to reverse engineer but this is it. Portals based on Conditional relationships.

Thank you!

[comment]

It's not very complex - just put the calc fields on a layout (make them tall, since they have multiple lines) and see what they show. That should make it pretty obvious.

[bradford]

I'm getting really tripped up here because the solution I'm working with has a middle file. Your example, while far from my ability, has two files. I've spent hours staring at my screen.

Students–StudentContracts—Contracts

It's like the data is on the Contracts file and the viewer needs to be on the Students file, but really, the Match Key is on StudentContracts. Ughhh.

[comment]

I am afraid I don't follow. Can you explain the problem in detail?

[bradford]

Hi, sorry for the delay. I get the example that you gave me and it's great but it's only using two files. My solution is three files...there is a joiner file in the middle.

Let me try to explain...

Students—Grants—StudentGrants

The Grants file has a bunch of grants with criteria for who is eligible. That's where the check boxes come into play.

The Students file has a bunch of students with their check boxes. Minority. At risk. Etc.

That joiner file is the intersection of students and their grants.

So....if you have a grant that's only for at risk minority girls then that choice and other matching choices should appear on the students file. Once the chose the appropriate grant from the pulldown a record is then created in the studentGrants file.

Does that make any sense? I was going to send the file but it'll take forever to remove the personal details.

[comment]

I may be missing something, but I don't see how it makes a difference.

Your existing structure connects Students to Grants by creating applications (? or actual grants?) in the join file, connected - I presume - by StudentID and GrantID.

Here we are talking about showing those grants that the student might be eligible to receive, based on the intersection of criteria in both tables (the two checkbox fields). This is an entirely separate affair. This takes place BEFORE you select a grant to be joined to the student.

You should place another TO of Grants on the graph - let's call it EligibleGrants - and connect it directly to Students as shown in my demo (if you're looking at this from the point of view of Students, then Viewer = Students and Data = EligibleGrants. Of course, the gCheckbox in Students will not be global anymore).

If you like, you can create a value list of EligibleGrants, showing only related records from EligibleGrants, starting from Students. Then you can select the grant from a drop down list of EligibleGrants only, and proceed as before.

[LaRetta]

This concept of key transformation ... from multiline to concatenated (well actually array, right?) is very powerful AND flexible!

WOW!! Thanks for presenting this, Michael!!

L

[kazza]

Any way of doing this in fm 6?

[bradford]

I still need some help with this. Is there any way I can get some additional help?

Perhaps I can email you?

[comment]

Why don't you attach a clone (no records) of your file?

[bradford]

Attached is the database. (It's a lot of files.) Under Contracts, in the record view, is the "funding restrictions" field that would limit what students can see when they look for aid under their card.

In Students on the Program card is where the conditional value list should magically appear, allowing a student to get aid. When they sign up for a program there's a chance they can get aid.

As part of this, the any/all "rule" also needs to function as there are many different reasons someone might be eligible for aid. That functionality is not on there on the layout.

I am stuck!

The files were a little too big so they're here instead: http://homepage.mac.com/bradford

[comment]

I'm afraid you grossly overestimate my capabilities - there's no way I could understand your solution in a reasonable amount of time.

There's one thing I think I have glimpsed, which begs the following question:

Suppose there are 4 grants:

Grant A is available to racial minority students;

Grant B is available to women students;

Grant C is available to students who are BOTH women AND a racial minority;

Grant D is available to students who are EITHER women OR a racial minority.

If I understand correctly:

A Caucasian female student browsing the database should see grants B and D;

An African-American male student should see grants A and D;

An Asian-American female student should see grants A, B, C and D.

Is this correct?

Also, are there really grants of the OR type? Or can we assume that any grant requirements are cumulative?

[bradford]

Hi.

First of all, let me say that any help is so very much appreciated. Normally I have a partner in this stuff. Bouncing ideas off people really helps.

Also, I would be overwhelmed if I was to look at this solution with fresh eyes. That being said...

Yes, to your question about the grants. That's exactly right. Also, the any/all is very real as some funders want to limit choices.

I remember an earlier post you explained how to create the relationship to view the matching grants in a pull down. THAT I get. To me though it feels like there needs to be a second relationship that IS the portal for the student once you select a grant.

I am working on this now and should be available for any questions you have.

[email protected]

[comment]

OK, this is a completely new ballgame. So if I get this right, the choice is in the Grants table - the Grant requirements may be either cumulative (the student has to satisfy ALL of them), or ... (what's the antonym for cumulative?)... anyway, it is enough if the student fulfills ONE of the grant's requirements.

In the Students table, OTOH, there's no choice - the student simply checks off whatever is applicable to him/her.

Now, the problem is to generate the key in the Student table. That's going to be quite a key, since you have 11 possible choices, and there has to be a line for every possible combination of them. If I am not mistaken, that's 2047 lines.

Let me think about this for a while - perhaps an alternative can be found using a multi-criteria relationship.

[bradford]

That's where the conditional value list comes in. I thought I could create a value list using only related values, as your earlier example showed.

I don't want to believe it is that complex. Sounds scary.

[comment]

The value list is not a problem. But a value list using only related values requires a relationship - and determining which records should be related is the complex issue here, I think.

[comment]

One more question, so that I do not bark at the wrong tree again:

Do you want to see the students that are eligible for the currently viewed grant, or is it the other way around, i.e. see the grants for which the currently viewed student might be eligible?

[bradford]

If the value list is legitimate, can't the user just select the grant and be done? There would be no issue of selecting outside the related values. Once the Grant or Contract ID is in place the rest of the information could pop in.

Sounds simple. ; )

[comment]

Uhmmm.. you haven't answered my question.

Regarding your remark: if it's so simple, why is it difficult?

Selecting from a list of related values is indeed simple. But you seem intent to disregard the problem of how to determine which values are related.

Consider a grant that requires the applicants to meet criteria A and B, out of a total list of 5 criteria ABCDE.

The eligible students are not only those who meet both criteria A and B, but also those who meet ABC or ABD or ABE or ABCD or ABCE or ABDE.

That's a simplified example, using only 5 criteria - you have 11.

The more I think of this, I become convinced this is the same problem as the one discussed here - only on a much larger scale.

[bradford]

Considering that you're a veteran and I'm a stranger, I'll defer to you.

[Søren Dyhr]

Any way of doing this in fm 6?

Since this thread pulls a lot of traffic, have I as a service to users on earlier versions copied Comment's excellent template into .fm5 format!

--sd

matchAnyAll2.zip

[bradford]

Sorry for the overlap in answers, didn't see your question above until now.

In the Students file one would find a grant or Contract from a list.

Your MultiMatchX2 file from the other post is great. Looking at it now.