14 Replies Latest reply on Jun 5, 2014 4:44 PM by philmodjunk

    Counts from Great Great Grandchild Table



      Counts from Great Great Grandchild Table


           Hello helpful Filemaker friends!

           So I have created a layout based on my table called "EventOccurances."  

           On this layout, I have a portal to "EventOccuranceLineItems" so that I can add employees to a certain "event occurrences" (you may have guessed that a given event can have several occurrences).

           I use this portal to add employees (from the table called "Employees") to an event occurrence.  Eventually I will want to email all the attendees the details of their attendance, but I digress, we'll get to that.

           Each employee has one "Role" in our store and I assign roles via the "PositionLineItems" table as a role can have several employees.

           As I mentioned before, in the layout based on the table called "EventOccurances"  I have a portal to "EventOccuranceLineItems".  I would like to ideally like to add a second petal to the layout that dynamically lists the number of employees in each role for an even occurrence.

           Another way of saying it is that when we plan events, it is important that I balance the number of each role that attends and event occurrence.  We would like to make sure that we keep each event occurrence balanced with a mix of the different roles and also that no one manager is too highly represented at each of these event occurrences.

           Any thoughts?  It is too confusing?


        • 1. Re: Counts from Great Great Grandchild Table

               Can an employee have more than one role assigned to them? (if not, there would not appear to be a need for the positionlineitems table.

               If an employee can have more than one assigned role this complicates what you are trying to do on your eventoccurrences layout as the same employee might need to be counted more than once as you would need to count them one for each role...

          • 2. Re: Counts from Great Great Grandchild Table

                 Hello PhilModJunk:

                 Thank you for your inquiry!  An employee cannot have more than one role at one time, you are correct.  I would add that a role can have certain different "classifications."  As an example, an employee can be a Bartender with full time status and yet another can be part time status. Also each roll can have different areas of the business.  As an example, some bartenders are part of the catering side of the business and others are part of the restaurant.  FT/PT & Areas of the business are not role specific, they are mixed.  Hence my reasoning for the  "PositionLineItems" join table.  We want to generate reports on all of those aspects.

                 Reports List:

            •           FT v PT [Per area of the business]           
              •                     I am using a sub summary report for this report with no "body" and with area of the business as the break field.  It is sorted by FT/PT (omitting exempt employees)
            •           Managers & Employees           
              •                     I am using a sub summary report for this report with the Employee Name & Role in the body.  The break fields are Manager Name and Role.  I must say it is a beautiful report and the managers already really like it as this can change frequently during our "season."

                 I did consider using the Role simply as a value list, but I couldn't work out how to separate FT/PT and other role specific classifications which help with reporting on number of people in each role blah blah blah... 

                 Does that help?  I'm open to feedback!  :-)

            • 3. Re: Counts from Great Great Grandchild Table

                   If an employee can only have one Role, is there any use for the join table (PositionLineItems)? The only use for that I can imagine is for historical purposes. You might hire an employee as a part time bartender and then promote them to full time or transfer them from "catering" to "restaurant" and need to keep track of their prior positions.

                   What this comes down to is that you will need to somehow isolate only the role (position) for each employee that is relevant to the event in question. Either you need to remove the PositionLineItems table and replace it with this relationship:

                   Employees::_Rolefk = Roles::__Role.pk (I don't recommend putting . into field names--it's a potential source of confusion in a number of areas)

                   Or you need to designate the Employee's specified Role in EventOccurrencesLineItems. This can be an auto-enter setting that inserts the first or last Role ID value from your other tables in order to record the employee's current role. (Whether that is the first or last related record depends on how you sort the relationship between employee and the PositionLineItems table.)

              • 4. Re: Counts from Great Great Grandchild Table

                     Hello again Phil:


                     i am trying so hard to to bother you with this same issue but I just cannot get it to work precisely. 


                     The he closest I am able to get is to now is that the portal will show me a count of  the number of each position that is used in ALL rows of EventOccuranceLineItems. But what I want is to see how many of each role are attending a given event occurrence. 


                     I THINK its kind of like each item in an invoice. If an item has a "category" like product or service, could.  A person place a portal on an invoice layout which totaled the number of products and totaled the number of services separately?


                     am I looking at this all wrong?

                     thanks for your help as usual. 

                • 5. Re: Counts from Great Great Grandchild Table

                       I am always amused by the "I don't want to bother you..." comments some people made. If I'm bothered, I can always choose not to respond or I can limit my responses to a lower frequency such as once every few days--which can result in others pitching in with the assistance needed.

                       I can't tell from your response what your current set up is. What changes did you make to try to get this to work?

                       Did you remove or keep the PositionLineItems join table?

                  • 6. Re: Counts from Great Great Grandchild Table

                         Thank you for your encouragement Phil.  I have been struggling with this and at this point I can't even really figure out where I'm at with this particular aspect as I feel like I have tried a lot of combinations.

                         On the layout based on the TO called "Sched|PD|Group|Event" there is a portal to the TO called "Sched|PD|Group|Event_lineitems" which is doing a lovely job allowing me to add employees to the "Event" so that I can later email everyone individually with the event details for their specific event.  An image of that layout/Portal was in my post above with the date of 5/14 (at least on the east coast in the US).

                         In order to give me a total of how many of each "role" are attending a given event, I do suspect the answer lies in the combination of the portal looking at the correct TO (be it "Sched|PD|Group|ROLE" which is connected to the TO of Employees called "Sched|PD|Group|Employee" via the previously discussed join table named here now "Sched|PD|Group|PositionLineItems") and/or some sort of aggregate function on either the Employee TO called "Sched|PD|Group|Employee" or the second occurrence of "Role" called "Sched|PD|Group|Role_sorting" which is related to the "Sched|PD|Group|Event_lineitems" TO via a calculation which pulls the employee's "Role" primary key into a filed called "_role.fk" and connected via the relationship graph.

                         In the attached image, I have endeavored to color the main TOs in orange while every yellow TO is a second/third etc occurrence of that table.

                         Am I being clear(enough)?

                    • 7. Re: Counts from Great Great Grandchild Table

                           My questions aren't just about the design of your database, but in how you need to manage roles in your business operations.

                           In your original design, you used a many to many relationship to assign roles to each employee, but indicated that each employee only had one role. I then asked if you really needed that many to many relationship as it complicates what you need to do here.

                           It comes down that key question: Do you need a many to many relationship linking employees to roles?

                           The only reason for having that join table given your previous answers that I could imagine was that you might want to keep track of past roles that have been assigned to a given employee. In other words, John Smith may have been hired by you and given role A. After some time of giving you good service, you might change his assignment to a different role with more pay and responsibility, so you assign him to Role B. If you need to keep track of the fact that he once had Role A, you need that join table. If you only need to know his current role in your business, you don't need the join table and we can simplify your database design.

                           Another possible reason for having more than one role for an employee is that you might require them to do a different role for a particular event. Maybe John works as a bartender for you in most cases, but for a particular catered event, you are short of waiters and so ask him to work as a waiter for that one event....

                           And there could be other reasons for the join table that I, not knowing how you need to operate your business, am not able to imagine.

                           So I need an answer to this one crucial question:

                           Do you need a Join table for managing role assignments for your employees?

                           And If your answer is yes, I then need you to describe why you need it so that I can use that info when suggesting how to get the correct Role info into each record of your event planning join table.

                      • 8. Re: Counts from Great Great Grandchild Table

                             Hello again Phil!


                             I do believe that the join table is necessary and here is why (although I must say I am not too prideful to admit that it is entirely possible I am wrong in this way of thinking):


                             I see employees and Roles and the positions to be three discrete different items. Sometimes I need to sort by a type of role because for instance some roles are recruited by HR and some are recruited managers. Another reason is sometimes I need to look at all employees in a business unit which is not role specific, but position specific (as an example bartenders can be in the position of catering or bartender can be the business unit of the restaurant). This is so far maintained in the position line items table. Additionally, I also sometimes need to see what full-time employees we have what part-time employees we have which is not role specific but position specific. 

                             I guess what I'm saying there are attributes of a given roll regardless of a position, And attributes of position regardless of its role.  I did consider making the role simply a value list, but I wanted to see when resources team and the management team to each be able to report either based on role or business unit or full-time part-time status and I did this all by showing the view of either the context of the role table or the position line items table.


                             For now, I'd like to maybe assume that this is still necessary. Although, I am willing to hear really good arguments as to why this is not a necessary step.

                             When planning these large group events, it would be good for the scheduling team to know:

                        •           How many bartenders are attending a given event regardless of which business unit they are in
                        •           How many employees of each business unit are in that same event regardless of their role
                        •           How many employees each manager has represented at these given events
                        •           Additionally, this whole layout portal situation is helping to e-mail each employee individually (and attached an ics file) with what is going on at the event in which they are attending and who else is going to be with them for the workshop or meeting.

                             My vision was to have four separate portals on the layout for the event. The main portal on this layout would be where the scheduling team would add employees to an event (this is working beautifully now).  For the other three smaller "FYI/Info" Portals:

                        1.           Show how many of each role or any given event
                        3.           Show how many employees from each business unit are at the event
                        5.           Show how many employees each manager has at a given event
                        • 9. Re: Counts from Great Great Grandchild Table

                               Yes, but nothing that you describe in  "I see employees and Roles and.." as describing a many to many relationship between an employee and multiple  assigned "roles". You can still have only one role for an employee and that role can be linked to different positions and be a member of different categories of positions. All without needing to have a join table of roles--which is only needed if an employee can have more than one assigned role--either at the same time, for different events or at  different times in their employment history.


                                    but I wanted to see when resources team and the management team to each be able to report either based on role or business unit or full-time part-time status

                               There's a lot there for which I simply do not have enough information to be sure, but at this point, that does not sound like tasks that require the complication of your added join table.

                               It may help to explain the difference between an employee's "position" and their "role" as those do sound like two names for the same thing to me though that is not why I am still of the opinion that your join table is not needed here.

                               I have understood what you had in mind for a report from the beginning, but there's a significant issue with multiple possible solutions for how to resolve them: That is how to designate the "role" for each employee for a given event.

                               I have already outlined two very different approaches to a solution:

                               1) Select the role for the employee at the same time that you select the employee for a given event. If the same employee might have different roles in different events, this is the most likely best solution here.

                               2) Extract the role from the table of roles linked to that employee's record. This is possible, but then for a given set of multiple join table records each linked to a different "role" record for the same employee--as is possible from the screen shot of your relationships, we need to identify which one should be selected automatically when you assign that employee to a given event. That is an option for which describing the needed solution is still not possible as I do not at this time see why you would have more than one role specified for a given employee.


                                    How many bartenders are attending a given event regardless of which business unit they are in

                               Am I correct that "bartender" is an example of one possible employee role?

                          • 10. Re: Counts from Great Great Grandchild Table

                                 Hello Phil:

                                 Thank you again for your detailed thoughtful responses.  I am confident through this dialog, I can come to an understanding or realization of how to accomplish my goal.

                                 While it is true that an employee can only have one position (and we do not track their history using this table), I don't believe that to be the only criteria by which the decision of a joint table should be made. Although I will once again I'll say that I am quite a new developer, only a couple of months; so I am still quite open to this discussion.

                                 Allow me to clarify the reasons why I believe we need a join table. First and foremost, our company thinks of roles and positions as completely different and unique items. We don't think of them as the same thing at all.  For this reason alone, I believe it is a good reason to keep positions and roles as separate tables.

                                 Here are a few other reasons why:

                            1.           When a new position is created there are certain attributes that need to be applied to the person who is assigned that position which are role specific.  An example of this is when we hire a new "business unit leader"; all "business unit leaders" are exempt (or salaried) and do not count in our labor used calculations.  Conversely when we hire a new bartender all bartenders are hourly employees and do get counted in our labor used calculations.  These are attributes that are understood when an employee is assuaged a position tied to a role.  When the recruiters create a new position, we don't want him or her to have to manually select or input any of the role specific attributes.  They should be applied automatically when a new position is created.  
                            3.           There are some attributes which are applied to an given position regardless of the role they are. An example of this is that while we have many bartenders (which is a role), some of them are considered full-time and some of them are considered part-time. An attribute like full-time or part-time is position specific not roll specific.
                            5.           When you click "create new position" (a button/script I built) on the Role layout, they user chooses 1) FT or PT and 2) Catering or Restaurant.  Then the script creates a new record in the positionlineitems table where the Role|fk came from the role which was active when the  "create new position" button was pressed and FT/PT was user driven as was Catering/Restaurant
                            7.           The Layout(s) that I have designed will help the HR team to organize their management of employees in the say way they think about employees/Positions/Roles.  I have attached a screenshot to show you the layout based on the Role table.  Each role displays two portals to the positinlineitmes table.  One portal filters to show only the roles that are currently assigned to an employee and the other portal show only the positions which are not currently filled.

                                 If we may, I would like to operate under the assumption that we do in fact need the positionlineitmes table and debug this portal I am trying to show. I hope that is okay.

                                 As for the two suggestions you made:  

                            •           #1 doesn't feel like it is a good match in that one employee can only occupy one role within the organization, so selecting the role after also having just selected the employee for a given event feels like it could be redundant.  I could just hear the scheduling team say "why am I selecting a role for them person when a role has already been assigned?"
                            •           #2 seems promising.  I have put in a filed in the "EventOccuranceLineItems" table which is a calculation that pulls the "Role" Primary Key from the Role table which is already assigned to an employee.  It is working beautifully.  Every time I put an employee into an event it is automatically pulling the correct Role Primary key.  I am not able to get these totals to display correctly in the portal. The second portal is just essentially  showing a new row for every employee instead of grouping/totaling the Roles.
                            • 11. Re: Counts from Great Great Grandchild Table

                                        While it is true that an employee can only have one position (and we do not track their history using this table), I don't believe that to be the only criteria by which the decision of a joint table should be made.

                                   I am puzzled as to why you said "position" in this statement and not "role". Apparently, these are two different things in your organization and we were looking at how to designate the correct role for a given employee.

                              Item 1. above,

                                   but what does that have to do with an employee's ROLE? is "Bartender" a "position" or a "role"? And the fact that all bartenders are hourly employees and management staff (business unit leader) are salaried would seem to have nothing to do with the need for a join table. From your original screen shot, the only reason you would set up a join table between Employees and Roles is if one Employee can have more than one Role and One Role can also be linked to more than one employee. It seems very obvious that more than one employee can have the same role, but you have stated repeatedly that an employee can have only one ROLE. If that is the case, then no join table is needed and removing it makes the automatic link between an employee linked to an event and their role very straight forward.


                                   But multiple attributes for the same position becomes data that is part of documenting the employee's position, not their role. A table of such attributes would be linked to either the position table or the employee table, but not the table of roles.


                                   But this does not then give the employee more than one role. If you are creating records in the PositionLineItems table in order to document this, that's OK, but there's no reason to then link this table to the Roles table--which should then be linked directly to the employees table.


                                   Your layout shows a lot of information about the employees position--data that is stored in the Position table and the PositionLineItems table, but nothing that looks like data from the Roles table.

                                   To clarify, you have these relationships in your original relationships:


                                   That looks incorrect for what you describe. It appears to me that this should be:


                                   Employees::_fkRoleID = Roles::__pkRoleID

                                   This would then allow you to link an occurrence of Roles to the occurrence of employees used to link them to Events and then a reference to this added occurrence of Roles will provide the needed info to your event management layouts.

                                   But that will not work with your current set of relationships.

                              • 12. Re: Counts from Great Great Grandchild Table

                                     I'm frustrated because this is the third time now that I've had to type this response to your most recent post. My browser has somehow closed the session each time and so I apologize for any typos etc that you are about to be subject to as a result of my impatience.

                                     Let me try this again, positions, employees, and roles are three completely different items that are of course related to each other. 

                                     When you say something like Sally is a full-time bartender for catering the term bartender of course comes from the role that Sally is assigned as a byproduct of the position she is assigned via the positionlineitem table. The terms "Full-time" and "catering" relate to the position.

                                     If Sally would be promoted to manager then her position of full-time bartender for catering with still exist and now the HR team would need to recruit for it and hire.


                                     Sally                    FT & Catering            Bartender

                                • 13. Re: Counts from Great Great Grandchild Table

                                       The screenshot that I had just posted was a layout based on the role table which displayed two portals:

                                       1.  one portal showed the position line items filtering out any "open" or "pending" positions

                                       2. the second portal showed the position line items filtering out any any occupied positions.  

                                       This way human resources could see which positions were filled in which physicians were not.

                                       In the instance I showed we were looking at the role called "bartender."  

                                  1.           One portal was showing all position line items who were assigned to the position that had the role bartender assigned to it.  Although the portal was showing the position line items, the information that I had the on portal was the name of the employee who currently occupied that position.
                                  3.           The other portal was showing all unsigned positions which have the role bartender assigned to it. Although the portal was showing the position line items, the information that I have the portal was whether or not the position was full time or part time and who was the previous occupant of that position.
                                  • 14. Re: Counts from Great Great Grandchild Table

                                         Due to a noxious forum bug, please protect yourself with a "Select-All, copy to your clipboard" action just before submitting a private message or comment to this forum. The bug can lose your comment and log you out of the forum--forcing you to sign back in and re-enter the comment or message. By copying to the clipboard before posting, you can re-enter your message by pasting from the clipboard instead of having to retype it all over again.

                                         I've read your last two posts. They do not change my analysis and advice. If an employee never has more than one Role record, you need to change the relationships you have in place. That change, in turn will make it possible to automatically show the role of an employee when you select them for an event occurrence.