8 Replies Latest reply on Jan 1, 2009 7:28 PM by dekade1

    Why can't I see a related field?

    dekade1

      Title

      Why can't I see a related field?

      Post

      I am fully aware that I have made a nuisance if myself on this forum. But, I am stuck and really need some guidance.

       

      PLEASE HELP

       

      Using FM Pro Advanced 9.0

       

      I am viewing one out of many records on Layout 1 in Table A.

      On Layout 1 there are various fields and there is also a portal for Table B.

      Table A is related to Table B via an ID field in each table.

      A portal row for Table B has a 'customer number' field and a 'quantity' field.

      In Table A Layout 1 I enter into FIND mode.

      I go to the 'customer number' field in the Table B portal.

      I enter in a text customer number.

      I press FIND and a found set of all records in Table A with that customer number is now displayed in Table A Layout 2.

      In Table A Layout 2 I have a field that is called "found quantity".

      For the life of me I cannot get each record in Table A to show it's respective Table B "quantity" in the Table A "found quantity" field.

       

      Could someone please help and tell me what I am missing here!?

       

       

        • 1. Re: Why can't I see a related field?
          Jens Teich
             > I am viewing one out of many records on Layout 1 in Table A.

          Very clear!

          > On Layout 1 there are various fields and there is also a portal for Table B

          OK.

          > Table A is related to Table B via an ID field in each table.

          Not completely clear because you don't say how the relationship is defined. Which are the key fields?

          > A portal row for Table B has a 'customer number' field and a 'quantity' field.

          Ok, but now it becomes strange.

          > In Table A Layout 1 I enter into FIND mode.
          > I go to the 'customer number' field in the Table B portal.
          > I enter in a text customer number.
          > I press FIND and a found set of all records in Table A with that
          > customer number is now displayed in Table A Layout 2

          Questions over questions.

          Why search procedure in layout based on table A and not B?
          Does table A also include a field ID_Customer?
          Does any of the portals show related records of different customers?

          Jens













          • 2. Re: Why can't I see a related field?
            dekade1
              

            > I am viewing one out of many records on Layout 1 in Table A.

            Very clear! Reply - OKAY

            > On Layout 1 there are various fields and there is also a portal for Table B

            OK. Reply - OKAY

            > Table A is related to Table B via an ID field in each table.

            Not completely clear because you don't say how the relationship is defined. Which are the key fields?

             

            Reply - Table A has Part ID and Table B has Part ID

             

            > A portal row for Table B has a 'customer number' field and a 'quantity' field.

            Ok, but now it becomes strange.

            > In Table A Layout 1 I enter into FIND mode.
            > I go to the 'customer number' field in the Table B portal.
            > I enter in a text customer number.
            > I press FIND and a found set of all records in Table A with that
            > customer number is now displayed in Table A Layout 2

            Questions over questions.

            > Why search procedure in layout based on table A and not B?

             

            Reply - I am searching this way because I want to find all of the Table A records that have the customer number that I typed in during the FIND mode. Then I get a list of Parts (found set) displayed through Tabel A via Layout 2. Each part or each record has the same customer ID. Then I can to go to each record of the found set and do some updating to each record. But some of the info that I want to see in Layout 2 about Table B will not show up.

             

            > Does table A also include a field ID_Customer?

             

            Reply - At this time it does not; except for a field like it that is called 'found customer number'. Kind of redundant I guess

             

            Does any of the portals show related records of different customers?

             

            Reply - I can't figure out how to answer you on this question. There is only one portal and it is onj Layout 1 Table A.

             

            I'm going to try and re-explain what I'm doing here. Hopefully it doesnot get more confusing.

             

            1  I have a table - it is called PARTS.

            2  Parts has Layout 1 and Layout 2.

            3  Then there is a table called INVENTORY with it's Default Layout.

            4  I enter inventory information (in house qty, pulled in bin, quantity allocated to customer, and, the customers ID number) into the INVENTORY table through a portal on Layout 1. A new record is thus created in INVENTORY.

            5  So now I have a PARTS table with a whole bunch of parts (each part is a record); and an INVENTORY table where each record is an accounting of something done to a part that exists as a record in PARTS. In other words I might change the quantity i have in stock for part #300 to a quantity of 1,200. An INVENTORY record is created for that action. Then I might allocate 50 each of part #300 to customer XYZ. Another separate inventory record is created for that action and the amount is placed in the 'allocated quantity' field of INVENTORY and XYZ placed into the Customer ID field. And so on. And so on.

            6  Then I might, in time, go back to PARTS and, via the portal, do a FIND for lets say customer MNO

            7  In Layout 2 of PARTS a found set is created. I now have a found set that might include part #259, part #314, part #300,and part #488; ALL FOR CUSTOMER 'MNO'

            8  In that found set I have a field that is supposed to show me how many of each different part number has been allocated to customer MNO. For one of the found set records this information, for example, is present on a record in INVENTORY for part #488 for customer MNO for an allocated quantity of 35 pieces. I am wanting to see that 35 pieces in a field, in the PARTS found set, and the field is called "quantity allocated". I CANNOT SEE THOSE 35 PIECES THE WAY THEY ARE PRESENT IN THE RELATED INVENTORY RECORD. 35 simply does not show up!

            9  As I am looking at the found set I can look down through them and see record part #488 has 35 pieces allocated to MNO. If I can't see the 35 pieces then I can't make a judgement call as what to do with part 488.

            10 I ultimately want to go into that record in the found set and take  7 of the 35 pieces and place them back into stock. I have no problem with that. I just can't get the 35 to show up as I have layed out above.

             

             










            • 3. Re: Why can't I see a related field?
              Jens Teich
                 Ok let's proceed.

              > In Table A Layout 2 I have a field that is called "found quantity".

              This field is a calc field you are currently working on and its definition is described in the next sentence, correct?

              > For the life of me I cannot get each record in Table A to show it's
              > respective Table B "quantity" in the Table A "found quantity" field.

              What do you mean with respective? Sum of all related records?

              calc field = sum( table_B::quantity ) would be the answer.

              Jens






              • 4. Re: Why can't I see a related field?
                dekade1
                  

                Jens

                 

                I just edited mt last message to you. At the bottom I have tried to explain better. Does it help or have I just created a big rats nest?

                • 5. Re: Why can't I see a related field?
                  Jens Teich
                     We're getting closer :)

                  You are located at table PARTS and you are looking in direction INVENTORY and what you want is the sum of related records for a specific customer, right?

                  If I am guessing correctly the solution would be

                  * creation of a new global field _g_ID_customer in PARTS

                  * a new relationship via PartID AND via PARTS::_g_ID_customer -> INVENTORY::ID_customer

                  * a calc field sum( new_Relationship::quantity )

                  Jens





                  • 6. Re: Why can't I see a related field?
                    dekade1
                      

                    No, I am located at Table PARTS, let's say record ID #P000112 which is Part Name #488. Record #P000112 in Table PARTS needs to reflect that in Table INVENTORY, inventory ID #IN01245 contains a field that shows #P000112 and a ID #IN01245 field that shows that it is also for Customer XYZ and a ID #IN01245 field that shows "how many are allocated" to Customer XYZ.  What I want to see for each record in PARTS is the "how many" of ID #IN01245.

                     

                    Not sums; just individual amounts

                    • 7. Re: Why can't I see a related field?
                      Jens Teich
                         My last suggestion does exactly what you want.

                      You can remove the sum() function but you can also leave it as it is.

                      Jens

                      • 8. Re: Why can't I see a related field?
                        dekade1
                           Okay-I'll take it from here. I think I can do it.