bug? find on related field via multiple-criteria relationship

[mscholtz]

Here's the setup:

A db converted from FM6, with several files.

table Individuals (in file A) is related to table Services (in file via a number of different relationships. So I have multiple TO's for Services.

The relationship in question is multiple-criteria. The match fields are:

[color:blue]HouseholdID = HouseholdID

gServiceType = ServiceType

We'll call this TO ServicesSelectedType

It's the second that's the problem. gServiceType is, you guessed it, a global text field in the Individuals table. ServiceType is an indexed (non-global) text field.

If I make a portal to Services in the Individuals file, using this relationship, it works fine. Only those services matching gServiceType show up.

However, if I do a simple related field find via the same relationship, it doesn't work.

Example: finding on ServicesSelectedType::DateOut = "1/1/2005...12/31/2005" returns all Individuals that have ANY SERVICE that has DateOut in that range, whether or not it matches gServiceType.

In other words, the relationship correctly filters for service type when displaying records in a portal, but fails to do so when finding records based on a related field.

Is this a known bug? Or anyone else got any suggestions?

BTW, if I change gServiceType to a stored calc instead of a global, it works as expected. Unstored calc doesn't work, and returns an error. (not sure why..)

TIA,

Matthew

[Søren Dyhr]

I'm a little puzzled here, did it work in versions prior to 7, having a global field for foreign key have never work to the best of my knowledge?? There is something in your post that don't get!

The way to handle the issue is to make both range-start and range-end be extra keys working as primary keys and combine them with the other keys needed to filter the related records into a set - not via Finds but instead via GTRR(SO)

Conversion of keys that used to work in 6 is tempting (from a "if it aint broke don't fix it" point of view). But reworking keys embracing the new features really pays off on limited sized sets. There can be issues where you wish to "allow creation of related records" with keyings including < or > making the option grey out. But lowering or increasing the thresholds so you can use the "...or equal" makes the option re-appear.

As you can see am I guessing here, couldn't you upload a template??

--sd

[mscholtz]

I'm a little puzzled here, did it work in versions prior to 7, having a global field for foreign key have never work to the best of my knowledge?? There is something in your post that don't get!

Thanks Soren, but there are several things in your post that I don't get either. I'll try to answer your questions.

re: the above, why do you say foreign key? As I describe in my post above, the global is on the "left hand" side of the relationship from the perspective of the Find. (i.e. I'm using the global in the currently-active table to match indexed text fields in records in another table.)

This should work. Read the FMP help entry "About Match Fields for Relationships" to see it in black-and-white.

As I mention in the post, one can tell it works, in general, by the fact that a portal based on the relationship shows the correct records.

The way to handle the issue is to make both range-start and range-end be extra keys working as primary keys and combine them with the other keys needed to filter the related records into a set - not via Finds but instead via GTRR(SO)

Sorry, but unless I misunderstand you, this doesn't help because it's not what I'm trying to do. I don't want to get a found set of Services recs that match a single Individual. Rather, I'm trying to get a set of all the Individuals that have one or more related Services that match the criteria outlined.

I do understand that I could include the date range as part of the relationship definition, and then simply find on the existence of the related recs (by finding on the existence of the primary key, etc.). But that would not help my problem here, which involves FMP failing to filter the related records it's searching in the Find based on the existing relationship definition.

Besides, the date range on finding on comes from globals, so including them in the relationship would only compound the problem, I assume. And the fact that we're finding on a date range is really beside the point.

As you can see am I guessing here, couldn't you upload a template??

I made an example file, but am having trouble uploading it - I'm getting an error from fmforums.

In the meantime: anyone seen this issue before?

[mscholtz]

OK, here's the example file (upload seems to be working again.)

In this file, I've set up a relationship similar to that described in the original post. If you look at the relationship graph, you'll see that the Individuals table has 4 different relationships to the Services table: one that matches all services based on ID, and 3 that match only those w/ a given ServiceType, based on 3 different methods (global, stored calc, unstored compound key calc).

To see the issue, go to the Filtered Portal layout. Notice that the global and calc are both set to "B". Flip through records, notice that the top 3 portals all show the same records. (All 3 relationships are "working" in the portals.)

Then try doing finds on each of the top 3 portals in turn (try finding on ServiceType = *.) You'll see that the Global relationship finds related recs that don't match the global (different behavior for find and display) while the Calc relationship behaves as expected and only finds those that match the calc. The Compound Key rel. gives you an error message.

FYI, these finds give the same results if the fields used are in a portal or not, and it makes no difference what field one uses in the find, or if the find is scripted.

TestRelationshipBug.zip

Edited September 6, 2005 by Guest

[Vaughan]

Global fields don't work in Find mode. Never have in any version of FMP.

[mscholtz]

Well, it makes perfect sense that finding on a global from the current table wouldn't make any sense, since it would always return all of the records.

But why shouldn't it work in a scenario like mine, where the global is only being used as the LH side of a relationship?

If it's never worked in any FMP version, then I might argue that it should've worked differently from the start.

Also: if I remember correctly, globals do work in Find mode in at least one respect. If you use a global as LH key in a relationship, as I describe, then base a conditional value list on that relationship, the VL will work in Find mode in addition to Browse. In fact, it'll work where another relationship (using a stored calc, for example) won't.

So obviously it's not the case that FMP can't internally access globals at all in Find mode.