Report from Related Tables

[natursalus]

Hello,

I have the following tables:

DocP

FormDocP

DocS

FormDocS

Their relationships are:

DocP -> FormDocP

DocP -> DocS -> FormDocS

DocP::__kpDocPID = FormDocP::_fkDocPID

DocP::__kpDocPID = DocS::_fkDocPID

DocS::__kpDocSID = FormDocS::_fkDocSID

I have a layout based on the FormDocP table where I can pull out the fields from the DocP & FormDocP tables and generate a report.

I have a layout based on the FormDocS table where I can pull out the fields from the DocS & ForDocS tables and generate a report.

My goal is to have a report with the following fields from the 4 tables:

DocP::Name

FormDocP::Name

DocS::Name

FormDocS::Name

A layout based on the FormDocS table pulls out the Name field from the tables: DocP, DocS and FormDocS, but NOT from the FormDocP table.

The Parent -> Child linear relationship is true for DocP -> DocS -> FormDocS table, but the branch DocP -> FormDocP somehow is not resolved when the layout report is created from the FormDocS context.

My question is: how to setup layout report for the following fields:

DocP::Name

FormDocP::Name

DocS::Name

FormDocS::Name

Thanks in advance for any guidance.

[comment]

In the interest of sanity, I have rewritten your question as:

I have the following tables::

Items >- Parent -< Child -< GrandChild

My goal is to have a report with the following fields from the 4 tables:

• Parent::Name
  
• Child::Name
  
• GrandChild::Name
  
• Items::Name
  

Now, two things should become immediately obvious:

1. A report listing grandchildren must be produced from the GrandChild table;

2. A parent has many items - but from the point-of-view of a grandchild, all parent's items are the same. If you put a field from Items on a layout of GrandChild, it will always show data from the first related record in Items.

Unless you have some additional data to associate a grandchild with a particular item of the parent, there can be no progress from this point.

[Vaughan]

Sounds like a reverse Left-Right-Outer Join would do the trick here.

[natursalus]

Helo Comment,

If you put a field from Items on a layout of GrandChild, it will always show data from the first related record in Items.

Certainly, that's the problem form FM's point of view (relational theory).

Unless you have some additional data to associate a grandchild with a particular item of the parent, there can be no progress from this point.

Difficult to come up with a non-existent relationship in the real world between FormDocP (Items) and FormDocS (GrandChild) tables other that the field DocP::__kpDocPID.

I wrongly thought that there was some way of putting together the two working reports: FormDocP (Items) and FormDocS (GrandChild)

Thanks for your feedback

Hello Vaughan,

Sounds like a reverse Left-Right-Outer Join would do the trick here

Okay. I'll give it a try.

Thanks for the tip.

[comment]

Difficult to come up with a non-existent relationship in the real world between FormDocP (Items) and FormDocS (GrandChild) tables other that the field DocP::__kpDocPID.

I wrongly thought that there was some way of putting together the two working reports: FormDocP (Items) and FormDocS (GrandChild)

Well, you could place a portal to Items in the sub-summary by Parent part of the report - so your report would look something like:

Parent: Alpha

Item: Alpha A

Item: Alpha B

Item: Alpha C

Child: Alpha 1

GrandChild:Alpha 101

GrandChild:Alpha 102

GrandChild:Alpha 103

Child: Alpha 2

GrandChild:Alpha 201

GrandChild:Alpha 202

Parent: Bravo

...

Another possibility is that your structure needs to be revised. I have no idea what your tables represent in the real world, so it's hard for me to tell.

[natursalus]

Hello Comment,

Thanks again for your feedback.

Another possibility is that your structure needs to be revised. I have no idea what your tables represent in the real world, so it's hard for me to tell.

If you feel like having a look at it, I would like you to have a look at my structure at criticize it as needed.

Real world meaning of the tables.

This is part of a Document Management project.

All documents have a herarchical relationship.

Manual (not relevant in this case) -> Standard Operating Procedure -> Work Instruction

Standard Operating Procedure -> Standard Operating Procedure Forms

Work Instruction -> Work Instruction Forms

Scenarios:

Always there are Standard Operating Procedures

Most of the time (but not always) there are Standard Operating Procedure Forms

Sometimes there are Work Instructions

When there is a Work Instruction it always has Work Instruction Forms

Nomenclature Correspondences

DocP (Parent) = Standard Operating Procedures (SOP)

FormDocP (Child) = Forms associated to each SOP

Relationship: one to many: DocP (Parent) -> FormDocP (Child)

It is possible that some SOP could not have any form associated to it.

DocS (Child) = Work Instruction

FormDocS (GrandChild) = Forms associated to each Work Instruction

Relationship: one to many: DocS (Child) -> FormDocP (GrandChild)

Every Work Instruction has a form associated to it.

