Sum hours by pay period

So far I have a table employees record [Staff Member](linked table) [Date] ...[Number of Hours] and I created a Payroll Calendar Table with [Pay Period] [Beginning Date] [Ending Date] [Pay Date](Which is always the ending date). I added a look up field on the time entry table that would allow the user to choose their pay period but I want it to assign the pay period. I tried to create a 3rd table as Damon described "Payroll" but do not understand how he got the automations/actions to match the Date in the time entry table with the Pay Period from another table to copy the 2 records into one on the Payroll table.

I am happy to try it another way. I started with trying to build it how Brian and you were working out at the beginning of this thread but since we don't use a traditional 2 week pay period I was struggling and tried to see what Damon did.

I added a field that identifies the pay periods as you described. I would need help creating a formula. I could imagine a really long formula that could result in those numbers but not one that will pull the related pay period. 

Did you set the Key field of the parent pay period table to be the YYYYMM-1/2 table?

Then I will help you with the formula and the relationship.

Hi Michelle, When I return from traveling can provide more details (by Close of Business tomorrow) on how my time card app automatically assigns the correct pay period (based on date of time worked) from the payroll calendar.

Damon

I set the key field to the new field as described :)

OK, here is a formula. 
I like Automations, but I prefer to avoid them if a simple formula will work.

Here is the formula to create on the detail time records and then the relationship to the time period table would be based on this field.



// first we gather up the ingredients for our final formula

var text YYYY = ToText(Year([Date]));
var text MM = PadLeft(ToText(Month([Date])),2,"0");
var number DayOfMonth = Day([Date]);

var text FirstSecond = If(
$DayOfMonth <=15,"1",
$DayOfMonth >=16,"2");

// now we assemble the ingredients

$YYYY & $MM & "-" & $FirstSecond

Ok I added the formula and that generates the correct pay period id. Still unsure of how to get that to relate to the table with the YYYMM1/2 pay periods in order to summarize hours worked by employee each pay period.

You would make a relationship where 1 Pay Period has Many Time entries, and on the right side of the relationship instead of letting Quick Base create a new field, you would select this YYYYMM1/2 field.

Then on the pay period record Form you would include the report link field which the Relationship created for you.

Then create a an unfiltered summary report of all hours worked by employee and use that report for the form to use.

The result will be that only the Time entries for that Pay Period will be on the embedded report.

once that is working, we can fancy up that Summary report by having the employees listed own the page as would be normal, but if you like we can show th date across the top in columns, so it would be easy to see any gaps where no time was reported for a particular EE for particular day(s). 

In fact you could put both the basic summary report on the form and then have that report link on the form again with a different version of the report - maybe one that had a summary with the columns I described, and then maybe a third version which as the detail entries.

The alternate reports could be put in tabs so that the form loads quickly as embedded reports in Tabs do not hold up the initial display of the form.

That works, I was able to add the dates across the top also. I have that one displayed on the form and know how to add the others if it will be helpful. Thank you very much for walking me through!