10 Replies Latest reply on Aug 6, 2009 4:45 PM by keycoachjohn

    Portals from several tables away.

    keycoachjohn

      Title

      Portals from several tables away.

      Post

      This board has been extremely helpful keeping me on track with my project. I’m down to my final report/layout and hit my last wall.  Considering three joined tables, “A”,”B”, “C”.  each with a key field.  Several records in “B” relate to “A”, Many records in C relate to individual records in “B”. 

       

      In layout mode (form view) for table “A” a collection of fields from throughout whole database are aligned and functional; including tables D through Z...quite nice, in fact.  Everything works as per plan except the final two portals that grab records from C. 

       

      In this case, for a found set in “A”, there are two related records in “B”.  When the portal in "A" referencing “C” displays, I want one portal for related record 1 of “B”, and second portal for related record 2 of “B”.  How to specify this in each portal in "A"?  Have tried nesting a portal in a portal, that semi-works but continues to display “C” records for the 2’nd related record of “B”.  Have tried portal sorting based upon B_ID…neither limits the portal lines to end until all records related to “A” have been displayed.  One final idea was suggested to create the layout from “B”, but that affects all the other table links.  Do I need a new “C” table occurrence joined directly to “A”?  Suggestions?  Thanks in advance.

        • 1. Re: Portals from several tables away.
          etripoli
            

          If you want one portal to display records from C that are related to the first related record in B, and the other to display the related records via the second related record in B, then I think the easiest way is:

           

          Create two calculation fields in table A

          First one = GetValue ( List ( Table B::key ), 1 )

          Secodn one =  GetValue ( List ( Table B::key ), 2 )

          Make two new table occurences of Table C in the Relationship graph

          Relate one to the first calculation field, the other to the second calculation field

          Use these two table occurences as the basis for the two portals

          • 2. Re: Portals from several tables away.
            mrvodka
               Could you explain why you would like to split up the C records into two portals as such? This seems very limiting and what happens if there are 3 records in B?
            • 3. Re: Portals from several tables away.
              keycoachjohn
                

              Thanks etripoli,

              I followed your ideas and had great success with the first portal, related to record 1 of "B".  On record 2 of "B" the portal only displays the first related record from "C". 

               

              The new table occurrance "c_A1" is mapped to the getvalue1 calc field.  Primary key (c_ID) in c_A1 did not work, portal only showed record one too, so I linked the foreign key (b_ID) in c_A1 to "A::getvalue1" and great success. Not sure why, that seems unintuitive to me.

               

              The other TO "c_A2 is mapped to getvalue 2 calc field in an identical fashion as TO c_A1, but only one record shows.

               

               

              In an answer to MrVodka's valid point which followed your response- the case of three or even four "B" related records can exist and actually will.  I figured I could extrapolate the lessons here and deal with that situation after I had a handle on this.  Happy to take any suggestions in that respect.

              • 4. Re: Portals from several tables away.
                comment_1
                  

                keycoachjohn wrote:

                I figured I could extrapolate the lessons here and deal with that situation after I had a handle on this.


                Not really. A parent record can have ANY number of child records - but in order to see a specific child's grandchildren in a portal, you need a dedicated field to hold the child's ID.

                 

                Perhaps you should present the real problem you are trying to solve, instead of an abstract excercise.


                • 5. Re: Portals from several tables away.
                  mrvodka
                     Ditto what comment said...
                  • 6. Re: Portals from several tables away.
                    etripoli
                      

                    @comment & mr_vodka:  In a perfect world, we wouldn't need to do such workarounds, but the need exists, and the solutions are limited. 

                     

                    @OP: It would seem that in the calculation (getvalue) fields, you need to grab the Key field from Table B that you are using to relate it to Table C.

                    • 7. Re: Portals from several tables away.
                      keycoachjohn
                        

                      Thanks for the two comments; I acknowledge the voice of experience here. Conversely, this was not/is not an abstract or futile exercise (for me); rather trying to keep focus on the immediate target, getting the db up and running to cover my here/now conditions.  The solution for two children to a parent covers 95% of my immediate needs which is why the question was asked in that respect.  Still learning the FM program, so between the manuals and guidance in the forum I have made ton of progress.  Thx and best regards. 

                      • 8. Re: Portals from several tables away.
                        comment_1
                          

                        keycoachjohn wrote:

                        this was not/is not an abstract or futile exercise (for me)


                        It is abstract to me, because I don't know what you're trying to accomplish by this. You mentioned a report: I have a vague feeling that what you REALLY need is to produce the report from the grandchild table, with sub-summaries by parent and child. 


                        • 9. Re: Portals from several tables away.
                          keycoachjohn
                            

                          Just did a recheck on the getvalue calc fields.  They both use primary key fields from "B".  The calculation fields appear correct when displayed on the layout too.  Both fields reflect the same equation, only difference is the end record that is specified, (1 or 2).  Tried deleting the 2'nd Table Occurrence and re-creating but no luck.  Similarly on the new table occurances a_C1 and a_C2 i'm linking the foreign key (from B) to table A calculation fields.  Thoughts why it works for one and not the other?

                          When excuting a find in "A", all tables one level down show the related records...should that happen two levels down too?


                           

                           

                           

                          • 10. Re: Portals from several tables away.
                            keycoachjohn
                              

                            Ok Comment, appreciate the idea.  I will give it a try...earlier I tried drawing the report/layout from "B"; unfortunately the found set from "A" gathered two records which I couldn't figure how to limit, basically it repeated all the data. 

                             

                            Only reason I chose "A" initially is it's central- like a hub with multiple child/grandchild relationships as spokes...the central point seemed reasonable to start from.