Find across databases, is this possible?

[MartinaL]

Hi,

I need to be able to do the following find and am not sure if it is possible.

Step 1 - do a find on Acts in a database and bring back a list and a few other bits and pieces and print out a report with details of these acts,

Step 2 - then click continue and jump over to the Events database where I want another report to be run which shows the events for the acts which where selected in the find above on the act database.

Is this possible?

[Fitch]

In the current version of FileMaker you can do this easily with GTRR (go to related records) because it now has a "go to ALL related" option. You can use GTRR in FM6 but it takes a little more work.

I'll assume that each Act has an ID that is used to relate it to Events. You need to get the Act IDs for the found set into a global field and make a relationship from the global field to the Act ID in Events.

The easiest way to do that is to make a layout in Acts that has just the ID field on it. Your script would:

- go to that layout

- copy all records

- go to a layout that has the global field on it

- paste

- GTRR

[MartinaL]

Ok, so would this work;

1. do find on Act database

2. go to layout with only actid field on it and copy

3. go to report in Act database that i want to print

4. have hidden field on this report and past the actid into the global field i created

5. continue and go to Events database

6. run find on events based on the values in the global field?

[Vaughan]

The BIG problem with this in FMP 6 and earlier is that text fields have a 640 KB limit. This means that if the record IDs are 8 characters long the process is limited to 640,000/8=80,000 records.

This sounds like a lot but it can easily be exceeded; if the record IDs are longer than 8 characters the max record limit decreases accordingly. Some people like having 20 character ids...

I used to put a found set check in the script to warn if more than 10,000 records were in the found set and kill the process.

[MartinaL]

It definately won't be any more than 1000, so this should work for me then?

[comment]

Ok, so would this work;

1. do find on Act database

2. go to layout with only actid field on it and copy

3. go to report in Act database that i want to print

4. have hidden field on this report and past the actid into the global field i created

5. continue and go to Events database

6. run find on events based on the values in the global field?

Not really.

1. Do a find in Acts;

2. Do your reporting, etc. in Acts;

...

Go to Layout [ IDs Only ]

Copy All Records

Go to Layout [ ]

Paste [ gActIDs, Select entire contents ]

Go to Related Record [ Events 2, Show only related records ]

Perform Script [ ]

The relationship Events 2 matches Acts::gActIDs with Events 2::ActID.

Another option altogether is to call a script in Events that shows all records, then does a dummy import from Acts (update matching set, match ActID, import some field into a dummy global field) - a.k.a "schlossing":

http://kevinfrank.com/demo-files-user-group.html

[MartinaL]

Thank you so much, this worked perfectly, bu now once I have all of the events for these acts displaying in the report I realised that people will want to filter this by date range, so is there any way to have a custom message before the report is run in the act database allow for an event date range to be entered and then the report is filtered by this.

I have tried this with find modes etc but can't seem to get it right?

[comment]

Have your users enter the range into two global date fields. Once you have a found set of Events, you can enter Find mode, set up a request for records within the range and do Constrain Found Set instead of Perform Find.

It's also possible to filter the relationship itself by a date range, so that GTRR will do all the work, but (in versions before 7) it requires a rather complex setup in Events. Since you are scripting this anyway, it's hardly worth the effort.