Relationship problem!

[Robert Collins]

I am currently developing a new database for our mobile phone retail store and have hit a brick wall with one of the features I am trying to implement. I will try to keep this brief!

this is what I have (that is relative to my problem)

1)Sales Invoice table (which holds customer orders)

2) Sales line item table (which holds the individual lines they have purchased.)

The main layout shows a portal to the line items table and there is a relationship to link them via an Invoice_number field. - this works fine.

There is also a second portal on the layout that shows all customer activity -for this I have created a relationship from sales_line_items to sales_line_items that matches both a customer name and password field (these 2 fields belong to Sales_line_items table) to make sure it only shows that customers purchasing activity -

Here is the problem - Each sales invoice needs a customer name / address and password field -

If I create a customer name and password fields in Sales_line_items then I can get the customers other purchases up on the second portal - BUT I have another layout that requires the customers name next to each line item which this does not do!. If I have the name and password fields created by the sales invoice table , I can get it to show the customers name beside each line entry but I cannot get it to show the customers purchasing activity on my main layout.

I hope that makes some sort of sense and any help would be appreciated.

thanks

[David Jondreau]

You need a unique id number for each customer. That is what you should use in all your relationships, not customer name and password.

Also, you don't need to put the fields that the relationship is based on in the portal at all.

[Robert Collins]

thanks for the quick reply . So are you suggesting that I have another portal on my main page layout to a customer_file table and have a field auto input a incremental serial number of 1 to give a unique reference?

[David Jondreau]

I'm saying each of the records in your Customers needs a unique serial number. Same with your Invoice table and Line Items table. Then your Invoice table needs a CustomerID field and your LineItems needs a InvoiceID field. Create your relationships on those fields. That's basic Filemaker stuff.

Let me restate what I think you're asking...

You have a layout based on the Invoices table. That layout shows records in a portal from the LineItems table for that Invoice. You want that layout to also show all other LineItems for the Customer to whom the Invoice belongs. You currently have that working (using CustomerName and Password rather than a numeric id field). You have another layout and some things aren't working the way you want.

Can you clarify that, as that seems to be your problem, not your relationships (except for the improper key fields)?

[Robert Collins]

"Let me restate what I think you're asking...

You have a layout based on the Invoices table. That layout shows records in a portal from the LineItems table for that Invoice. You want that layout to also show all other LineItems for the Customer to whom the Invoice belongs. You currently have that working (using CustomerName and Password rather than a numeric id field). You have another layout and some things aren't working the way you want."

Yes, that is correct.

I see your point about using a customer id and am going to have play around with that. I do believe can acheive what I want if I can just get a field in sales_line_items to lookup the name field in Sales invoices - that way the customers name is entered once but it appears in all of the related Sales_line_items records. The relationship they have is via invoice number (which you mentioned earlier) but for some reason, it doesn't want to look it up.