2 Replies Latest reply on Feb 25, 2010 4:44 PM by Meulendijk

    How do you write a calculation using specific records from a Related Table?

    tfinnegan@ccmg.com

      Title

      How do you write a calculation using specific records from a Related Table?

      Post

      I am trying to write a calculation using specific records from a Related Table.

       

      Example: 

       

      Table One (where I am writing the calc)

      Name

       

      Table Two (related Table)

      Addresses

       

      In the calculation, I want to be able to pull your 2nd and 4th addresses and add them to a text field.

      Is there a Get function that could do this?

       

      I'm using Filemake Pro Advanced 9.0 on Windows XP.

        • 1. Re: How do you write a calculation using specific records from a Related Table?
          mrvodka
            

          GetNthRecord ( Addresses; 2 )  & ¶ & GetNthRecord ( Addresses; 4 )

           

           

          You can get rid of the ¶ character and use a comma "," if you want it on one line.

           

           

          If you have multiple fields in Addresses such as address, city, state, etc. Then create a calculation field that concatenates those fields and use that in the calc where it refers to 'Addresses'.

           

           

          P.S. You could also use List ()

          Let ( x = List ( Addresses ); GetValue ( x; 2 ) & ¶ & GetValue ( x; 4 ) )

           

           

          * Edit - reworded

          • 2. Re: How do you write a calculation using specific records from a Related Table?
            Meulendijk
              

            I am (have been for quite a while, unfortunately) trying to do something broadly similar to the topic raised here, and have considered using GetNthRecord, but couldn't really find out whether it would allow me to do what I want to do.

             

            When we take a booking for a cottage in our "Bookings" table, a Booking_ID is auto-generated. This is being used as the testfield in a "not IsEmpty" calculation for the "CottageWeek_Booked" field in the relevant "Cottage" table record, so that that shows "Booked" for the week in question.

             

            So far, so good.

             

            Now, we also take bookings for two or three weeks. The "Bookings" record generated for a booking has a Week_Start_Date field, which is used in the relationship to create the "CottageWeek_Booked" entry in the relevant "Cottage" table record. A booking for 2 weeks gets assigned "2 weeks" in its "Period" field. At that point the "CottageWeek_Booked" field in the relevant "Cottage" table record for one week later should also read "Booked". Likewise for 3-week-bookings, again one week further in the calendar.

             

            I have been playing around forever, creating fields and auto (calculate) filling them for week records, such as "Booked_Previous_Week" and "Period_BookedFor_Previous_Week", to then allow lookups or calculations within one record, to autofill the "CottageWeek_Booked" field if needed. Whenever I set up a calculation that would do such a thing,  the fields start returning "?". I'm guessing that is because in essence there is then a "circular" calculation being created.

             

            I have also been thinking of using something like "GetNextRecord", or "GetCurrent+1stRecord" and "GetCurrent+2ndRecord" in the Cottage table, but cannot find anything about this anywhere. Is something like this possible, and if so, how?