8 Replies Latest reply on Jan 8, 2014 9:08 AM by erolst

    Displaying a Different Record from Related Table

    j.hall

      I have a a set of tables that relate to each other. The Invidivdual Table relates to the Training Tracking Table via the individual ID. The Training Tracking table is setup to track the cumulative, remaining, rolloever from previous years and prorated (if necessary) hours an individual is required to have for the year. However, what I need to be able to do is keep the historial from the previous years. The problem I'm running in to is that the best way to to do this is to have the relationship with the training tracking table sort by Tracking ID in decending order. This will keep the newest records at the top and so Filemaker will find them first.

       

      But I need to be able to select a drop down and look at previous years. But I can't seem to make that work. I can give screen shots if it will help anyone udnerstand, but I've been struggling with this for a few days and thought I would ask before I cause anymore dents in my forehead.

       

      Thanks,

       

      J

        • 1. Re: Displaying a Different Record from Related Table
          siplus

          Investigate portal filtering.

           

          Define a global, gPortalFilterYear. Define a calc field in the individual table, TrainingYear = Year(TrainingDate).

           

          Every portal line will show when IsEmpty(gPortalFilterYear) OR IndividualTable::TrainingYear = gPortalFilterYear.

          • 2. Re: Displaying a Different Record from Related Table
            erolst

            Have a look at this quick'n'dirty example that showcases several possibilities on how to filter related records. If you don't have a dashboard-type system, but rather want to use this on each ”Individuals” record, make gYear a standard field (which then brings problems within a multi-user environment, but that's another story …)

             

            Also, make sure you understand the pros and cons of relationship-level filtering vs portal filters.

            • 3. Re: Displaying a Different Record from Related Table
              j.hall

              I have the relationship and portals worked out, the problem is that the infomration is only needed for about 3 or 4 week at the begining of each year for reference purposes. The red box is what needs to change based on the slection at the top. If this helps explain it more visually.

               

              Screen Shot 2014-01-08 at 10.20.09 AM.png

              • 4. Re: Displaying a Different Record from Related Table
                erolst

                siplus wrote:

                Define a calc field in the individual table, TrainingYear = Year(TrainingDate).

                 

                If you have discrete dates instead of years, then instead of creating said field, you could change the calculation to

                 

                Let ( [

                fy = Individuals::gPortalFilterYear ;

                td = Tracking::TrainingDate

                ] ;

                IsEmpty ( fy )

                or

                ( Date ( 1 ; 1 ; y ) <= td and Date ( 12 ; 31 ; y ) >= td )

                )

                • 5. Re: Displaying a Different Record from Related Table
                  erolst

                  j.hall wrote:

                   

                  I have the relationship and portals worked out, the problem is that the infomration is only needed for about 3 or 4 week at the begining of each year for reference purposes. The red box is what needs to change based on the slection at the top. If this helps explain it more visually.

                   

                  I think the sample file shows you how to do that. Also note that a portal doesn't have to look like a list (e.g. a square one-row portal …), and that you don't need a portal at all if you only have (presently) one related record, or only want to see the first record as per the sort order.

                   

                  In short: within the bordered area, simply show the fields from the first/only related record via the filtered relationship that you control with the popup. If you don't want to see anything there, just reset (i.e. empty) the filter; if you use a filtered portal, you need to change the calculation result to be False instead of True if the filter field is empty.

                  • 6. Re: Displaying a Different Record from Related Table
                    j.hall

                    Ok, if I turn the items in the red box in to a single portal record, how can I get the portal record to display different infomation based on the selection? Simply match the dates?

                    • 7. Re: Displaying a Different Record from Related Table
                      beverly

                      J, you've gotten your answer. You create a relationship and you can change the records shown by applying a filter. This can be triggered by setting any field(s) used to calculate the filter. Filters are set inside the portal dialog. Check the box and define the calculation that will result in narrowing down the related records displayed.

                       

                      Research portals in your FMP help documents. Perhaps that will give you a better understanding of all that they can do!

                       

                      -- sent from my iPhone4 --

                      Beverly Voth

                      --

                      • 8. Re: Displaying a Different Record from Related Table
                        erolst

                        j.hall wrote:

                        Simply match the dates?

                         

                        Probably, but then I don't know your fields. If the related records you want to filter have a date field instead of a year field, then you can use the workaround illustrated above for a portal filter, or (it's easier) create a calculated year field and use that to match. 

                         

                        Why don't you experiment a bit with the sample file?