Multi-criteria relationships

[Hijack]

As I understood FM7 multi-criteria relationships to work (perhaps incorrectly now, it seems) I thought you could create a relationship with say, three elements and continue to narrow down your relationship output as you defined each element.

More specific example....

Simple IT problem tracking database. Key fields of:

Status (Open or Closed)

Assigned To (An IT support person's user ID)

Request Type (Project or Break/Fix)

Globals defined in the same table to anchor the relationship.

Status Anchor

Assigned To Anchor

Request Type Anchor

Relationship built on all three in an EQUALS eval statement each.

Then, I was hoping that by setting the "Status Anchor" to Open, I would get a return of all open calls. Then, by further defining the other anchors, I could narrow down the list accordingly.

BUT, FM7 returns only related records that match ALL the criteria.

Is there a way to get the relationship to behave as I want it to (i.e. more dynamically) with a cascading narrowing affect when more relationship anchors are set or am I just barking up the Impossibility Tree?

[RalphL]

John Mark Osborne has a sample file "TypeAhead" that may give you some insight to this type of relationship. He uses calculated fields based on the globals and then uses >= and <= relationships rather than =. His web site is http://www.databasepros.com

[Hijack]

Wow. Thanks for the great link. I'll be bookmarking this baby.

Unfortunately the match fields are specific values rather than variable names that a >= or <= relationship will narrow.

BUT, I was looking for this type of solution for another database I'm admin'ing so it is a great find for me.

[RalphL]

Glad you found the link useful. I still think you could use this method for your problem.

Status Key = Case(IsEmpty(Status Anchor); "0"; Status Anchor) & "

[Hijack]

You sir, are correct sir!

I gave it some more thought after my earlier reply and realized that even though I'm linking my key fields to their relationship targets in an EQUALS eval, I can still populate the calculation global with the content of the value list of the choices if nothing is present in the select global. The Status, Assigned To and Request Type fields are all populated via selections from value lists.

Whereas the DB author in the example file is pre-populating a 0 for the start character and zzz for the end character if no selection is made, it can just as easily be done by dumping the value list into the anchor field if no select is made, which, in essence, links ALL records at that level.

So, MANY thanks for pointing this out to me. My solution is working as I need it to.

I love FMForums.