How do I avoid co-dependent fields?

[eljefejb]

This is probably an easy design concept, but my dense brain isn't seeing it.

 

I'm working on a timecard system, and I'm running into a problem where I have co-dependent fields.  In my hours table, I have a field which calculates the amount of VTO (Variable Time Off - like vacation time) that is used if the total hours entered on a workday is less than 8.  However, the system can only fill the deficit in hours (8 - hours worked) if you have enough VTO available, so the calculation first checks the value of the VTO_Available field from the related VTO_Month table.  It looks like this:

Let (
[
  Worked = TotalHours;
  Deficit = 8 - TotalHours
];

Case (
  Worked >= 8; 0;
  //No VTO needed to fill in for the current date

  Deficit > 0 and (VTO_Month::VTO_Available >= Defict); Deficit;
  //If there is enough available VTO to cover the time deficit, return the amount of the deficit

  Deficit > 0 and (VTO_Month::VTO_Available < Deficit); VTO_Month::VTO_Available;
  //If some VTO is available, but not enough to cover the deficit, use up the available amount (take to zero)

  0
  //Otherwise, you can't use any VTO if you don't have any!
)

) //End of Let

In the VTO_Month table, the VTO_Available field is calculated as (VTO_Start + VTO_Earned) - VTO_Used.  VTO_Used is a calculated field defined as Sum (Hours::VTO_Today).

 

Obviously, I can't have two fields whose values depend on each other, but I can't think of how to set up the design to avoid this problem.  I seek your guidance!  Help me get to that moment where I smack my forehead and say, "Duh!  I should have been able to figure that out!" 

[comment]

It seems to me that VTO is an attribute of the record in the VTO_Month table - not of any individual timecard record.  What should happen if a previously entered timecard is modified in a way that increases the VTO used? IOW, you are describing a dependency between sibling records - which is never a good thing to have.

[eljefejb]

It's a little of both.  You start a month with X amount of VTO (the amount you had at the end of the last month, carried over) and you accrue new VTO based on hours worked in the current month.  Then the system tries to fill in any time deficit on a daily basis, if you have enough VTO available.  The goal is to show a running tab of available VTO, based on the hours entered thus far.

 

Maybe I should make a script that's triggered OnRecordModify, that recalculates and sets the VTO_Available field in the month table each time a change is made?  That should take away the cyclical calculation - only the daily VTO would be a calc field, then.

 

As far as previously entered timecards go, the system will only let you enter hours within the current month.  You can make as many changes as you like to the records of the current month's timecard, until it is locked for payroll processing by the administrator at the end of the month.

[comment]

The goal is to show a running tab of available VTO, based on the hours entered thus far.

 

Perhaps this goal can be better achieved with the help of a summary field (running total) - although that will only work within a currently displayed (and correctly sorted) set.

 

Note, however, that conceptually such a display is meaningless. What really matters here is the monthly totals - not how they were accrued over time (I think).

 

 

Maybe I should make a script that's triggered OnRecordModify,

 

IMHO, that's a complexity that you should try to avoid. There are other ways for a timecard to know the amount of VTO used up by previous cards in the same month (e.g. by a self-join relationship). But again, is it really necessary?

[Wim Decorte]

Timecards really should be tightly scripted process with no reliance on triggers.  Once submitted a time card entry should not be modified but you can allow for a modification to be entered.  The benefit here is that you can keep track of those changes and figure out who the biggest offenders are.

Since the whole process is tightly scripted it is easy to validate entries, allow or disallow them and calculate new totals.

[eljefejb]

I would that it were so!  Unfortunately, TPTB want records to be automatically created and populated with a user's default project when they log in for the first time each month.  This would be the case for most users, who only perform the same job function day in and day out.  For other users, such as the engineering staff, who are often working on multiple projects, records of time spent on each project should be created manually.  So, for the most part, users will be modifying existing records by decreasing the default 8 hours on a certain day (to indicate VTO taken) or to divide their time between their default project and another.  In the latter case, they will have to enter a new hours worked record for the same day, selecting a different project.

 

I have created a separate table for VTO hours, related to the hours worked table by Employee ID and date.  This helps me get totals for the number of hours worked per day and the number of hours of VTO recorded for each date.  I'm still stuck with the design problem of having two fields that rely on each other.  Making one of the fields a number (instead of a calculation) and setting the value via script is the only thing I can think of to do.  The hours entry screen is a list view.  I could put a "Save" button next to each record (and turn off automatic record saving) to run the script which does the field update, but what would be the difference between that and using the OnRecordModify script trigger?

 

I appreciate your advice!  Thanks for devoting so much of your time to helping people with questions! 

[comment]

I have created a separate table for VTO hours,

 

I am not sure how that helps. IMHO, you should seek to reduce the number of moving parts, not increase them.

 

 

I'm still stuck with the design problem of having two fields that rely on each other.

 

I have already suggested two possible alternatives, but you didn't respond to any of them.

[eljefejb]

 

I am not sure how that helps. IMHO, you should seek to reduce the number of moving parts, not increase them.

 

You're right, I don't think I need that.  I was thinking back to what I had read a long time ago in a FileMaker training book, about having separate tables for each type of "thing" that is being tracked in the database.  VTO isn't really its own entity, though, it's a property of hours worked. 

 

 

I have already suggested two possible alternatives, but you didn't respond to any of them.

 

Sorry, I thought I had!  I am now using self-joins to pull together the data I need.

 

I think I worked through the cyclical calculation matter.  Instead, I am displaying total VTO available to use (carry-over + newly-earned), the hours entered for the month, the deficit (total hours in month - hours entered), and the amount of VTO one will have at the end of the month (available - deficit).  Hopefully, this will work for TPTB. 

 

Thanks again for the guidance.