3 Replies Latest reply on Jan 11, 2017 8:05 AM by philmodjunk

    Filtering List based on another field value

    jayemtee91

      I am doing a project for a Science fair that has 3 tables: Judges, Students, Scores.

       

      Judges and students both have their own unique ID, Judge ID and Project ID, respectively. These IDs are have a relationship to the scores table by the same ID's (Judge and Project ID).

       

      Each score has field that says if it is a "finalist score" or not (boolean 1 or 0 value) and I want to use this value to determine what projects the Judge gave a score to based on if they were in the finals or not.

       

      In the judges table, I want to display both of these lists on the layout page for the Judges table. I am having a problem with getting these values from the scores table because I can only get the list of all the projects they scored, but not dependent on the status of the "finalist score" field. The two fields I would like to be populated are Projects Scored are: "Non_Finalist Projects Scored" and "Finalist Projects Scored"

       

      I spent a lot of time googling this and the only thing that I came across was the conditional value list, which seems like what I want to do but I couldn't figure out how to implement it for my use case.

       

      Attached is the relationship graph of the three tables that I am working with.

       

      Any thoughts or suggestions are appreciated. Thank you!

        • 1. Re: Filtering List based on another field value
          philmodjunk

          Seems to me your "list" should be a portal. Either a pair of match fields in your relationship or a portal filter expression in portal set up can limit the records shown in the portal to "finalist" scores.

          • 2. Re: Filtering List based on another field value
            jayemtee91

            Hi philmodjunk. Thank you for your reply. I tried the portal functionality out and it worked the way I expected.

             

            I'm still pretty new to databases and relationships, so can you explain in a bit more detail what you mean by a pair of match fields in my relationship? Would that be Judge ID and then an additional field (not sure which one)it?

            • 3. Re: Filtering List based on another field value
              philmodjunk

              Doesn't sound like you need it here, but if you defined a calculation field in the Judges table, with a number result type and this expression:

               

              1

               

              You can then set up a relationship to a new table occurrence of scores like this:

               

              Judges::Judge ID = Scores|Finalists::Judge ID AND

              Judges::constOne = Scores|Finalists::Finalist

               

              A "table occurrence" BTW, is what we call a "box" on your relationships graph. You can create more than one such occurrence that refers to the same table so if you select Scores and click the duplicate button (two plus signs) you get a new occurrence that refers to the same Scores table so that you can set up a different relationship to it than your original Judges to Scores relationship.

               

              It's important to keep in mind that while you frequently have layouts, tables and table occurrences with exactly the same name, they are not the same thing.