I have divided the forms in two tables to assure that I would not have problems with the reports for each document type: Standard Operating Procedures & Work Instructions.

Additionally I have tried to make data entry a more intuitive task for the user.

From the Standard Operating Procedure table the user goes directly to the Standard Operating Procedure Forms table.

From the Work Instruction table the user goes directly to the Work Instruction Forms table.

Since the project is full of work arounds to make up for FileMaker pecularities, I would like to keep the current table organization; otherwise I would have to redo the whole project; a daunting scenario at this time.

So, is there any chance of success for me?

I appreciate your time

[comment]

I'm afraid you're giving me too much credit here - I'm not too familiar with the expanded terms either.

On a pure hunch I'd say that this seems like a good candidate for a recursive structure. Probably one table listing all document parts (such as sections, chapters, etc.) with a self-join to indicate the hierarchy. Possibly another table for the forms - which can then be related to any document part, at any level.

I would like to keep the current table organization; otherwise I would have to redo the whole project;

So wouldn't the type of report in my previous post work for you?

[natursalus]

On a pure hunch I'd say that this seems like a good candidate for a recursive structure. Probably one table listing all document parts (such as sections, chapters, etc.) with a self-join to indicate the hierarchy. Possibly another table for the forms - which can then be related to any document part, at any level.

I'll try to apply your suggestion in future projects.

So wouldn't the type of report in my previous post work for you?

Didn't have a change to give it a try yet. Today my time belongs to my family.

Tomorrow morning I will have the opportunity to try it. I'll let you know how it goes.

Thanks again

[natursalus]

I'll try to apply your suggestion in future projects.

Didn't have a change to give it a try yet. Today my time belongs to my family.

Tomorrow morning I will have the opportunity to try it. I'll let you know how it goes.

Thanks again

[natursalus]

Hello Comment.

Well, you could place a portal to Items in the sub-summary by Parent part of the report - so your report would look something like:

Parent: Alpha

Item: Alpha A

Item: Alpha B

Item: Alpha C

Child: Alpha 1

GrandChild:Alpha 101

GrandChild:Alpha 102

GrandChild:Alpha 103

Child: Alpha 2

GrandChild:Alpha 201

GrandChild:Alpha 202

Parent: Bravo

...

So wouldn't the type of report in my previous post work for you?

Unfortunately it doesn't work

I don't know if it makes any sense at all, but I am going to try to use a virtual table and the left outer join approach as per Adrew Walters LeftOuterV3 file.

Regards

[comment]

Unfortunately it doesn't work

It doesn't work, because...?

[natursalus]

Your suggestion didn't work (meaning I couldn't get any Items records) in the GrandChild report:

Header

Sub-summary by Management System (leading)

Sub-summary by Child (Parent Work Instruction)(leading)

Body GrandChild

Footer

What I did, surely wrong, was:

Header

Sub-summary by Management System (leading)

