9 Replies Latest reply on Jan 4, 2009 2:20 PM by dekade1

    Relationship

    dekade1

      Title

      Relationship

      Post

      I'm having trouble doing a particular relationship to get specific data from another table.

       

      Here's what I've got.

       

      1)    I have a record from (Table A).

      2)    On the record I have a portal (Table B).

      3)    One field of a portal row is a (Table B) customer ID number.

      4)    One field of the same portal row is a (Table B) 'piece quantity'

      5)    I then do a FIND in the portal to bring up a list of (Table A) records where each (Table A) record has the same customer ID number

      6)    That set of found records is displayed in a different layout in List Form

      7)    I now have a list of different (Table A) records and they all have the same customer ID number

      8)    With (Table A) now in List Form I have a field on each record for the found set that is (Table A) 'customer piece quantity'

      9)    PROBLEM: I cannot get the (Table B) 'piece quantity' field - to show up in the List Form view of - the (Table A) 'customer piece quantity' field.

       

      For some reason I am missing the relationship here and cannot figure out what it is.

        • 1. Re: Relationship
          Jens Teich
             For each table A record there can be multiple piece quantities. When you want to see all of them you have to change to a list layout based on table B. From table A you can show

          - portal or (without portal) the first record from relationship: tableB::_pieceQuantity
          - the nth record with GetNthRecord()
          - the sum of count of related records: sum( tableB::_pieceQuantity )

          Jens


          • 2. Re: Relationship
            dekade1
              

            You did not understand my issue. I have tried to rephrase it for clarity.

             

            Here's what I've got. Read each step at a time to see where I'm coming from.

             

            1)    I am viewing a record in(Table A).

            2)    On the record there is a portal (Table B).

                   The portal is a relationship to (Table A)

            3)    One field of a portal row is a (Table B)customer ID number.

            4)    One field of the same portal row is a (Table B) 'piece quantity'

            5)    I then perform a FIND via a script(while in a layout in Table A). The script references the (Table B)customer ID number field in the portal. The scripted FIND brings up a found set of(Table A) records. In the found set each(Table A) record is the same customer ID number

            6)    That set of found records is then displayed in a different (Table A) layout in List Form

            7)    I now have a list of different (Table A) records and they all have the same customer ID number

            8)    Next, With (Table A) now in List Form I have a field on each record in the new layout for the found set that is (Table A) 'customer piece quantity'

            9)    PROBLEM: I cannot get the (Table B) 'piece quantity' field to show up on a record in the new layout. (Table B) 'piece quantity' needs to show up in the (Table A) 'customer piece quantity' field.

             

            For some reason I am missing the relationship here and cannot figure out what it is.

             

            I Hope this make things clearer.

            • 3. Re: Relationship
              Jens Teich
                 We need some insight in the underlying data model.

              How many table occurances (TO) are involved and how are they connected? With which tables are they connected?

              Which is the base TO of the layout you try to get a solution for?

              Jens


              • 4. Re: Relationship
                dekade1
                  

                Hi Jens

                 

                1)  TABLE A is Raw Materials

                2)  TABLE B is Raw Materials Inventory

                3)  TABLE B, as a portal, can be seen on each Table A record via 'layout 1'

                4)  The relationship is: Table A, Field: RMID (auto create Raw Material ID) and Table B, Field: RMID (auto create Raw Material ID)

                5)  There is only one TO of Table B

                6)  There is only one TO of Table A

                7)  Table B (Raw Material Inventory) Master Layout contains these fields:

                     Customer Number (data exists as a result of an earlier separate portal entry)

                     RMINVID (auto create Raw Material Inventory ID)

                     Part Number (as "displayed data" from Table A Raw Materials)

                     Piece Quantity (data exists as a result of an earlier separate portal entry)

                8)  Table A (Raw Materials) Layout 1 contains these fields:

                     Customer Number Search (in the Header part) (text entered by user)

                     Customer Number (Main Body) (data exists as a result of some of the steps of the scripted FIND in the Table A) The FIND was generated on layout 1 - in the portal - and in the portal row 'customer number' field)

                     RMID (Main Body) (auto create Raw Material ID)

                     Part Number (Main Body) (text entered by user)

                     Customer Piece Quantity (Main Body) (This is what I want to show from Raw Materials Inventory)

                 

                In Summary:

                 

                I do a scripted portal FIND in the customer number field in the portal in Layout 1 in Raw Materials. The scripted find returns a found set of all Table A Raw Materials that have the same customer number as entered manually in Customer Number Search in Table A Raw Materials. Layout 1 displays the results in List Form.

                 

                Now, here is where I am stuck. I can see the RMID and the part number but I cannot see the Customer Piece Quantity that was on the same portal row that the scripted find found the 'customer number' per my manual data entry in the field 'Customer Number Search'.

                 

                Does this help? I sure do want it to help you. Whatever you need from here on I will continue to my best to describe what I'm doing.

                 

                • 5. Re: Relationship
                  Jens Teich
                     I am not yet sure wether I understood everything.

                  But why do you start with this weird portal search? I would start in the TO customer because you look for information about a special customer. Do you have a table customer?

                  Jens

                  • 6. Re: Relationship
                    dekade1
                      

                    Jens

                     

                    No I do not have a 'Customers' Table.

                     

                    Let me try to explain the wierd search. The portal exists on the Raw Materials Table Layout 1 so that I can show how many of a inventory part has been allocated to different customers. All of the data in this portal is basically manually entered. The table that references this portal is where I store all of the information about how many of a Raw Materials part is allocated to Customer 1, how many of a Raw Materials part is allocated to Customer 2, how many of a Raw Materials part is allocated to Customer 3, and so on. This portal is the Raw Materials Inventory Table. I can just scroll through it and see how many of a part i s allocated out to different customers.

                     

                    There is a portal row field known as Customer Number. Each time I create a new Raw Materials Inventory record I manually type in a customers number. Then I type in the quantity of the part for that customer.

                     

                    I have about Raw Material 1,000 parts. So I need to know which of those 1,000 parts are being used by Customer 1.

                     

                    So then I go to the portal in FIND mode and go the field - customer number. Then I type in "1" for customer 1. Then I perform the find.

                     

                    I then get a found set, Layout 1 in the table Raw Materials, showing customer 1 is using part #500, part #750, and part #888.

                     

                    What I cannot achieve is for the found set to show how many each of part #500, part #750, and part #888 is allocated to customer 1. That quantity, if you remember was manually entered into a portal row for a customer for each of the parts (500,750, and 888).

                     

                    What do you now suggest.

                    • 7. Re: Relationship
                      Jens Teich
                         I suggest to create a customers table.

                      If you want to explore data related to customers you need such a table. There is the right place to create a portal which shows related data per customer.

                      Jens

                      • 8. Re: Relationship
                        swj
                          

                        Hi dekade,

                         

                        Did you ever get this one worked out? I duplicated your design, and have been able to get it to work the way you expect by making three changes:

                         

                        1. The list view needs to show records from 'Raw Materials Inventory' as it contains the Customer ID.

                         

                        2. 'Customer Piece Quantity' needs to be a Summary Field in this table defined as 'Total of Piece Quantity'.

                         

                        3. On the List Layout, change the Body part to 'Sub-summary when sorted by Part Number'.

                         

                        Find the customer ID, then switch to Preview and sort records by Part Number. I think that's what you were after.

                         

                        You could even add another part above that, defined as Sub-summary by Customer ID. Move your customer ID field into this part, sort by Customer then Part number to print a report of all customers and their parts!

                         

                        Forgot to add a link to my sample file Raw_Materials.fp7

                        There we go.

                        • 9. Re: Relationship
                          dekade1
                            

                          Scott

                           

                          Thank you very much for all of that info. I am applying it and other help and I feel that I am on my way to solving beyond what I had originally set out for.