Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4819 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Hi guys,

I've been searching through the forums here to get some insight into a problem I've been having, but I can't quite find enough info to figure it out.

I have a FileMaker solution that tracks time spent on a job. Each job can have multiple time entries logged for it, and since a few people might be working on the same job, those time entries can overlap.

For each job, I want to calculate the net amount of time that was spent. In other words, I want to ignore any time overlaps and calculate the total time spent in general, not just total up each separate time entry.

Visualized, my data might look like this:


9 ------- 10

9 ------- 10

			  11 ----------- 1

                                  12 ------------ 2

 

In the above example, I would consider the net time as being 4 hours, since some of the time logs overlap.

I've attached a very basic idea of my database with some sample data. I feel like there should be a way to do this with self-joins and a couple calculations, but I still haven't gotten my mind wrapped around it. Any help would be much appreciated!

OverlappingTimes_Demo.fp7.zip

Posted

I feel like there should be a way to do this with self-joins and a couple calculations

I don't think there is a simple way to do this. Before getting into it - what is the significance of the result? If Adam worked for 2 hours and Betty for 3, there are 5 man-hours in the project - regardless of overlaps.

I think the attached does what you want

Well..

OverlappingTimes_Demo_v2.zip

Posted

I dont think that this can be solved with just using relations on the actual timestamps.

So to solve this I ended up with adding a row field that i populate with serial numbers and then base my relations on that.

And i got a second problem, trying to calculate the effective timestamps, i need to compare with the the related previous timeLogs ditto, and FileMaker see's this as trying to have a calculation that is based on itself, even if its in another TO. So i had to make an autoenter calculation instead.

So all in all this make the solution not very dynamic.

But when you push the button It seem's to come up with the right result.

...... Well the site was not responding, so I used the time to figure out a way to make it dynamic, and thought that a Custom funtion should do the trick.

So I added a custom function to the solution, leaving the rest as it was, a bit messy to look at, but hopefully it can helpyou figure out the mechanics.

The custom function dosnt need any of the ekstra fields, besides the result field.

--- been thinking - the current Custom function is way to error prone, if there is missing some of the timestamps, the result will be invalid.

Sorry I wont have time until late sunday to make a new one.

OverlappingTimes_Demo_v3.fp7.gz

Posted

Wow, thanks for the replies everyone! This was exactly what I was looking for. I haven't got into Custom Functions too much yet, so this is great to have some solid examples to learn from. Thanks for the input!

I don't think there is a simple way to do this. Before getting into it - what is the significance of the result? If Adam worked for 2 hours and Betty for 3, there are 5 man-hours in the project - regardless of overlaps.

It's going to be used as a way of determining total time if multiple people are involved in the same task. Let's say Adam and Betty were both in a meeting for 2 hours, and they separately log their time for that. We want to be able to say that 2 "company" hours were spent in the meeting, instead of 4 cumulative "man hours."

Posted

Let's say Adam and Betty were both in a meeting for 2 hours, and they separately log their time for that. We want to be able to say that 2 "company" hours were spent in the meeting, instead of 4 cumulative "man hours."

Perhaps this would be better handled on the data entry side, by entering "company hours" separately from man-hours.

This topic is 4819 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.