If you have a table containing company1 … company22, then you have a data structure problem. You really should have the companies in a separate table with one record per company. Get the basics right and the rest will become far easier.
What you show looks like a Cross Tab report.
Search this forum using that phrase and you can find a numbet of discussions on the subject that describe several different ways to get that type of report up and working.
As Keywords had said, you have to first get the right data model.
Implementation depends on the purpose of that matrix. Will there be interaction? Is it for display/informational purposes only? How is it accessed - via FileMaker Pro, Go, WebDirect, or any of the above?
As previously said, your data structure needs to be laid out correctly first, which you may have already done. I'd envision these tables:
- Employee (join table for Company/Person, storing at least id, id_company, id_person)
- EmployeeRole (join table for Employee/Role, storing at least id, id_employee, id_role)
That will allow for people to be in multiple companies and have multiple roles within each.
To get your matrix, if for info only, you could loop through the records and built HTML in the format you need, then display in a WebViewer.
If it needs editing from this view, then I suggest not using that type of interface and simply editing roles from an employee layout.
Agreed, but I would add one more join table, e.g. Assignments which would store id, id_employee, id_role, id_company, and status
The entire matrix could be generated with that one table. Like you suggest, HTML would make the most sense.
With the Company 1...Company 22 I was just trying to show that the columns would be expanding. My plan was to have a company table.
I was thinking that might have to go the cross tab route, I did a search last night, but wanted to confirm that is the way I should go.
Edward, thanks for the suggestions on the needed tables. I am starting from scratch, so I have some flexibility. Primarily it will be FM Pro, maybe down the road WebDirect and Go. I was thinking initially to do as much as possible on the matrix itself (display and edit), just to make it quicker for the user, but I am open to suggestions.How would generating HTML work, can that be done within FileMaker, or would it need to be coded separately? If I didn't go the HTML route, how would I get the matrix look and feel using FileMaker only?
Peter, are you suggesting that I use the Assignment join table, instead of the Employee join table and the EmployeeRole join table?
Roles store the various roles that employees can take. In your example, there would be two records: Designer and Studio Engineer.
An Employee could conceivably be both a Designer and a Studio Engineer, at least according to Edward's model. I'd plan for it in any case. This is what the EmployeeRole table is for... to link an Employee to one or more Roles.
Things get interesting because an Employee can play neither, either or both roles for a company. This is where the Assignment rable comes in. Adam can be a Designer for Company 1 and a Studio Engineer for Company 2. Or, he could be a Designer and a Studio Engineer for Company 3.
In simplest terms, and in terms of your example, each of the records you display would represent the intersection between Employee, Role and Company. Adam was Rejected (status, an attribute of the Assignment) as Designer at Company 1. Adam was Rejected as Designer at Company 3.
I hope this helps.
What is described is exactly the situation. In fact an employee (contractor) only have one role will be the exception rather than the rule. Currently there are actually ten roles, with some expansion over time. I've set up the first two join tables, do I still need those or only need the Assignment table, it seems that it has all of the relevant elements. If I need all three join tables how would they be interconnected. Thanks for the help.
Here's an ERD I whipped up based on Edward's suggestions, except that as I was building it I came to the conclusion that the Assignment table is really just Edward's Employee table with the additional link to PersonRole. The term Employee doesn't really seem to apply here also since these are contractors, so I removed Edward's Employee join table from the equation and used the Assignment table instead.
Anyway, for context I've also added some other fields to PersonRoles and Assignment to give an idea of why we would use join tables like these to begin with. Although a person could be a Designer AND a Studio Engineer, they might bill different rates for each of those roles, which is stored in PersonRoles::baseRate. When the Person is assigned to a company, the baseRate would copy into the Assignment::rate field, which is the rate the person bills for that role for that company.
Thinking forward, what if a person were hired for multiple projects for a given company? You would need a Projects table as well and your Assignment table would require a link to a given project.
I hope this helps.
ERD.graffle.zip 3.6 K
Yes extremely helpful Peter, thanks. Any suggestions or point me to an example on a data entry UI? Right now I'm just on a Client layout and run a script to select an employee and then create the join record.
In filemaker you represent a matrix by showing a list view of records, and for every record you show a repeating field.
We use this in our agenda,where you can calculate a matrix of a doctor's appointments and see at a glance where a hole in the agenda is, available for giving an appointment.
In our case every record in the list belongs to a given date.
Using the ERD I posted, you could put a portal to Assignments inside a layout based on the Company table. In the portal row you could have popup menus which would let you select the id_Person, id_PersonRole, and Status for each assignment.
EDIT: you could also include a similar portal on a layout based on the Person table, except you'd replace the id_Person field in the portal with id_Company.
Thanks Peter, I have been flushing out others parts of the application. I assume your explanation is for creating new assignments for a given company. I implemented what I think you suggested and it worked great.
siplus, could you elaborate a little more on your implementation.
How would I insert the names in the records with the repeating field?
How would I insert the company names in the header of the report with the repeating field?