3 Replies Latest reply on Oct 19, 2015 8:05 AM by pasleeth

    Relationship using OR instead of AND


      I have a student database with and a separate Financial Database. I have a relationship established between the two with lookup fields in the Financial Database. If I add another pair of fields to the relationship, it automatically defaults to AND. My question is can that be changed to OR, in other words, can the lookups be based on whether either of two fields match?

      Thanks in advance!

        • 1. Re: Relationship using OR instead of AND

          You can't; the only way to achieve an OR result across several match fields would be to employ several relationship where each uses a single one of the match pairs, then collect the primary keys from the respective related records and combine them in a multi-key field.


          OTOH, if you describe your setup and your issue in more detail, there could be another solution.

          • 2. Re: Relationship using OR instead of AND

            Thanks for your help. I just wanted to see if I could have lookup fields based on one of two different relationships (student number and personal code). I think I may have found a way around it by just adjusting the relationship when needed.



            • 3. Re: Relationship using OR instead of AND

              Hi, Mark


              I assume you directed this to me because you saw a recent posting and

              correctly deduced from it that I work in an academic setting, and concluded

              we might be utilizing similar data systems and/or record populations.


              Whether or not that's true, yes, there's most definitely a way to define

              relationships in an either/or sense.


              Unfortunately, there are several major deadlines pending on my calendar

              that prevent me from going into details at the moment.  So you may want to

              see if one of the resident FMP experts jumps in soon.  They're usually much

              more adept than I at answering.


              In the meantime, you may want to consider redefining the relationship by

              adding the other key fields as separate, distinct relational criteria.  You

              do that by adding a new instance of the related table to the graph under a

              different name (e.g., FinanceTable2/3/etc.) and then linking it via the

              other field.


              There's probably a more elegant way to structure the data or code the

              relationship, but I'm no computer scientist and so don't know of one.






              P. Andrew Sleeth

              Admissions Specialist

              Computer Science Graduate Program



              NC State University

              Campus Box 8206

              890 Oval Drive, 2310 EB2

              Raleigh NC 27695-8206