10 Replies Latest reply on Mar 21, 2016 7:14 AM by disabled_morkus

# Calculate hours from multiple fields

I have a table called Request Schedules - which I would like to create a calculation field that adds up all of the fields, just numbers so 30 minutes would be (30) or 0.5 hours for each related request and show a value.

Requests -------< Request Schedules

pkRequestId -------< fkRequestId

Request Schedules

30 mins

45 mins

1 hour

2 hours

3 hours

O/N (Overnight - 8 hours)

• ###### 1. Re: Calculate hours from multiple fields

Could you give a clearer example of the table, the data, and what the sums should be (and how calculated)? From your screenshot and description above, it's not clear.

- m

• ###### 2. Re: Calculate hours from multiple fields

Each Request has a related record (a few related) depending on the request type - this is broken down into time gaps which fall into for example 7-10pm - for each gap a time schedule might be applied so for one request the time schedule 7-10pm on Monday will be 30 mins.

I would like to create a calculation field that adds all of the related fields (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday) up to give me a Total Hours calculation field. I will need to convert the values in to numbers - I guess I need to trim the mins and hours of the end and for the 30 mins and 45 mins schedules I need to convert to hours.

Depending on what type of request a user chooses depends how many time gaps the request has, some might have 3 or others might 5.

I've attached the whole table this time which might be easier to illustrate what I'm trying to do:

• ###### 3. Re: Calculate hours from multiple fields

Do you mean like this (see total column at right).

Simple calculation field.  FileMaker is maybe the only product I know that can add 1 Hour + 2 Hour and get 3. Nothing fancy needed.

Pretty cool.

- m

• ###### 4. Re: Calculate hours from multiple fields

Oh great - the only problem I will encounter I guess is with the fields that are 30 mins, 45 mins, O/N (8 hours) - what's the best method to change those?

• ###### 5. Re: Calculate hours from multiple fields

For historical data, I would write a quick script to update the existing minute times to their decimal equivalents.  Since "30" could mean thirty hours or 30 minutes (How would FM know?), I would write a script to visit each field in every record and if "min" is part of that field, then do the conversion.

So 30 min -> 0.5 Hours, 15 min -> 0.25 Hours, "ON" -> 8 Hours.

Then, going forward, the ideal solution would be to enter the times as 1.5 hours or 1.25 hours.

You could get creative and create some auto-enter calculations to try and handle strange user data entry situaitons, but that may be going overboard depending on your requirements or users. Or, possibly the other way around, depending on your users, you may need a more involved data validation solution.

Make sense?

- m

• ###### 6. Re: Calculate hours from multiple fields

Okay great, I might do a Replace Field Contents on the 30 mins, 45 mins and O/N fields

Would "0.5 hours" and "0.75 hours" be sufficient?

• ###### 7. Re: Calculate hours from multiple fields

Yep, looks like it:

To get a better idea of what works, which is what I did when I read your interesting posting, you can experiment with stuff like this in the data viewer.

- m

• ###### 8. Re: Calculate hours from multiple fields

disabled_morkus

Working great now thanks again, managed to do a Replace Exisitng Fields with my 30 mins and 45 mins - I might try and still display those fields as 30 mins and 45 mins.

One question though - when I have multiple related records for one request say, how would I do a final calculation field which adds up the calculated hours for each related record?

• ###### 9. Re: Calculate hours from multiple fields

Managed to work that out myself:

Requests::cTotalHours = Sum (Schedules::c_Hours)

• ###### 10. Re: Calculate hours from multiple fields

Sure, from the parent table, you could create a calculation that adds up each field in the many side. Or, maybe create multiple calculation fields you could then add up. As long as you have the 1:M relationship (and "=" in the relationship graph), your "SUM" field would only "see" the related records in the child table.

-- m