Multiple records from same related field?

[crane]

I'm a relative newbie with FileMaker, and am not sure how to go about what I'd like to do, and not sure if it's ridiculously easy/obvious or not entirely possible. I have three databases: the main one is an inventory of products (ProductID, Name, Description, Quantity, etc.)--let's call it INVENTORY. The second one (that I'm working on now) would be a record of all purchases/sales of these products for our non-profit--let's call it SALES. This database would store the name and contact info of the purchaser, and the items that they purchased. I have it set up to do the calculations for quantities, sub-totals, taxes, shipping etc. Each item in INVENTORY has a unique ProductID, so I would like to be able to either type in the ProductID or select it from a drop down menu in SALES, and it would automatically populate the Product Name, Product Retail, and ManufacturerID fields from INVENTORY. I can do this now with just one record/product, but can't figure out how to have a list--like on an invoice--of multiple items, each with a unique ProductID.

 

If I can figure this out, I would like to link a third database of the contact info and details for the manufacturers--actually artists and designers--called ARTISTS. I would like to link which of each artist's items are purchased into the ARTISTS database, so that I can pay them their part of the proceeds and keep track of inventory. Unfortunately, this is kinda over my head--so hoping that someone might be kind enough to help me figure this out!!

 

Thank you in advance!!

[kirkrr]

To get related record content to display, the field that you are setting must be the one that is defined in the relationship. That is the way a relational database works. You need to be setting the unique field on one side of the relationship, that will give you the unique desired information to be displayed.

[crane]

The field I have defined in the relationship is the ProductID. Each record is unique and has a unique ProductID. Right now, I have it set up in the second database as a relationship and it works as I had hoped, but I can only have one record. I can type in a ProductID to the relationship field (i.e. EM01), and it does as I would expect: it populates the Product Name, Artist Name, and Retail Price. Unfortunately, many of our orders have more then one item. Is there no way to have it pull more then one record using the same field?

[kirkrr]

What is on both sides of the relationship, and what TO are these in?

 

That might help to know.

 

To get multiple related records to display, they would 1) be displayed in a portal and 2) have a foreign key field (the child side of a relationship, non-unique, not serialized) value be the same as the unique primary key of the parent TO.

[crane]

Hi kirkrr et al,

 

Sorry--but I really don't understand your reply. What is a TO? My understanding is that a portal won't work because it will show data from all related records. I did lots of trial and error to try to figure this out and tried to put a portal into the layout, but I can't get it to allow entry into any of the fields.

 

This is what I would like to do, and I'm still not clear whether or not it is possible:

 

I have 2 databases: INVENTORY & SALES

 

Example Records in INVENTORY:

Record 1: WG01 (ProductID) Whirligig, small (ProductName) $15.00 (ProductRetail)

Record 2: WG02 Whirligig, medium $25.00

Record 3: TH01 Tophat, black $30.00

 

There are many other fields in the database w/ details about each of these records, but these are the fields that I want to have available in the 2nd database.

 

In SALES, I would like to create an invoice for Joe McDonald. Joe bought 2 small Whirligigs, and 1 Tophat, so in the SALES database this is what I would like to do:

 

- create a new record

- type in Joe McDonald's name, shipping, and billing addresses, type in the date of his order, the order number will automatically be create by FileMaker, I will also select the type of shipping

 

ALL of these parts are working fine, but the most important part is this:

 

- under ProductID, type in or select "WG01" in a field, and it will populate the ProductName and ProductRetail fields, i.e. "Whirligig, small" and "$15.00" (I can do this for 1 record)

- still under ProductID (or ProductID2?) type in or select "TH01" in a SECOND area/field, and it will populate the ProductName and ProductRetail for TH01, namely "Tophat, black" and "$30.00"

 

How do I do this?

 

Thanks!!!

[mjonas]

Hello Crane,

 

I'd say your problem is not quite with Filemaker but with the logic of a database so let me try to help you on that.

 

You are trying to connect multiple Products to one single Invoice, which sounds like a n:1 relationship (n Products - 1 Invoice) But it isn't, as one single Product may well be in multiple Invoices, i guess, which is a n:m relationship. And they can only be implemented properly with something called "Join-Table".

 

You need more "Databases" as you call them, I'd prefer to call them "Tables" as Filemaker does it, because with "addresses" "Invoices" and "Products per Invoice" you are actually trying to store 3 things in one table you call Sales: what happens if the two or more invoices go to the same person? You don't want to store name and address twice, that's not quite database-like. So I'd recommend you the following structure:

 

Table 1: Clients

with ClientID, name, adress, etc.

 

Table 2: Products

as you have it already

 

Table 3: Invoices

Contains InvoiceID, FK_ClientID (to get the address from the clients table) and all your calculation and summary fields

 

Table 4: Sales (the join-table I've mentioned earlier)

Contains FK_InvoiceID ("to which invoice does this single Sale-Event belong?")

and FK_ProductID ("which product belongs to this single Sale-Event")

a field for the quantity of a product which is sold in one single Sale-Event

 

I can't upload files here but maybe you can pm me your mail and i can send you a example file..

 

greetz

Jonas