Creating a single instance table from an existing table
I have a problem that I have a direction that I think I need to go but would like to know if there was another option:
My database has a list of employees that is created upon startup. The list of employees is created by viewing an external data source (Filemaker). The external data source I'll call it "Data", the local file, I'll call local. The data file has a list of employees, manager names, email addresses, etc. In addition, it has a boolean field called "include". When my database is opened, it does a search for "include" and adds all employees to the local empoyees table.
A little more detail about my local file... It provides a ranking of employees. The local employees links to multiple metric tables, such as "Sales". The local employees table is linked to these metric tables by employee id. The local employees table then has calculation fields that provide totals. It then grades the employee and puts a score that I rank.
I am now wanting a manager summary of all the employees underneath him/her. Previously, I used summary fields to gather this information but this limits the way I can sort that report. I would like to be able to sort by Sales, TimeOnCalls, Score, etc.
My thought is that I need a manager table that is cleared and then created upon launch. Something similar to http://help.filemaker.com/app/answers/detail/a_id/3441/~/finding-and-deleting-duplicate-records-in-filemaker-pro
Am I going in the wrong direction? Is there an easier way to this? My basic goal is to have a manager summary that I can sort by their various metrics.