TO's, foundsets, layouts, and windows

[Mandu]

I have two relational tables (Donors and Contributions) which are related in a parent-child (one-to-many) way. Over time, some Donor records have disappeared, leaving orphaned Contributions. In addition, there are some Donors who have never made Contributions.

The key field is Donor_ID.

I want to create a report that lists ONLY the Donors who have contributed, and their Contributions. The best way I know of to do this is to create a layout based on the Contributions table, with a leading sub-summary part triggered by Donor_ID, showing Donor information.

In order to get the intersection between Donors and Contributions, I

• create a new window, and go to a layout that shows records from Contributions
  
• Goto Related Records in Donors to "find" the parent records - this eliminates non-contributing Donors.
  
• Goto Related Records in Contributions to "find" all contrib. records for those donors - this eliminates all orphaned contributions.
  

It's not working, and I suspect it's because I don't precisely understand how TOs, foundsets, layouts, and windows are related to one another. In particular, when stepping through the script, I see that the first GTRR->Donors (which specifies using a Donor-based layout) doesn't change the appearance of the current window's layout at all! It remains in the Contributions-based layout it started in. At this point I don't know whether the GTRR actually worked, but after the second GTRR nothing is right.

Can anyone spot a "gotcha" in how I'm doing this?

Thanks

Chap

[Fitch]

Your GTRR steps should work, did you set them to match all records in current found set?

You can only GTRR to a new window or in the same window. You can't GTRR to a different existing window, at least not directly.

[Mandu]

Your GTRR steps should work, did you set them to match all records in current found set?

They are both set to "Show only related records, match all records in current found set."

You can only GTRR to a new window or in the same window. You can't GTRR to a different existing window, at least not directly.

In both cases I requested that it create a new window.

On the first GTRR, it neither puts up a new window, nor changes the layout in the current window. It appears to ignore the GTRR entirely!

Baffling.

[comment]

It seems there are NO related records. Shouldn't there be a Show All Records step in contributions to start with?

BTW, I believe you could just start with all donors. GTRR to contributions, show related only, match found set, should omit all orphan contributions.

[Mandu]

It seems there are NO related records. Shouldn't there be a Show All Records step in contributions to start with?

There was - I'll be more complete this time.

BTW, I believe you could just start with all donors. GTRR to contributions, show related only, match found set, should omit all orphan contributions.

I think you're right.

Here's the script (modified for your suggestion), slightly pseudocoded since I don't know how to cut and paste it.

/* BIG NOTE: this is not the beginning of the script.

However, as far as my understanding goes, the following

steps have no hidden dependencies on anything that may

have happened up to this point. */

New Window("All Donors")

Go to Layout("Donors" (Donors))

Show all records

GTRR(Show only related; Match found set; FromTable "Contributions", UsingLayout "Donor/Contributions report" (Contributions); NewWindow)

Enter Browse Mode

View as (List)

Sort Records(Contributions::Donor_ID, Contributions::Date)

Enter Preview Mode

Zoom(100%)

Show Status Area

AdjustWindow(toFit)

PauseScript

As I step through, the GTRR step does NOT pop up a new window. Yes, most likely no related records... only there are! So, am I somehow going implicitly against a previously-computed foundset??? Donors and Contributions are related by Donor_ID ... nothing tricky there.

[Mandu]

"Subtle" - to put it politely.

Stepping through with the debugger, I happened to notice "Last Error" change to 3 after the GTRR. (Set Error Capture was not on, nor did I receive an error alert.) Error 3 is something like "command not available - wrong OS, etc.". Not terribly enlightening, but somehow I then noticed that the New Window that had just been thrown up had come up in Preview Mode by default. No can GTRR in that mode.

Getting an error alert would have been reasonable (it set the error code, after all) and helpful.

Problem solved.

[Dave Graham]

• create a new window, and go to a layout that shows records from Contributions
  
• Goto Related Records in Donors to "find" the parent records - this eliminates non-contributing Donors.
  
• Goto Related Records in Contributions to "find" all contrib. records for those donors - this eliminates all orphaned contributions.
  

I realize that you've figured it out, but this approach is a bit expensive if you have a lot of records. It can be accomplished much more efficiently with a find. In the contributions table simply do a search for related donor record IDs with a wildcard match (e.g., contribution_DONOR::Donor_ID = "*"). This will eliminate orphaned contributions, and the donors who haven't contributed wont be included in the report by nature of the current context.

Edited January 17, 2009 by Guest
clarified that the search was for related donor ids, not the donor ids in the contribution table

[comment]

That's interesting. I never tried GTRR in Preview mode, and didn't know it was disabled (undocumented "feature"). In any case, a new window always inherits its initial attributes from the originating window.