14 Replies Latest reply on Mar 28, 2017 12:29 PM by philmodjunk

    Year to date fields based on current year

    BERGSTEN

      Screen Shot 2017-03-22 at 2.04.01 PM.png

      On our payroll layout- I'm trying to find a way to have year to date totals propogate based on the current year. Right now they are simple summary fields that total the fields in question. Regular hours, Over time and Flex (aka time off).

      My concern is when 2018 comes around, I won't be able to distinguish the totals.

       

      As you can see each pay period is defined by two separate date fields at the top.

       

      I'm wondering what I can do to set the field to pull totals strictly from 2017 records, and then next year, from 2018.

       

      Chances are its gonna be more complicated than I feel it should be, that's how my general experience with formatting simple requests in FMP has gone so far anyways.

       

      Any help is much appreciated.

       

      Thank you.

        • 1. Re: Year to date fields based on current year
          philmodjunk

          The simplest, "old school" approach is to set up a self join relationship that matches to all the relevant records. For payroll, I'd guess that you'd match by year and by employeeID (not employee name). A year field can be set up to calculate just the year if you do not already have such a field. You can reference your summary field from the related table occurrence to get a YTD value or a sum function can use this relationship to calculate the value.

           

          There are other options as well.

          ExecuteSQL can calculate a sum of the values using a WHERE clause that specifies employee ID and the year.

          • 2. Re: Year to date fields based on current year
            BERGSTEN

            So I have specific fields for each employee. Ie: Employee1_Reg_Hours, Employee2_reg_hours

             

            In hopes that I could do a sum of all Employee1_reg_hours across one year.

             

            I do not currently utilize a tech id. The only date field is the period start and period end - which is specific dates - 2 weeks worth.

             

            Is there any way to incorporate the calculation without having to create additional fields? Probably not right?

             

            I can't wait until I know how to do more simple stuff like this lol. Thanks.

            • 3. Re: Year to date fields based on current year
              philmodjunk

              So I have specific fields for each employee. Ie: Employee1_Reg_Hours, Employee2_reg_hours

               

              Sorry, but that's not going to make reporting very easy to do. Better to have separate records for each employee. In fact, separate records for each time an employee clocks in and out is best.

              • 4. Re: Year to date fields based on current year
                BERGSTEN

                The problem is my employer is already hesitant to move out of excel but my direct superior wants me to reflect the spreadsheet set up within an FMP layout. As such I thought that by creating a spreadsheet-like layout with employee specific fields that we could easily total etc. It wasn't until the entire layout was built that I was faced with this realization that I can't easily total based on calendar year. I was hoping on the off chance that there was a way to make this happen..

                 


                As usual I'm just stuck in the middle of trying to give everyone what they want while working with pretty limited knowledge at the moment as to how to make FMP work for us.

                • 5. Re: Year to date fields based on current year
                  philmodjunk

                  That's a tough place to be.

                   

                  But you don't have to use separate fields to get a "spreadsheet like" layout. It sounds like you need to invest some time into learning more about FileMaker. You also may have to carefully, but firmly indicate that if they want to use FileMaker some aspects of how it looks on the screen or printed page will change in order to make a more optimum use of the software. FileMaker is not a spreadsheet program. It does some things much better than a spreadsheet, other things not so well.

                   

                  To get a "speadsheet like" layout, here are some terms to research here and in training materials:

                  1. List view layouts
                  2. Table view layouts
                  3. Setting up a "horizontal" portal (puts data from different records into columns instead of rows)
                  4. Repeating Fields (There are a lot of bad ways to use a repeating field, but they still have their uses. Don't use this option without a lot of thought and research. They are easy to set up on a layout, but can create a lot of issues with relationships and reporting.)
                  • 6. Re: Year to date fields based on current year
                    BERGSTEN

                    So you suggest I have a separate layout where I can collect records- each featuring an individual employee's hours etc. for any given time period?

                     

                    So in theory by the end of the year, I'll have 24 records per employee (based on a biweekly payment system)-- which make everything easier to report?

                     

                    Just making sure I understand.

                     

                    I would have their tech ID associated with each entry per employee... but I need to specify the exact pay period so would the year field you suggest also have to exist somewhere?

                     

                    Thank you for your patience. I am trying to learn filemaker but the tutorials I come across and the videos I find are not specific to my exact needs and it results in a lot of wasted time searching through material that doesn't end up helping. My superiors know I am learning as I go but I was utilizing this forum to help pinpoint specific goals.

                    • 7. Re: Year to date fields based on current year
                      philmodjunk

                      Nope, you'd have 100's of records per employee by the end of the year. At a minimum, it would be one record per employee per day that they work.

                       

                      If employees can split shifts or need to charge hours worked against specific projects or clients, the number of records could be far higher.

                       

                      And then you would likely have other tables linked to it as well.

                      • 8. Re: Year to date fields based on current year
                        BERGSTEN

                        Hmm. We have physical time sheets that the guys fill out per pay period. Our employees are professional sound engineers- we are not your average work environment where shifts are switched or anything like that. They are assigned to specific events and submit their hours at the end of 2 weeks.

                         

                        My superiors are interested in pay period data, not daily data- if that makes sense. Would your suggestions of pulling data by current year still work if we were to have individual records per employee, per pay period?

                        If I were to suggest daily records, it would undoubtably be shot down.

                        Mind you this would be for internal use only, not for employees to interact with.

                        • 9. Re: Year to date fields based on current year
                          philmodjunk

                          You are confusing user interface design with data modeling. The fact that you have one record for each day for each employee does not mean that employes have to report their time daily nor on a screen where only one day's time is shown at a time.

                           

                          A layout can show multriple records in both rows and columns at the same time. This can be on a screen that replicates their current paper time cards.

                          • 10. Re: Year to date fields based on current year
                            BERGSTEN

                            Hmm. Yeah, I'm definitely confused.

                             

                            Can you recommend any resources that could help me have a better more thorough understanding of FMP? I am not understanding your concept in terms of how I would go about setting this all up. Daily entries sounds like a lot of work for me, vs. pay period totals. I understand a layout can show multiple records, but the idea of needing to input each employee's hours on a daily basis doesn't sound realistic when I wear so many hats here in this small business.

                            • 11. Re: Year to date fields based on current year
                              philmodjunk

                              You need to read my last post again. Daily logging of hours is NOT needed.

                               

                              But if you mean that you don't want to enter specific hours for each day, just totals for the week, that is also possible. I assumed this would not work for you given your reference to overtime. In most locations, overtime is something that has to be logged on a daily basis in order to document that it is being correctly calculated.

                               

                              There are many training resources available. I haven't used any of them--being a developer that has used FileMaker since FileMaker Pro 2.5, so I can't really recommend for or against any of them.

                               

                              There are books, videos on You tube, Lynda.com and there's the FileMaker Training series that you can look up.

                              • 12. Re: Year to date fields based on current year
                                realgrouchy

                                If I understand your setup correctly, you have one record for each pay period, and within that you have different fields for each employee. As Phil said, it's not an ideal setup, but given that here's what I would do:

                                 

                                - Create a calculation field, PeriodEndYear, that calculates Year ( PayPeriodEndDate )

                                - In the table relationships (File > Manage Database > Relationships ) set up a second instance of the same table (by dragging the PeriodEndYear field out of the table's field list in the relationship table and back into itself). This second instance contains the same data, but it can be filtered based on this relationship. In this case, it will only contain records from pay periods that end in the current year

                                - For each field you want to have a YTD value for, create a "Summary" field that is a "Total of" [field].

                                - On your layout, when you add this summary field, reference the copy in the second table instance (on the "Specify Field" dialog, select the table instance name at the top)

                                 

                                If you want this calculation to work retroactively (i.e. so that looking up a previous pay period will only show YTD up to that period), do the following: in the relationships graph double click on the = symbol that appears on the line between the two table instances, and in the dialog select the PayPeriodEndDate field on both sides and select the "≤" variable in the dropdown between the two tables, then add this to the criteria (in addition to the PeriodEndYear = PeriodEndYear)

                                 

                                This is all very similar to the method that Phil referenced in his initial reply. Yes, you'll have to create a number of new fields (three per employee, if I understand your setup right), but it won't require any new data tables. You will only have to configure the new fields once; after that you won't have to populate them manually. You can still import and export from/to Excel and customize whether these fields appear, although you can't import these summary fields from Excel, and if you export them to Excel they won't update if you change the numbers.

                                 

                                Going forward, when new employees come along, you'll have to create that many more fields for the new employee. Or if you're no longer there, whoever manages the database will have to do this. As Phil has suggested, that's not ideal. You might want to take this opportunity to reconfigure your database so that you have one record per employee per pay period (as opposed to one record per employee per day). That will take some time and some, and you'll likely have to create separate tables (or at least value lists) for employees and pay periods, in addition to having a new table that has the entry per employee per pay period. Aside from helping to future-proof your database against being unable to add employees after you leave if nobody knows how to use FMP, it will also allow you to create layouts that look up data by employee in addition to by pay period.

                                 

                                You've already had a taste of the power that FMP can give you in being able to calculate YTD totals. There's a lot more it can do for you once you learn how...

                                 

                                Good luck!

                                 

                                - RG>

                                • 13. Re: Year to date fields based on current year
                                  BERGSTEN

                                  Thanks for your insight.

                                   

                                  I am trying to set up a separate table now just for individual employees hours per pay period as previously suggested.

                                   

                                  I have a portal fields that allow tech name and ID number to propagate on each record.

                                   

                                  My question is- how do I get hour totals per employee? How do I use their tech ID to get employee specific totals, YTD or otherwise? If I just get a summary field for 'regular hours'-- it would total everyones regular hours together- not employee specific hours.

                                   

                                  This is why I created individual fields for each employee in the first place because that made sense to me. No matter how much outside research I try to read and experiment with I can never find solutions for my specific problems :/

                                  • 14. Re: Year to date fields based on current year
                                    philmodjunk

                                    This takes us back to my original response:

                                     

                                    The simplest, "old school" approach is to set up a self join relationship that matches to all the relevant records. For payroll, I'd guess that you'd match by year and by employeeID (not employee name). A year field can be set up to calculate just the year if you do not already have such a field. You can reference your summary field from the related table occurrence to get a YTD value or a sum function can use this relationship to calculate the value.

                                     

                                    There are other options as well.

                                    ExecuteSQL can calculate a sum of the values using a WHERE clause that specifies employee ID and the year.