1 Reply Latest reply on May 12, 2014 10:32 AM by erolst

    Filtering Value Lists


      I have hit a bit of a mental block on filtering value lists.


      Below is a copy of the relationship between the two tables in question


      What I am trying to do is produce a filtered by CTR (Contractor) value list for picking contacts to send a message to. When I get the drop down list all that I would like to see are the contacts related directly to that contractor.


      With in the contacts I assigned a self relationship from contractor ID to contractor ID and then set up a global field that has the ID stored in it.



      I think at this point I have allowed myself to go in the wrong direction.


      Any thoughts of suggestions would be greatly appreciated,


      Best regards,


      Jason Farnsworth

      Midland, TX

        • 1. Re: Filtering Value Lists

          Strictly speaking, a self-join is just a relationship between two TOs from the same table; it doesn't specify the type of relationship. Joining on the primary key will only show you the Contractor record you're already on, which has its uses, but not in this case.


          What you can do is create a pick list – e.g. use a portal pointing at a Cartesian (x) self-join – of all Contractors; pick one to put their ID into a global field (say, gSelectedContractorForContacts) and use that to filter into the contact's foreign contractorID. (Or use a static value list of your Contractor IDs/names and use it to format the global field as a popup/dropdown, though that may be of limited usability.)


          So you have


          • a picker relationship (Cartesian) between two Contractors TOs for a selection portal (or a value list), and

          • a filtered relationship between Contractors::gSelectedContractorForContacts = Contacts::contractorID_FK


          Using the portal approach and the right script, you could utilize the global field as a multikey to select multiple Contractor IDs and show all their associated contacts.