How to avoid circular relationships

[obazoolo]

Here is my setup:

Tables:

Company Table with ID

Invoice Table with CompanyID

Item Table with InvoiceID, ProductID and ShipQuantity

Product Table with ID

Order Table with CompanyID, ProductID, and OrderQuantity

Relationships:

Company::ID = Item::CompanyID

Invoice::ID = Item::InvoiceID

Product::ID = Item::ProductID

This setup allows invoicing and reporting for each shipment.

I take wholesale orders - placed once per season - and fulfilled as products become available. Therefore I have an Orders table that has one entry for each combination of Company ID and Product ID (i.e. company 1 orders X# product 1, Y# product 2... company 2 orders A# product 1, B# product 2... etc.). I need to have a report that tells me the (OrderQuantity - ShipQuantity) per customer per product (i.e. I need to ship 200 more products 1s to customer 1, 100 more product 2s to customer 1 etc.) This was very easy to setup in MS Access using a crosstab query.

The problem in FileMaker is that I cannot relate both Item::ProductID=Order::ProductID AND Invoice::CompanyID=Order::ComanyID without creating a circular relationship (and thus the "There cannot be more than one relational path between any two tables in the graph" error). Creating a separate Table Occurrence does not work because I need each entry to relate the Order table's Company ID AND Product ID in order to perform the operation. (i.e. if I link Item::ProductID=Order::ProductID and use a new TO for Invoice::CompanyID=Order::ComanyID, the Products line up, but the Invoice::CompanyID's are all the first company and so I cannot calculate OrderQuantity - ShipQuantity. and if I use Invoice::CompanyID = Order::CompanyID and a new TO for Item::ProductID=Order::ProductID, the customers line up but the Item::ProductID is all the first product and I cannot calculate OrderQuantity - ShipQuantity).

Is there anyway to set this up? I do not want to reorganize the shipment process - I am hoping to find a solution that involves tweaking the Orders table if possible.

Any help would be appreciated.

Thanks.

[Vaughan]

"Creating a separate Table Occurrence does not work..."

Well, that's the only way to do it, and it does work.

"I need each entry to relate the Order table's Company ID AND Product ID"

Relationships can be based on more than one field. Have you tried doing this with a separate TO?

[comment]

The problem in FileMaker is that I cannot relate both Item::ProductID=Order::Pr oductID AND Invoice::CompanyID=Order: :ComanyID without creating a circular relationship

I think the REAL problem is that you need a relationship between Orders and Items, based on both CompanyID and ProductID - but there is no CompanyID field in the Items table. This could be solved in a number of ways, the easiest one would probably be to lookup the CompanyID into the item record.

BTW, I am surprised that there is no relationship between an item and the order it's supposed to fulfill. By your description, a company can have many orders for the same item (in different seasons), and it's not clear to me how you can distinguish between them - even if you add the lookup.

I believe the relationship between Orders and Items should be based on OrderID, and an item needs to be actively assigned to an order at the time the item is created.

[obazoolo]

Thanks for your help.

Vaughan - I have tried using separate TOs, but it is not working as intended.

Comment - That is the exact problem I am having - trying to link Orders and Items, based on both CompanyID and ProductID. I will try to lookup the CompanyID into the Items table.

While I do use Order IDs, I currently have a separate database file for each year - therefore there is only one Order per customer in each database file.

[comment]

Two notes:

1. Since Orders need also to be related to Companies, you MUST use a second TO of Items in order to create the relationship between Orders and Items. This is normal and shouldn't pose any problems. You just need to be careful to sum Items 2::Quantity, not Items::Quantity, from the point-of-view of Orders.

2. Lookups from parent records do not work if the parent record is not committed.