1 2 Previous Next 18 Replies Latest reply on Feb 25, 2017 5:56 AM by PeterDoern

    Matrix Display

    laguna92651

      Looking for some ideas how I might implement this matrix. The matrix is to track the status of contractors (name) to various clients (companies) by department (skill set).

      Matrix.png

      There are currently 22 companies on the horizontal, which could still be added to, probably not more than 30. The departments listed on the vertical is currently 12, which might grow to 20.

      Each name could be under multiple companies and/or in multiple departments in the same company.  Any thoughts would be appreciated.

        • 1. Re: Matrix Display
          keywords

          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.

          • 2. Re: Matrix Display
            philmodjunk

            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.

            • 3. Re: Matrix Display
              EdwardMcPikeJr

              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:

               

              • Company
              • Person
              • Employee (join table for Company/Person, storing at least id, id_company, id_person)
              • Role
              • 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.

              • 4. Re: Matrix Display
                PeterDoern

                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.

                • 5. Re: Matrix Display
                  laguna92651

                  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?

                  • 6. Re: Matrix Display
                    laguna92651

                    Peter, are you suggesting that I use the Assignment join table, instead of the Employee join table and the EmployeeRole join table?

                    • 7. Re: Matrix Display
                      PeterDoern

                      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.

                      • 8. Re: Matrix Display
                        laguna92651

                        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.

                        • 9. Re: Matrix Display
                          PeterDoern

                          Hi there,

                           

                          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.

                           

                          Peter

                          ERD.jpg

                          • 10. Re: Matrix Display
                            laguna92651

                            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.

                            • 11. Re: Matrix Display
                              siplus

                              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.

                              • 12. Re: Matrix Display
                                PeterDoern

                                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.

                                • 13. Re: Matrix Display
                                  laguna92651

                                  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.

                                  • 14. Re: Matrix Display
                                    laguna92651

                                    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?

                                    Thanks

                                    1 2 Previous Next