2 Replies Latest reply on Dec 14, 2013 1:45 PM by duncanbaker

    Conditional formatting help

    duncanbaker

      Hi folks

       

      I'm back with another query that I need to fast track through. I've given it a go but not been successful.

       

      In brief, I have people and people are tagged to assignments via a join table. I tag them using a picker that is based on an assignment>people cartesian relationship. In this picker I want to highlight those that have been tagged to the assignment already. This I can do using this in the conditional formatting of the picker field:

       

      FilterValues ( asn.PLP_People~Cartesian::__zkpPeopleID ; List ( asn.JPA_JoinPeopleAssignments::_zkfPeopleID ) )

       

      That's all well and good, but for reasons that we don't need to go into, I only want to highlight those that are in the join table AND those that have a value of "Candidate" in another one of the join table fields. I've tried:

       

      FilterValues ( asn.PLP_People~Cartesian::__zkpPeopleID ; List ( asn.JPA_JoinPeopleAssignments::_zkfPeopleID ) )

      and

      FilterValues ( "Candidate" ; List ( asn.JPA_JoinPeopleAssignments::Type ) )

       

      But I can see this isn't right and of course it's not working... Any ideas? I'll keep trying in the meantime.

       

      Thanks

        • 1. Re: Conditional formatting help
          erolst

          You'd need another relationship from People to your join table where (a global field) Type = "Candidate", and use a list of IDs via that relationship as filter.

           

          If you have 12/13, you could use ExecuteSQL instead, along the lines of

           

          Let ( [

          pid = asn.PLP_People~Cartesian::__zkpPeopleID ;

          ls = ExecuteSQL ( "SELECT \"_zkfPeopleID\" FROM asn.JPA_JoinPeopleAssignments WHERE Type = ?" ; "" ; "" ; "Candidate" )

          ] ;

          FilterValues ( pid ; List ( asn.JPA_JoinPeopleAssignments::_zkfPeopleID ) )

          and

          FilterValues ( pid ; ls )

          )

          • 2. Re: Conditional formatting help
            duncanbaker

            That triggered an idea, so thanks very much for taking the time. I made a new field in assignments (global and auto enter "Candidate") and made another relationship from Assignments to the join table with two predicates, one for the assignment and the other from the global to the Type field. I then based my conditional format on this. The relationship restricts the list to those that are added and the second predicate restricts it to those that are also tagged with Candidate. Seems to work based on quick testing. Would have been banging my head on my desk for a while without your help. Thanks again.