Hi, I'm new to filemaker and need help generating a calculation. I am creating a scheduling database and in an effort to save time and eliminate error i would like the user to input the amount of hours projected to work and the start time. From those two fields i would like to generate the end time automatically. Can someone please help me out. i'm using field names: start time, hours, and end time.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Calculation for time based on start time and hours worked
- Thread starter LoffKat79
- Start date
- Thread starter
- #3
To start on this we need a little more information.
Suppose you have your startDate field, a startHour field and a duration field, which are date, time and number.
What will be your 'duration' unit, hours or days ?
This will bring us to the next questions, if duration is hours, what is the 'working time', 8 hours a day, 12 hours a day, are there shifts, what is the shift duration, is Saturday and Sunday a working day, what about the Holydays.
I can give you a simple, dirty calculation, but the outcome will not be very meaningful nor useful.
Don't worry, it looks as a complex thing to do, but it is not.
It's coming down to incorporate all the possible variables at the right place.
So, how far do you want to go ? Lets just start with a few variables and see how far we can go together.
Suppose you have your startDate field, a startHour field and a duration field, which are date, time and number.
What will be your 'duration' unit, hours or days ?
This will bring us to the next questions, if duration is hours, what is the 'working time', 8 hours a day, 12 hours a day, are there shifts, what is the shift duration, is Saturday and Sunday a working day, what about the Holydays.
I can give you a simple, dirty calculation, but the outcome will not be very meaningful nor useful.
Don't worry, it looks as a complex thing to do, but it is not.
It's coming down to incorporate all the possible variables at the right place.
So, how far do you want to go ? Lets just start with a few variables and see how far we can go together.
- Thread starter
- #5
Ok! Here is more info.
~I can put a start date field (right now i have an event date but I can create a start date and have it default as the event date to carry the times over to the next day if it runs past midnight.)
~My start time field is a time of day HH:MM AM/PM but I can change that if there is a better way.
~The duration is in hours HH:MM
~there is no 'working time'. it's basically 24 hours a day 365 a year if needs be. but events at our venue normally don't occur on holidays so I do not think that will be a problem.
I know it's not all that complicated, I'm just a newbie and my head is spinning with all that I'm doing. I'd like to take the calculation all the way if possible to get it as accurate as possible! If you need anymore info let me know. I am flexible and this database is not in use so anything can be changed if there is a better way/format etc. Thanks for you help ahead of time! I appreciate it very much!
~I can put a start date field (right now i have an event date but I can create a start date and have it default as the event date to carry the times over to the next day if it runs past midnight.)
~My start time field is a time of day HH:MM AM/PM but I can change that if there is a better way.
~The duration is in hours HH:MM
~there is no 'working time'. it's basically 24 hours a day 365 a year if needs be. but events at our venue normally don't occur on holidays so I do not think that will be a problem.
I know it's not all that complicated, I'm just a newbie and my head is spinning with all that I'm doing. I'd like to take the calculation all the way if possible to get it as accurate as possible! If you need anymore info let me know. I am flexible and this database is not in use so anything can be changed if there is a better way/format etc. Thanks for you help ahead of time! I appreciate it very much!
- Thread starter
- #6
Easy part first:
>> Also, how would I force the Hours field to be HH:MM format?
Right click your field in layout mode, go to format and format your field in the screen displayed.
You need a few fields, just substitute the my namefields with yours.
dateScheduledStart = datefield
timeScheduledStart = timefield
actionLenght = numberfield
dateEnd = calculation, result Date =
dateScheduleStart + actionLenght/24 + Hour(timeScheduledStart)/24 + Minute(timeScheduledStart)/1440
** Hour and Minute are functions **
timeEnd = calculation, result Time =
Mod(timeScheduledStart + actionLenght *3600;86400)
**will give you the end time on the dateEnd day
JW
>> Also, how would I force the Hours field to be HH:MM format?
Right click your field in layout mode, go to format and format your field in the screen displayed.
You need a few fields, just substitute the my namefields with yours.
dateScheduledStart = datefield
timeScheduledStart = timefield
actionLenght = numberfield
dateEnd = calculation, result Date =
dateScheduleStart + actionLenght/24 + Hour(timeScheduledStart)/24 + Minute(timeScheduledStart)/1440
** Hour and Minute are functions **
timeEnd = calculation, result Time =
Mod(timeScheduledStart + actionLenght *3600;86400)
**will give you the end time on the dateEnd day
JW
- Thread starter
- #8
- Thread starter
- #9
I substitute my fieldNames with yours and it works at this side.
Make sure your Cone Guard Start Date field is type Date.
Cone Guard Hours is type number and your end Date field calculation has to be type Date, where your Cone Guard Start Time has to be type Time.
BTW: it's better to avoid spaces in your fieldNames.
Make your fieldNames something like coneGuardStartDate....
Just to avoid problems later when you want to import/export data.
Input in StartDate is just a date, usual format.
Hours is a regular number field where you imput the hours as a 100 part, 10 hours = imput 10, quarter to ten = 9,75,
half past 3 = 3,5
Make sure your Cone Guard Start Date field is type Date.
Cone Guard Hours is type number and your end Date field calculation has to be type Date, where your Cone Guard Start Time has to be type Time.
BTW: it's better to avoid spaces in your fieldNames.
Make your fieldNames something like coneGuardStartDate....
Just to avoid problems later when you want to import/export data.
Input in StartDate is just a date, usual format.
Hours is a regular number field where you imput the hours as a 100 part, 10 hours = imput 10, quarter to ten = 9,75,
half past 3 = 3,5
- Thread starter
- #11
I got it to work! apparently i don't really need the end date which is fine. they just lump it into the start date! yay! you wouldn't by chance know how to make repeating fields adjust according to how many data lines are put in it would you? THANK YOU FOR YOUR WONDERFUL HELP! i am so excited!
Great !!
For the repeating fields part.
I only use repeating fields to store basic things like colors, buttons and basic static info to make my application work.
I never use repeaters for data, unless in very complicated techniques where the ony way to make things working is by using repeating fields.
All the rest can be achieved with relationships.
If you have to use repeaters to manipulate your imput data, chances are there is something wrong with the way you built your application.
For the repeating fields part.
I only use repeating fields to store basic things like colors, buttons and basic static info to make my application work.
I never use repeaters for data, unless in very complicated techniques where the ony way to make things working is by using repeating fields.
All the rest can be achieved with relationships.
If you have to use repeaters to manipulate your imput data, chances are there is something wrong with the way you built your application.
- Thread starter
- #13
nuts! ok. here is my app setup. it's a scheduling db for events. I have repeating fields for "housemen" (which is a drop down list) and repeating for "elevator operators" because there are many. in a seperate table i have the housemen's names and their pay rates. so whenever the people change the drop down list in the repeating fields change. so i have my event list of people and a drop down list for the user to choose who is working, then start time and hours worked (obviously)which are also repeating fields. For each sub category as you can see above, i have specific time and hours for the specific personnel category. should i be doing this a different way?
Best way is to go relational for that and showing the houseman and elevator operators in a portal.
In those portals you can ad the date and time, the records for those itams will be created through the portals in the apropriate tables, the calculations will be in the tables, while the result will be reflected in the portals.
In those tables you can make all the needed scripts/calculation/etc. to make your reports on the activities of each operator/man, like monthly hours, dates, events etc.
In the portals you can have the drop down list, reflecting the records/fields in the different tables (value lists).
The advantage is also that you can make a control to prevent that one houseman is assign the same 'time' to different events, should that be a problem. A control that is nearly not possible with repeaters.
If you are in an early stage of development, I suggest to rethink/draw your application and dig into the techniques of relatinal tables.
In those portals you can ad the date and time, the records for those itams will be created through the portals in the apropriate tables, the calculations will be in the tables, while the result will be reflected in the portals.
In those tables you can make all the needed scripts/calculation/etc. to make your reports on the activities of each operator/man, like monthly hours, dates, events etc.
In the portals you can have the drop down list, reflecting the records/fields in the different tables (value lists).
The advantage is also that you can make a control to prevent that one houseman is assign the same 'time' to different events, should that be a problem. A control that is nearly not possible with repeaters.
If you are in an early stage of development, I suggest to rethink/draw your application and dig into the techniques of relatinal tables.
- Status