A real puzzle for me - - Potentially long Looping script

[Baylah]

I have raw data in a table that looks like below.

Machine..Start.....End

132b.....11:32.....13:53

132b.....14:24.....15:17

132b.....15:39.....16:12

142a.....10:45.....11:16

142a.....11:23.....13:54

142a.....14:14.....15:17

153b.....09:08.....10:11

153b.....11:19.....12:39

153b.....14:21.....15:07

What I would like is a script that will loop through all of the records and then grab the first start time and the last end time from each group of machines and then place them in a global field (I think) so I can calculate the total time the machines were run on any given day.

Machine..g_start..g_End...Elapsed

132b.....11:32....16:12...4.67 hrs

142a.....10:45....15:17...4:53 hrs

153b.....09:08....15:07...5:98 hr

(g_end-g_start)/60/60

Do I need a global field start, end and calculation for each machine I have on my floor or is there an easier way? (More than 100)

Any help is very much appreciated.

Thanks,

Steve16

Edited March 16, 2007 by Guest

[LaRetta]

Yes. Easily. But do these machine time records have a date associated with them? Because if not, how will you find the start and end DATE to know when to stop adding them? And if they cross the midnight boundary, how will you know how many days to add?

A standard relationship from gMachineGroup = MachineGroup, with a calculation in your table of: cElapsed = End - Start (result is time) and then a calculation cTotalElapsed (Sum ( cElapsed ) would do it. Or you can use a report and a summary. But without dates, how will you tell which records you even want to find? And how can we add times without knowing the dates they represent?

I don't believe a script is needed at all; just a good relationship. Using the same relationship as above, a calculation of Min ( yourMachineTable::Start ) and another calc of Max ( yourMachineTable::End ) would give you SOMETHING ... but that wouldn't be logical from a time perspective.

If you have a date, then just have gDate and include THAT in the same join to filter the records further. Then, whenever you want to know a total (for a script or action), just plug the date into the gDate global, the Machine Group into gMachineGroup and ask what the total of cTotalElapsed is. I envision it to playing a musical instrument; using globals to make/break relationships to feed my scripts information to know how to proceed. ^)

If I've missed the mark on your needs, just let me know. I'm flexible. Here's a file which should help you. If you can clarify the date-span issue, we can adjust as needed.

LaRetta

MachineTimes.zip

Edited March 16, 2007 by Guest
Added demo

[Baylah]

There is a start date and end date field as well in this DB. However, 98% of records do not cross midnight. I wasn;t ignoring them, but I didn;t want to press my luck on asking too big of a question.

I'll check out the attached file and get back.

Steve

[LaRetta]

If they never go over 24 hours (one day) then you can test for that condition in cExtended in your Machine time records. It would be something like (pseudo code), replacing the current cExtended calculation:

Case ( End and Start ; End - Start + 86400 * ( Start > End ) )

... then if the start time is less than end time, FM will know that it is the next day and add 24 hours. In this way, you could skip the dates alltogether. But you still will need to limit the records eventually, right? Sum() depends upon a relationship. Summaries will do the same thing but can depend upon the found set. A date filter will eventually come into play (and probably should from the beginning - planning ahead if nothing else). And you will want to also see records' totals for the month? Year? Week? I don't know the display requirements so I can't advise further ...

[comment]

It sounds like a summary report would be the easiest way: find the records of the given day, sub-summarize by machine. If you delete the body part of the layout, you will get a single row for each machine - just like your example in the first post.

[Baylah]

Each machine can have several start and end times in any given day. This was an important fact to leave out of my first post.

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

132b.....2/1/07...11:32.....13:53

132b.....2/1/07...14:24.....15:17

132b.....2/1/07...15:39.....16:12

142a.....2/1/07...10:45.....11:16

142a.....2/1/07...11:23.....13:54

142a.....2/1/07...14:14.....15:17

153b.....2/1/07...09:08.....10:11

153b.....2/1/07...11:19.....12:39

153b.....2/1/07...14:21.....15:07

Machine..Date....g_start..g_End...Machine Utilization

132b.....2/1/07..11:32....16:12...4.67 hrs

142a.....2/1/07..10:45....15:17...4:53 hrs

153b.....2/1/07..09:08....15:07...5:98 hr

Sorry about this major oversight. What was obvious in my own mind was obviosuly not clear to anyone else. This is also why the reply immediatly above will not work.

Laretta I tried your file (thank you)but it does not work either, but I think it is because you were missing one of the main facts.

Steve

Edited March 16, 2007 by Guest

[mr_vodka]

Each machine can have several start and end times in any given day. This was an important fact to leave out of my first post.

I dont understand why this would be an issue. If you are doing a search for a particular date then the summary report would be for that found set. Since you are grouping by machine, you can use a summary field with min for the earliest starting time, summary of max for the last time it ended for that machine, and finally one last one that sums up the calc for the time used.

The only concern that I see is more of a process issue for you. If it indeed goes over midnight, which report do you want it to report on; the day or the next day?

[comment]

you can use a summary field with min for the earlies starting time and summary of max for the last time it ended

