Multi-table problems

[morespam]

Hi,

I am fooling around with a mock client/order/invoice system and am having problems referencing some records from one table to another.

I think it’s a simple problem, probably to do with an incorrect table relationship but I can’t seem to work it out ( I cant seem to find how to make a 1to1 relationship in filemaker, which is what I think I need.)

This is the basic layout if anyone can comment.

* = Key (auto/serial number)

Basically the problem is I have a “clients” layout with a portal to “invoices” the portal displays information from the invoice table but I need it to also display a field from the orders table as well in the same portal.

At the moment it always displays data from the first order for that client in all invoice entries.

EG:

The portal for invoices on the client layout displays

It seems to be it has problems referencing data from the orders table.

I could add extra fields to the invoice table and store the values from the order table I need to display but then I have redundant data

Any comments or suggestions would be great.

testing.zip

[IdealData]

Welcome to FMForums

I had a look at your file.

As you're still learning then I'd recommend you simplify your test at this point, and stick to a single relationship to master the relationships and portals. Here's some pointers:-

1. You CANNOT mix data from different relationships for a PORTAL. The related fields must be for the same relationship that the portal is based upon.

2. You probably will have some "redundant" data to support your design, but these can be calculated by relationships.

3. What are the repeating fields ? They don't seem to have much place here. Don't confuse repeating fields with related fields, and the best advice is stay away from repeating fields wherever possible

[morespam]

Thanks allot for the pointers.

>>1. You CANNOT mix data from different relationships for a PORTAL. The related >>fields must be for the same relationship that the portal is based upon.

Is there anyway to do it with what I currently have or would I have to re-design the entire table/relationship arrangement.

>>3. What are the repeating fields ? They don't seem to have much place here. Don't >>confuse repeating fields with related fields, and the best advice is stay away from >>repeating fields wherever possible

I am using the repeating fields to store multiple products on the order (instead of storing them in a separate table). I might do this later on, I am just fooling around now.

What disadvantage is there to using repeating fields?

[IdealData]

Repeating fields:

Okay so long as you're still experimenting. Have a search through FMForums for comments on repeating fields - you'll soon get a feeling for it.

Design:

There's nothing essentially wrong with your outline but understand that FMP won't behave the way you want it to - you must build your structure to accommodate the features in FMP.

Check out "Self join" relationships and "Many to Many" relationships for more clues. There's plenty of help here or the knowledge base at www.filemaker.com.

[comment]

I took a brief look at your file.

1. You should break the line items into a separate table, using a relationship

Orders::OrderID = LineItems::OrderID

with allow creation of records in LineItems.

You should avoid using repeating fields for real data entry - it will backfire when it comes to working with the data. For example, if you want to report sales grouped by product, you won't be able to do that with your structure.

2. It is not clear what is the purpose of the Invoices table. Are you going to have more than one Invoice per Order, or vice versa? If not, then you don't need this table.