7 Replies Latest reply on Mar 9, 2014 5:41 PM by erolst

    OR match for filtering portal records

    slayden@msn.com

      I'm trying to have all portal records show records in a CRM JOIN table tracking referrals called JOIN_Referrals

       

      PrimaryContactTable

      PersonID

      PersonName

       

      Join Table

      Referred_BY_ThisPersonID (=PrimaryContact:PersonID)

      Referred_THIS_PersonID (=PeopleReferredByContact:PersonID)

       

      PeopleReferredByContact (instance of primaryContactTable on graph)

      PersonID

      PersonName

       

       

      How would I filter ONE portal to show if the contact's ID shows up in either field above?

        • 1. Re: OR match for filtering portal records
          erolst

          slayden@msn.com wrote:

          How would I filter ONE portal to show if the contact's ID shows up in either field above?

           

          You need a new, Cartesian relationship to the join table and filter a portal into that that by

           

          Let (

          theID = PrimaryContact:PersonID ;

          the ID = Referrals_cartesian::Referred_BY_ThisPersonID OR theID = Referred_THIS_PersonID

          )

           

          which, depending on the total number of join records, may work rather slow, since you need to filter all records.

           

          Two other approaches:

           

          1. create a calc field = List ( Referrals_cartesian::Referred_BY_ThisPersonID ; Referred_THIS_PersonID ) in the join table and use that as a match field against PersonID from the Contact table for a new relationship. This has the advantage that it works on the relationship level and thus is more performat than a filter, but has the caveat that you wouldn't know what role each match actually constitutes, just that it is a match …

           

          … on second thought, once you do have a match, you could use Conditional Formatting to take care of that by examining the two original fields.

           

          2. create an ReferralEvents table (the join table you have now) plus a ReferralRole table (where each two records belong to one event; the fields you now have in the join table), and look from Contacts into ReferralRole to a) find all ReferralRoles a person has, and b) see/display the respective other person of that event (by self-joining ReferralRole on eventID = eventID and role ≠ role).

           

          A bit more work to set up, but ultimately more flexible (and normalized …).

          • 2. Re: OR match for filtering portal records
            slayden@msn.com

            With the ReferralRole table, should it simply be a list table (roleID, roleNameTxt) for a "referralRole" field in the ReferralEvents table, or should there be a joinTable in between the ReferralEvents and ReferralRoles tables? I'm thinking a join table isn't needed there, but am I missing something?

            • 3. Re: OR match for filtering portal records
              erolst

              You could just use one table and make sure they have a referralEventID in common, but if you need/want to store more data concerning the referral event per se (like date, notes etc.), then create a dedicated referralEvent table.

               

              That table also provides you with a list of those events, without having to cut the referralRoles table in half …

               

              See here (plus an implementation of the low-overhead, but ultimately less flexible 1Table/2Fields approach).

              • 4. Re: OR match for filtering portal records
                slayden@msn.com

                I kind of follow your structure  Will study some more, thanks!  Is there anything wrong with the structure below?

                My current plan is to create ONE event record, but, a join record for each person involved in a referral (typically two people) with the join records listing the role the person played (referrer/referee). 

                The other two tables are merely list tables so that I can control data entry.
                Portals and Layouts would be base on the ReferralEvents table, and the other data would be pulled from this via the relationships below,

                Thoughts?  Thanks!

                Filemaker_Referral_Event_Structure_Screen_shot.png

                • 5. Re: OR match for filtering portal records
                  erolst

                  slayden@msn.com wrote:

                   

                  I kind of follow your structure  Will study some more, thanks!  Is there anything wrong with the structure below?

                  My current plan is to create ONE event record, but, a join record for each person involved in a referral (typically two people) with the join records listing the role the person played (referrer/referee)

                   

                  Ehhm … isn't that what my sample does?

                   

                  You have a Contacts table, an Events table (note that this could be any type of event), and one (join) table where each record is one Contact in an Event in a specific Role, and who finds the other contact(s) of that event via their common Events_fk. (I think that one Event could have one refferrer and several referrees, probably not vice versa; but maybe better constrain it to one odd couple …)

                   

                  You don't need other tables; those two (or three, if you count the RoleTypes table) additional ones give you all you need to extract lists & reports, create portals and control data entry (depending on your UI design), like “this combination of Referrer/Referree already exists” etc.

                  • 6. Re: OR match for filtering portal records
                    slayden@msn.com

                    Now that I've reviewed your structure several times it looks like a slightly different but likely better way to do this.  A few questions:

                     

                    1. below should match _pkreferralEventID, correct?
                    2. ErolstMatch.png
                    3. the table instances on the right side fo the cartesian join are to show the OTHER person in the referral pairing who does not have the role of the person on the left side of the cartesian join, correct?

                     

                    Thanks!

                    • 7. Re: OR match for filtering portal records
                      erolst

                      Yes, the predicate in the screenshow is set incorrectly (which didn't register because that relationship isn't actively used in the setup); it should match referralEventID pk on fk.

                       

                      What you mean isn't a Cartesian relationship; it's a relationship that includes a predicate with the "not equal" operator, which in the connection line also shows an "x” (of sorts), but that's where the similarity ends … and yes: same eventID, different roleID will match the other person of that Event.