3 Replies Latest reply on Aug 3, 2011 7:09 AM by disabled_menno

    Conditional Relationship possible ?



      Conditional Relationship possible ?


      Hi again,

      I have a CUSTOMERS Table relationship that matches via an achorbouy in our main file 'staff user record'

      from a global pop up

      (fields = Territory, inside rep, customer)

      The relationship is essentially
      Territory = Territory

      This shows our inside reps all customers in a selected territory

      What I'd like to do is change this relation to match

       inside rep = inside rep (so our guys can view a list of all of their customers in all territories

      I've noticed that relationship elements are AND and there doesn't seem an option for OR...

      Sure I could simply create another relationship and all new sub relationships - but a conditional relationship would be way less messy.

      I suppose I could replace the data in the relationships... via a script - but that might be messy for all the other calls to these tables.  

      any thoughts ?

        • 1. Re: Conditional Relationship possible ?

          You could use portal-filtering, but you need to keep in mind that that's only for displaying in a portal and will not influence the outcome of a sum() over a relationship.

          If you need filtered results in calculations in scripts, you may want to look at one of the SQL-plugins. With these you can query FM and there you would have conditional relationships at your disposal, you can even query even totally disregarding the ERD you've defined with your relationships in FM. Useable SQL-plugin are available from several plugin-developers: myFmButler, DracoVentions, 360Works to mention a few. I use this quite often (but only in scripts, not in calculated fields)

          regards, Menno

          • 2. Re: Conditional Relationship possible ?

            Yes... portal filtering is a good tip - hummmm.....

            UPDATE I figured out I can do OR relationships based on making one of the match field include a carrige return between two values.



            • 3. Re: Conditional Relationship possible ?

              Yep that's a multikey relationship, however that is not really an OR relationship:

              In the RSG you can define relationship using more than one field, but then the condition on all the field must be met. A true OR relationship would more behave like a join with the same tables based on different fields.

              With the multikey you have created a join based on the same fields.

              But if this works for you it is okay, isn't it? :-)

              have fun ;-) regards, Menno