GTRR problem

[sandyinlet]

I'm probably missing something obvious, so bear with me.

Addresses table with a foreign Company ID field, Company table with its Company ID field. I need to switch to the Company layout such that only those records show which have a match to the current set in Addresses. In other words, a straight match of Company ID in Addresses to Company ID in Company won't do. That will provide all Company records which have a match in Addresses. I'm looking for just a match to the current set in Addresses.

Here's an example. The current found set in Addresses shows these Company IDs — 101, 103, 106. Addresses records showing Company IDs 102, 104 and 105 are currently hidden. I need a Company layout to show only Company records 101, 103, 106.

Is there a way of storing the current set in Addresses and then doing a Go To Related Record in Company to establish the set I'm looking for? I presume I could put the string of desired Company IDs delimited with a paragraph return into a global and then match the global field against Company ID in Company, a technique I used to resort to in FMP6. I'm hoping there's now a more efficient way.

Much appreciated.

[bcooney]

Gtrr Match Found Set

[David Jondreau]

It depends a little on the size of the found sets and how the file is being served. "GTRR matching found set" can be brutally slow in the wrong conditions.

I presume I could put the string of desired Company IDs delimited with a paragraph return into a global and then match the global field against Company ID in Company, a technique I used to resort to in FMP6.

This is often how I do it now. 10x faster.

[sandyinlet]

Gtrr Match Found Set

Nope, not working here. I'm on a Company layout. In Get from Related Record I'm using this TO: Company-Addresses, which calls for a CompanyID to CompanyID match. I'm using the current layout and I'm using "Match records in the current found set." This produces both the records I want plus the hidden records in Addresses.

What am I misunderstanding?

[sandyinlet]

It depends a little on the size of the found sets and how the file is being served. "GTRR matching found set" can be brutally slow in the wrong conditions.

This is often how I do it now. 10x faster.

Because there will be thousands of records being processed, I'm cautious about recycling the FMP6 method. Have the old size limits gone away?

[David Jondreau]

Because there will be thousands of records being processed, I'm cautious about recycling the FMP6 method. Have the old size limits gone away?

I'm not clear on your objections or what limits you're concerned about. I would use a custom function to grab the related CompanyIDs. Then set a global to that list and GTRR matching a single record through a new relationship. How you construct that function(s) will depend on whether there are <10K, <50K, or >50K Company records.

But your posts seem to be conflicting. Are you starting from a layout based on the Addresses TO or Companies TO?

[bcooney]

First post says he's in a found set of Addresses and each has a foreign Company key. So, gtrr match found set should work. I haven't found it to be slow.

I find the custom functions that gather IDs limited by the recursion limit.

[sandyinlet]

Fantastic! Got it. I was indeed starting from Companies.

I set up a new TO that starts from Addresses and links to Companies via a CompanyID match. While still on the Addresses layout I invoked GTRR and used that TO. For the "show record using layout" call I chose a Companies layout.

Direction is critical in the relationship model FMP7 is using. I'm still getting used to it. Much thanks for your patience.

[David Jondreau]

I recently had a GTRR that took way to long. It pulled about 100 records from a set of 5000. The relationship was a simple ID number field. The file was hosted over the internet, FMS9 to FMP9 client. The GTRR was "a match current found set."

The GTRR took 9 minutes.

I implemented the technique I described above and it brought the time down to under a minute.

I'm aware of the recursion limit, but limits can be circumvented.

I'm not sure what's going on under the hood on a GTRR, does FMP interpret it as a Find with a request for each relationship criteria?

Edited May 6, 2009 by Guest

[mr_vodka]

I agree with DJ here. I hardly ever use GTRR found set due to exactly the speed difference. If I throw the IDs into a global field and do a single GTRR it always runs faster. My guess was always that it seemed as though it is doing a GTRR for each record separately and just returning the results.

[bcooney]

Alrighty, then. Then I propose we drag out the old layout with only one field (the ID needed) and use copy all records to capture the IDs. Then set the global to the clipboard. Gtrr using the global. I still think I'd trust that more than recursive CFs.

[mr_vodka]

I submit about once every 2 months on the new features list for the following function:

ListFoundSet ()

Now with Script Triggers, I want it even more.

P.S. Now that they FINALLY have fixed my biggest pet peeve of all the GTRR bug with FM10, which I used to message them about every month... I can devote more time into sending a request for ListFoundSet (). lol.

Edited May 6, 2009 by Guest