Joining 4 tables (files or more...

[noni1]

Hi,

Is it possible to create a report from a 4 (or more) related tables?

Thank you

[Søren Dyhr]

it is...

Basic reporting skills are obtained by watching this:

But you need to get the relations sorted and this is probably what yo need:

http://www.fmp.it/download/files/FM7_key_concepts.pdf

--sd

[noni1]

Thank you.

I just saw the video and read the PDF you mentioned... But it still doesn't solve my problem...

I need to create a report which envolves 5 tables.

Is that possible?

These are 2 many to many relations:

Order - OrderProduct (join table many to many) - Product - ProductPart (jointable many to many) - Parts

From an order I have to print which parts are used.

Thanks

P.s. - I am using File Maker 6

[Søren Dyhr]

P.s. - I am using File Maker 6

Ah thats another kettle of fish, sorry for ignoring this... Well it's still possible, but each value arriving from different tables/files must have it's calc'field to make it present in current file, which must be the most atomic of the ones. Here are the join tables a good candidate.... as to which table to choose for the reporting.

--sd

[noni1]

... Thank you...

I know..

The issue here is that I have to "join" several tables, 2 of them beig join (many to many) tables.. and Filemaker (6) does not work exactly like SQL (calculating the cartesian product)

I think the only way to do this is problably creating a file that will "support" my reports and create a script to join all the files I need.. I am kind of new to FM and I don't have a clue where to start scripting... or even if what I want is possible.

[Søren Dyhr]

I am kind of new to FM and I don't have a clue where to start scripting.

And it doesn't help that most of the participants here left the fm6 discurse some 4 years ago and have been made accustomed to the radical changes since then.

I've just made a check to see if the http://www.cnsplug-ins.com/library.htm?ID=h2QP2ohx&q=sql plugin should have any bearing in your version - but unfortunately it doesn't!

Isn't there a chance to allocate something in your budget to facilitate a purchase of the newest or newer version? The calc'filed tunneling of related values are possible in your version but, but would take for ever to implement but isn't imposssible, and if you salary for this job departs ever so slightly from nil ... would I consider it a reasonable move indeed.

However wouldn't I think a new table for the task at hand is a move in the best direction, and we should perhaps take a look at the task in hand ... since we perhaps could eye another way of getting the summarized info you're after.

Perhaps will this get you somewhere:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000461

--sd

[noni1]

Hi Søren,

Thank you so much for the pointers you gave me... Unfortunatly there seems to be a wall between version 6 and 7 of Filemaker. Clearly V6 is dead and burried.

Unfortunatly my budget is 0. This was a request from a company I work with and afaik they don't want to spend any money with the upgrade.. at least for now

Can you point me any resources on FM scripting (v6) to see if I can come up with my copy betwen tables idea... Again, all the resources I found are for Version 7 or upper.

Thank you.

[Søren Dyhr]

to see if I can come up with my copy betwen tables idea...

This is where it gets terribly wrong, to de-normalize to obtain a subsummary report is plain and simple - no go!

You have to fledge your join table with calc'fields that in the many table in the set gets the data from the one-side ... from both sides. If you need to script this is your normalization not quite up to what it should have been.

--sd

[Fenton]

Basic technique overview: Create another file (table). Import records from each of the other tables. You really only need the primary ID of each table, an indicator of which table it came from (what it is), and some fields you might want locally for sorting (data which is not going to be modified later in its parent table).

Create relationships from this Reports file (table) to each of the other files (tables). This allows you to show other data from them, which was not imported.

Create a layout with Subsummary parts, sorted so that your data appears somewhat as you want it.

It is not going to be easy nor fun. There may be examples around. But it's the kind of thing that one generally does only when one has to, in actual client files.

Another possibility, if it is a short report, of pretty much known lengths, is to copy a page in Preview mode, then paste into Container field repetitions. Somewhat tedious and tricky.

[P.S. More difficult and tedious methods -]

1. Script to build a single big text field with pieces of the data. Mostly a PITA because you often need to separate data with tabs, which means you need to know the maximum size of each field, etc.. It can be done, but you need a complex script to do so.

2. XML exports with XSL stylesheets. It would actually be a good choice, for a pure data report, if you know how to do it. XML supports an "import" function, to bring in data from other xml files (like HTML on steroids). But the learning curve starting from nothing is a little steep.

Edited October 25, 2009 by Guest

[Fitch]

Good suggestions so far. Another avenue you might try is a series of hops from A to B... to E.

Order - OrderProduct (join table many to many) - Product - ProductPart (jointable many to many) - Parts

Make a global field in OrderProduct, let's call it gProductIDs.

Make a relationship from gProductIDs to Product ID in OrderProduct

Make a layout in OrderProduct that has only the Product IDs on it, and another layout with the global.

Now make a script in Orders:

Starting from the Order, Go to related records (GTRR) in OrderProduct.

Perform Script (in OrderProduct, the following)

Script in OrderProduct:

Go to layout Product IDs

Copy All Records

Go to layout global field

Paste

GTRR Product

Do the same in Product and ProductPart, and you will end up with all the parts for the order. You could then copy the Part IDs, go back to the Order, and paste into a global field there. Make a relationship from this global to Parts and you could show the parts in a portal.

Did that make sense? Note that you don't need any of this in FM7: you can GTRR and display related data from many tables away directly.

Also note that in FM6 there is a significant size limitation to text fields (64,000 characters).

(Also note: using Copy in a script destroys the user's clipboard. You may want to get IDs into the global using a looping script instead. If you ultimately use this technique I would recommend that, but I've used copy/paste here for simplicity.)

[noni1]

Thank you to all.

You have given me some good advices.

[noni1]

Hi Again,

After some messy trials with FM6 I gave up.

I finally got version 10 of Filemaker...

The question remains: How can I get the expected result from the tables and relations above mentioned. Thank you.

Edited November 10, 2009 by Guest

[Vaughan]

The need to create a report from multiple tables can indicate that there is a data design problem. not always, but often.

Is it possible to create the report as 4 separate parts that are combined with what I call a "external hard join" ie: staple the printed pages together.

A more modern alternative is to append the separate pages to a single pdf file, then open the pdf.

Another alternative is to print through portals but this has its own problems in that there will be a limited number of portal rows and the rows themselves cannot resize themselves.

Lastly, use calculated fields with the List() function to concatenate the related records together then print the cal fields.