Take the relationship challenge!...

[Dave J]

Hi folks,

does anyone know how to set fields & relationships for pulling data through multiple tables? For ease of comprehension, the example below uses generic tables to illustrate what I am trying to do.

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

Order Table

Order_ID

Contact_ID

Line_Item Table

Line_Item_ID

Order_ID

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 in FileMaker is 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

Order Table

Order_ID

Company_ID

Contact_ID

Line_Item Table

Line_Item_ID

Company_ID

Contact_ID

Order_ID

This is a real pain as you have to pay rigorous attention to maintaining the integrity of the links and just a couple of crashes could seriously compromise the integrity of the system. Even without crashes, as you add more tables, the problem quickly becomes insurmountable from a practical, if not from a technical, standpoint. It gets worse if you wanted to add more tables. The Line_Items Table must already be updated any time data is changed in any of three other tables and any tables further down the chain will need even more updating. For example to track products and parts, obviously you would need

[Ugo DI LUCA]

1776,

If I clearly understood your problem, you need to be able to find any product in your invoice line item that used Component B in the production process.

Well, if you are dealing with production, I think you should have a file for "Components" and a File for "Production Table". In brief, your whole database will look like :

Products (Product_ID, Price,...)

Components (Component_ID,...)

Production Table (Production #, Product_ID, Date,...)

Line Item Production (Production_ID, Product_ID, Component_ID)

Company (Company_ID, Company Name, Adress,...)

Contacts (Contact_ID, Adress, Phone, function,..)

Line Item Contacts (Line #, Company_ID, Contact_ID,...)

Orders (Order#, Date, Company_ID, Company Name)

Invoices (Invoice#, Date, Company_ID, Company Name)

Order/Invoice Line Item * (Order#, Invoice#, Company_ID, Product_ID, Production#, Quantities)

* I do not see the need for the Contact_ID here.

Looking precisely to the Production File.

Production # (autoentered serial).

Product_ID

and any parts from "Components" for this Production Cycle should be entered in a Portal in "Production" through the "Production Line Item".

This would lead to records like

----> Production#1 Product A would use component 1, component 2, component 3 and 4

----->Production#2 Product A would use component 1, component 2, component 3 and 5.

----->Production#3 Product B would use component 2, component 3, component 4 and 5

Your Product still remains the same. You should only "attach" the Production # to the Product in the Invoice Line Item (have a field Production# + a concanated field Product_ID&"-"&Production#) It's quite like having a Validity date for Consumption goods. Products remains the same (price,...) but you need this "code" to find out if some products are out of date . Or easier example a serial # for Computers.

As this code is included in the Invoice line item, you could easily find out which product has been sold within this Production Code while being in the "Production Table" file, and even while being in the "Component" File, using a global, you could find any Production Code attached to it and find out what was sold...No need for a script here (just relationships)...

This would probably get somehow complicated, but this can be done with FM. Do not know much about Access, but I'm quite sure this is simplier here.

Also, reading your post :

The Line_Items Table must already be updated any time data is changed in any of three other tables and any tables further down the chain will need even more updating

Not sure about that. Let say you have an Adress for an Invoice #1050 in Dec02, and the Adress changed from Dec02 to Feb03, you won't update the invoice #1050 with the new adress. This is a single example, but there are lots of them...