Setting relationships for multi-table querying

[Dave J]

Hi folks,

does anyone know how to set fields & relationships for pulling data through multiple tables? A brief example of what I am trying to do is below.

Say you have the following tables...

Company

Contact

Orders(ie Invoices)

Line_Items

...and each table is related to the next up the chain, for example...

Company Table

Company_ID

Contact Table

Contact_ID

Company_ID

Orders Table

Order_ID

Contact_ID

Line_Items Table

Line_Item_ID

Order_ID

Now, in FileMaker, it is easy to find, say, all contacts at a given company, but what happens if you want to find all line items for the company, perhaps because you have had word that the co. is going bankrupt? In Access, as I understand it, you can do a query that pulls data through the chain of relationships so you can do a single query that can report data from any table between and including the Company and Line_Items tables, so you could, say, find only line items for which the order has not been paid (Orders Table) or pull only those ordered by a certain contact (Contacts Table) and have the report show any field from any of the tables in the chain (obviously there would be repeated data in some fields).

The only way I can see to do this is in FileMaker to have links between every table and every other table further up the chain ie...

Company Table

Company_ID

Contact Table

Contact_ID

Company_ID

Orders Table

Order_ID

Company_ID

Contact_ID

Line_Items Table

Line_Item_ID

Company_ID

Contact_ID

Order_ID

This is a real pain as you have to pay rigorous attention to maintining the integrity of the links and it grows horribly complex very quickly as you add more tables.

Anybody got any better solutions?

Thanks in advance.

DJ

[jasonwood]

You could put the Company ID on the Orders (as a lookup based on contact ID maybe?), then a calculation field in the line item that equals...

Orders::CompanyID

Then search for a particular company ID in that field and you should get all the line items.

[sav]

Hi-

Glad to see I'm not the only one experiencing difficulties with this functionality. I'm developing an application that has tables nested 3 or 4 levels deep. I came to the same conclusion as shown in the logic outlined in the original post - i.e. an ID for the table nested above the current table has to be copied into the current table. Extending that logic shows that, for example, if you want the company ID to show in the orders table, which is nested below the contacts table, the company ID has to be in the contacts table, and you need some kind of a mechanism to copy the company ID into the contacts table and then into the orders table. Yes, this gets seriously complicated very quickly - but I think it can be done if you're careful and meticulous.

The real problem, and the one I'm struggling with now, is how to pull data from tables related to a table more than one level up from the current table into a report created in the current table. In the application I'm developing, we want to track the history of an employee's position assigments (e.g. analyst, project manager etc.) within the organization, and the consulting projects to which an employee is assigned. I have a join table that combines personnel ID and department ID, along with dates of assignment and title. This join table provides a lookup so that I can assign the correct employee with his/her current position assignment and department to a particular project. The kicker is that I need to represent the heirarchical structure of the organization in the department table. I've done that by using a series of self-joins on a field called "owned by" that holds the ID of the next department up the line. This is great for showing the heirarchical structure of the organization. The problem is that in the projects file I want to aggregate at various departmental levels in my report. I can't figure out how to a) pull the data from the self-joins in the department table across the department/staff assignment table and into the projects table, and how to aggregate data in the projects table by whatever I can bring over in a) above.

Sorry this post is more musing and less advisory! I'm happy to hear any ideas anyone might have about getting around or solving this problem.

Thx!

[Pupiweb]

Not sure to follow you properly ...

As far as isolating related records 3-4 levels away from the master file you need (as you're already doing) to "tunnel" relevant keys to the "away" levels ... this indeed makes updating data difficult but IMO this is inherent to the nature of lookup fields

One keys are in place a relationship can isolate "far" records.

Say you want to isolate all line items belonging to a company: relate CompanyID in Compnaies.fp5 to CompanyID in LineItems.fp5 and use a Go to relate record step to isolate relevant records

If you're running a search pass the value of CompanyID to a global in LineItems.fp5 and add that to the search ...

HTH