Creating a one to many relationship

[mcole]

I am trying to figure out how to create a one-to-many relationship. I have a customer file and an invoice file. Each cutomer will have more than one invoice. I have been able to create a relationship by making a foreign key in the Customer file (cust|invID) and by making it a repeating field, I have been able to add each new invoice as it is created. But then I run into problems when trying to display this information in a portal, because it only displays the first related match information. I then read that it would be more versatile to make the foreign key a non-repeating field because you can then add thousands of keys to the field. Okay, I have done this but I have to enter one key, press Return and then enter the next one. Is this what you are suppose to do? When I had the repeating fields, I was able to make the field a Popup List of all the primary keys to choose from but I can't seem to do this with a non-repeating field. It works with the first entry but then you can't get the listing to reappear to add other primary key values if you hit the <Return> key and if you tab back into the field to get the popup then it simply replaces the first listing. Each invoice will only have one customer so this is pretty straight forward, but I really don't see intuitively how you make a one-to-many relationship and put the multiple keys into the foreign key field. I would appreciate any help on this and I apologize if this is not clear.

Thank you,

Michael Cole

[LiveOak]

I think you are working too hard. A one-to-many relationship doesn't require multiple keys in repeating fields or multiple keys in one field. To link multiple invoices to the same customer all you need to do is have a unique CustomerID for each customer and enter this as the foreign key in the Invoice file.

e.g.

CustomerID = 5 ---> ForeignKey = 5, Invoice 1001

ForeignKey = 5, Invoice 1008

ForeighKey = 5, Invoice 1058

To create a new invoice for the current customer, just create a new record in the Invoice file and set the ForeignKey to the CustomerID of the customer you are looking at. This can be done automatically by clicking on the next blank row of a portal for the relationship (CustomerID ---> ForeignKey). If your relationship has "allow creation of related" records checked, clicking on a blank portal row will automatically create a new record and populate ForeignKey for you as soon as you type anything in the new row of the portal.

I think you are getting one-to-many relationships confused with many-to-many relationships. One implementation for these is to use multiple entries in a field.

-bd

[mcole]

Thanks for the reply bd. Yes I am working too hard. But I think that my confusion has to do with how FM works. If I am entering an invoice and have brought in the customer information, then I have a one-way relationship which is one-to-one in that invoice and from the invoice file I can find the customer. But then I want the customer to have a record of all the invoices so that all the invoices can then be viewed from the customer file. If I have the relationships made (customer ---> invoice and invoice ---> customer). I make the new invoice AutoEnter and select the customerID from a popup list in the foreign key field (pointing to the customer) of the Invoice file. Can I automatically have the invoiceID added to the foreign key field of the customer file? Wouldn't this be a multivalued key field? Am I just losing my mind?

Thanks,

Michael

[LiveOak]

You confusion is not with how FileMaker works, but with the concept of relational databases in general (they all work this way). To create the reverse relationship you DON'T have to enter a foreign key into the Customer file. If you have entered the CustomerID into the ForeignKey field in the Invoice file, just create a reverse relationship with:

ForeignKey ---> CustomerID

If you want to initiate the creation of an invoice from the Invoice file, create a new record and enter (from a pulldown showing CustomerID and Customer Name) the desired Customer ID into the ForeignKey field. You don't need a separate foreign key field in the Customer file.

-bd

[mcole]

OK let's see. So if I make a relationship from the customer file (invoiceID) to the invoice file (InvoiceID) and from the invoice file (InvoiceID) to the customer file (InvoiceID) then I will have a two-way relationship.

So I made a relationship: custInvID (customerfile) ----> invID (invoice file) and from custID (cust file) ---> invCustID (invoice file) and from invCustID (invoice file) ---> custID (customer file) and invID (inv file) ---> invCustID (customre file). I now have a two-way relationship between the custID and the invCustID and from the invID and the custInvID fields. And now when I enter a new invoice and put the customerID key in , FM automatically creates the link. Then when I go back to the customer view I have a portal that show all the invoices that are related to that customer. The portal is defined by the custID ---invCustID relationship and shows the information in the field invID. FM enters this automatically and the links are there. I would assume that when you create a two way link that the software assumes that you wnat that information. Or is it by virtue of making the relationships that you are telling the software what you want it to store?