Work Order & Time Sheet System

[Sammy]

I have developed a fairly straightforward work order database for our company's maintenance department. Work Orders have a Time Spent field and a Time Estimated to Complete field ... these feed into a Percent complete calculation field. Useful for our management structure, etc.

I've created a second Time Sheet database to log time spent on work order codes. Time is recorded on paper; at the end of the day it is input into the database. 1 Record = 1 Day's times.

How do I add up the total time for the day, and add it to the existing time already recorded in the Work Order database? ie. something like Sum(Exisiting+New) . Here's my problem. Different day's time logs will/may have the same work order code with the total times. Also, instead of the work order number, some of the time entries will be arbitrary (ie. Mike-Pumps or Fixed Chiller). on a regular basis, we will go back and write a work order for anything like that and then assign it a code, but there is a possibility for non-numeric data in the time entries.

One more question: is there a way to force input in .250 increments? To reject anything that's not an integer or .250, .500, .750 ? I'm guessing it's tough, but anything thoughts?

I'm trying to keep the system from being obtuse. I'm not sure I'm succeeding. ;-) Any help would be appreciated!

I've attached the time sheets file so you can see what I mean. Go to the Layout Data Entry (Click on the Create New Record script)

ot_timesheets.txt

[QuinTech]

Hi Sammy. For the second question (i always answer the easier question first to make you think i know what i'm talking about --validate the field with Int ( Field * 4 ) = Field * 4. For any value that does not correspond to your options, Field * 4 = [some non-integer] and thus the validation fails.

For the first question--when you say "1 Record = 1 Day's times", do you mean there are separate fields storing different times? Or is there 1 field that represents the total time for that work order for that day? If it's the latter, your question is fairly straightforward--Create a relationship to the work order number called WorkOrderRel. Now the calculation Sum (WorkOrderRel::TOTAL ) will give you the total time spent on that work order. As for entries with false work order numbers, there's no way to account for them, but when you change that to the appropriate work order number, the total will be included in your sum for the work order.

Does this help?

Jerry

[Sammy]

Okay, easy answer first. I messed with it a little bit, and it worked like a charm! Thanks much for your help! .. it's always the seemingly obvious that I miss. :-)

I spent all day thinking about this problem yesterday; I realized I'm going about it all the wrong way. I'm making it frightfully complex, but I'm still not sure how to simplify things.

Forget about my initial Time sheet database. Let's try a different tack:

How do I write a relationship in my Work Order file that is Sum(Hours) for only matching serial numbers in the timesheet file?

[QuinTech]

Well, you don't write a relationship that is Sum(Hours), you write a calculation. (Not trying to be pedantic, it's just important to keep terms straight.) I think if you follow my suggestion above, you'll get what you're looking for. To demonstrate this in action, take a look at the attached file and especially look at the definition of the relationship and the calculated field.

J

Sammy.zip

[Sammy]

Works like a charm. Turns out I guess I was thinking about it too complicatedly (word?). Anyway, another question for you:

How do I dump the different personnel that have worked on a specific work order into one field delinated by comma's or maybe a repeating field? I can get *one* with a relationship, but I'd like to get all, say, 3 people that have worked on a work order. Bear in mind that they will have duplicates ... ie same personnel, same work order number, different entry.

[RalphL]

If you have the relationship use a portal. Another method would be to use the SumText function in the Troi Text Plug-in http://www.troi.com

[Sammy]

Okay, the portal works fine, but I get duplicate names. Is there any way to filter out duplicates and only display one each of the unique names?