4 Replies Latest reply on Jul 17, 2014 4:20 PM by philmodjunk

    Hey brothers I just started building this data base like 3 months ago and I have never use filmmaker...

    MUSTAF

      Post

           Hey brothers I just started building this data base like 3 months ago and I have never use filmmaker pro 12 before or build a database in mu life, but I have picked it up pretty well. My only problem is that I am stuck on the conditional formatting section: I have phase1, 2 & 3 and each phase is 90days after the end day and I have that populating from the date the phase one start date is entered. But their vacation time don’t start until phase 2 that's 90days after phase one and the same for the rest of the phase. In phase 2 they accrue 4 vacation days, 2 for phase 3 and 2 for phase 2. i want phase 2vacation accrue to be the only one green until the next phase and if the client don’t use the vacation day in a certain phase, I want it to auto add to the next phase day and the next phase to turn green as soon as the current phase is past 90days. And I want only the active vacation hours to show in total hours remaining and vacation use to just keep track of the total vacation used. And I want all those changes to take place as i input information in the used vacation time portal and I want  this to happen by phases. But my phase field is a drop down list.. I am stuck bro ...if there's any way you can help me with the calculation  I will really appreciate your help.Thanks’ in the advance.

      Screen_Shot_2014-07-15_at_1.10.57_PM.png

        • 1. Re: Hey brothers I just started building this data base like 3 months ago and I have never use filmmaker...
          philmodjunk

               A picture of your layout, while a good start, doesn't tell anyone reading your post how you have designed the tables and relationships shown.

               I see what appear to be 4 portals shown, but have no idea how they are related to your layout's table. I am guessing that the layout's table is a table of employees with one record for each employee?

               Do you have this relationship?

               Employees----<UsedVacationTime

               Employees::__pkEmployeeID = UsedVacationTime::_fkEmployeeID

               If an employee takes two vacation days for 7/18/2014 to 7/19/2014, will that be recorded as a single record with a Date of 7/18/2014 and a 2 in Vacation used? Or would it be a number of hours used such as 16? (8 hours for each accrued day...)

               If so, Sum ( UsedVacationTIme::VacationUsed ) will compute the total vacation time used.

               

                    In phase 2 they accrue 4 vacation days, 2 for phase 3 and 2 for phase 2.

               I think that you actually meant: In phase 2 they accrue 4 vacation days, 2 for phase 3 and 2 for phase 4. Is this days of vacation per year? So that Phase 4 employees accrue 8 days of vacation each year?

                

                

          • 2. Re: Hey brothers I just started building this data base like 3 months ago and I have never use filmmaker...
            MUSTAF

                 The table is related to the client table.which is the Parent table.  If the Employee take two vacation day, it will be recorded as vacation days used, not as 16 hours. and the days of vacation is not per years but every 90days. And the Employee accrue 4 days in phase , 2 vacation days in phase 2 and 2 vacation days in Phase. total of 8 vacation day in all the years. Vacation days is available when the employee start Phase2.  I want Phase 3 to say blank until employee reaches Phase 3, once the employee phase 3 start that will be 90days after phase 2, then i will like Phase3 to activate and if the employee did not use there vacation in phase2 or whatever remains from Phase 2 should automatically add to Phase3 and phase 4 will shows blank and Phase 3 will be inactive because the employee is no longer in that phase.  

                 All table are related to one tabele like in the screen shot and yes it is one record of each employee

            • 3. Re: Hey brothers I just started building this data base like 3 months ago and I have never use filmmaker...
              philmodjunk

                   The table is related to the client table.which is the Parent table.

                   Does that mean that "Client" is selected in Show Records From for the layout shown in your screen shot?

                   

                        and the days of vacation is not per years but every 90days.

                   I understand that each Phase is 90 days, but you only mention 4 phases = 360 days. What happens after Phase 4 is reached by an employee? Is that intended to = a year and then they start over at phase 1? (Should have done this math earlier...)

                   

                        And the Employee accrue 4 days in phase , 2 vacation days in phase 2 and 2 vacation days in Phase. total of 8 vacation day in all the year

                   Is a direct quote from your last post. How's that again? wink

                   I think you mean entering Phase 2 adds 4 days, entering Phases, 3 and 4 add 2 more days each producing 4 + 2 + 2 = 8 days. Is that what you meant?

                   Seems like your basic calculation for each phases vacation day accrual would be:

                   Phase 2: If ( Get ( CurrentDate ) > Phase1Enddate ; 4 ) // accrue 4 days once Phase 2 has been entered

                   Phase 3: If ( Get ( CurrentDate ) > Phase2Enddate ; 2 ) //accrue 2 more days once Phase 3 has been entered.

                   Phase 4: If ( Get ( CurrentDate ) > Phase3Enddate ; 2 ) //accrue 2 more days once Phase 4 has been entered.

                   But once an employee enters (I assume) Phase 1 for the next year of service, you won't want to lose the unused accrued days, I would imagine. Correct?

                   I suggest that you set up a "ledger" table for managing both the accrual an usage of vacation days:

                   The fields in that table would be:

                   _fkEmployeeID  (Links to client table by employee identifier)
                   Date                 (date vacation days have accrued or been consumed)
                   vacation used    (Number of days used)
                   vacation accrued (Number of days accrued, added by script when the next Phase is entered.
                   cVacBal           (calculation field; Vacation Accrued - Vacation Used)
                   sUnusedVacationDays ( summary; total of cVacBal )

                   This can be set up in your portal much like a check book register but with accrued days in place of deposits and days used in place of withdrawals. And this will keep the accrued days so that the first day of entering a new Phase 1 doesn't automatically drop the user back to 4 days vacation.

              • 4. Re: Hey brothers I just started building this data base like 3 months ago and I have never use filmmaker...
                philmodjunk

                                    That makes for a very different situation. Then the calculations that I specified would seem to be what you need here.