8 Replies Latest reply on Dec 23, 2011 7:54 AM by johnkerr48

    Sorted relationship not working


      I have a problem with a sorted relationship that is not working as expected. I've tested it various ways and nothing seems to fix the problem. I can even get evidence that the relationship sort works if I open a new window for the relationship (the records appear in the order expected).


      Here is what I have done thus far:

      1. I have a Date table that is related to all the records from Habits. I can then create a DateHabit record based on a specific Habit.

      2. When a Habit is added to a Date, I use a filter to exclude those that have been added.

      3. I have added a new relationship between AllHabits and the DateHabits (LatestDateHabit4AllHabits) that are related to them. This relationship is sorted in descending DateID order.

      4. I have added a relationship between LatesetDateHabit4AllHabits and Date so that I can display the date. The relationship is based on Serial Numbers. This relationship works so that I can show the date in a ToolTip, but the wrong date appears. The first date in the relationship appears. I want the latest date.


      The relationship between AllHabits (Habit table) and LatestDateHabit4AllHabits (DateHabit) is sorted by descending DateID (the Serial Number in Date). If I open a new layout and show the records, they appear in descending DateID order.


      When I show a ToolTip however, the date that appears is the first one in the relationship. All the ID's are numbers. I've changed the sort in the relationship and tested it with the new layout and it shows the records in the expected order. But the ToolTip doesn't work. The calculation for the tip looks like this:

      AllHabitsFrequency::Description & ¶ & "Most recently occurred on " & PreviousAllHabitsDate::Date


      I would swear that I have gotten this to work in other places in my application, which is what is so curious about this instance. I've been a FM developer since version 3 and am currently using 11 (not Advanced). I've done all the reasonable things I can think of. Is there something obvious I might be missing?

        • 1. Re: Sorted relationship not working

          Hi John,


          Your relationship description isn't quite clear enough but one thing I spot is your tooltip calculation:


          AllHabitsFrequency::Description & ¶ & "Most recently occurred on " & PreviousAllHabitsDate::Date


          You are using table occurrence names (in bold) that you have not described in your post.  It also helps to understand the current perspective (in FileMaker, perspective is pretty-much everything); what is the layout's table occurrence name where the tool tip is being displayed?


          It would help to see a clean view of your graph where these relationships (and their key fields) can be viewed.  Please colorize the referenced table occurrences if your graph is complex.  Or better still, provide an empty copy of your file.  It is not entirely clear how these relationships are set up.  It seems Cartesian Product is used but I cannot differentiate the table occurrence names for the same tables.


          You are correct in that it should work.  I suspect it is a context issue.

          1 of 1 people found this helpful
          • 2. Re: Sorted relationship not working

            (Images referenced in this reply are attached at the end of this post)


            (Image 1 - Selected relationship map)


            DateHabit is relationship between Date and Habit and it has its own ID

            - the keys to DateHabit are DateID and HabitID

            LatestDateHabit4AllHabits is an instance of DateHabit

            - this is the relationship that I want to use to find the most recent Date, so I know how long ago it was

            - the relationship is sorted by DateID in descending order, but the sort doesn't work in the ToolTip

            AllHabits is an instance of Habit

            - the relationship is unsorted. All the Habits are available from Date.

            - when a Habit is assigned to a Date, it can be filtered to exclude it from the portal

            PreviousAllHabitsDate is an instance of Date

            - this is the record I want to look at to tell me the most recent date


            The current result looks like this:


            (Image 2 - ToolTip)


            When I hover over the pink field on the portal, the ToolTip appears but shows the earliest date of all the DateHabits related to the Habit. The sort seems to be working because I get this result when I click on the pink field and go to the related record.


            (Image 3 - List of related records)


            The most recent record is the first record in the selection, but I get the date of the last one. Changing the sort doesn't seem to make a difference in the ToolTip. All it does is make the records in the Test window appear in ascending order.


            Writing this all out made me realize that perhaps the problem was the field I was sorting on - DateID. I tried changing the sort so that it would sort the DateHabit records on their ID, which is also increasing as a serial number, but the results for the ToolTip came out the same.


            Does this provide enough information to answer your reply to my earlier post?


            I just found out my pictures don't work if I just paste them in. I need to use attachments I guess.


            I've added a copy of my database to demonstrate the problem. Instructions for how to get to it are in my last reply.

            1 of 1 people found this helpful
            • 3. Re: Sorted relationship not working

              Thank you for providing the detailed information we requested.  Can you explain the purpose of the Date table and provide me with the important fields it contains?  This adds a layer of unnecessary complexity (from my observation).  Date tables aren't required since dates can be filtered, grouped and accessed directly. 


              However, if you use a calculation and within calculation dialog, change the pop-up (at the top) to 'evaluate from context of' then you might get the results you require.  You may also be hitting the change in perspective (known as the Grandchild issue).  But I could not even attempt to sort it out without understanding the purpose and context of your solution; I'm just too visual - maybe others can sort it out.  Even if we solve the one tooltips issue, it would be better to address the structure...


              It just seems like there are far too many table occurrences being used to provide functionality to two (or even three including Date) tables.  And many of them look like their only purpose is to attempt to address the needless Date table issue.


              I would encourage you to provide your real file (less data).   If you can't, then we will continue to work with what we have.   I started to create a stripped-down file showing the relationships.  It would be good to step back and see where you might be going wrong and correct it before you get much further.

              • 4. Re: Sorted relationship not working

                I have created a copy of my database with minimal data in it to demonstrate the problem. I've changed the startup script so that it goes to the layout that is having the problem, but when you get there, you need to select a different tab. Here is how to see what the problem is doing, in a working copy.


                1. When you open the attached file, it should automatically go to 12/19/11. If not, use the Previous Date/Next Date fields to get to 12/19/11.

                2. Once on 12/19/11, select the Add Habits and Routines tab. If the number below the Check Habits button is not 212, click it and it will set the relevant value.

                3. The three records that appear are the Habits/Routines that have not been assigned. In the test data, this is three routines.

                4. Hover your cursor over the pink FREQ (Frequency) field for the first routine. Notice that the tool tip says "Most recently occurred on 12/14/2011".

                5. Click on the gray text description of the first habit/routine. This will go to the Habit table and show all the DateHabits assigned to the Habit. Notice the most recent value is actually 12/18/11, and that the first is 12/14/11.

                6. Click on the Date field for 12/18/11 and you will go back to that date. Click Next Date to get back to 12/19, then go back to the Add Habits and Routines tab.

                7. Hover over the other two routines to see that they have different dates, and navigate to them to confirm that they have dates later than the one displayed.

                8. Open Manage...Database and go to Relationships. Click the relationship between AllHabits and LatestDateHabit4AllHabits. Click the Specify button for the sort to see that the DateHabit records are supposed to be sorted in descending order, based on their ID.


                This is the crux of the problem. The sort says sort descending, yet the first record available for display on the tool tip is the first record in the relationship, which is supposed to be sorted so that the last record in the relationship would appear first.


                I hope this provides a enough information for other TechNet members to offer help. This solution is something I've been working on for myself for over a year, and the reason for having a Date file is specific to the problem I'm trying to solve. I realize that the earlier reply is correct, so perhaps the name of the table is misleading. I was a database administrator for 10 years and a programmer/analyst for 40 before I retired. I've used Mac database software since Reflex Plus back in 1985, and FileMaker since version 3. I am sure I have other examples in other databases I've developed where the sort works the way I want it to. Since this isn't a portal being sorted, I'm at a loss as to what is going wrong. I'm pretty sure it will be a simple thing somewhere obvious, but I have no idea where to look anymore.


                This application was developed in FM 11 (not advanced) and uses a lot of filters. None of those have any impact on the current problem. In fact, they work so well they made me realize I needed this solution, since I want to be able to tell the last time a routine was assigned. Once I figure this out, I can change the color of the FREQ field so that it is a different color when the routine needs to be added to the daily agenda.


                And that, in the end, is what this whole application is about. It's a daily planning and tracking tool. I hope to have a version of it as a commercial product soon, but the current feature is probably not needed for anyone but me. I just thought I would see if someone else could help me figure this out. I renewed my TechNet a few weeks ago and intend to be more active. This is me putting my toe in the water.


                I've attached the database to my first post above. The attach files button wasn't available until I edited my post.

                • 5. Re: Sorted relationship not working

                  Hi John,


                  I removed this message because it was inaccurate.  In your relationship, sort the PreviousAllHabitsDate descending on date.


                  Message was edited by: LaRetta ...

                  • 6. Re: Sorted relationship not working

                    Hi John,


                    I agree with LaRetta. The relationship you are requesting the date information from is a grandchild. The sorting you have in place on ID is in the parent record and will not affect the tooltip.


                    Sort the PreviousAllHabitsDate relationship in descending order on Date, not ID and the tooltip will display the data you expect.



                    • 7. Re: Sorted relationship not working

                      Thanks to LaRetta and MicheleOlson. Your comments got me to rethink the whole issue and recognize there was something FileMaker wasn't doing the way I thought. I re-engineered the underlying structure and it works now.


                      And thinking further about your comments about my Date table, I realized you were right. In my database, Date is an inappropriate name for the collection of data. It should be called Agenda or Calendar or something like that. The records in the table are a way of collecting things that get done on a "date". I've been calling it that so long I stopped thinking of it that way. Your observations were very helpful, because recognizing the table was named wrong got me to look at the business problem I was trying to solve.


                      Thank you both.

                      • 8. Re: Sorted relationship not working

                        See my reply to MicheleOlson below.


                        And thanks for all the help.