Report pulling from multiple tables and ordering chronologically

[(username_here)]

First, let me give you the premise for why I'm trying to do what I'm trying to do, because there very well may be a completely better way to do this.

I have a database (call it the main database) that is connected to another external filemaker database. The main DB will be used to plan travel itineraries for my work (among other things) which involves visiting high schools, attending college fairs, and holding special info sessions.

It has a table called Trips that has the general trip information (Trip Name, start and end dates, traveler, etc.). It is related to a High School Visits table by the Trip Name field, and contains detailed info about each individual visit (can be many visits per each trip).

Trips table is also related to a Events table by field Trip Name, and there can be multiple events assigned to each trip.

Finally, the Trips table is related to a College Fairs table from the connected database so that it pulls related data from the College Fairs table based on if the Traveler fields match, and the fair falls between the start and end dates of the trip.

My dilemma is I need to make a report that lists all the high school visit, college fair, and event information in a itinerary that is sorted by date and time. I.E. on Monday there may be first a high school visit at 9am, then a college fair at noon, then an event at 3pm, and Tuesday may have only high school visits.

I don't know how to make such a report that can pull the data from the four different tables and then order them accordingly. My thought was:

1. Make a dummy table that has fields for all fields in the four tables

2. Run a script that copies all the data related to that particular trip from the different tables and then pastes that data as individual records into the dummy table

3. Search within that dummy table for all records with the same Trip Name

4. Make a layout with that data sorted as stated above.

Got everything working until I came across the College Fairs table (the one in another database). I don't know how to isolate only those fairs that display in the portal in the Trip layout (so, I have gotten it to successfully show only those related fairs that the individual traveler is attending on that trip) and copy that info to the dummy table.

I thought maybe there wouldn't be a way, and so tried building a dummy table for College Fairs in the main database, that would auto enter the information from College Fairs when it pulls up into the travel layout, but not working.

Any thoughts? Or questions? sorry it's so lengthy but wanted to try to give all the info that may be needed. Thanks!

[comment]

sorry it's so lengthy but wanted to try to give all the info that may be needed.

Unfortunately, you left out a very important part: what is the difference between a 'visit', a 'fair' and an 'event' - in terms of the data that needs to be entered about each type?

[(username_here)]

Unfortunately, you left out a very important part: what is the difference between a 'visit', a 'fair' and an 'event' - in terms of the data that needs to be entered about each type?

They essentially have the same data, just are for different things. Date of Visit/Fair/Event, Time of Event, Name of Event, Address of Event, Contact Person of Event, Contact Person Phone Number of Event. That's it for this report, nothing fancy.

[comment]

They essentially have the same data

Why not put them in the same table to begin with, then?

[(username_here)]

For a few reasons. First, the high school visits are tied to a table that stores information on every high school (like address and contact info; stuff that doesn't change). This is so that you can view the high school information, and also see a record of all the visits we have made there in the past. If we put the fair and event info in the same table, going to lead to very messy data storage and mess up some other things.

Most importantly though, is that like I originally said the college fair data is stored in a table in an external filemaker database. For reasons not worth getting into, this has to be done. I've definitely wanted to just put that table into the main database, but it needs to reside in its current database and so that alone makes putting everything into one table hard.

In fact, having it all in one table is essentially what I am trying to do, I'm just initially keeping the data in separate tables to overcome the issues mentioned in the first paragraph of this post. As I said, I have successfully managed to copy the data all tables within the main database, just can't do it correctly from the table in the external database.

[comment]

the high school visits are tied to a table that stores information on every high school

I don't see why that would pose a problem. If an itinerary item happens to be of type "High School Visit" it would have a value in the HighSchoolID field. Otherwise that field would be empty and the HighSchools table wouldn't know anything about it.

the college fair data is stored in a table in an external filemaker database.

It doesn't matter much in what file a table is - as long as you can define a relationship to it. However, if a college fair is not a 'deliberate' itinerary item, but just happens to fall in the trip's date range, then obviously it needs to be imported - if you want to list it chronologically with the other items.

As I said, I have successfully managed to copy the data all tables within the main database, just can't do it correctly from the table in the external database.

You should be importing, not copying and pasting. If the source file is open, and you have created a found set in its frontmost window, only the found set will be imported. And creating the found set is very easy: just Go to Related Record [show related only].