1 Reply Latest reply on Aug 23, 2013 8:48 PM by keywords

    Create a found set for a value in "field1" or "field2" and then creating a summary report

    timmcmanus

      I have a database with two fields, Chef and Cook in the same table. Both of these fields are populated by a drop-down value list that is created from another file/field named Employees. The data may look like this:

       

       

      Employees
      Tom
      Bob
      Joe
      Jane
      Sally

       

       

       

      Date ChefCook
      Day1TomBob
      Day2TomJoe
      Day3JaneBob
      Day4JaneTom
      Day5SallyJane
      Day6SallyTom

       

       

      From this data I want to generate a report counting the total times an employee was in either role. It might look like this:

       

       

       

      EmployeeRoleCount
      Tom


      Chef2

      Cook2
      Tom Total
      4
      Jane


      Chef2

      Cook1
      Jane Total
      3
      Sally


      Chef2

      Cook0
      Sally Total
      2

       

      I know I can do a logical "or" find within one field, but I am not sure how to do it with two different fields. I could do it with two separate finds, but each new found set replaces the old, so I don't know how to capture the values. It might not be important, but it won't be a simple Find request. I want the option to list the Date and Role so I can generate a detailed report as well as a count at the bottom in the summary. Something like this:

       

       

      EmployeeDateRole
      Tom


      Day1Chef

      Day2Chef

      Day4Cook

      Day6Cook
      Tom Chef
      2
      Tom Cook
      2
      Tom Total
      4

       

      I need help figuring out how to do the Chef or Cook find to create a found set and then a summary report. The find would be:

       

      Find Tom in Chef or Cook between dates X...Y.

       

      I'm not sure if a summary report would work to break down the counts listed above. Although I only listed Tom in the last example, that report would list every employee who worked in either role, summary count for each role, and a count for all roles.