8 Replies Latest reply on Dec 10, 2009 4:07 PM by comment_1

    Calculation fields and Relationships

    KenS

      Title

      Calculation fields and Relationships

      Post

      I want to be able to use a calculation field in a relationship. In this case the calculation field utilizes a date from another related table. It doesn't seem to work. Calculation fields can be used in relationships. Are they limited to calculations based on the table the field is in?

       

      My situation:

       

      Session Table

      date_calc = if ( cond ; Detail Table:: date ; other date )

       

      Detail Table

      date 

       

      where  Session Table is related to Detail Table by a key field.

       

      I want to set up a relationship between a filter table and the Session table so that I can show all sessions on a particular date in a portal. When I try it I get no records in the portal. The date_calc field works in displays otherwise. Is this a limit of how calculations and relationships? Should I use a lookup instead?

       

      Thanks 

        • 1. Re: Calculation fields and Relationships
          philmodjunk
             Calculation fields on the child (many) side of a relationship must be stored/indexed fields. By definition, calculations that reference data in other tables cannot be stored. Therefore your calculation as defined cannot be used as a match field on the child side of a relationship.
          • 2. Re: Calculation fields and Relationships
            KenS
               Thanks for the confirmation. I'l stop trying to make it work that way. How can it be defined so that it will work?
            • 3. Re: Calculation fields and Relationships
              philmodjunk
                

              Not sure from your description. Can't tell exactly how you want your "filter" table to function here.

               

              Just to clarify my first post:

               

              MainTable::date_calc = PortalTable:: Date

               

              will work--where your layout is based on MainTable and your portal is based on PortalTable--but the reverse will not.

              • 4. Re: Calculation fields and Relationships
                KenS
                  

                The filter table holds fields for

                 

                filter table 

                SessionID_fk 

                WorkDate

                 

                I want to have the relationship

                 

                filter::SessiontID   = SessionTable::SessionID_kprime

                filter::WorkDate    = SessionTable::SessionDate_calc

                 

                Where

                 

                SessionTable

                SessionDate_calc is define as a calculation field =  DetailTable::Date

                 

                The layout is based on the filter table with a portal into SessionTable based on the SessionDate, but the SessionTable::SessionDate gets the actual date from a related table. The SessionTable is actually a "join" table connecting various details of the session, participants, billing, notes, etc. So from the filter I want to be able to get a list of all session records for a particular TreatmentID and date to show in the portal. I am certain you are correct in your original response because I get an almost identical error message from FMP when I tried to make it stored. 

                 

                Can you suggest an alternative way to set up the field definition so that I can use it in a relationship like this?

                 

                Thanks 

                • 5. Re: Calculation fields and Relationships
                  philmodjunk
                    

                  I think you'll need to physically copy a date into SessionTable::SessionDate (no "calc").

                   

                  You can define an auto-enter option such as Looked Up value to copy the data. The challenge here is whether the date in DetailTable:: Date is subject to change. If it doesn't/rarely changes, the looked up value option will probably work for you. The trick is that you have to script an update of the value in SessionDate any time the matching DetailTable:: Date field's value changes.

                  • 6. Re: Calculation fields and Relationships
                    KenS
                      

                    Phil,

                     

                      Thanks for your follow through on my question. The date will probably be changed at times and a single date is reference from many of the session records. Unfortunately, I dont see any option other than what you described, to use one version or another of something that needs to be scripted, regretably so.

                     

                    Thanks again.

                     

                    Ken 

                    • 7. Re: Calculation fields and Relationships
                      philmodjunk
                         If you are using FMP 10, this is a good place for a strategically placed script trigger that will perform an update script every time the date field is updated.
                      • 8. Re: Calculation fields and Relationships
                        comment_1
                          

                        Ken S wrote:

                        Unfortunately, I dont see any option other than what you described, to use one version or another of something that needs to be scripted, regretably so.


                        See if this helps:

                        http://fmforums.com/forum/showtopic.php?tid/202377/post/321053/#321053