5 Replies Latest reply on Jun 14, 2011 10:24 AM by philmodjunk

    Calculation based on an empty field



      Calculation based on an empty field


      I am doing a database for employees.  I have a record for each employee with different information in each layout.  I have one layout for 'sick days'.  I am using calculations based on the start/end of our work day and calculating the # of hours each person is out 'sick' and then calculating how many sick days they have remaining based on their allowed time away (10 days).

      At the moment I have it set up so that each person's record has a sick day layout with a repeating field for 'sick day date', 'time out' and 'hours out sick'.

      I then have a calculation for 'Sick Days Left' (the number of days they are allowed - the number of days they have taken).

      When I do my record, however, it automatically inserts that there is one day out sick when there is no information entered in the 'date' and 'time out' fields.  So I was thinking of doing a calculation in the 'sick days left' to say:

      if the field 'Sick Day Date' is empty then the 'Sick Days Left' should read "10", but, if there is something in "Sick Day Date" then the calculation should be "Sick Days Allowed - Sick Days Left".

      Is there an easier way of doing this or can someone provide me with the appropriate calculation to use?  

        • 1. Re: Calculation based on an empty field

          The "easier way" i would recommend would be to not use a repeating field for this. Instead, you would set up a related table. One approach might be to set up a table where you add one record for each working day for each employee--related by an Employee Id number to the original table of employees. You'd log the hours worked, hours out sick, on vacation... etc in this table and use calculaitons and summary fields to compute the total hours worked, on vacation, sick, etc to compare against the totals that they are allowed--values you'd store in the original employees table.

          • 2. Re: Calculation based on an empty field

            Ok.  So perhaps a silly question, but how do I set up a related table?  Also, not sure if this changes anything but I have version 6 of FileMaker Pro.

            • 3. Re: Calculation based on an empty field

              There are many, many differences between FileMaker 6 and Filemaker 10 or 11. I strongly recommend you include that info with your question if you return to this forum with new questions in the future. It'll really help make sure the suggestions you get are appropriate for your version of FileMaker.

              To create a new table in FileMaker 6, you'd create a new file and then use "Define Relationships" (I think that's what they called it in version 6) to define a relationship linking the two files. (FileMaker 6 was the last version released that limited you to one table for each file.) You may need to read up on how to do this in the help system.

              Once you've done that, you can access the data in the related table via a portal or even by adding fields from the related file/table directly to your layout. You can also navigate to the other file and edit data directly on a layout to your new table in that file. Buttons/scripts can be set up for that purpose.

              • 4. Re: Calculation based on an empty field

                Thank you, PhilModjunk.  I did mean to write that I was using version 6 in my original post but in my eagerness to get my problem fixed I forgot.  Thank you for the suggestion I will definitely indicate this next time.

                I have used the relational databases once before but I am just not sure what I would be putting in it for this database.  I want any my original database to always be the one in which information is entered, so what information am I referencing in the other relational database?

                • 5. Re: Calculation based on an empty field

                  See my response to the new thread where you have asked this question.

                  "I want any my original database to always be the one in which information is entered,"

                  While you can use a portal to the new file's table for that, it's also possible to use scripts/buttons to switch files and enter data directly into the new file with employee data from the original file displayed on the layout in the new file. In some cases, this can make for a better option than keeping all your data entry actions in the original file.