9 Replies Latest reply on Nov 9, 2013 1:50 AM by WeirdMan

    List of active employee and total days of absent

    WeirdMan

      Title

      List of active employee and total days of absent

      Post

           Hi, I am currently working for a salary database solution. I am looking to show a list of active employee with count of absent day in a specified date range.

           I have employee table with resigned field (boolean value), daily transaction table for working hour and work category. Absent is in working category value list. So the way to seek if an employee is absent in a specified date is to check their working category on that day.

           I tried to use solution from this thread: http://forums.filemaker.com/posts/5851b45723 with addition that I create a calculation field (absent) in daily transaction table with this formula < If (ID_Category = absent ; 1 ; 0 ) > and a summary field < total of absent>. I use relationship as attached (karyawan is employee in Indonesian language), and the summary result is total of all absent category from all employee

           What do I miss? How to get it work?

      Screen_Shot_2013-07-24_at_18.49.42.png

        • 1. Re: List of active employee and total days of absent
          philmodjunk

               is "Tanggai" a date field that records the date of an employee's work history?

               On what layout based on which Tutorial: What are Table Occurrences? did you place the summary field?

               If you define the summary field in Transaksi Gajian but place it on a layout based on Master Karyawan u..., you should get the total days absent for the specified date range for the current employee record.

               You can also get a count of the days absent if you place that same summary field inside a sub summary part "when sorted by" IdKaryawan on a Transaksi Gajian layout if you then sort the records by IDKaryawan. With such a layout, you can perform finds for one or more employees and for a specified range of dates to get the number of days absent for one or multiple employees.

          • 2. Re: List of active employee and total days of absent
            WeirdMan

                 yes, "Tanggal" is date field.
                 I made the layout based on Master Karyawan U.. that you mentioned. It only show employee name and Transaksi Gajian::AbsentSummary. But it shows nothing on summary field. When I clicked on Transaksi Gajian::AbsentSummary field it tell me that "This operation cannot be performed because one or more of the relationships between these tables are invalid." Is that because I have other Master Karyawan table related to Transaksi Gajian?

                 I don't like to see all  transaction to see summary, it is overwhelming since the range is about a year.

                 Is there any clue to show only name with "not resigned" status?

            • 3. Re: List of active employee and total days of absent
              philmodjunk

                   What storage options have been selected for IDKaryawan?

                   The "T" connector type in the relationship line shows that this field is either unindexed, unstored, global or a combination of the three.

                   This field should be a field of type number and it should indexed and cannot be a field with global storage specified.

              • 4. Re: List of active employee and total days of absent
                WeirdMan

                     I can't make IDKaryawan as indexed, because it is a calculation from table Form Transaksi. Here is a layout based on "Form Transaksi" table where user is inputting data for all transaction. This is a weekly wage, and name is inputted on Form Transaksi table.

                     As you can see on my previous post, relationship between Form Transaksi table and Transaksi Gajian table is only related on ID Transaksi field. Is there a workaround to make it work?

                • 5. Re: List of active employee and total days of absent
                  philmodjunk

                       It should not be a calculation field. It should be a number field that is assigned a a value at the time data is entered into that table. This will happen automatically when you enter data in the portal to Transaksi Gajian if you enable the "allow creation of records via this relationship" option.

                  • 6. Re: List of active employee and total days of absent
                    WeirdMan

                         I have thousands record already, can you help me to show the way to fill that number field for existing records? All I can think is auto enter feature with calculation, but it will only work when I create a new record, so it won't work for existing field.

                         EDIT: do I have to change all field to not have T shaped relationship? Or I just need to change Transaksi Gajian::IDKaryawan, and leave Master Karyawan u..::THRmulai and Master Karyawan u..::THRselesai as global storage?
                         THRmulai and THRselesai is just a reference record for range.

                          

                    • 7. Re: List of active employee and total days of absent
                      philmodjunk

                           Replace field contents can be used to do a batch update on all the records on your field. Be sure to save a back up copy of your file first, then be sure to select Show All records so that the update updates all the records in your table.

                           You should not modify the storage options on all your match fields to make them stored, indexed, non-global fields. You just need to understand how they work and when they will not work. Such fields function as match fields in a "one way" relationship. You can access other related records from the context of the table that defines the unstored or global field but not the reverse.

                      • 8. Re: List of active employee and total days of absent
                        WeirdMan

                             Thank you Phil.

                             But now the final problem is to filter only working employee on that layout. I never be able to filter using "perform find" command in script. The value to filter is in Keluar field, with value of  "1" (without quote) for resigned, and "" (without quote, empty) for working employee.

                             I use that script on a button to filter resigned employees, then go to absent list layout. Can you help me with that too?

                        • 9. Re: List of active employee and total days of absent
                          WeirdMan

                               bump for script to filter to omit when Master Karyawan = 1
                               I can search it manually, but I cannot apply it on script