Please forgive me if I've missed something, but if you can already see what you need to see from the Customer layout, isn't the solution as simple as creating a self-relationship in the orders layout? The relationship would be from ProductTracker to ProductTracker using the FKCustomerID field. Then you simply copy the portal from the customer layout to the producttracker layout and change the references.
It seems likely that your relationship Customers2Products:PKCustomerID = ProductTracker:_fkCustomerID is what's being used to display the data in the Customer layout. Your new relationship would be something like:
ProductTracker:FKCustomerID = ProductTracker:_fkCustomerID
I am also using <<Full Name>> on the Ordering field, which is failing to show.
This is a calc. Full Name = Customers::Customer First Name & " " & Customers::Customer Last Name This calc is in the ProductTracker Table... Since ive added my line item page, did i break this relationship?
A few things...
1) Product Tracker Layout is writing to the ProductTracker Table.
2)Customers layout has the associated table of customers.
When on the Customers page, if my relationships are correct i should be able to point my portal to the product line items, to see a list of the previous purchased items right?
When on the Product Tracker Page my portal should also be set to line items to see a list of all previous orders...
Am i confused or is this bassically right.
1) Enter customer info.
2) go to the Order page (AKA product tracker layout)
3) Select customer name from drop down menu. This populates the _FKCustomer ID on the table ProductTracker.
4)Enter ordering info.
5)Magically make an overview of the order using a portral on the same layout which shows all previous orders by the same customer already selected via the forignkey field populated in the ProductTracker table.
Questions, if my relationships are right then should forign keys get automatically populated with a primarykey from another table. If not then help, ive fried my brain with this problem today.
Im happy to post some photos, but i know this issue revolves around populating forign keys with primary key info from another table, with out switching layouts. Please help me find out what I am missing to help salvage my relationship and solution.
There's nothing magical about it and the foreign key isn't getting populated automatically. You are manually entering the foreign key by selecting the customer from the drop down menu in step 3. Your relationship between customers and products will allow you to see customer order history in the customers layout as well as allowing you to see customer details (address etc.) in the producttracker layout.
As I see it, you just need to create another table occurrence (TO) establishing a self-relationship from producttracker to producttracker based on the fkCustomerID field. Go into the relationship diagram and select the ProductTracker TO. Duplicate it, rename it ProductTracker_fkCustomerID and pop both open. Drag fkCustomerID from one TO to fkCustomerID in the other and you're done.
Switch back to the customer layout, select the portal and it's contents. Copy it, switch to the producttracker layout and paste it. Change the portal to show data from the new relationship ProductTracker_fkCustomerID and then change the fields in the portal to the matching fields in the new relationship.
That should allow you to see a customers order history from within the producttracker screen.
Thanks sooo much. I was almost rehatching my plan.
This worked as you described. Sorry I could not follow you the first time. I tried so many different things and had changed soo many options by the time you posted trying to DIY... Thanks to you, in the end i was able to get rid of my line item table, yeay! Just what i wanted.
This was my first experience creating another instance of a table and pointing it to its self.
Welcome to the wonderful world of self-relationships. Glad I was able to help.
P.S. Just noticed that submitting a correct answer has earned me four points. Woo Hoo. How many points do I need for an iPod? lol