4 Replies Latest reply on Sep 3, 2016 4:43 PM by johan_chou

    Cannot display data from line items

    johan_chou

      Please help.

      I have been having this problem with displaying data from the Inkoop table or the inkoop_items table on the producten table via a portal.

       

      The setup of the relationship is as follows (see the picture) :

      I have a producten (which means products) table from which I made a layout from where each product is displayed (see picture 2). On this page i made a portal to display data from the inkoop_table. The inkoop_table is then linked to the inkoop_items table from where product purchases from suppliers can be made. These product purchases are the same products as the products displayed in the producten table.

       

      The relationship of the table are as follows:

      producten:: primary key is linked to inkoop::foreign key

      Inkoop:: primary key is linked to inkoop_items::foreign key

      and then there is a link from inkoop_items::foreign key to the inkoop_items proud::primary key (table occurence of the producten table)

       

       

      I have tried different combinations of linking the tables and nothing seems to work.

        • 1. Re: Cannot display data from line items
          philmodjunk

          producten:: primary key is linked to inkoop::foreign key

          Not according to the relationship graph that you posted. You have the opposite:

           

          producten:: foreign key is linked to inkoop::primary key

           

          I can tell this from the way the relationship lines connect. A single line connector indicates a field that must function as a primary key (unique identifier) in the table. It is either an auto-entered serial number or "unique values" are specified for the field. Either way, this would seem to be the first thing to correct.

           

          And for those of us trying to make sense of your translation, what does "inkoop" mean? Invoice?

          • 2. Re: Cannot display data from line items
            johan_chou

            Thanks philmfdjunk for your reply

            but i did have the correct relationship that you mentioned in the graph that I posted. The producten::_Pk_Product_ID is the primary key and it is then linked to the inkoop::FK_product_inkoop (which is the foreign key). Or at least by double checking it now, i do have the correct relationship, but it still isn't working (that is the display the data from the inkoop table on a layout based on the producten table.

             

            "inkoop" is Dutch for purchase (so the opposite of sale)

            • 3. Re: Cannot display data from line items
              philmodjunk

              Please look at the edited clip of your relationship graph that I have attached to this reply. The connector circled in red cannot be a foreign key. If it could function as a foreign key, it would have the "crow's foot" connector as shown circled in blue.

               

              On further reflection since my first response, the field with the blue circled connector may or may not be set up as a primary key. With the table occurrence box collapsed to its smallest size, I can't see for sure, but "B2BOrdernumer" is not a foreign key and its name suggests that this is intentional, that it serves as the primary key for "InKoop" what we in English would call a "purchase order".

               

              Since this is the relationship directly linking Producten to Inkoop, you do not have the relationship that you indicated that you have here.

               

              Going by the table occurrence names, a link between Producten and Inkoop really does not make sense. A given Purchase order (Inkoop) will list many products (Producten) in a related table of line items (Inkoop_Items). Each record in Inkoop_items can link to a record in Producten.

               

              It would look like this:

               

              Inkoop----<InKoop_items>-----producten

               

              Inkoop::__pkInKoopID = InKoop_Items::_fkInKoopID

              producten::__pkProductenID = InKoop_Items::_fkProductenID

              • 4. Re: Cannot display data from line items
                johan_chou

                Hey philmodjunk

                 

                You were right about the relational graph that i posted, but that was the wrong screenshot. This is the actual screenshot i wanted to post with the question. Which corresponds with the description of the relationships of the table:

                producten:: primary key is linked to inkoop::foreign key

                Inkoop:: primary key is linked to inkoop_items::foreign key

                and then there is a link from inkoop_items::foreign key to the inkoop_items proud::primary key(table occurence of the producten table)

                Screen Shot 2016-09-04 at 01.24.24.png

                However, even with this relationship, i am not able to show data from the inkoop table and or the inkoop_items table through a portal that is located on a layout based on the producten table. (as shown in the other screenshot)

                 

                The way I have the layout orderd is that you have a page where your products are shown using a list view where the  on the right side. And on the left side I have the different tabs (inkoopopdrachten, Orders uit, Voorraad correctie). The Inkoopopdrachten tab contains the portal based on the Inkoop table. within this portal you the user can see how much purchases has been made for that particular product.

                Screen Shot 2016-09-04 at 01.28.54.png

                Do you still think that i need to change the relationships based on your previous answer, or is it something else?:

                          It would look like this:

                 

                          Inkoop----<InKoop_items>-----producten

                 

                          Inkoop::__pkInKoopID = InKoop_Items::_fkInKoopID

                          producten::__pkProductenID = InKoop_Items::_fkProductenID