7 Replies Latest reply on May 12, 2017 8:12 AM by philmodjunk

    Multiple Criteria Question

    Dobieg5750

      Hey all - just running into an issue and figured I'd poll the community.

       

      I'm trying to nail down a better script option to count (& Calculate a field or two) records that meet two criteria.

       

      First option is to use: ValueCount ( FilterValues (List ( Instance::Field1 ); "Criteria 1" )) BUT I need to use TWO criteria...(i.e. where Field1 = Criteria1 AND Field2 = Criteria2) - I just can't find an option to use these script options (or similar alternative) to get my result.

       

      Second option is to use Portals....the problem is that I need to count how many records show up in Field1 (total records) and then how many show up in Field2 (subset of Field 1) and then calculate the % of Field2.

       

      My real example is I have a table of Vote Records - by different legislators who have multiple votes.

       

      I first want to nail down the records by the Legislator (Field 1), then how many are "Floor Votes" (Field1) and of those Floor Votes, how many are "Good" votes (voting as we want them to)....so I can determine the Good Voting % of 80% if they have 4 Good votes of a total of 5 Floor Votes (4 Good, 1 Bad).

       

      If I could use multiple criteria in the ValueCount/FilterValues/List option above then I could get a count of "Floor" and "Floor/Good" and simply do the math.

       

       

      Third option - I can display the counts using Portals (and filtering them) BUT I can't add two separate Portal Fields (not the fields back in the table....the fields as a result of the filtered portals).

       

      Anyway, sorry for the incoherent query....hopefully I'm just missing a better/more appropriate Script option or way of adding filtered portal fields.

        • 1. Re: Multiple Criteria Question
          Dobieg5750

          Temporary Work-Around:

           

          I assigned the two Portal Fields an Object Name, ran a script that moved to each and grabbed contents (Get) and put them in Variables, then calculated what I needed and BOOM...problem solved.

           

          Still interested in the "right way" so if my question is clear and there is a better way, I'm always all -ears!

           

          SG

          • 2. Re: Multiple Criteria Question
            philmodjunk

            There is no one right way and the "best way" will depend on scale and a clear description of what you need.

             

            "BUT I need to use TWO criteria...(i.e. where Field1 = Criteria1 AND Field2 = Criteria2) - I just can't find an option to use these script options (or similar alternative) to get my result."

             

            is not consitent with:

             

            "I need to count how many records show up in Field1 (total records) and then how many show up in Field2 (subset of Field 1) and then calculate the % of Field2."

            • 3. Re: Multiple Criteria Question
              Dobieg5750

              Agreed...I really only need the 2 criteria in one part - Floor Votes (vs Committee Votes) which is easily done with the single criteria script....and then "Good" Votes that were also "Floor" Votes - in order to score a legislator's voting record and % of Good Votes.

               

              Thanks

              • 4. Re: Multiple Criteria Question
                philmodjunk

                So you need both things? Matching by each criterion indivdiually and then also matching by both?

                 

                That can be done, just need to be clear about what you want. There are actually multiple ways to get those counts.

                 

                You've figured out one method.

                 

                But you can set up multiple occurrences of the same table and then set up different relationships to each occurrence. This can give you a relationship that matches by criterion #1, another that matches by criterion #2 and a third that matches by both.

                 

                You can also use ExecuteSQL to get these counts. This makes your relationship graph simpler, but your calculation expression becomes much more complex.

                 

                And a script could perform finds for these records and put the results of get ( FoundCount ) into variables or fields.

                • 5. Re: Multiple Criteria Question
                  Dobieg5750

                  Thanks for the point in the right direction. I always begin with the relationship graph but always run into trouble because while I can narrow down everything this way when using Portals - when it comes to performing calculations, I run in to trouble.

                   

                  Cool!

                  • 6. Re: Multiple Criteria Question
                    philmodjunk

                    When setting up a calculation that references related data, you have to keep track of several important details:

                     

                    Context.

                    If this is a calculation field, there's a "context" drop down at the top of the specify calculation dialog. Selecting from this list selects a Table occurrence that then determines what relationships control what data is accessed when you include a field from a different table occurrence in your calculation. If this is a script step, conditional format or Hide Object When based calculation, the layout's Table occurrence specifies the context. (for a script, you can get a different context by executing the script with different layouts as the current layout if the layouts are not based on the same table occurrence.)

                     

                    The Relationship.

                    Does the table occurrence specified by your context match to related records in a one to one, many to one or one to many relationship. The first two match to a single record and your calculation can refer to and use that record's fields as though they are part of the same record as the current record in your context table. The third possibility matches to multiple related records. In this case, a calculation that refers to a field from the related table references only the "first" related record. In an unsorted relationship, the first related record is the first record to be created of that set of related records. In a sorted relationship, it's the sort order that determines which record provides a value to your calculation. To access data in a different record, you have to use either a different relationship, a special function such as ExecuteSQL, Last, or GetNthRecord. Aggregate functions such as Sum, count, average, etc. can be used here to get a combined value based on all the related records. You can reference a summary field from the related table to also get an aggregate value based on the entire group of related records.

                     

                    Updates to the related data.

                    If you define the typical calculation field that refers to data in a related table, any changes to data in related records will automatically update the calculated value, but the calculation field is unstored. Sorts and searches on this unstored calculation field can be slow. Thus, we sometimes use a data field with the calculation defined as an auto-enter calculation or that is updated by a script that uses the same calculation to calculated a value that it then sets the data field to that calculation result. But in the latter two methods, you then have to take care in your design of your system to manage the updates that no longer take place automatically.

                    • 7. Re: Multiple Criteria Question
                      philmodjunk

                      You may also find this tutorial on table occurrences helpful:

                       

                      Tutorial: What are Table Occurrences?