6 Replies Latest reply on Apr 13, 2011 2:10 PM by johnhorner

    Calculation Context Behaving Mysteriously

    johnhorner

      Title

      Calculation Context Behaving Mysteriously

      Post

      I have a table of CONTACTS, and a table of CONTACT RELATIONSHIPS which is used to display related contacts in a portal on a CONTACTS layout.  There are multiple relationships set up between CONTACTS and CONTACT RELATIONSHIPS.  For example, one portal shows "employees" of a given contact, while another displays non-employment related contacts (such as a friend or family member).  both of these portals use the same container field as a handle to add drag and drop sorting capability to the portal.  this container field has an auto enter calculation to help determine the appropriate sorting behavior.  I set this up first in the "employee" portal and it worked fine.  i then set it up in the "non-employment related" portal and, to my surprise, it also worked fine even though the calculation was set up to evaluate from the context of the "employee" relationship using the "current" (same) table.  here is the calculation:

           Let ( [
           _trigger = Handle_ContactRelationshipsSort ;
           $$DroppedIntoField = "Handle_ContactRelationshipsSort" ;
           $$DroppedInto_ID = __kp_ContactRelationship_ID ;
           $$CreationTimestamp = Timestamp_Creation ;
           $$DroppedIntoEmptyRow = If ( ( Get ( CurrentTimeStamp ) - Timestamp_Creation ) < 1 ; "Yes" ; "No" ) ;
           $$SortOrder_Down = Case (
                IsEmpty (contacts_CONTACT_RELATIONSHIPS_Employees_Sort_Lower::SortOrder_Related ) ; Ceiling ( SortOrder_Related ) -1 ;
                ( SortOrder_Related + contacts_CONTACT_RELATIONSHIPS_Employees_Sort_Lower::SortOrder_Related ) / 2
                ) ;
           $$SortOrder_Up = Case (
                IsEmpty ( contacts_CONTACT_RELATIONSHIPS_Employees_Sort_Higher::SortOrder_Related ) ; Int ( SortOrder_Related ) + 1 ;
                ( SortOrder_Related + contacts_CONTACT_RELATIONSHIPS_Employees_Sort_Higher::SortOrder_Related) / 2
                )
           ] ;
           GRAPHIC_ELEMENTS::g_DragAndDrop [1]
           )

      My question is: How can this calculation possibly work correctly in the "non-employment related" portal when it is evaluating the sort field based on a different relationship and a different T.O.?  it works even if there are no empoyment related records so anything seeking to evaluate the field "contacts_CONTACT_RELATIONSHIPS_Employees_Sort_Higher::SortOrder_Related" should return empty... right?  How does the "Evaluate this calculation from the context of:" actually work?

        • 1. Re: Calculation Context Behaving Mysteriously
          philmodjunk

          Both portals refer to the same data-source table and it is at the data level where this calculation evaluates.

          What name do you see in the "context" drop down at the top of the specify calculation dialog? This will be the same table occurrence regardless of the portal being used to display it.

          • 2. Re: Calculation Context Behaving Mysteriously
            johnhorner

            well, that is exactly what is so strange to me.  at the very top where it says, "Evaluate this calculation from the context of:" i have selected, "contact_CONTACT_RELATIONSHIPS_Employees".  this relationship is defined as follows:

            __kp_Contact_ID = _kf_ParentContact_ID

            AND g_True_B = Employment_Related_B

            AND g_False_B = Project_Related_B

            Whereas the relationship for "non-employment related" records is defined as the opposite with regard to "Employment_Related_B" which is a number field that contains a "1" if the relationship is employment related and a "0" if it is not.  this relationship, "contacts_CONTACT_RELATIONSHIPS_Related" is defined as follows:

            __kp_Contact_ID = _kf_ParentContact_ID

            AND g_False_B = Employment_Related_B

            in the "Specify Calculation" window, there is a second drop down to select the relationship (T.O.) to be used.  Here also, i have selected "Current Table" ("contact_CONTACT_RELATIONSHIPS_Employees").

            So if there are no related "employee" records (the employee portal is empty), how is it finding a value, much less coming up with a correct calculation when this same field (but from a different T.O.) is placed in the "non-employee related" portal where it works as expected to produce the correct sort values. for example, a possible result of the calcualtion is:

            SortOrder_Related + contacts_CONTACT_RELATIONSHIPS_Employees_Sort_Higher::SortOrder_Related) / 2

            this produces the correct value for "SortOrder_Related" even though the relationship refers to the "employees" T.O. which is empty?  Somehow, it is getting the correct value from a similar relationship, "contacts_CONTACT_RELATIONSHIPS_Related_Sort_Higher::SortOrder_Related.


            I hope this is somewhat clear?  Please let me know if more information is needed. Thanks!

            • 3. Re: Calculation Context Behaving Mysteriously
              philmodjunk

              Keep in mind that the TO's here all refer to the same data source table. Thus, it's not the TO that's empty, it's the portal that's empty. Your calculation will still evaluate from the context of contact_CONTACT_RELATIONSHIPS_Employees even though it is not an employee record.

              A screen shot of these relationships from Manage | database | Relationships might make it easier to explain the context issues involved here.

              • 4. Re: Calculation Context Behaving Mysteriously
                johnhorner

                ok... i have posted a screen shot of the relationship graph and a screen shot of the relevant portals to the following link:

                http://johnhornerphotography.com/Projects/Filemaker/

                however, in continuing to look at this and think about it with the help of your responses, i think perhaps i understand now.  please let me know if this is an accurate assessment:  although the non-employee related records do not exist naturally as part of the "employee" T.O. (or is it more proper to say that it does not meet the criteria for the relationship which defines the T.O., or are not "included" or "found" records?), by specifying the context, it treats that record temporarily for the purposes of the calculation as if it did exist as part of that T.O. (or meet the criteria of the "employee" relationship even though it doesn't) and returns what you would expect had it been an "employee" record?  i suppose it seems sort of obvious now that "context" is simply imposing a "context" on what might otherwise be not an unrelated record, but an un-included record, whereas i had always conceived of the context as actually looking for the records that were defined by that relationship specified by the context...

                • 5. Re: Calculation Context Behaving Mysteriously
                  philmodjunk

                  "although the non-employee related records do not exist naturally as part of the "employee" T.O. (or is it more proper to say that it does not meet the criteria for the relationship which defines the T.O., or are not "included" or "found" records?),"

                  It's a mistake, IMO, to think of records as being "part of a TO". What records you see in a portal are based on the relationship between two TO's. The current record in the parent TO controls what if any records are visible in the portal's TO. Look at the records in  a portal to this TO from a different record or from a layout to a different TO and you may see a completely different list of records.

                  What's happening here, is that you are working from a context that starts from the parent record looking at the related records and saying "there's no records there!", but the calculation evaluates from a context that starts from a record in the portal's data source table, to the portal's TO and from there on out to any other TO's referenced in the calculation. The relationship to the Parent TO isn't part of this "path" of relationships and thus does not affect how it evaluates.

                  If you defined the calculation to evaluate from the context of the parent TO, then it would affect your results.

                  • 6. Re: Calculation Context Behaving Mysteriously
                    johnhorner

                    thank you philmodjunk!  i have been toying around with filemaker for more than 5 years and (to my embarrassment) only now do i really have any understanding about how the calculation "context" works.  for the most part i have just ignored it and things generally seemed to work ok despite my ignorance.  it has only begun to trouble me more recently that the database (the relationships, and the calculations) have become more complicated.  this new understanding of the "context" will no doubt be very helpful going forward!  thanks again...