1 Reply Latest reply on Jul 23, 2010 3:00 AM by LaRetta_1

    CALCULATED FIELD doesn't work if related to more of ONE record.

    surfparadise

      Title

      CALCULATED FIELD doesn't work if related to more of ONE record.

      Post

      Hi, I would calculate  in automatic a field... the function is this one:

      **

      If ( customer_table::address_type = "PT" ; customer_table::country_code ; "X" )

      **

      Calculated field name: Country_code in the table ORDERS.

      Basically if in the field address_type is present the value PT.... the calculated field (contry_code) must take the value from customer_table::country_code.

      The problem is that for each order I have 2 record related in customer table; one with the value AT and the other one with PT. 

      The calculated field work well with this function: If ( customer_table::address_type = "AT" ; customer_table::coutry_code ; "X" ) but doesn't work when I search the value PT.... it is stop on the first record 'AT' (I think) and doesn't continue for find the other record (PT) ...and I have all the results with value X.

      I think that there is a way to tell him to search in ALL related record...and not stop to the first that meet....

      Could you help me please?.....

      Thanks in advance!

      Marco

        • 1. Re: CALCULATED FIELD doesn't work if related to more of ONE record.
          LaRetta_1

          There are a few ways to go, Marco, depending up how you need the information and what version of FileMaker you are using.  These two methods will work no matter the version (7-11):

          1) Go to your graph and sort your Addresses relationship (where it is joined to your customer table) in descending order by the PT/AT field.  Now your PT record will be your first related.  Then, since your perspective from Customers sees only the first record (which is standard FM behavior), it will see PT first.

          2) You could also filter your relationship by adding a calculation (c_string_PT, result of text and unstored) to your Customer table and create another table occurrence of Addresses (maybe called PT Addresses).  Join by:

          Customers::CustomerID = Addresses::CustomerID

          AND

          Customers::c_string_PT = Addresses::pt/atField