4 Replies Latest reply on Apr 26, 2013 7:10 AM by philmodjunk

    Display most recent date from sub-set of dated records

    arronman

      Title

      Display most recent date from sub-set of dated records

      Post

           Hello again. I have a table of records we'll call Visits, another table called Suburbs and a third called Shops. Visits, the master list,  contains records holding data of shops, suburbs, date(s) visited, and more. Every visit to every shop has its own record.

           The shops table (& layout) lists every shop with a portal  displaying data of each visit. Works fine.

           The Suburbs Layout displays the list of suburbs containing (or known to have no) shops.

           In the Suburbs layout, I need to display how many shops there are in each suburb, and another field displaying the date of the most recent visit that has been made to any shop in that suburb. Ideally I would have the latest visit date for each of the shops.

           As usual assistance would be greatly appreciated. Thanks.

        • 1. Re: Display most recent date from sub-set of dated records
          DillipKumarMahapatra

               You have to establish one  relationship between "Suburbs" table and "Shops" table like below and named as Suburbs_Shop__RelatedShops

               Suburbs::SuburbID = Shops::SuburbID

               Create a new calculation field in Suburbs table as ShopCount and set calculation as Count (Suburbs_Shop__RelatedShops::ShopID)

               Now to display date of most recent visit to any shop establish one  relationship between "Suburbs" table and "Visits" table like below and named as Suburbs_Visit__RelatedShopsVisit

               Suburbs::SuburbID = Visits::SuburbID and sort the relationship with "dates visited" field with descending order

               Create a new calculation field in Suburbs table as RecentShopVisitDate and set calculation as Count Suburbs_Visit__RelatedShopsVisit::DateVisit. It will show the very first record of the reationship which is a recent one.

          • 2. Re: Display most recent date from sub-set of dated records
            philmodjunk

                 You can also use Max ( Visits::DateField ) to get the most recent date of a visit.

            • 3. Re: Display most recent date from sub-set of dated records
              arronman

                   "You have to establish one  relationship between "Suburbs" table and "Shops" table like below and named as Suburbs_Shop__RelatedShops

                   Suburbs::SuburbID = Shops::SuburbID"

                   Dillip, tried but the count always = 1??? This is what I did:

                   Created a table instance of Suburbs called Suburbs_Shop__RelatedShops.

                   Have the relationship link Shops::SuburbID=SuburbsID.

                   Had also tried Shops::SuburbID = Suburbs_Shop__RelatedShops::SuburbID

                   Always count of 1 even though I have as many as 4 shops in a suburb.

                    

                   PhilModJunk: thanks for:

                   "You can also use Max ( Visits::DateField ) to get the most recent date of a visit."

                    

                   However i don't know how to apply it. Sorry, bit of an amateur trying to be clever!

              • 4. Re: Display most recent date from sub-set of dated records
                philmodjunk

                     I suggest looking up summary fields and aggregate functions in FileMaker help.

                     If you have a relationship:

                     Table1---<Table2

                     Then you can define a calculation in Table1 as Max ( Table2::DateField ) to get the most recent date of the set of records in Table2 that are related to Table1. You can also define a "maximum of" summary field in Table2 to get the same date--just place that summary field from table2 ond table1.

                     And summary fields also max dates for a found set of for sorted groups within the found set in a summary report.