Noob Multi Relationship Question

[wlisac]

So I'm pretty new to FileMaker and I have a relationship graph question. A screenshot is attached to make this easier.

My companies have many line items. My products have many line items. However, my products also need to have many prices, but these prices depend on which company is getting the line item. All fields in LINES need to be looked up from PRODUCTS and PRICES.

How would I hook PRICES up to the relationship graph? I tried creating different TOs of Prices but I can't seem to figure out how to get LINES to properly look up PRICES dependent on the company.

Thanks for any help!

[bcooney]

What kind of transaction are you creating? A Purchase Order or an Invoice?

Because, Companies don't have line items. POs and Invoices do. And then, that line item is related to a Product. The price that comes into the PO or Invoice also take into account the Company related to the transaction.

[wlisac]

It is an invoice. I removed the Invoice table from the graph to simplify it. But what you are saying is exactly what I'm trying to do.

Create an invoice, add line items, get the product info from the products table, and get different prices for each product depending on which company is placing the order.

Thanks for your help!

- Will

[wlisac]

Maybe this will make it a little more clear.

This is what I've come up with but I can't find a way to properly connect Prices to Line Items.

The real kicker is that while many companies can have many prices, and many products can have many prices - one company can only have one price for each product. Lol I hope I'm making sense!

Thanks for any help,

Will

Edited March 26, 2010 by Guest

[wlisac]

Ah ha! I just got it to work, but is this the best way:

I created a field called _pk_prices_id in the PRICES table that = _fk_companies_id & _fk_products_id with unique validation. I then linked _pk_prices_id to Line Items with a field called _fk_prices_id where _fk_prices_id also = _fk_companies_id & _fk_products_id but this time not unique.

Does this make sense to create a key that is determined by concatenating other keys?

Thanks!

[bcooney]

You should have a child table to products that is a join between companies and products. It'll have CompanyID and ProductID and Price.

Then, the invoiceline item (which needs the CompanyID from the Invoice) will relate to this join by companyID and ProductID and lookup Price.

[wlisac]

That sounds like what I did - I called this "child" table "PRICES".

However, I'm not relating PRICES and LINES through the fields CompanyID and ProductID directly, instead through another field called PricesID that = CompanyID&ProductID

Is there a reason to hook it up through the two fields? I didn't because it ended up being a many-to-many relationship. By forcing PricesID to be unique it creates a one-to-one relationship.

[bcooney]

A multi-predicate relationship is better than a concatenated key, esp since CompanyID&ProductID for company 1010 and Product 60 = 101060, and CompanyID 10 and Product ID 1060 both equal 101060! So, you'd need more than a simple combo of the IDs.

Why can't you relate Line Item to Prices by CompanyID and ProductID. Why do you see this as a many-to-many? Each line item is one product, and so will "find" that one product in Prices (when also matched to CompanyID).

[wlisac]

Ah, you're right. It does work! I was confused by the 3-prong connectors on both ends of the relationship:

I guess I could still make sure the price is unique by having a PriceID field in the Prices table still = CompID&ProdID... I was told early on to make sure my serial numbers had unique leading text... for example company serial numbers are COMP0000001 and Products are PROD0000001

Is that dumb to have leading text? Also, is there a better way to make sure that there is only one price for each comp/prod combo?

Thank you so much for being so generous with your help!

You are awesome!

- Will

[bcooney]

I imagine that on your Product form, you have a portal to prices. Since a company can only have one price for each product, it is during the operation of adding a price to a product that you would verify that this is a unique entry.

Therefore, you need to script entering a record into Price.

Yes, you could use your concatenated key, and make sure that it is unique. Leading text is fine. I prefer a number for my keys, but afaik, there is no significant drawback using a text field.

[bcooney]

I was wondering, could you have a customer type? Rather than a price point per product per customer, perhaps a price per product per customer type? It would be a lot less data entry in the join table.

[wlisac]

It isn't a price per customer, it's a actually a price "per team" that will be selling to the end customer. There likely won't be more than 10 teams so I'm not too worried about the stress on the join table. However what you said has made me think. I might have categories.... but with a twist haha :)