Link to home
Start Free TrialLog in
Avatar of cinamitton
cinamittonFlag for United States of America

asked on

Lotus agent to create a weekly timesheet for a list of users.

I am a Notes administrator that has been asked to create a project database to track total number of hours spent by different departments on each project.  One of the requested features is to have the app send a create a weekly time sheet for the list of users involved and email a link at the start of the week to each user for their time sheet.  I assume a scheduled agent would be the best way to accomplish this but don't know enough lotus script to write the agent.  Would be very grateful for any assistance in creating an agent to accomplish this task in the database.  The time sheet form is titled TimeEntry.  Also not sure what is the best way to store the list of users for the agent to reference.
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Why on paper? I once built a Notes database that was use by the entire group to track hours. Every user had access to the database to append their weekly documents.

There are several timesheet applications available on the internet, here's one that I suppose can be used fairly quickly:
http://www.openntf.org/projects/pmt.nsf/f56d5b5bd31be970862572a70053f5cd/3a183c9ad65d907b862571c8007afd22!OpenDocument
Avatar of cinamitton

ASKER

Sjef,

Poor wording on my part, nothing is being done on paper, the time sheets are notes docs that they wanted generated automatically and a doclink sent via email to each user.  Thanks for the database link, I have been looking it over and it is more detail than the requesting group wants.  The database they want is to track hours spent on projects.  The user will enter the total number of hours worked for the week then select projects from a list and enter hours spent that week on each project (see Attachment).  Users are not required to account for all hours worked and any hours not assigned to projects are calculated as "catch all process sustaining".
timeentry.jpg
My (also) bad, i could have guessed that you use a Notes form. Do you create weekly report documents manually, or is that done by each user individually? Just for my understanding...

I guess indeed that a scheduled agent could do the trick. Wouldn't it be enough though to send them a link to the database instead of a link to a specific document? Which would mean that you get an action in the db for the user to "create last week". Is it Notes or browser? Users have to add their projet names manually? Who has to check the inputted work sheets?

Do these questions make sense?
Sjef,

Notes based, Project docs are entered by management and user selects project# and description from picklist.  No approval required on work sheets.  

Following some of your questions maybe the best solution would be a scheduled agent that sends a reminder each week to each user to create and complete the current weeks work sheet
and let them click a button to create the worksheet?

Attached is the entry form for defining a project.  The project number is generated combining the BU + Dept# + 4digit sequential number.  Each project is placed into a classification.

The goal of the database is to track hours spent by classification for each business unit to determine if sufficient time is being spent innovating new products.

I have the forms ready but need help with some coding to automate some features that have been requested, such as a reminder to users to create each weeks work sheet or an agent that creates the worksheet for them and emails a reminder to fill it out.

The other area I need help with which I intend to create a new question for is some way to create a view that summarizes hours by project.  I have not been able to think of a way to display that information collected on the weekly worksheet.
project.jpg
ASKER CERTIFIED SOLUTION
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
A snag indeed, I have been struggling with how to separate the data so it can be used in a view sorted by project number.  So let me see if I understand your comments correctly.  It sounds like each entry on the time worksheet would need to be saved as a separate document with a link to the original worksheet.  Then the documents generated from each line of entry could be sorted and hours summed in a view filtered with only those entry documents.  And if the worksheet is allowed to have changes made to it then the corresponding entry documents would either need to be modified with the changes or deleted and replaced with new entry documents which would reflect the changes.

If I understand it right it sounds like a great way to solve the data access problem.  So I would need to create a form for the line item entry documents that will be associated with the parent worksheet document.  I understand the concept but don't have enough understanding of Lotus Script to code the functionality into the form.  I'm guessing that somewhere in the save process some code would be required to loop through and generate the line item documents and if modifications are made to edit or delete and recreate the line item documents.  A really slick solution but probably beyond my abilities.
If you can wait a few more days, I'll be back at home. I happen to have a similar application "on the shelf", but under a thick layer of dust. It was written some 15 years ago, but I suppose it still works. Give me some time to do some digging, and if you send me a mail, I can send it to you. You can find my mail address in my EE-profile.
Thanks Sjef,

That would be fantastic!  I'll send you an email you can send the app to when you find it.
Sorry, I've been too busy, couldn't find the time to respond correctly... :-$
No Problem,  I have been following your concept for getting the line item data from the Time Entry documents into separate documents to be able to track time by projects.  Since I am not a programmer I used the Data moving Animal from opennsf.org and hidden views sorted for each line of entry.  The data moving animal pulls the data from each line and creates a new document with using another form in the database.  I have a hidden field which creates a key by combining the username plus the week# plus the line number on the form so any changes are updated when the import is run automatically in the DMA.  Thanks for the concept of how the data needed to be handled.
Ah, it works?? Excellent!
Sjef,

Yes your direction to create a document for each line of entry was the solution and it is working beautifully.  I posted a new question in relation to the same database about working with a multivalue field.  The next issue to get working.

Thanks for your help!