Create Report from Multiple Tables

[Alok W]

Hi All,

I need to create resumes for several designers from a database with 4 tables (imported from a MySQL database, used to do the same thing, but online).

The data for our resumes is contained in multiple tables:

designer_data

designer_education

credits_shows

credits_other

Each table has drastically different data, but links each contains a unique field, "designer_id" which corresponds to their "designer_data."

What I need to do is create a layout that has the following parts:

1. Displays fields from "designer_data" where "designer_id" = <>

2. Displays fields from "designer_education" where "designer_id" = <>

3. Displays fields from "credits_shows" where "designer_id" = <>

4. Displays fields from "credits_other" where "designer_id" = <>

Problem is, each of these parts will need to display a different number of records for each designer (with the exception of designer_data, which will only find 1 record).

I know I can use portals, but since I'd have to define a static number of rows for each portal, the spacing on the "resumes" will be inconsistent.

What would be ideal would be to use multiple "body" parts, specifying which tables to find records from - but as far as I know, that's not an option.

Any thoughts?

Thanks!

Edited May 15, 2009 by Guest

[bcooney]

Ideally reports are created from a child table. Since you have several child tables you could:

1. Print from the parent using portals that contain as many rows as the maximum and slide up.

2. Export all to a report table and print from there.

3. Use Append to PDF to create one pdf of several child report layouts.

[titanium]

I have to deal with the same thing, and also a very similar thing (yes... twice the fun!)...If you figure out how best get this done, please let me know how to get started... im searching the forum trying to get insight into this!

1) i have multiple tables which need to come together on one report. However, each report will be diffrent as some clients have multiple items in one table and only one in another, and perhaps even none in another.... Hummm....

2) i have one table with multiple items. Each customer could have anywhere from one to twenty items in this table. The report has to expand to sute and also pull items bassed on date ranges and so forth... So...

Im very interested in the solution you find... ill keep checking back!

[Søren Dyhr]

m very interested in the solution you find... ill keep checking back!

Why, when you haven't done the normalisation in your solution properly? Your choise of tables are obviously made on whims instead of method ... do not expect quick fixes and tricks here!

--sd

[titanium]

Im not sure what you're on about... Perhaps you woke on the wrong side of the bed... i didn't realize that i offended you with my enthusiasm to learn. How dare you bash my DB without having seen it. Maybe next time, after you have read a post, just move on if you don't have any constructive advice to offer.

[Vaughan]

Be nice to Soren. Not everybody is from USA and not everybody's first language is English. (I have my suspicions, too, that not everybody who posts to the Forums is from Earth but that's another story.)

Probably the easiest method I've found is to use the List() function to concatenate all the related records into calculation fields in the "master" record. These calc fields can be placed on the layout and allowed to shrink as required.

This drags a lot of data around but it's unstored so it does not have a huge impact on file size.

[Søren Dyhr]

i didn't realize that i offended you with my enthusiasm to learn

The distinction between fishing and farming! Try to read or perhaps even reread the chapter "An Object Lesson" in:

http://www.foundationdbs.com/Downloads/WhitePaperForFMPNovices.pdf

I am not offended by your enthusiasm, but can see where it have landed you ... but are you sure substantial trial and err really could count as method and provide you wisdom in a jiff?

You kind of disregard that the topic even might have established and even mathematical proved methods, to prevent crap shots - because the tool at hand are cunningly practical to handle.

I'm fully aware of fortunes can be made at poker tables, and that it's impossible to make Big Blue or any other number cruncher to behave skilfully in bluffing at a game of poker, but bluffing on the other hand can't be universally applied - look at Leman Brothers fate:

http://www.youtube.com/watch?v=KE5VUPOUOKo

--sd

Edited May 19, 2009 by Guest

[titanium]

I spent the afternoon and read the white paper you recommended. Much was insightful. I am trying to approach my problem per the recommendation outlined in the document. I have three tables each with various relationships. I think what i need to do is create a "gathering" place for all the data needed for said report, then pull it into the layout (probably using sub summary) then just write the script to make the magic happen. (Yes, im making this sound rather dumbed down, but its not the point of my question at hand)

What im not sure of now is how to approach the new table. Should it have all the fields from the other three tables then have them filled by set variable/ set field via a script? If so, how should i setup the relationships for this...? Any thought you can offer to help me would be great. If you would rather not i fully understand.

I have attached a PDF of my relationships that are relevant to this.

Thanks muchly.

relationships_1.pdf

[Søren Dyhr]

Right ....this is pretty well illustated in this video:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

But as such should you read the all the links provided in this post:

http://fmforums.com/forum/showpost.php?post/250528/

...when getting utterly eager to learn!

--sd