Sub-summary by Parent (SOP) (leading

Portal to Items

Sub-summary by Child (Parent Work Instruction)(leading)

Body GrandChild

Footer

My apologies if I did something dummy, but it is a common outcome when one doesn't have any clue about what he is doing.

Regards

[comment]

My question - "wouldn't the type of report in my previous post work for you?" - was not about implementation. I was wondering whether the arrangement of listing the parent's items as a group, either at the top or at the bottom of each parent group, would suit your purpose.

If yes, then a portal to Items placed inside a 'sub-summary by parent' part should work. Note that this is not the best solution, as portals do not always print well across page breaks. But it's cheap in resources and easy to implement (or should be, in any case...). Make the portal tall enough to accommodate the maximum items expected, and set it to slide up.

[natursalus]

My question - "wouldn't the type of report in my previous post work for you?" - was not about implementation. I was wondering whether the arrangement of listing the parent's items as a group, either at the top or at the bottom of each parent group, would suit your purpose.

Yes the arrangement is the desired one:

Procedure (Parent)

Procedure Forms (Items)

Work Instruction (Child)

Work Instruction Forms (GrandChild)

If yes, then a portal to Items placed inside a 'sub-summary by parent' part should work.

To double check that I am on the same line of thought as yours, the report should be structured as follows:

Header

Sub-summary by Management System (leading)

Sub-summary by Parent (SOP) (leading)

Portal to Items inside the sub-summary by Parent part.

Sub-summary by Child (Parent Work Instruction)(leading)

Body GrandChild

Footer

Regards

[comment]

Yes, that is correct. Also make sure the layout is set to show records from the GrandChild table.

[natursalus]

Also make sure the layout is set to show records from the GrandChild table.

The portal to Items does not show any values.

So the following setting is not achieving my goal:

Header

Sub-summary by Management System (leading): Shows records

Sub-summary by Parent (SOP) (leading) : Shows records.

Portal to Items: Does not show records.

Sub-summary by Child (Parent Work Instruction)(leading): Shows records.

Body GrandChild: shows records.

Footer

I am afraid that I am back to square one.

Regards

[comment]

I am afraid that I am back to square one.

No, I think you are merely having an implementation problem. Does this work for you?

GrandChildReport+Items.zip

[natursalus]

Comment,

Many thanks for your demo file.

Definitely there is something wrong with the structure of my project, since I did as in your demo and still it is not showing the data from the Items table.

Anyhow the troubleshooting is my task.

Thank you for your patience.

[natursalus]

Hello Comment,

Seems that everything works fine as long as there are values (records) for the GrandChild table. But if there are no records in the GrandChild table then the report is blank and doesn't show the values (records) of the upper levels (Child, Parent, Items, System).

Management System Documents in Browse mode:

Management System Documents in Preview mode:

As you can see there are records in the upper levels and can be shown using their respective report layouts:

Working Instructions (Child)

Procedure Forms (Items)

Procedures (Parent)

Is this something to be expected?

How can this be fixed so that I can get the report of the upper level records even when there are no values (records) for the lowest level?

Thanks

[comment]

But if there are no records in the GrandChild table then the report is blank

Well, yes: if there are no records in the report table, then there is nothing to report. If you want to report on child level only, do the report from the Child table.

However, you may run into a more acute problem if you try to produce a full report (all levels) and there are child records with no grandchildren, or parents with no children. Filemaker doesn't do a left outer join, and childless parents will be skipped.

There are various workarounds for that, but it only raises again the question why not have all levels in the same table.

[natursalus]

Hello Comment,

only raises again the question why not have all levels in the same table

The only reason is that I don't have the skills to do it yet.

On a pure hunch I'd say that this seems like a good candidate for a recursive structure. Probably one table listing all document parts (such as sections, chapters, etc.) with a self-join to indicate the hierarchy. Possibly another table for the forms - which can then be related to any document part, at any level.

All this sounds very alluring but also scary for a fm beginner like myself.

Certainly your comment called my attention and looked for some built example I could use to do some reverse enginnering, but I dind't find any workable candidate.

I understand that my "workarounds" finally got me.

There are various workarounds for that

What is going to be less painfull to learn the workarounds or to try out a new structure?

Thanks

[comment]

What is going to be less painfull to learn the workarounds or to try out a new structure?

LOL, I am afraid I haven't acquired the skills of a torturer yet...

Seriously, the workarounds come down to these:

1. Use a portal to show the grandchildren;

2. Create dummy children for childless parents;

3. Use a reporting table.

I cannot in good conscience recommend the first two. The third one uses a reporting table where every record is either a parent, a child or a grandchild. The table can be populated by importing, by relationships or by drawing from a "virtual table" (data held in variables). In any case, the reporting table must have a record for each reported element - so basically you are creating a union table for the reporting, instead of having one to begin with.

I should also mention:

4. Have a script write out the entire report to a global field;

5. Export the data and produce the report in another application.

The only reason is that I don't have the skills to do it yet.

I don't want to minimize your concern here. It is not a simple undertaking, and you should be prepared to spend some time with it.

[natursalus]

Seems like nothing comes easy with FM...

Certainly the Reporting table would save my project current structure and would go along the current topic subject.

The third one uses a reporting table where every record is either a parent, a child or a grandchild

Since you say "or" I understand that I cannot have on the same reporting table:

System (Manual)

Parent (Procedure)

Item (Procedure Form)

Child (Work Instruction)

GrandChild (Work Instruction Form)

Is this assumption right?

For me the Reporting table concept is one I would like to be able to use since it could be useful in multiple scenarios.

The table can be populated by importing, by relationships or by drawing from a "virtual table" (data held in variables)

What is the most robust alternative?

Thanks

[comment]

Since you say "or" I understand that I cannot have on the same reporting table:

...

On the contrary: the idea is to have all of these in the table, each one being an individual record (of different type).

What is the most robust alternative?

"Robust" is undefined. Importing is the simplest one to implement, IMHO. But keep in mind that in a multi-user scenario it may be difficult to allow more than one user to report at the same time.

[Vaughan]

Seems like nothing comes easy with FM.

FMP is an exceptionally easy-to-use database development environment. However, some databases are difficult to build and a lot of work. Nothing will change that.

[natursalus]

Hello Vaughan,

However, some databases are difficult to build and a lot of work. Nothing will change that.

After reading in several FM forums I realize that most of all FM users are confronted with the same challenges. And usually for each type of challenge there is a common solution. However, for the uninitiated it is very difficult to understand the language of the trade and spot the common solution. But this is a common trend in many software applications.

Since neither the challenges nor the solutions are well documented in the available FM books and online courses (I don't know about FM training series for developpers) it would make sense for FM to introduce those common solutions inside the application itself.

To me, the limits of a good software should be determined by the user (developper) imagination only and not by his/her lack of figuring out workarounds for common tasks.

But it's just the opinion of somebody that has difficulty understanding the FM language (not so the rationale behind the workarounds) and the limitations of FM.

FMP is an exceptionally easy-to-use database development environment

You are right. There are a myriad of tasks that are wonderfully easy to carry out with FM.

What I wonder is why isn't so for all the essential tasks of database building?

Vaughan the purpose of these lines is to acknowledge the difficulties that FM users like myself encounter with FM.

It is also true that FM is a wonderful database application.

And last but not least without the support of FM experts in this forum and others, neither FM Inc would be a success history nor FM users like myself would be able to pull out any project.

So I hope that somehow all FM experts that help people out get some type of reward.

[comment]

I agree with some of your points, but you also need to take into account that:

• some data models are more difficult than others (for relational databases in general, not just for Filemaker);

• Filemaker was designed primarily for running the "Aunt Millie's Guns & Tobbaki Store" out on the prairie. As such, some tasks were made easier at the expense of others.

So I hope that somehow all FM experts that help people out get some type of reward.

Well, I've heard that the first person with 20,000 posts gets an iPad 2 - but I'm sure that's just a rumor... :P

[Vaughan]

There are a myriad of tasks that are wonderfully easy to carry out with FM.

What I wonder is why isn't so for all the essential tasks of database building?

What "task of database building" does FMP make hard?

A power saw makes cutting wood easier than a hand saw. But it doesn't make building a house any easier because you still have to know how to build a house. Learning how to cut timber isn't learning how to build a house. The power saw just makes cutting the wood easier.

That what FMP is: a power saw. It does not "know" how to build databases. You still have to know how to build a database.

Vaughan the purpose of these lines is to acknowledge the difficulties that FM users like myself encounter with FM.

New users have two learning curves: one is with FileMaker Pro, the other is with relational database design. Relational database design is by far the steeper and longer learning curve, and it has nothing at all to do with FMP's ease of use (or otherwise) because relational database design is plain hard.

BTW FMP now comes with a reasonably useful set of templates, take a look at these and see if any can be useful.

[natursalus]

Hello Vaughan,

It is not my intention to stry away from the topic subject but I wanted the FM experts of this forum to know how difficult it come be to deal with FM pro.

I use FM so that means that I have already made my choice, but believe me there are at least one piece of software out there able to establish the relational relationships under the hood. My yet unfinished FM project, I started it in January (really embarrasing!,) I was able to do in two weeks with that piece of software.

Still I prefer Fm because I can look under the hood and see how the relationships are established. And because FM is by far more powerful concerning layout and report design, etc.

My point is, it is possible to create a complex working relational database knowing nothing about relational database design. Something that so far it is not possible with FM. But I want to create relational databases knowing the basics with FM. The problem is that the information about FM use as a database application is, in my view, very scanty.

I, like many others, read FM experts and moderators answers to FM user questions. I have also red almost every book about FM out there. I have bought John Osborne DVDs on FM Pro 11 ( beginners, intermediate and advance level). But still a comprehensive publication addresing mundane, repetitive, factual FM usage questions is needed in the market.

Hello Comment

Well, I've heard that the first person with 20,000 posts gets an iPad 2 - but I'm sure that's just a rumor...

The fact is that you deserve more than just gratitude words for your time and expertise.

[comment]

I wanted the FM experts of this forum to know how difficult it come be to deal with FM pro.

No one was born an FM expert, so I believe we all know what you're going through.

My point is, it is possible to create a complex working relational database knowing nothing about relational database design.

I'm afraid I'll have to disagree with you here.

there are at least one piece of software out there able to establish the relational relationships under the hood.

It's not against the rules to mention competing products by name.

[natursalus]

Hello Comment,

I'm afraid I'll have to disagree with you here.

It's not against the rules to mention competing products by name.

Brilliant Database v9

It's amasing how easily you can create relatonships. You can even do things that on the outside goes against relational theory but my feeling is that the right connections are established behind the user eyes.

I have been pondering about the options for the "reporting" table and for what I have red a virtual table would do the trick.

Knowing how to set up a virtual table would be great tool to learn. But I have no clue about how to do it.

I know that I need to set up the virtual table and I also need a bunch of scripts for pulling out the data.

Any suggeestion as to what I should be reading or where to get a simple working example?

[comment]

I don't know about "simple", but perhaps this could get you started:

http://fmforums.com/forum/topic/71160-subsummary-reports-in-list-view-unable-to-get-desired-layout/

You'll find more if you search the forums for "virtual table" or "virtual list".

[Vaughan]

It's amasing how easily you can create relatonships.

It's not creating relationships that is hard. It's know WHAT relationships to create that is hard and takes time.