Totaling a field with matching criteria from an UNRELATED table

So this is easy with a twist.

The easy part is simply to make a new table called Employee Dates where the Key field is the hyphenation of the EE# and date for every date say looking out for the next two weeks. 


Then you can summarize up the # of  O/T hours worked and the O/T hours Projected and have any formula you like on that record to decide which one to use for which dates.

For example for dates in the past ( before Today()  ) you would use actually and for the future you would use Projected and then for Today, you might use the higher of the two.

So easy.  Ah, but how to maintain that Employee Dates table infinitely into the future when Active Employees come and go. 

I will describe one method which can be used which involves some brute force and Automations.  If I were dong this for myself I would use a looping Automation which would be more elegant.  Contact me directly fi you want some help with that.  www.QuickBaseCoach.com

Let's say that you need to project up to 10 business days ahead at all times.

Make 10 fields named like [EE# - D0]


List("-", ToText([EE #], WeekDayAdd(Today(),0))

The formula for [EE# - D1]

would be 

List("-", ToText([EE #], WeekDayAdd(Today(),1))

So that would give you then next 10 business days ahead, ie skipping weekends).

Then make a saved table to table import, importing the EE# table into the Employee Dates table mapping the field [EE# - D0] into the Key field and merging on that [EE# - D0] field.

The copy that Import 9 more times and change the mapping to [EE# - D1] etc.

Lastly make One Automation to run these 10 saved table to table imports every day.