I don't think that will work well, since that would include the gaps. I presume we only want the total of the times the machine was ON.

You need (a) a calculation field, let's call it cDuration (result is Time) with the formula in LaRetta's post:

End - Start + 86400 * ( Start > End )

and ( a summary field, defined as Total of cDuration. The summary field needs to be placed in a sub-summary (when sorted by Machine) part.

[mr_vodka]

Hi Michael.

I was refering to LaRetta'a calc and your previous post, when I was talking about summing up the "time used" with a summary field. I was agreeing with you guys.

The talk about using a min and max summary field was in regards to his final output of:

132b.....2/1/07..11:32....16:12...4.67 hrs

The 11:32 would be the earliest time it started and 16:12 the last time it ended for that particular machine.

[LaRetta]

Baylah, I'm unsure why either a relationship or summary report will not work here. As mentioned, if you have a date then just include the date in either your relationship (as described) or include the date in a leading part (and sort on machine group then date). Your report would then summarize by machine and by date; depending upon your found set.

If you go the report route, the report would be IN the time records. And it would add together cDuration (the time used by each machine for each time record) on a per day basis. Again, we know you need the total time used but we don't know how you need this information displayed. It would help to know the User perspective here, ie is this just for a daily/weekly/monthly report generation? Or do you need ability to (on the fly) take a total of a machine for a given day and act upon the results (via script)? Will you also want portal displays with filtering ability per day/week/month of the detail?

Can you explain exactly why neither of our suggested methods will work for you?

LaRetta

[Baylah]

Before anything else...Thank you to everyone for trying to help!

In the table view below (from my original post)raw data is displayed. The second table down shows exactly how I would like this data reported.

Based on my understanding of how reports are built in FMP I don't understand how I can get the results in the bottom table through reporting and sub summaries. How would FMP know where to go to pick the "first start time" and "last end time" from each group of machines?

The result at the bottom is exactly what I would like the DB to return. I would like to be able to run this report any day at any given time. I originally thought that I would need a looping script that ran through each set of machine groups and it would pull into global fileds the first and last times as stated earlier.

I realize there are probably better ways of going about this but I am stumped. especially on how to do this in a "regular" report.

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

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

132b.....2/1/07...14:24.....15:17

132b.....2/1/07...15:39.....[color:red]16:12

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

142a.....2/1/07...11:23.....13:54

142a.....2/1/07...14:14.....[color:red]15:17

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

153b.....2/1/07...11:19.....12:39

153b.....2/1/07...14:21.....[color:red]15:07

Machine..Date....g_start..g_End...Machine Utilization

132b.....2/1/07..11:32....16:12...4.67 hrs

142a.....2/1/07..10:45....15:17...4:53 hrs

153b.....2/1/07..09:08....15:07...5:98 hr

If I can pull just the information above (from 1,000 of StartTime-EndTime) from the top table records I am golden!

What we are actually trying to display is the "under utilization" of our equipment. For example, machine 132b above on 2/1/07 only ran for 4.67 hours even though there was a machine operator available 15 hours on that day.

We are trying to determine our true capacity first, and second determine why we are so under utilizing at this point. This is why I actually do not want to measure the "gaps" between machine cycles. I just want to know when the first time a machine ran in the morning and the last time at night. In other words, on 132 b, why the heck was the machine not running for 10.33 hours out of 15? we can account for much of that...but not all. Everything else comes from that information.

Thanks again to everyone.

Steve

Edited March 16, 2007 by Guest

[comment]

Ah, so John was right after all (though I still wonder why you want to exclude the gaps - suppose I started the machine at 8:00, ran it for a minute, then stopped, then started again at 15:59 and ran it for another minute: your report will say 8 hours).

Anyway, I believe the attached will do what you want. You might want to add a find for a specific day before the rest of the script.

Machines.fp7.zip

[Baylah]

Thank you....

I did not know that the minimum and maxium functions worked this way in the summary.

We do not need to know the "in-between" time because some of our machine operators are responsible for running more than 1 machine and utilization is not as critical at their secondary machine as it is at the primary. However, we are considering re-arranging the way we deploy staff to the machines. Because of this we need to analyze the total amounmt of time a machine is available on any given day. If it is above a certain point for a studied period of time then that machine (or bank of machines)may become primary as opposed to a secondary.

We are aware that an operator could "cheat" the system by starting and stopping in the fashion you describe, but the automated warehouse systems track operator out put and would catch that in a hurry. 8 hours of machine time and no product completed......The flags would go up.

Now that I think on this more though I will have to consider the "after midnight" issue. Because even though oeprators never cross midnight, sometimes a second shift operator will start a job one day and it won't be finished until first shift the next day. This may be particulary problematic for product started on a Friday night and not revisited until Monday morning. Thanks for pointing out the fallacy Mr. Vodka!

Thanks again for your help on this. It is very much appreciated. If you are US based drop me your address off line and I will send you a really nice golf shirt from my stack of design samples. I am the Technical Embroidery Manager for Ashworth, Inc. (this offer is for anyone who helped on this string!)

Steve Freeman

Edited March 17, 2007 by Guest