Totaling a field with matching criteria from an UNRELATED table

Question

Background:An [EMPLOYEE] can have many [OVERTIME] records (which are added by a supervisor after the overtime has been accrued).An [EMPLOYEE] can have many [OT PROJECTION] records (which are projections of potential overtime added by the supervisor the week prior).It is possible to have multiple overtime records assigned to a particular employee and date.It is possible to have multiple overtime projection records assigned to a particular employee and date.Typically, our client wants us to provide a projection of all employee potential overtime for the upcoming week ahead of time. Therefore, the supervisor will create many overtime projection records.The actual overtime records can't be created until the overtime is actually accrued, because only then would the supervisor know what to enter.I need a way for [OVERTIME] records to display the correllating SUM of [OT PROJECTION] hours for that particular employee and date (so we can easily determine if an employee's actual overtime exceeded the amount of overtime that was projected earlier).I can't seem to find a way to do this easily. Relationships between the [OVERTIME] and [OT PROJECTION] tables doesn't seem to work because there is no set order in which the records will be created.It's possible to have overtime records entered prior to any related overtime projection records created as well.TARGETA supervisor can pull up the report of all overtime for a particular week.He'll see that employee "x" accrued overtime on various dates, and can also see the total amount of projected overtime on that date for that employee as well.

adamkeever1 · Answer

You need a unique key to relate the Ot and OT Projection records. Something like Employee # &amp; OT Date.

quickbasecoachd · Answer

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.&nbsp;Then you can summarize up the # of&nbsp; 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()&nbsp; ) 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.&nbsp; Ah, but how to maintain that Employee Dates table infinitely into the future when Active Employees come and go.&nbsp;I will describe one method which can be used which involves some brute force and Automations.&nbsp; If I were dong this for myself I would use a looping Automation which would be more elegant.&nbsp; Contact me directly fi you want some help with that.&nbsp; www.QuickBaseCoach.comLet'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&nbsp;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.

jasonbowen · Answer

I actually tried a similar method, but for an unknown reason, it didn't work.

I created a formula field in the OT table, concatenating date serial and employee id. The field isn't unique because it is possible for an employee to have more than one OT entry on a given day (there are reasons).

I created a similar field in the OT Projection table, also not unique for same reasons.

I then created a summary field in the OT table, which would attempt to total the duration of all OT Projection records in which the two formula fields matched.

It didn't work. I suppose because the only way I could use a summary field was to create a relationship between OT and Projections, and they aren't really related directly to each other.

quickbasecoachd · Answer

... so do you plan to try my suggested method?

adamkeever1 · Answer

This is more simple than I originally thought after I reread your post.

Use two relationships:

Add a summary field for the hours in each OT and OT Projection relationship to the Employee:

Then you can add a record for an employee for a specific date and add multiple OT hours and OT Projection hours and use a formula numeric field to calculate the difference between the summary fields:

formula field:

Then you can see a list of employees and dates and you can set up filters for specific dates, departments, etc...

Forum Discussion

Totaling a field with matching criteria from an UNRELATED table

Related Content

Running Total

Get data from an unrelated table

Totaling Child Records Live on Parent Form

Match date to End of Month (EOM) date

Total Formula with 2 IF statements