AnsweredAssumed Answered

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

Question asked by timmcmanus on Aug 23, 2013
Latest reply on Aug 23, 2013 by keywords

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.

Outcomes