You Guys were SO right..Now I'm stuck again!

[Baylah]

Hellow again...

Thanks to everyone who helped me solve my last problem! However, since I solved it so well, now my boss needs to know one more piece of the puzzle that was eluded to by several of you as you helped me solve the last problem!

In the table below my boss now wants to know if we can capture the "dead time" between the machine start and stops.

Machine..Date.....Start.....End

132b.....2/1/07...11:32.....[color:red]13:53

132b.....2/1/07...[color:red]14:24.....15:17

142a.....2/1/07...10:45.....[color:red]11:16

142a.....2/1/07...[color:red]11:23.....13:54

153b.....2/1/07...09:08.....[color:red]10:11

153b.....2/1/07...[color:red]11:19.....12:39

Is there a way to capture this data(the amount of time between red numbers) at the line level and then report it is a cumulative summary?

Once again I'm stumped....But I am sure there is a way that I don't know yet.

Thanks,

Steve

Edited March 19, 2007 by Guest

[comment]

Instead of capturing the 'dead' time, summarize the 'live' time (as discussed in one of the iterations of the previous thread), and subtract it from the total.

[Baylah]

Instead of capturing the 'dead' time, summarize the 'live' time (as discussed in one of the iterations of the previous thread), and subtract it from the total.

Thanks for the inspired insight...you got me most of the way there.

This absolutely works for getting me an aggregate number. However, it does not afford me the ability to see the individual times between jobs. There are several factors that can contribute to the amount of time between jobs.

If there is a way for me to see the individual times between jobs(RECORDS) then I can build a relationship between cause and effect.

Is there a way to capture the amount of time between jobs (red numbers) in the table below?

Machine..Date.....Start.....End

132b.....2/1/07...11:32.....[color:red]13:53

132b.....2/1/07...[color:red]14:24.....15:17

142a.....2/1/07...10:45.....[color:red]11:16

142a.....2/1/07...[color:red]11:23.....13:54

153b.....2/1/07...09:08.....[color:red]10:11

153b.....2/1/07...[color:red]11:19.....12:39

Thanks again, sorry for being so high maintenace these past few days.

Steve

Edited March 19, 2007 by Guest

[comment]

It should be possible, but it will be more difficult, since (to paraphrase Asimov) you're trying "to analyze Nothing".

Now the question is this: suppose there are 3 records, thus 2 gaps. Where (in which record) should each gap be calculated? In the record before the gap, or in the record following the gap?

[LaRetta]

What is our base? We need to know what a day represents. If it is based upon a relationship filtering to one day then Max ( EndTime ) - Min ( StartTime ) should do it. Then when we subtract Sum ( Machines::cElapsed ), [color:green]we have the usage which we can represent as a percent. Or is a day represented by 8 hours, subtracting from that instead?

UPDATE: Oh. I forgot the last issue here. Then one would need to divide total down time for the day by the number of entries and display only that percentage of 'down time' per line. It wouldn't be totally accurate unless you used GetNThRecord() and looked at the next entry's start time. It feels a bit wonky. Is 'close' okay?

[Baylah]

LaRetta Said [color:blue]<

Grretings,

Again, thanks for all of the help and insight.

Attached is an FM7, DB (all I have at work, at home I use 8.5.) This DB is ONLY raw data imported into FMP and displayed in TABLE View.

I have moved the 6 most critical fields (for this discussion) to the left most colums in the table and added one field (TimebetweenJobsinSecs) that is where I would like the "new information" to display.

The first record in each batch of machines would not get populated (or be populted with a zero as a default)as the time between jobs would always be displayed on the next record down. I placed 20 examples on the first OPERATOR/MACHINE. Times are rounded for ease of discussion.

This is one days worth of data only, 3,639 records.

For the purposes of what I am trying to accomplish I would not be trying cross over a day as each record has a start date and end date that are on the same calander day and never crosses midnight.

What I am looking for is the amount of time between the end of one job and the start of the next.

I know I am asking for a lot of help here, but I do not know a better resource to turn to so I thought I would try here.

Any help is very much apprecitated and is always paid forward whenever I can help another user.

Thanks,

Steve

Production.zip

Edited March 20, 2007 by Guest

[comment]

Here's one possible way. Since this needs to work in version 7, you cannot use GetNthRecord(), so it must be thru a relationship. This ignores the found set, but it doesn't look like you'd want to look at only SOME records of machine x on day y anyway.

I don't know about speed using an unstored calculation like this, but if it gets too sluggish, you could set the field by script or use a lookup.

ProductionRaw.fp7.zip

[Baylah]

I never cease to be amazed at how amazing this community is. This is hands down the best resource I have ever run across on the Internet.

What I need to learn more than anything is how to "think" the way some of you all do. I know how to do many of the things you have all demonstrated but knowing where and when to apply the core knowledge is what I think seperates the mice from the men/women.

Thanks to all for your help.

Steve

[Baylah]

[color:blue]<>

On the lap top that I bring to work I have FMP 8.5adv. Would you be so kind as to show me your solution with getNth record so I can learn how that function works?

Steve (Asking for the Moon) Freeman

Edited March 21, 2007 by Guest

[Baylah]

Hello again,

As you eluded to in your post...this is slow! It is not so bad in the sample data I sent because that is only one day. But after I programmed the relationships and calculations into my full DB (more than 500,000 records)it is too slow to be practical. I would have to have it run over night to get my results in the morning.

You mentioned a script with a set field type solution. Do you have any suggestions. I ahve been trying to get something similar through a looping script but have not had any success yet.

Thanks,

Steve

[comment]

show me your solution with getNth record so I can learn

You could make the calculation field =

Case (

StationID = GetNthRecord ( StationID ; Get ( RecordNumber ) - 1 )

and

DateStart = GetNthRecord ( DateStart ; Get ( RecordNumber ) - 1 ) ;

TimeStart - GetNthRecord ( TimeEnd ; Get ( RecordNumber ) - 1 )

)

Then you won't need the self-join relationship.

Whatever the calculation, you can replace it with a number field, and have a script populate it with the result: use the Replace Field Contents step with a calculated result using the same formula. If using GetNthRecord(), records needs to be found and sorted before applying the replace.

If you have 500,000 existing records, that's going to take some time to calculate - whatever the method is. But if you only need to analyze a small number of records at a time, you could leave the field as unstored calc, and place it only on a layout dedicated to this analysis. Then go to that layout AFTER finding the records you want.

[Baylah]

I'm starting to sound like quite the broken record...But thanks again. Even though I;ve given credit where credit is due my boss thinks I'm a genius becauseI get this stuff figured out.

I wish you lived close by so I could buy you a beer!

Thanks,

Steve

[comment]

With that offer, so do I! :beertime: