2 Replies Latest reply on Jan 28, 2012 5:29 AM by tomo17

    If statement using two tables

    tomo17

      Title

      If statement using two tables

      Post

      i have fields from two tables on one layout and i am trying to calculate a text result based on these fields.  when i try to use the "IF" function it seems to only look at one of the fields.  I have tried nesting the conditions, using OR and the case formula.  if i change the order of the "or" statement ie field 1 second and field 2 first it will look at the other records that it previously did not calculate.

      Athlete List::House Colour                  Team Name             cName 

       

      This is the formula i have tried to use:

       

      If(Team Name or Athlete List::House Colour ="Red";"Rice"; If(Team Name or Athlete List::House Colour ="Green";"Pascoe"; If(Team Name or Athlete List::House Colour="Blue"; "McAuley"; If(Team Name or Athlete List::House Colour="Gold";"Kelly"))))

       

      Thanks

        • 1. Re: If statement using two tables
          Sorbsbuster

          You could make it simpler to debug by re-writing it as a Case Statement:

          Case (

          Team Name = "First Team" or Athlete List::House Colour ="Red";"Rice";

          Team Name = "Second Team" or Athlete List::House Colour ="Green";"Pascoe";

          Team Name = "Third Team"  or  Athlete List::House Colour="Blue"; "McAuley";

          Team Name = "Fourth Team" or Athlete List::House Colour="Gold";"Kelly" ;

          "Not specified"

          )

          This will check in sequence down the list (exactly the same as an IF ststement) and will stop as soon as it reaches a matching value.

          Maybe you meant the criteria to be

          Team Name = "Red" or Athlete List::House Colour ="Red"

          • 2. Re: If statement using two tables
            tomo17

            Maybe you meant the criteria to be

            Team Name = "Red" or Athlete List::House Colour ="Red"

            D'oh - of couse i did. cant believe i didnt see this!

             

            Thanks