5 Replies Latest reply on Oct 31, 2011 10:44 AM by philmodjunk

    calculated matching fields help

    AlexXander

      Title

      calculated matching fields help

      Post

      Hi I'm trying to create a calculation using the IF Fuction where one field in the current table equal another field in a differnt table returns the text "Yes" if not "No". However all i get is the return "No" for all of the records even when the two field values match.

      If(D123=Calc 123::D123;"Yes";"No")

        • 1. Re: calculated matching fields help
          philmodjunk

          What is the relationship linking the table where this calculation in defined to the table occurrence named "calc 123"?

          • 2. Re: calculated matching fields help
            AlexXander
             The table "calc 123" is linked to the current table "PL" by a unique "ID" field. What I'm trying to do is that in that the current table "PL" it has all of the values that can be used related to the "ID" field with a total of 15,000 records.  Each record is not unique there are several of the same values.. But in the table "calc 123"  the field "D123" holds the values that have been used with 680 records which are not all unique as well. But what I want is that if the value has ever been used in "calc 123" that it will return a "yes" in the current table calculation and "no" if the values has never been used.
            • 3. Re: calculated matching fields help
              philmodjunk

              Please post the field definition for calc 123::D123

              In a one to many relationship, a calculation defined in the parent table (PL in your case) that refers to the related records in the many table (calc 123) will only reference the value in the referenced field of the first related record. It will not "see" the values in any additional related records.

              So if you have this relationship:

              PL::PL_ID = Calc 123::PL_ID

              And your current record in PL has a value of 1 in PL_ID, and you have 3 records in Calc 123 with values of 1 in Calc 123::PL_ID with these values in Calc 123::D123L:

              34
              45
              32

              (Each number is in a different record in Calc 123). And the value in your current PL record for D123 is 45, your If function will return "No" because the only value referenced will be 34.

              Is this the situation you have here?

              Please confirm and then we can discuss a way to do what you want here.

              • 4. Re: calculated matching fields help
                AlexXander

                Yes it is though I have reconstructed my whole database to be a bit smaller to manage but will like to add this field to it. Is there a way for filemaker to use all the records and not just the first record. or even specify to use only the first 5 records.

                • 5. Re: calculated matching fields help
                  philmodjunk

                  The calculation :  List ( Calc 123::D123L ) will return a list of all the values.

                  Then this calculation will return True If the specified 45 is one of those listed values:

                  Not IsEmpty ( FilterValues ( List ( Calc 123::D123L ) ; 45 ) )

                  You can also define a relationship that matches both by the specified value in D123L and also PL_ID:

                  PL::PL_ID = SpecifiedCalc 123::PL_ID AND
                  PL::SpecifiedValue = SpecifiedCalc 123::D123L

                  I've used a new table occurrence name in this example: SpecifiedCalc 123 as it is very likely that this would be a new relationship you'd want to add without changing the existing relationship between these two tables. SpecifiedCalc 123 would be create by clicking Calc 123 in Manage | Database | Relationships, then clicking the duplicate button (Two green plus signs), then double clicking the new occurrence in order to change it's name to what I have here.