1 2 Previous Next 16 Replies Latest reply on Feb 23, 2015 9:50 AM by jdevans

    designing a timesheet for employees who allot their time towards account numbers

    jdevans

      Title

      designing a timesheet for employees who allot their time towards account numbers

      Post

      I have a sample layout created by another person to use as a goal for layout...(file attached)
      The idea: have the user select a year, which will go to the correct set of calender months.

      The months will be displayed as a set of horizontal buttons. The user selects the month, and then the week number at the bottom.

      Once the correct year, month and week have been selected- the week chosen will show up as headers to columns for Sun, Mon, Tues, Wed....with the correct date displayed on each.

      Then under the "day" headers will be "cells" for the user to input their time in hours.

      Off to the left of each row of "hours cells" will be a drop-down list of accounts to which the users will apply their time. This will enable the user to select the account ONCE for the week, then input their hours to that account on the same "row" on any or all days of the current week displayed.

      Each cell of hours data should then be routed to its own individual record storing EmployeeID, AccountID, dateCharged, hours charged.
       

      This is sort of an "Excel" view of a timesheet. This is what is being used now (MS Excel) for timesheets. Is it possible to do this using FileMaker Pro 13? Somehow each cell on the layout would have to be mapped to a create a record based on the person chosen, and the day selected, and the account selected. I think it is a tall order, but the designer of the layout says that' is how it ought to work.

       

      timesheet_layout_jud.png

        • 1. Re: designing a timesheet for employees who allot their time towards account numbers
          philmodjunk

          It's possible. Each row with time recorded in it would be a different record in the same table. The rows with just the name of the account could be set up using a Sub Summary Layout part "when sorted by" account.

          • 2. Re: designing a timesheet for employees who allot their time towards account numbers
            jdevans

            So, Phil, what about all the other stuff involving the calendar, choices, etc? Could you point me to a good reference to help me get started designing this?

            Right now, I'm sort of clueless unfortunately.

            • 3. Re: designing a timesheet for employees who allot their time towards account numbers
              philmodjunk

              I can't really point you to any one resource. You have a number of choices from free youtube videos to books to training materials from FileMaker. Which is best for you is a decision you'll need to make for yourself as the option that works best for you will not be the best for another given the different ways we learn and the differing prior skill and experience that we bring to FileMaker.

              And we can help you here as well, but forums like this are best suited for answer specific questions so as you work out your solution and hit a specific snag, you are welcome to post your question here for others to suggest answers.

              • 4. Re: designing a timesheet for employees who allot their time towards account numbers
                jdevans

                So you said, "Each row with time recorded in it would be a different record in the same table." Would this be a repeating field? I figured it would need to be to allow for input of hours from Sunday thru Saturday...1 plus 6 repeats.

                • 5. Re: designing a timesheet for employees who allot their time towards account numbers
                  philmodjunk

                  I strongly advise against using any repeating fields here. It may seem like that would make the design of your layout easier, but it makes it harder to work with the data once you have your time card records completed.

                  The columns of recorded time are something that I would implement with a series of one row portals to a related table such that I can get a table where each record logs one employee's work on a given account on a given day.

                  • 6. Re: designing a timesheet for employees who allot their time towards account numbers
                    jdevans

                    "I strongly advise against using any repeating fields here. It may seem like that would make the design of your layout easier, but it makes it harder to work with the data once you have your time card records completed.

                    The columns of recorded time are something that I would implement with a series of one row portals to a related table such that I can get a table where each record logs one employee's work on a given account on a given day."

                     Thanks for the help. I'll keep plugging away at it. If I get stuck, I'll ask more specific questions.

                    • 7. Re: designing a timesheet for employees who allot their time towards account numbers
                      jdevans

                      The columns of recorded time are something that I would implement with a series of one row portals to a related table such that I can get a table where each record logs one employee's work on a given account on a given day."

                      So, for this would I need to place a series of single 1-column-wide portals stretching across underneath the date labels? Would each of those portals only have a single field for hours? Then to the left of those, where the account is selected, would I need a pop-up/drop-down or a one-row portal for that too? I'm new and have only created layouts with a single portal, so I'm confused as to how this would create a single record using multiple portals.

                      Sorry I'm asking for so much hand-holding here, but I really need the education. Thanks for your understanding a patience. 

                      • 8. Re: designing a timesheet for employees who allot their time towards account numbers
                        philmodjunk

                        Helping out "newbies" is a primary function of this forum. wink

                        This isn't the most simple of tasks for someone new to FileMaker and/or Relational database design. We need to start with a data model that makes sense before working out the details of your layout design.

                        Note that each row in your example represents a single week. So let's start with a record that represents the work record for an employee's work on a given account over an entire week and link it to the table of records that I previously described. We'll also need a table of employees with one record for each employee:

                        Employees-----<AccountWeeks------<Hours

                        Employees::__pkEmployeeID = AccountWeeks::_fkEmployeeID
                        AccountWeeks::__pkAccountWkID = Hours::_fkAccountWkID

                        In the AccountWeeks to Hours relationship, enable "Allow creation of records via this relationship."

                        For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                        With this data model, we can base the layout in your original post on the AccountWeeks Tutorial: What are Table Occurrences?. That means that you will select "AccountWeeks" from the "Show Records From" drop down when you create this layout. Your portals will be portals to the Hours layout and I'll go with the portal filter option here, though some might opt for 7 different occurrences of the Hours table in 7 different relationships instead. (there are pros an cons with either approach). With the filtered portal option, we don't need to add more occurrences, but we will need to be careful how we create Hours records to ensure that they appear in the correct portal. You may find it simpler to set up a different layout for data entry purposes that does not use separate columns for each week and then keep this layout for reporting/review purposes.

                        In AccountWeeks, you'll need a field to identify the account (most likely an ID field that links to a table of accounts (or subaccounts)) and a date field that records the date for Sunday of a given week. You will also need a date field in Hours that records the day those hours were worked and which your filters will refer to in order to control which column will display the hours from that record.

                        You can then define portal filters for your portals like this:

                        Column 1: AccountWeeks::Sunday = Hours::Date
                        Column 2: ( AccountWeeks::Sunday + 1 ) = Hours::Date
                        Column 3: ( AccountWeeks::Sunday + 2 ) = Hours::Date
                        and so forth...

                        The final part of this initial setup (only needed if you use this layout for data entry) is to set up the OnObjectEnter trigger on each portal to run a script that sets Hours:Date to the correct date:

                        For the first portal, that would be:

                        Set Field [ Hours::Date ; AccountWeeks::Sunday ]

                        for the second:

                        Set Field [ Hours::Date ; AccountWeeks::Sunday + 1 ]

                        If you know enough on how to script things, you might use a single script for all 7 portals set up like this:

                        Set Field [ Hours::Date ; Get ( ScriptParameter ) ]

                        where the script parameter would be Sunday, or Sunday + 1.... to pass the correct date for the portal to the script as a script parameter.

                        Ok, that crams a lot of info into a single post and even then, I've left out a number of details in how to do this that you may or may not need, so don't hesitate to ask follow up questions if there is a part of this that you don't understand. I've also embedded links to two other threads that provide supplementary info to help you with this post if you find that you need that information.

                        • 9. Re: designing a timesheet for employees who allot their time towards account numbers
                          jdevans

                          This is great info. I  knew it would involve a change in table design, and relationships, but I couldn't see the forest for the trees, so to speak. This post clears up a LOT. I have been going thru the VTC FileMaker Pro 12 Beginner/Intermediate/Advanced lessons by John Mark Osborne, and these help a lot in when it comes to exposure to different tools and development methods, but I suppose with lots of actual start to finish solution design experience comes the ability to know when to use what, why and how.

                          I'll be working on this throughout the rest of the day. Thanks so very much!!!

                          • 10. Re: designing a timesheet for employees who allot their time towards account numbers
                            jdevans

                            find the image of the relationship model.

                            OK, I populated the layout with a single row of portals...set them to display hours_worked (number) field from the Hours table. I set up the OnObjectEnter Scripts for each one (I did one each for Sun-Sat, just duplicated, and changed the day). It is generating a list of records in the Hours table (in table view, I can see 'em all). But I suppose somewhere along the way, I realized I hadn't tied each one of these records to a specific account (or sub-account). I haven't created a mechanism for it yet. Since the portal is only big enough to put in hours, how can I tie each portal to its appropriate account?

                            Another thought- this all seems a way to create a single row ....what about subsequent rows underneath the first one? How would that be accomplished? I can see how everything you've described would work to create a single row, but each portal for that row adds a new record to Hours.

                            I can't picture how to add subsequent rows. Unless it is to place all the above stuff inside yet another portal. Still kinda foggy understanding the overall big picture. Thanks so much for all the detailed content thusfar.

                             

                             

                            • 11. Re: designing a timesheet for employees who allot their time towards account numbers
                              philmodjunk

                              You don't have to. You have already selected the account via _fk_Project_Account in the AccountWeeks record that serves as the parent record for the hours records you record inside the portals. You can use that relationship to identify the account for any given record in Hours.

                              • 12. Re: designing a timesheet for employees who allot their time towards account numbers
                                jdevans

                                OK,  I ctrl+dragged one of the hours portals to create a duplicate, but in that one, I used the _fk_project_account  and did a popup menu to select the account for the hours on that row. It works. It creates individual records (one for each day) in the Hours table for a single employee on a single account.
                                However,
                                I still am stuck as to how to go about adding the next row(s) for this same layout. The employees will need to be able to create several rows if necessary. I'd like it to be dynamic, in that it only shows as many rows as the user needs for a given week. One week, he may work 35% 65% on a couple different accounts, but on another week, he may have to divide his time up between several (I've seen as many as a dozen) accounts.

                                I wrote some scripting that forces the user to select a Sunday as well. That works pretty good.

                                One little glitch I noted in the Hours table, after I'd created several records for different employees was that on days when they registered no hours, it still created a record with a null for hours_worked. I suppose it'd take a script to check for that and eliminate those rows, or maybe there is a more elegant way to do it to keep it from creating those records in the first place.

                                I'd love to be able to control when the data gets "saved" to the Hours table. There are times when an employee may have to sort of juggle around his/her hours depending on the situation. Is there a way to enter time in hours in these portals but have it "hold" them until maybe a "SUBMIT" button is clicked? This would give the user the opportunity to make sure the records that they are submitting are correct before they become official.

                                • 13. Re: designing a timesheet for employees who allot their time towards account numbers
                                  philmodjunk

                                  That's not what I have suggested that you do. your layout should be based on the AccountWeek table and you should not need any such portal for displaying information about the account. Ideally, you would have a separate related table of account information and by selecting a value from the _fk field for the account ID, you link that record to a particular record in the account table and you can display any data about that account, such as the name once you have selected such a value.

                                  To add a new row to this layout, you simply do one of the following: Select New Records from the records menu, or use the keyboard shortcut for the same or put a button that does the new record script step when you click it.

                                  I have previously suggested that it would be simpler to set up a very different layout for employees to use to log hours and to use the layout shown to review their hours. I still think that would be a better option here. No new records should be created in Hours just because you create a new record in the layout's AccountWeek table. Such a layout might use a set of global fields for recording the employee's time and a submit button on that layout could perform a script that uses the data in the global fields to create a record in the hours table with the data they entered. Such a script can even check for a previous entry by the same employee for the same combination of date and account information and respond as you deem appropriate. (It could combine the new data with the old by adding the hours together, it could replace the old with the new or it could refuse to apply the change without an authorization from someone higher in authority such as the employee's manager or supervisor.)

                                  And please note that a new record should not be created in Hours until the user actually enters the field inside the one row portal.

                                  • 14. Re: designing a timesheet for employees who allot their time towards account numbers
                                    jdevans

                                    My layout is based on AcctWeeks. What was not clear in your initial description was exactly how/where to place the fk_accID field on the layout. I chose to put it on the same "row" as the day/hour fields (which are each portals to Hours). This was an attempt to make it mimic Excel behavior.

                                    I like your idea of having an "Input" layout, and a separate "review" layout. I may end up doing something like that, but I'd like it to resemble visually the way it currently looks in Excel.

                                    Adding a new record via the FMP13 menu does in fact create a new row of records as you described....but not visually on this layout. What the user sees on this layout is the same single row of fields- one for account, and the rest for hours of each day. The hours they entered for the previous account are no longer visible. 

                                    Is it possible to create this second layout (the "review" one) and show it off to the side of the "input" layout? So that the user has a visual representation of the hours they've put in over the current week as they enter them?

                                    Here's what I have so far...the Xd out portion is not functional. Just the top portion

                                    1 2 Previous Next