1 Reply Latest reply on Dec 17, 2010 12:49 PM by philmodjunk

    Calculation field display data from related field

    raykennedy

      Title

      Calculation field display data from related field

      Post

      I am attempting to put a calculation in my table that displays data from related table. Here is the setup...

      Table 1 = Transaction

      Table 2 = Customer

      Transaction table has two foreign id fields pulling the customer into Transaction...

      Transaction::_k_seller1_id = Customer::_p_customer_id

      Transaction::_k_seller2_id = Customer::_p_customer_id

      So as you can see Transaction pulls two Customer into one transaction using the ID and this is displayed in the app using a popup menu or portal.

      What I would like to do if possible is to put a calculation in Transaction that would pull each customers first name and last name based off the id fields and concatenate them together in one field for custom display in certain areas of my app. The reason I want it to be a calculation is so if one of the sellers information changes it will not be redundant data that needs to be changes. For example, someone misspells the last name.

      Is this possible? If so, any suggestions on how to proceed?

      Thanks.

        • 1. Re: Calculation field display data from related field
          philmodjunk

          The relationship you posted is not possible in FileMaker.

          This is what you should have:

          Transaction::_k_seller1_id = Customer::_p_customer_id

          Transaction::_k_seller2_id = Customer 2::_p_customer_id

          Where Customer and Customer 2 represent different Table Occurrence "boxes" in your relationship graph.

          You can then define two calculation fields in Transaction that look like this:

          Customer::LastName & ", " & Customer::FirstName

          Customer 2::LastName & ", " & Customer 2::FirstName

          A simpler approach would be to define a single calculation field, cFullName, in the Customer table as:

          LastName & ", " & FirstName

          and then add the fields Customer::cFullName and Customer 2::cFullName to your Transaction layout.

          If Table Occurrence is new term for you, you might want to read this thread:  Tutorial: What are Table Occurrences?