Jump to content
Sign in to follow this  
Robert Collins

Relationship problem!

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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)?

Share this post


Link to post
Share on other sites

"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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Nuri Baba 
×

Important Information

By using this site, you agree to our Terms of Use.