1 Reply Latest reply on Sep 8, 2009 10:54 AM by philmodjunk

    Combining selected data from multiple tables into another table in FM 10



      Combining selected data from multiple tables into another table in FM 10


      I'm working on case management database.  Each case consists of series of events, but each event has significantly different types of data.  The differences between events make putting all the events into a single table too unwieldy.  I've also developed a "case overview" layout that would really benefit from a portal that would contain an overview of all the events that have occurred in the case.


      In order to get this done, I've created three fields common to all the events tables: Type (simply a text field that defines the what kind of event it is), EventDate (the date entered for the event), and Description (a short text statement entered by the user to provide a little context for understanding the event).  I'd like each portal row to simply carry these three common fields along with a button to jump the user to the detailed record in the layout I've constructed for each event table.


      I've tried several experimental things to try and come up with a way to create the overview portal but they've been pretty bad failures.  Is there a way to funnel all of the individual event tables through another table to filter out the common Type, EventDate and Description fields so I wind up with a list/table of the events that I can turn into a portal on the case overview layout?


      Any help would be greatly appreciated.



        • 1. Re: Combining selected data from multiple tables into another table in FM 10

          Option 1:


          CaseTable :: CaseID = EventsTable :: CaseID


          With additional tables and relations for each type of event:


          EventsTable :: EventID = Eventtype1 :: EventID

          EventsTable :: EventID = Eventtype2 :: EventID



          Option 2: (Better I think for most cases)


          CaseTable :: CaseID = EventsTable :: CaseID


          In events table, add all fields need to document all events. Fields not used by a given type of event will be left empty. As needed, create detail layouts for each type of event that display only the fields needed for that event type. This may be a better approach as you can create a report that lists all the details for a given case in a single list type report.


          In either approach, you can place a button in the portal row and script it to pull up the desired record on a detail layout.