7 Replies Latest reply on Jun 1, 2009 9:26 AM by typeleven

Omit records in a portal based on date range

typeleven May 30, 2009 6:11 PM

Title

Post

Im making a time clock for my employees. I have a record for each employee and a portal that lists all the shifts that relate to that employee. So it shows each shift date, the clock in time, the clock out time and to total time for that shift. above that portal I have the total shift time for all the records shown in the portal. I want to filter that portal so It only shows the records relating to that employee for the current week, id also like to have a start date and end date drop down menu so that I can see the shifts between those two dates.

any ideas?

1. Re: Omit records in a portal based on date range

TKnTexas May 30, 2009 6:17 PM (in response to typeleven)

I would think you could have relationship for EmpNum+WeekNum. You could trigger in a number of ways. You could have one record with a drop down field for WeekNum and another field for Employee. That would change the contents of the portal.

You could have a record per week with a single field for ALL employees or for a single employee.
Like Show 0 Likes(0)

Actions
2. Re: Omit records in a portal based on date range

LaRetta_1 May 31, 2009 5:44 AM (in response to typeleven)

I would not use a week number global for filtering the portal by week. You would then also need a calculation in the Schedules indicating the week number for every Schedule date but also, as your Schedules records grew and crossed more than one year, filtering to the week number would group two years for an employee in the same week number.

I would use global dates for the filtering. gStart and gEnd in Employee file (both global dates). Then your relationship would be:

Employees::EmployeeID = Schedules::EmployeeID
AND
Employees::gStart <= Schedules::ScheduleDate
AND
Employees::gEnd >= Schedules::ScheduleDate

You can have the gEnd date pre-populate with the ending Saturday (depending upon the date started in gStart) but the User could still manually change gStart and also the gEnd to whatever date they wish (in case they wish to view a larger range such as a full month or year). Auto-enter calculation on gEnd (to pre-populate) would be: gStart - DayOfWeek ( gStart ) +7 (and be sure to uncheck 'Do Not Replace Existing Value...' right below the Calculated Value Specify button.

UPDATE: Your gStart could be a calculation of Get ( CurrentDate ) but you wouldn't ever be able to filter the portal by any other date. As for using drop-downs, of course you could use them. You could also attach a drop-down calendar on the global dates.
Like Show 0 Likes(0)

Actions
3. Re: Omit records in a portal based on date range

typeleven May 31, 2009 9:29 AM (in response to typeleven)

Thanks! that worked great!

This now leads me to something else I want to do now that I can do this.

I want to make a script that sets the value of a field in all the records displayed in the protal. This will be used as a button that marks all the shifts in that date range as paid.
Like Show 0 Likes(0)

Actions
4. Re: Omit records in a portal based on date range

LaRetta_1 May 31, 2009 10:08 AM (in response to typeleven)

Well there are several ways to handle this. But instead of flagging a related Schedule as Paid, you might consider letting the act of Paying itself flag them, ie, have another table of payments (where you create a Payment record instead of marking the Schedule record). When there is a match as:

Schedules::ScheduleDate = Payments::ScheduleDate
AND
Schedules::EmployeeID = Payments::EmployeeID

... then the Scheduled record is automatically considered paid via an unstored calculation indicating that there is a matching payment record in Payments. If you get into record marking (flagging a record as paid with a 1) then you will not know when it was paid. You also risk the possibility that one of the Schedule records you are attempting to flag as 1 (for paid) might currently be modified by another user. In that instance, it will NOT set the field as paid because it will be record locked. It is best to have other relationships (such as a payment action) and the existance of a relationship indicate these types of status' rather than flagging records.

I would think it also important to track what day a Schedule (for an Employee) was paid (and maybe even check number etc). By using a Payments table, you can handle the history of the payments by Employee and an added benefit is the ability to add adjustments, ie, you forgot an Employee should have gotten a raise for that Schedule Date or it should have been paid at holiday time etc. By using a Payments table, your Employees will be happy that you are tracking everything properly and you won't have to use a script at all ... except to create your Payments records when you pay them.
Like Show 0 Likes(0)

Actions
5. Re: Omit records in a portal based on date range

typeleven May 31, 2009 10:39 AM (in response to typeleven)

Right now this database is being used as an inbetween for my employees and my accountant. The acountant is using quickbooks for payroll so all Im trying to do is get the shift hours to him in real time. I want to be able to lock those shift fields once he has marked them paid. So what Id like to do is let him click a button and have the current date entered into a "Paid" Field in the list in the portal. That way no changes are made once the check is written. Keeping track of all the pay period details is being done by quickbooks and is beyond the scope of what I want to do here. But that was a good idea and I will probobly move in that direction if I want to stop using quickbooks.
Like Show 0 Likes(0)

Actions
6. Re: Omit records in a portal based on date range

LaRetta_1 May 31, 2009 2:17 PM (in response to typeleven)


"I want to make a script that sets the value of a field in all the records displayed in the portal."

Potential issues:

1) The Accountant filters the portal for an Employee to the range he wants to pay (a full week). He must be sure that there are not Paid records in this set to begin with. If there are, he could pay it twice and overwrite the DatePaid.

2) You must be sure that nobody can add a Schedule record to any date within that range when he is working. If they do, and he makes the check for $1,500 (paying someone for four days and that Employee forgot to enter Friday) and, after the Account cuts the check, Friday suddenly appears in that portal and changes the sum to $1,875 then you click the button to PAY all the records, Friday will be flagged as paid even though it was not. Any time the human-factor comes into a mix, there is more chance of error.

To address the possibilities of 1 & 2 , I suggest that the script print out a list of records that it successfully flagged as Paid AFTER it successfully flags them as paid. The printout can have the global fields in the header showing the range for the payment; the body can have the Employee ID and Schedule Date ... THEN the Accountant makes out the checks from that printed list. Notice that script will omit a record if it is listed as already paid. Such a script could then look like this (when fired from the Employee record after date globals are populated):

If [ not Count ( Schedules:: EmployeeID ) ]
Show Custom Dialog [ No shifts to pay ]
Else
Go To Related Record [ Show Only Related Records; using Schedules; Schedules layout ]
Set Variable [ $date ; Get ( CurrentDate ) ]
Loop
If [ Schedules:: DatePaid ]
Omit Record
Else
Set Field [ Schedules:: DatePaid ; $date ]
Go To Record/Request/Page [ Next ; Exit After Last ]
End If
End Loop
Go To Layout [ Schedule Printout (Schedules) ]
Print []
Go To Layout [ Original Layout ]
End If

Keep in mind that you can also pay ALL your employees by performing a search for a time-period, switching to your Schedules, flagging as paid then producing a sub-summary report with the check totals and detail for each employee.

But I STILL believe that using a Paid table, even if it only holds the ScheduleID and DatePaid is the way to go. How you handle it behind the scenes doesn't matter. When the Accountant 'clicks the button', a record can be made in the paid table ... much safer overall since the above process still won't fully protect from record locking ... it will only not flag the Schedule with the DatePaid if the schedule record is currently possessed. You can use Allow Creation of Related and simply set the DatePaid in the Payments table.

But now you have options. :smileyhappy:
Like Show 0 Likes(0)

Actions
7. Re: Omit records in a portal based on date range

typeleven Jun 1, 2009 9:26 AM (in response to typeleven)

Thank you for showing me that. I will probably use that in something else. Here’s what I ended up doing.

When I enter a new record in the portal it wants to automatically enter in the date for the end of the pay period even if I have it set to auto fill with today’s date. Any idea why it does that?
Like Show 0 Likes(0)

Actions

Go to original post