I come from a SQL background and am used to being able create joins between multiple tables to get at the data I need. With FileMaker, I am limited to a one-to-one join and I would appreciate any ideas for how I can accomplish the following:

I have a Company DB that relates to an Invoice DB. The Invoice get LineItems from a LineItems DB that gets product information from a Product DB. On the Company DB Layout, I want to have a portal that shows all Products that the company has purchased (i.e. been invoiced for). Essentially, I need to find all Product ID's that related to that Company ID through an Invoice.

How can I do this? I already have a portal that shows Invoices, but I want to go one level further and show the individual products purchased from a Company layout. Is this possible? Am I making it more difficult than it really is?

Thanks for your help!

Your answer is here:

Matrix Data Modelling techniques

http://www.nightwing.com.au/FileMaker/resources.html

Thanks Fitch, this looks like it should do the trick. Sorry about the slow response, last week was vacation.... wish I was still there!

OK, I set up the fields and relationships following the Matrix Data Modelling method (thanks Fitch). I currently have a Portal on my Company DB that can list Products that have been listed on an invoice for the company. However, it only lists products that appear on the first invoice for that company.

I need to somehow get it to show all products from all invoices in a company. Is it possible to put a portal inside another portal? Or am I just crazy? Is there any way to display all products that have been listed on any invoice for a company?

Thanks again for your help.