tracking hours used/hours remaining

[Miko]

I am creating a time tracking database for our macintosh consulting business. the database contains an area for techs to put in their hours spent, client visited, day visited and details of work performed. it also contains an area for an account person to put in contracts purchased, date of purchase, amount of hours purchased.

what i need to do is to track, probably via a portal (that is how i approached it thus far) the date, how many hours used (there can be several entries for a particular date, reflecting different techs at one place on the same day. so these do not need to be summarized in any way) the amount of hours remaining. so for example, if my client purchased a 100 hour contract yesterday and i did 5 hours of work today, it would report 95 hours left. if i did 2 more hours today it would report 93 hours left as of today's date.

have this working fine. the trouble is, sometimes clients run out of hours and need to buy new contracts. i have the database set to display red when that happens, however, when you add a new contract, the total number of hours gets added to the top portal row as if they started out that way. what i need it to do is to only add tne new hours after the date specified in the contract start date window.

right now it looks like this:

If ( Clients::Contract Type = "BH" ; Contracts::Total Contract Hours - Total Hours ; "NA" )

where BH is block hour (some clients pay as they go and for this i do not need it to subtract anything)and contracts::total contract hours is a summary field that adds all contract hours when sorted by client. how can i have this field only add contract hours on or after the contract start date to have an accurate picture of the client's activity?

last but not least, all summary information is in the sro table with the exception of the total contract hours which is in the contract table. the portal is displayed in the contract layout.

thanks,

Limore

[Vaughan]

I'd say you need two tables: one for "hours available" the other for "hours used".

When a client buys some time, a record with the number of hours gets made in the "hours available" table. When work is done, a record is made in the "hours used" table.

Calcuations add the related records to show the number of hours available, the hours of work done, and the balance.

[Miko]

yes but i believe that this would still put me in the same position which is needing to know when the amount gets added. for example. lets say the client has 10 hours as of last week.

on monday i work 5 hours, on tuesday i work 6 hours and on wednesday the client buys another 10 hours and i work another 2. here is how i would like it to look:

date hours used hours remaining

8/20/07 5 5

8/21/07 6 -1

8/22/07 2 7 (here is where i show the new 10 hours cause it was bought on or after that date)

what i don't want is for the first line of the portal to show 15 hours remaining when i add the 10 hour contract on the 22nd. and this is where my problem lies. it all checks out for me except for that.

is there a specific calc that you have in mind that would achieve this?

thanks a bunch,

Limore

[Vaughan]

I'm not sure what you want.

If you wanted to display a customers position at a particular date, this could be done by including the date in the hours tabloes, and creating relationships that include the date to filter the relationship.

I've attached a file that demonstrates this.

TimeSheets.fp7.zip

[Miko]

i need an ongoing balance. but what i need it to do in the hours remaining column is to only show the hours purchased to date. in other words, the client hunter bought a 100 hour contract on 1/15/07 and another 100 hours on 8/1/07. i dont want the hours remaining column to reflect the 100 hours purchased on 8/1/07 until sro is entered on or after that date. right now the top portal row is debitting hours out of a total of 200 hours. from 1/15-7/30 it should show the remaining hours subtracted from the original 100 hour contract. then all time entered on or after the next contract was purchased, it should debit the hours from what remains from the first contract plus the second contract. is that more clear? if it worked the way i wanted it to, it would show 85 hours remaining on the portal row dated 7/23/07 and 184 hour remaining on the portal row dated 8/2/07 (which would reflect the newly purchased contract correctly, only after it was purchased)

if you think about it this way. client purchases a 100 hour block in august, your system really shouldn't consider those hours available in january because they weren't purchased in january.

i am attaching a file for you to look at. i think it illustrates what i am trying to do a little bit better.

thanks for your help,

Limore

Templeton_SRO.fp7.zip

[Vaughan]

So you are saying that each block of bought hours has a time limit or a set period in which it can be used?

Something with your data file: don't use names as relationship match keys. Use something that is unique and invariant: Auto-entered serial numbers are simple and sufficient. Users need never see them.

[Miko]

no not quite a time limit but a set start time that it can be used. a contract purchased on 8/1/07 can't be used prior to that date and therefor should not show up as being available for use prior to the contract start date of 8/1/07.

can you give me an example of a relationship match key by name? and why not? just curious.

Limore

[Vaughan]

"no not quite a time limit but a set start time that it can be used"

So, when a client's support runs out the work has to stop? No problem, the sample file I sent will do that.

The sample file will also only show the hours purchased to date: that's what the "Date" relationships are all about.

[Miko]

ok. so i finally looked more closely at the file that you submitted. i was only looking at the portals that show all the hours and all the time purchased (my fault) and it is perfect. i worked it into my solution and my business partner who is responsible for contract tracking really liked it.

so thank you very much for all of your help.

Limore