4 Replies Latest reply on Nov 13, 2013 4:48 PM by raphthepenguin

    Help needed with related Table


      Hey there,


      I think this is not too hard but I fried my brain this last few days and do not see things clearly...


      So I have Layout with People as record

      On there is a Tab with a Portal where you can enter a Contact Interaction Summery

      This one is sorted so the newest will always be on top


      Now I am trying to have a Table on another layout that shows me from each contact the last interaction. Later I want that to be so only contacts are shown that havent been contacted for a while... Kind of like an reminder!


      But I am super stuck... I canned the whole thing again and again and now I need help!
      I even started wondering if its even possible to do so.


      Any Ideas how I could fix this?



        • 1. Re: Help needed with related Table

          You might consider doing the sorting in the Manager Database relationship between table occurrences instead of at the portal level.  That way even in a relationship, you can see the most recent comment in a relationship.  Or you could always do an ExecuteSQL and sort by the comment date descending and you don't have to rely on a relationship in the relationship graph. It may be something like this:


          Let ( [


          F1 = PeopleTable::ContactID ;

          F2 = "SELECT Comment FROM CommentTable WHERE ContactID = ? ORDER BY Comment_Date DESC" ;

          F3 = ExecuteSQL ( F2 ; Char ( 9 ) ; ¶ ; F1 ) ;

          F4 = GetValue ( F3 ; 1 )


          ] ;





          • 2. Re: Help needed with related Table

            Thank you! Sorting the relationship in the "Manage Database" did the Job.


            Right now in my Table layout it is only showing the First Portal entry (Most recent). That is how I want it. Is that normal thou? I didn't do anything that this will be the case.

            So just out of interesst, is there a way of showing another portal row instead, or even multiple ones?



            The other solution I sadly did not complete grasp


            Thank you so much!


            • 3. Re: Help needed with related Table

              The ExecuteSQL solution does the same thing, just without having to use the relationship graph. 


              SQL is a wonderful and powerful language, but it does have a steeper learning curve than traditional FM approaches.  FM's solution with the sort in the table occurrence relationship works just fine and is easier to understand.  So go with that and all will be good.  But whenever you're feeling a little dangerous, you might explore the world of SQL. 

              • 4. Re: Help needed with related Table

                Thanks I will go on that Adventure in the future...