7 Replies Latest reply on Feb 23, 2012 8:47 PM by MarcDolley

    Help populate line items so I can have an overview on my Order layout.


      Hi, I have been working on a database that has 3 major sections. Reporting, Customers and Orders.


      I need to let my end user have a list of the previous products a customer has been ordered.

      I had no problem creating this overview with a portal in the customers section, as the portal points to the Order table and all is well.



      Now for clarity I need to have this same order history show up on my ordering page... This is the problem.

      I realize I need to start working with line items to make this happen.




      I have the following Tables: Customers, ProductTracker, and ProductLineItems


      The new table, ProductLineItems, has the following fields __FKOrderID as well as __FKCustomerID


      My relationships are listed for the tables below Prettty much in this order Customers-LineItems-Product



      Customers:_PKCustomerID = ProductLineItems:__FKCustomerID


      ProductLineItems:__FKOrderID = ProductTracker:_PKOrderID


      For some reason I have also drawn a second instance of the customers table Called Customers2Products


      Customers2Products:PKCustomerID = ProductTracker:_fkCustomerID - Is this instance nesscary?




      How can i use the same layout (product tracker) to create an overview for my orders.


      If i use these relationships assuming they are correct. I need to create a record in the Line item table, which I would like to reflect the info which is entered on the ProductTracker table. Users will be using the Track a produt layout and I dont want them to have to leave this layout. My concern is if i get creative and make scripts to brute force values into my Lineitem table im not really setting up the database right and if a user simply clicks the new record button in the ProductionTracker (or product tracker) my coded relationship will be broken and a new record wont be created in the line items.

        • 1. Re: Help populate line items so I can have an overview on my Order layout.

          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



          Marc Dolley

          • 2. Re: Help populate line items so I can have an overview on my Order layout.

            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?

            • 3. Re: Help populate line items so I can have an overview on my Order layout.

              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.

              • 4. Re: Help populate line items so I can have an overview on my Order layout.

                Work flow.


                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.

                • 5. Re: Help populate line items so I can have an overview on my Order layout.

                  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.




                  • 6. Re: Help populate line items so I can have an overview on my Order layout.

                    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.

                    • 7. Re: Help populate line items so I can have an overview on my Order layout.

                      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