Wrapping Head Around Seemingly Simple Relationship

[SShefer]

I'm just coming back to FM and finding some difficulty on creating, what seems to be, a simple relationship.

In our database we have Inventory Items. A single item can be purchased from multiple suppliers, ex: a pencil. We also get different pricing form each supplier. A supplier is just a company that provides an item, a company is used elsewhere in our database as well.

The basic schema is attached to this post.

Here's what works:

- I can create an item

- I can create suppliers for the item in the portal

What's doesn't work:

- When I create a new price tier, it creates a new record in the supplier table and sets the SupplierID incorrectly in the PricingTier table.

The lookup in the PricingTier portal is a value list that pulls the supplierid from the Supplier table and the company name from the Supplier_Companies table where related to the Inventory Items table.

Not sure if that made sense but I would love any input on what I am doing wrong. Another tip I would find helpful is how I can get the potential suppliers to only appear once in a portal value list and then not appear for following records. For example, if I can Company X as a supplier of Paper and I go to add another supplier I do not want to see Company X in the value list again.

Any help would be GREATLY appreciated!

[comment]

If I understand correctly, you should have:

Companies -< Prices >- Items

Note that you cannot create new suppliers (companies) in a portal from Items to Prices (or new items in a portal from Companies to Prices).

Re your second question, see:

http://fmforums.com/forum/showpost.php?post/274656/

[SShefer]

Not sure if I explained it correctly. Supplier_Companies is just a reference table so that I can create Supplier relationships between items and companies.

Items -< Suppliers -< Prices

Essentially I am trying to build the following relationship:

- ITEM: Pencil

-- SUPPLIER: 1 - Mead

--- PRICE TIER: Min 1:, Max: 100, PricePerUnit: 10.00

--- PRICE TIER: Min 101:, Max: 500, PricePerUnit: 9.00

-- SUPPLIER: 2- Pentel

--- PRICE TIER: Min 1:, Max: 100, PricePerUnit: 9.50

--- PRICE TIER: Min 101:, Max: 500, PricePerUnit: 9.20

I need the separation because suppliers also have other child elements that need to link back. This is to calculate the best price/supplier to order from on large orders.

If there is a better way to do this please let me know.

[comment]

Let me expand a your example a bit:

- ITEM 1: Pencil

-- SUPPLIER 1: Mead

--- PRICE TIER: Min 1, PricePerUnit: 10.00

--- PRICE TIER: Min 101, PricePerUnit: 9.00

-- SUPPLIER 2: Pentel

--- PRICE TIER: Min 1, PricePerUnit: 9.50

--- PRICE TIER: Min 101, PricePerUnit: 9.20

- ITEM 2: Paper

-- SUPPLIER 3: Acme

--- PRICE TIER: Min 1, PricePerUnit: 12.00

--- PRICE TIER: Min 101, PricePerUnit: 10.00

-- SUPPLIER 4: Pentel

--- PRICE TIER: Min 1, PricePerUnit: 11.50

--- PRICE TIER: Min 101, PricePerUnit: 10.30

As you can see, Pentel supplies both paper and pencil. Going by your structure:

Items -< Suppliers -< Prices

you would have to enter Pentel twice in the Suppliers table. I am not sure what you mean by "just a reference table": if Suppliers is an occurrence of the Companies table, then the above structure cannot work (because - hopefully - you do not have two records for Pentel). If it's another table then it must contain redundant records (and actually, the table itself is not required either).

[SShefer]

Thanks again for the response.

Yes - that example is correct, and I apologize if my terminology is off. Supplier_Companies is an occurrence of the Companies table so that I could link up the two (I'm also trying to use the anchor bouy method for more readable relationships and that may be throwing me off) - do occurrences not work that way?

Even when I remove the Supplier_Companies occurrence and try the example with a direct relationship to the Companies table, the problem persists and every time I add a Price Tier a new Supplier relationship is created.

The suppliers table just captures the inventory_item_id and company_id and assigns that relationship a supplier_id so that the pricing and additional child records can be attached.

I tried taking some screenshots of the interaction. Everything looks as if it's working but when a new price tier is added it also creates a new Supplier relationship.

Thanks again for any help and I apologize if I'm not being 100% clear.

[comment]

Even when I remove the Supplier_Companies occurrence and try the example with a direct relationship to the Companies table, the problem persists and every time I add a Price Tier a new Supplier relationship is created.

As I said earlier, I believe you need to change the basic concept to:

Companies -< Prices >- Items

For the current purposes, It makes no difference if you use the "original" TO of Companies or another one (although there are some practical advantages to using the same TO). The important thing is to link the prices directly to the items they represent, instead of going through the suppliers.

[SShefer]

Thanks for the quick reply - I will try it out.

Just out of curiosity, why can't FileMaker interpret the original relationship I've set up? My concern is that we have hundreds of Company records, but only 1-3 will be suppliers of an individual item. I'd like the related elements (pricing, specifications, documentation, etc.) to only show the available suppliers to reduce database load. I'm also using this architecture because it was the framework for our SQL based app.

Am I just over thinking this?

[SShefer]

As a follow up, what is the best way to tie in these relationships? I will have a need to connect the Companies and Inventory Items table together using other tables for specific data and am now not sure what the best way to move forward is.

[comment]

I think there are two separate issues here, which may be slightly confusing. The first issue is technical: you cannot use a portal to create records in a table connected through another table, for example if you have:

Parent -< Child -< Grandchild

and you try to create a new Grandchild from Parent, Filemaker will also create a new Child (or at least it will try to). However, I believe this is a side issue, and can be ignored for the moment, at least.

The real issue, IMHO, is your basic data model. As I said earlier, the only way you can make:

Items -< Companies -< Prices

work is by having a duplicate company record for each item it sells. That, of course, is not "working" at all - and it won't work in any other relational database either, for the same reason.

The fact is that the relationship between Companies and Items is many-to-many. Therefore you require a join table to be placed between them. The simple arrangement is to use Prices as the join table, so that each individual price is a "join" (with 3 price tiers, you'd have 3 join records joining the same pair of parents).

Alternatively you could use a dedicated join table, and make Prices the child of this table. But I don't see any advantage to such complication.

I will have a need to connect the Companies and Inventory Items table together using other tables for specific data and am now not sure what the best way to move forward is.

I don't know - so far you haven't said anything about inventory. I assume the "items" we have discussed are items that you can purchase from a supplier (or several), not items already purchased. That's a whole different ball game.

Edited October 2, 2012 by comment