Possible relationship problem

[xiaobai]

I am a newbie to Filemaker - found a copy lying around in the office and decided to try it.

I am trying to do something that simply won't work, and I suspect that it is a relationship problem. If someone can point me in the right direction I would be eternally grateful!

Anyways, here is the problem:

The database has 3 tables: Products, Clients, and Invoice.

Each product in the Products table has an owner (the client), and is linked to the client table by a matching field. On the Invoice, I was able to select the client to invoice to in my Invoice layout, and have pop-up lists that list only the relevant items the client would buy. This part works flawlessly. (and may be irrelevant to the problem itself)

Now the problem. I have fields in the invoice defined as product1, qty1, descrip1, price1, subtotal1, and product2, qty2, and so on (laid out in a table, typical invoice stuff). It's just supposed to just lay out the products on rows, and calculate some numbers. What I want is to select a "relevant item to the client", as above, and have all of its details such as quantity, price, etc. pop out automatically via a lookup.

The problem is the lookup works great for the first product - popup list works, I choose the item, and all the associated data gets filled in. But it refuses to work for the subsequent products. I read that table relationships is based on one matching field - which i suspect that the database has chosen product1 (Invoice) -->product ID (Products Table). I tried joining Product1,2,3,n to Product ID (many to one?) but that doesn't seem to work. So I think the way I'm doing this is fundamentally wrong somehow.

Should I have n occurences of the product table, one for each product(n) --> product ID relationship?? Or maybe scripting would resolve this?

Any advise would be great.

Thanks!

[RalphL]

What you are missing is another table, InvoiceLine, which has the fields InvoiceID, Product, Qty, Descrip & Price. Replace all those fields in the invoice with a portal. The lookup will be done in the InvoiceLine table.

[LaRetta]

Hi xiaobai,

You are missing one table: LineItems

It's best not to have multiple 'like' fields in Invoices to hold your sold items. LineItems should be related to Invoices on Invoice Number (or unique ID). In this way, you can add multiple purchases (and payments/adjustments) to an Invoice.

I'm unclear why you have Clients attached to Products but I would suspect it's in an attempt to resolve these issues. Products should be attached directly to LineItems. This allows easy selection of a product to create each line item.

Update: Hi Ralph! What Ralph calls InvoiceLine and what I call LineItem are the same thing. Put them both together and you should have a pretty good picture of what you need.

LaRetta

[xiaobai]

Thank you Ralph and LaRetta!

I'm a little confused about how I can populate the LineItems table automatically. Also, the way I have setup my tables, LineItems is connected to the products table, but it would only link to the Invoice table (by invoice #) if another occurence of LineItems is created.

Ralph: Will using the extra table and the portal give me the ability to select which products to include in my invoice one by one?

LaRetta: I have clients attached to products because each client has a very specific product line they have us manufacture for them - and I am a sucker for those pop up lists. I like to choose a client to invoice, then have all the products in the client's product line (but I'm selling these things to him) pop up. So in Products table, for each product I have a field that says what client this item belongs to.

Is that a stupid way to go about it?

[LaRetta]

Nope. Not stupid at all. And I can tell by your clarity that you'll work it out just fine.

Add that LineItems piece and you'll be in business!!

Update: Oh, you slid additional words into your post! I'll let Ralph take that one (if he can and so wishes) ... he's better at relationships than I am.

LaRetta

[xiaobai]

Let me post my relationship diagram, perhaps it will clarify my clumsy description?

[LaRetta]

Posting your relationship graph will always help us. But I'm unsure how best to provide the conditional products you require. There is nothing wrong with your needs nor your description of them.

I believe you will need another relationship from LineItems to Products (filtered through Clients) and I've just never done that before. So I would hate to make suggestions on something I've never used myself in a business environment. There are many great Developers on this Forum who possess direct experience on it. They can provide more qualified assistance.

I'll give my two-cents if I think I have something worthwhile to contribute though.

Update: Even full screen, I can't read the text ... my eyes aren't so great. Maybe others can read it - or can you make it bigger?

LaRetta

[xiaobai]

Thanks LaRetta. Here's it is enlarged abit if you are still interested!

[comment]

See if the attached helps.

1120725561-customer_sProducts.fp7.zip

[RalphL]

Comment's file looks good to me. The InvoiceLineItem is also the join table in aa many to many relationship between Invoice and Product. In Product you could add a portal to show the invoices in which the product was used.

As it is setup now only one customer can buy a product. Is this what you want?

[xiaobai]

Thanks Comment, that is a clever solution and works nicely!

Ralph: Well, one customer per invoice.

I'm surprised that there isn't something like MS Access's dlookup() in filemaker, which would do away with the need for such fancy table design.

[Fenton]

What Ralph is pointing out is that you said:

"Each product in the Products table has an owner (the client)"

That is odd. It is more common that a product would have 1 "vendor." Usually a Customer (or Client, whatever that means exactly) does not "have" a product, they "buy" products. So "their" products would be whatever products they have bought, which would be single instance of each unique product from all their purchases (line items).

In that case a "customer's purchased products" would be available directly, in 7, by a straight relationship path from:

Client -->Invoice-->Invoice Lines-->Products

If you want to choose from preselected products for each client, and if a product could be selected by more than 1 client, then you really need a "join" table between Clients and Products. It would need to be pre-populated with all clients and all their products, and/or a mechanism to add a client-product pair; such as the portal in Client, but to the join table.

FileMaker has lookups, as either an auto-enter function, or as a calculation (new to 7), as well as the "lower, higher" options.

It is probably true that FileMaker often uses more "fancy table design," if you mean the relationship graph; whereas maybe Access uses more "procedural" methods (I've only looked at Access briefly, so can't really compare). The graph may seem complex, but it is built using the relational logic of your solution.