Simple date field question

[dodsonmd]

Having an issue with different length months.

I have a layout with a month date field and 31 vertical day fields.

I need each record to start with the 1st day of the month, but only show the days of the actual month selected.

Is it possible to not show the last field (Day 31) if I select a 30 day month and still remain in the same layout? Or some way to leave the Day 31 field empty and not filled with the 1st day of the next month.

If I create a separate 30 day layout, it seems I'm unable to construct a found set containing all the related records for one person. So I presume I need to add some calculation to the day fields to avoid entering the next month days.

Hope this is not too confusing.

Thanks in advance.

John

[aldipalo]

Can you explain what you are trying to do? It sounds like you really want a report of based on a person for every day of the month.

[dodsonmd]

I need a monthly time card for each individual. They will enter hours worked on the appropriate day. Separate fields will calculate totals. I then need a monthly summary report comprised of those individuals who worked that month.

I would like to set up a privilege set so that each person would only see a found set of their time cards and be able to navigate through them to past records. So I'm presuming that I can only use a single layout for the record in order to provide a set of their individual records.

Thanks for your help.

John

[aldipalo]

Not necessarily. You can have two tables

Employees

Time Card

I've attached a quick file.

This should get you going in the right direction.

TimeCards.zip

[dodsonmd]

Thanks for the sample.

In fact I do have an employee (provider) table and time card tables.

But it seemed I had to make a separate table for each of the different length months. The problem with that is trying to construct a found set comprised of all time cards for each provider. I can make a set of the 31 day cards, the 30 cards, etc. but I can't figure out how to develop a set of one person's time cards to allow them to view just their own. Every set I find comes from only one particular layout (table). I have unique provider ID numbers, and a unique record number for each time card. I created a user account for each, arranging access to just their records (and that's not working so well either), but I only get months of a specific length when I log in as a user. I can change to a different layout, but the months are not in chronological order.

Soooo......I figured I needed to make just one table containing all time cards, but need to find some calculation that will avoid the day/date spillover to the next month on the shorter month records.

I attached a couple screen shots to give you an idea of what I'm trying to do. I can make a stripped down FM file if that would be better.

Thanks a million for taking the time to help out. This is a bit tricky, but I'm really enjoying learning the program.

John

[aldipalo]

I think what you are looking to do could more easily be accomplished using some Custom Functions. But, that requires FM 9 advanced. I'm not a calculation wizard so perhaps someone with more knowledge in that area can help you further. The question I have though, is why do you need to display everyday of the month in advance? Can you not take your Time card and fill in the dates of the month as they enter their hours? Then you can set up a formula, within your create script, to check the date and if it is past the end of the current month, start a new card. Also, if you have a sign-in script you can do a find on account name to bring you to 'only' that persons time card. I set up the TimeIN and TimeOut example so it is automatically entered and can not be changed. Do you want to give the employee the capability of changing that data?

Perhaps a clone of the file would make it easier to assist you.

[dodsonmd]

Thanks for the great Set Time script in your sample. That was very informative.

I'm using a whole month appearing record as much for historical reasons as well as ease of recognition. I have some rather computer-antagonistic physicians who work for me, so the more the record looks like the previous sign-in sheets, the easier the transition will be. In the future I should be able to construct something more like you suggest.

In your sample the time in/out fields are current time (punch-in clock) fields. That would be problematic in our situation, so I'm allowing them to choose from a pop-up to select a time already rounded to the quarter-hour. But I do need to prevent changes to the field once the user logs out. Any suggestions as to how to adjust the script would be great.

Can you think of a script or calculation for the hour in/out field that would allow a time input (24 hr format) and on commitment would round it to the nearest quarter-hour. That way I could avoid the 56 choices on the pop-up list (sure sign of an FM novice).

As for the days of the month, I think I need to figure out some type of Case calculation that would go into the last day fields of the month, entering 'Month + 29 or 30' if there is a 31 day month in the month field, and leaving the default response blank. But I haven't been able to get it to work correctly.

Thanks again for your kind attention.

John

[dodsonmd]

Solution found!!!

Problem: vertical array of date fields that should only show day/date of the associated month, whether 28, 30 or 31 days long.

I think I finally came up with a calculation that should work. In the last day/date fields I put this calculation:

Case (

Month = Date(1;1;200 ) ; Month + 30 ;

Month = Date(3;1;200 ); Month + 30 ;

Month = Date(5;1;200 ) ; Month + 30 ;

Month = Date(7;1;200 ) ; Month + 30 ;

Month = Date(8;1;200 ) ; Month + 30 ;

Month = Date(10;1;200 ) ; Month + 30 ;

Month = Date(12;1;200 ) ; Month + 30 ;

)

I left a space after 200_, that seems to allow for 2008 and 2009, maybe I should leave two spaces to cover more years? The 'default result' was left empty resulting in the field being left blank.

There's probably a shorter, more elegant way to do this, and I'd love for someone to show it to me. But for now this seems to do the trick.

John

Edited July 19, 2008 by Guest

[dodsonmd]

Spoke too soon. The space after 200 , did not give me the year correctly. Any ideas on this? Anyone?

How can I alter the calculation to give me the correct year, without having to put each individual year into the formula?

Edited July 19, 2008 by Guest

[comment]

This CAN be done within the calculation, but it's easier with conditional formatting (and after all, it is a display issue). See attached for a couple of alternatives.

You should be aware that having a record per month with all the entries in repeating fields severely limits your finding and reporting capability. For example, it would be quite difficult to get the average workday length, and much more difficult to get such average broken by day of week.

TimecardR.fp7.zip

[dodsonmd]

Thanks for the terrific suggestions. I'll spend some time dissecting and digesting the examples.

I know repeating fields are not the most desirable choice, so what would you suggest? Perhaps create individual fields for the times, like I did for the dates?

The formulas to adjust the # of days are great. I really like the 'hide' option. What's the best way to incorporate that into a layout?

Another quick question: If I have a user log in, how do I construct a Find and Sort, so that he only sees his own records, and they're arranged chronologically.

I really appreciate the help.

John

[dodsonmd]

Hope I'm not being a pest, but your suggestions have been very helpful.

Question on the time issue. I created a new time card and entered 0700 in the timein field, it gave me 2520000:00:00 even though the time format for the field is hhmm, 24hr. I'd like to find a way to just have the four numbers entered and return the 24hr time rounded to the quarter hour.

I have ER physicians entering their times, and even in this day and age some of them are really keyboard averse and always in a rush.

Thanks, John

[comment]

I know repeating fields are not the most desirable choice, so what would you suggest? Perhaps create individual fields for the times

No, that would be even worse. At least with repeating fields there is a simple way to convert them into individual records. Because that would be the proper structure: an individual record for each entry.

However, displaying individual records against a fixed grid is not easy - perhaps you should postpone this task until you are more familiar with Filemaker.

I really like the 'hide' option. What's the best way to incorporate that into a layout?

All that's left to do is make the text objects invisible, e.g. remove the borders and replace the explanatory text with a space.

entered 0700 in the timein field, it gave me 2520000:00:00

I don't think "0700" is a valid entry into a time field. Anyway, the field has an auto-entered calculation that allows time to be entered as "6.08" and interpreted as "6:08". IIRC, it does some rounding too (it's an old file I adapted for this). If you play with it, you can adapt it to your desired format of entry.

[aldipalo]

Hi John:

As someone who's been recruiting Physicians for many, many, many years I appreciate your predicament. How about a simple SignIn and signout script? This way all they have to do is click a button and they are signed in. You can have the time round to the nearest 15 minutes if you wish and they don't have to do anything else.

Btw, 2 things; Comment is one of the Calc wizards I told you about. So you have gotten a suggestion from one of the best, if not the best. (That's from my experience.) Second, I totally agree with him that repeating fields is NOT the way to go. There are much better ways. If you look at the file I sent you each day is a separate record. It is much easier and more efficient that way. If you are only interested in TimeIn and TimeOut it is even less complicated.

If you have any other questions let us know.

[dodsonmd]

Thanks again, I'll spend some more time with your suggestions.

John

[comment]

Just a note: I am not saying repeating fields are necessarily a bad choice here. It all depends on what one hopes to get out from the solution - now, and down the road.

Thanks for the compliments - but any community where I am a calc wizard is in serious trouble...

[dodsonmd]

Hi Al,

Thanks for all the help. You're right, I can see by Comments suggestions that he's very adept with Filemaker. Certainly has given me some serious work to do, trying to understand where he's coming from.

As you can see from my questions, my needs are really quite simple. And I'm sure it's obvious I'm transitioning from a paper sign-in system, and trying (probably inappropriately) to emulate that prior arrangement. And undoubtedly making it much more complicated than is necessary. I'm beginning to realize, and hopefully learn, the mind-set needed to properly develop a database solution. Plus I really enjoy learning the processes involved in creating such a solution.

The idea of a sign-in/out button seems to be very common in the timecard solutions I've come across. The difficulty, as you're aware, herding cats is easier than getting physicians to remember to click a button at the right time. I would get endless emails/phonecalls/post-it notes asking me to correct their time sheet.

Times rounded to the quarter hour are more than sufficient for me, and my providers can be trusted to just enter the time, and I ultimately would like to make the time sheets available via web browser so they could input their times from outside the hospital. My local management company only needs to see a monthly record for any given individual for input into their own payroll system, hence the attempt to create a month-based record.

I looked at your sample, and could see that each day would create another record. And I really don't 'need' a record of the empty days, but my docs will want to look back at their timesheets to review when they worked. I think I was intimidated about creating that many records for a group of 20-25 providers.But I'm realizing that most likely my inadequate experience with database programs is leading me that way.

Lastly, I see by your location that you're here in Pinellas county with me. If you do this kind of work on a consultant basis, maybe we could work something out.

Thanks, John

[aldipalo]

Hi John:

If you click on my username you'll get my profile. Send me a private email with your contact info and we can talk privately about this.

Al

[Søren Dyhr]

Comment hinted in his responses that the entire registration business usually serves a purpose which is somewhat beyond neat looking forms, which dynamically could prevent entries being made in the following month.

Although forms plays an often important role in kafkan mazes, could the registration also serve other than disciplinary measures, such as statistical ones.

When statistics comes in as purpose, begins repeating fields to exhibit inadequacies, the data needs to be copied into other fields and split into records, if such demands arises - the question is then if we instead could come up with a relational structure allowing us to make the statistics or summaries without moving data around.

I have made a solution which allows the creation of atomic data, although somewhat byzantine and less intuitive than the use of repeaters with conditional formatting....

One camel to digest is that the record every entry is the same and the two global a relational jump directs the entire set of records shown in the portal. This is jeopardizing the metaphor of a card for each employee ... because it becomes a set of records assigned to each individual we should wish to make our statistics upon.

--sd

Timesheet.zip

[dodsonmd]

Hi Søren,

Thank you for the thoughtful response. You really sound more like a philosopher than a database developer, but your ideas are very interesting. I would love to hear what Comment and Aldipalo think of your approach. Perhaps they could paraphrase it into something a bit more accessible to a novice like me.

I beginning to understand that Filemaker, like many programs, offers many paths to the same end. I am grateful for the valuable responses in this forum. They have prompted me to rethink my solution and approach it from a database perspective. Hopefully, I can come up with a solution that will initially fill my needs, but not paint me into a corner when I want to expand or improve it.

You're exactly right that my conception was a time card for each individual. You all have impressed me that such an approach may be self defeating, and limit my options in the future.

I would like to ask a couple questions about the sample solution you sent.

1) The Browser table. Is this intended to be the form the user would see when accessing his own time card? There are no fields, so I see it is not a layout and it is related to all records. Just trying to understand its purpose.

2) The table named 'Pling', I'm not sure what the name is in reference to, as I don't see that name in the Relationship diagram. I think the 'Placeholder' concept is confusing me.

3) Entry Form layout. The record ID is not in the layout. Is this because it is not actually important for the employee to know it, but just for purposes of the database?

4) Within Same Month field. I'm not sure how this is used by the layout. Once again, trying to comprehend the Placeholder concept.

I like the approach, and really appreciate your taking the time to help such a novice as me get his feet on the ground.

John

[Søren Dyhr]

1) The Browser table. Is this intended to be the form the user would see when accessing his own time card? There are no fields, so I see it is not a layout and it is related to all records. Just trying to understand its purpose.

Strictly speaking could it be the pling-table used once again, what's important here is the cartesian relation. I have chosen to make a blank table and call it browser to hold the portal of the records from 2(3) relational jumps away.

2) The table named 'Pling', I'm not sure what the name is in reference to, as I don't see that name in the Relationship diagram. I think the 'Placeholder' concept is confusing me.

I've been scatching my head almost balled, to invent a saying name for it's function, what it is way to arrange a record for each day with offset in the global field .... What then happens is that the same table again is joined via a selfjoin, to make the filtering happen.

This means that only those records which falls into the same month as the global field makes a relational link to the second occurence of the table where the "placeholding" is done is filtered thru. The rightmost table is the one you write the hours in.

3) Entry Form layout. The record ID is not in the layout. Is this because it is not actually important for the employee to know it, but just for purposes of the database?

The record ID is loaded into the other global field, which then provide the other criteria for the final relation in which the hours are written. This means that the value in the two globals point at individual records in which the atomic data is written.

In real life would a popup load the correct persons ID but the user will only see the name of the person, the same way the date is chosen.

4) Within Same Month field. I'm not sure how this is used by the layout. Once again, trying to comprehend the Placeholder concept.

I have explained it above it's for the relational linking to single out dates in the same month as the global date field is in. The Case( have only one field for the result ... if it's calc turns false will it turn empty, while it forwards the records ID to link with it self in the second occurence of the same table.

--sd

[aldipalo]

Hi John:

You'll find that there are many incredible people on this forum who will be able to give you superb advice and guidance. Soren, like Comment, is another one of those people. I have found that there are many ways to approach a single problem in FM and you will get that here.

Soren's approach is very elegant and at the same time simple. I don't mean simple in its design, I mean simple in it's execution. A few quick and well designed calculations to accomplish the goal.

I think you are correct, you must stop and think about what you really want in your application before starting to develop it. May I suggest a little reading material.

Go here: http://www.foundationdbs.com/downloads.html

And download and read David Kachels':

White Paper for FMP Novices

and

Database Design

I think these papers will help you to better organize your thoughts.

hth

Al

[dodsonmd]

Making progress here. But got questions about getting the calc and formats correct in the time fields.

Here is Soren's file, I've added TimeIn/Out fields. Is there any way to allow the input of any 3 or 4 number sequence, and have the time appear in the 24 hr format, hh:mm? I've put Comments rounding calcs in those fields, but the time appears in a odd format, and I'm not sure why. I then need to calculate the difference, adjusting for shifts that extend past midnight. I think I have that calc correct.

Thanks for the help.

John

TimesheetSoren.fp7.zip

[dodsonmd]

Al, thanks for the link. I actually had already come across the White Paper for novices, I'll get the DB Design doc also.

Thanks, John

[aldipalo]

On the same link I gave you David has a Custom Function called UniversalTimeFormatter. That may solve your problem.

[comment]

Earlier in the thread,

displaying individual records against a fixed grid is not easy

I believe Søren's file shows why. Although his basic approach could be simplified, there are additional aspects that are not shown in the demo, but are essential in a practical implementation of the technique. For example, one needs to prevent users creating empty records by clearing entries.

BTW, I tend to use the name 'Slots' for what Søren calls the "pling" table.

Is there any way to allow the input of any 3 or 4 number sequence, and have the time appear in the 24 hr format

This is more complex than I thought. In my file, you need to use the decimal point as the separator between hours and minutes. But it seems that when the entry is not IMMEDIATELY recognized as being a number, it's already a time BEFORE auto-enter operates on it.

So the auto-enter formula needs to be something like:

Time ( Div ( self ; 360000 ) ; Div ( Mod ( self ; 360000 ) ; 3600 ) ; 0 )

Note that this will cheerfully accept input of "161" and convert it to 2:01:00 (1 hours and 61 minutes).

[dodsonmd]

This is more complex than I thought. In my file, you need to use the decimal point as the separator between hours and minutes. But it seems that when the entry is not IMMEDIATELY recognized as being a number, it's already a time BEFORE auto-enter operates on it.

So the auto-enter formula needs to be something like:

Time ( Div ( self ; 360000 ) ; Div ( Mod ( self ; 360000 ) ; 3600 ) ; 0 )

Note that this will cheerfully accept input of "161" and convert it to 2:01:00 (1 hours and 61 minutes).

Comment, the calculation works perfectly in your demo, but not in Sorens (file attached). They seem to be formatted the same, but 715 in your file returns 7:15, but in Soren's returns 715:00. I've looked and looked but can't seem to find the difference.

Soren, when I enter a time in the TimeIn field, the tab key works to enter the time and move to the next field, but the Enter key clears the field. Any way to avoid that?

Thanks, John

TimesheetSoren.fp7.zip

[Søren Dyhr]

but the Enter key clears the field. Any way to avoid that?

Indeed, you have started to use peoples names instead of numbers to identify them by, this requires a type change of the global into text - the same is required in the foreing key field, so the relational linking doesn't look for combinations of 1 thru 9 (and 0) here absent ... earlier versions were more relaxed when attempting deliberate or accidental typecasts.

Take a look at the modification I've made to it, and see if it zaps all gremlins??

--sd

timeSDFixed.zip

[dodsonmd]

Soren,

Thank you very much for the adjustment. The time entrys work great. Serious gremlin zapping now in progress.

I have a question on the Hours Worked field. In the 'Specify Calculation' box, the 'Do not evaluate if all referenced fields are empty' is selected. But the Hours Worked field shows a value even if only the TimeIn field has a value, and TimeOut appears to be empty. Did I miss something? My calculation for Hours Worked seems to be entering 24:00 even if TimeOut is empty. I then need to click in the TimeIn/Out fields again to force the calculation. Any way to make this automatic?

I've added the Totals fields to summarize the worked-days time inputs. But I don't know how to obtain the summary for all the days in which there are times entered. Please excuse my novice questions, but I'm really learning a lot from this forum, and the samples are very, very helpful.

The time sheet grid is coming along very well. Now I need a way to create individual records (reports?) that each user can access for review in the future (but not edit). In the current file if I create a 'New Record' all the records have the same date (global date). It seems you understand my metaphor of a time card for each person, each month. So perhaps I need to create a report for that person, that month that will be saved. Am I on the right track here?

I would like to arrange the solution so that each person, with unique password, logs in to enter their times, but can also see the previous months with the totals and calculations intact.

Thanks again, hope the weather is good in Denmark.

John

timeSDFixedTotals.fp7.zip

[Søren Dyhr]

I have made a couple of changes to the file you uploaded, there are unfortunately a lot of things you have gotten wrong.

Do not use autoenter for your calculations unless you have to.

Aggegate functions needs to deal with set in order to give any meaning, these could be either records a relation away or stuff in repeating fields.

The need to create records at all is totally irrelevant in this template, since it's taken care of by the "...allow creation of related records" feature last in the chain of relations.

Your template needs an extra table containing both regular holidays as well as national ditto - only weekends are grayed out for the moment.

The autoformatting does indeed need some reworking, the feature to enter 24:00 in empty fields, gets in the way for the formatting of military time enties, I changed it deliberately since the mod of a timefield unfortuantely easy catching the entry wrongly.

Now the global field pointing at the records of a certain person, but this could instead be an unstored field, which get the value of this function:

http://www.filemaker.com/help/FunctionsRef-22.html

Making the provisions for each user only makes entries on their own behalf.

I suggest you study the changes made and then ask to each you don't quite get the idea of?

--sd

timeSDmodTotals.zip

[dodsonmd]

Soren,

Thanks for the changes. I'll get to work tring to understand what you've done. Many camels to digest as you say.

I have made a couple of changes to the file you uploaded, there are unfortunately a lot of things you have gotten wrong.

[color:green]The story of my life.....

Your template needs an extra table containing both regular holidays as well as national ditto - only weekends are grayed out for the moment.

I don't need to worry about holidays. In the world of emergency medicine, holidays have no meaning. We work all day, every day.

The need to create records at all is totally irrelevant in this template, since it's taken care of by the "...allow creation of related records" feature last in the chain of relations.

This seems to be a very sophisticated feature that is difficult for me to understand. It looks to the user as if there is only one record, he just changes the date and the time sheet layout appears. Very nice. So the previously input data is stored as a record for each day?

When I go to a new month, I need the scheduled hours fields to be empty to allow user to input. The # of scheduled hours will change every day and may be different for any given day. The field is locked somehow and will not allow modification. Is that part of the conditional formatting, where the formula say 'not Mod'? I don't want to change it and mess things up.

Thanks again, John

[Søren Dyhr]

So the previously input data is stored as a record for each day?

Indeed!

The # of scheduled hours will change every day and may be different for any given day. The field is locked somehow and will not allow modification.

I have of course taken the liberty to take something for granted, which obviously isn't. The good question is if the employee's then should be allowed to modify these? And if they shouldn't be allowed - how would individually agreed work hours on say Wednesdays get there?

--sd

[dodsonmd]

Thanks Søren,

The good question is if the employee's then should be allowed to modify these?

In fact the employee will be expected to input the scheduled hours. A drop down with #'s 1-12 would be sufficient. But once input it should not be edited. The hours in/out should also not be editable once it has been entered, except by admin. An entry in the 'sched hours' field should be mandatory if there is an entry for time in/out that day.

I placed such a drop-down in the 'sched hours' field, but I couldn't figure out how to keep that entry associated with a particular record. When I changed the date to another month, the same entries were still there. If I manually cleared them, it also removed them from the previous records.

I really appreciate your help.

John

[Søren Dyhr]

I placed such a drop-down in the 'sched hours' field, but I couldn't figure out how to keep that entry associated with a particular record. When I changed the date to another month, the same entries were still there. If I manually cleared them, it also removed them from the previous records.

Alright they need to go to the values table then, individual for each employee - could an idea be to have default work hour entered, which then could be modified? How would you deal with individual weekdays off then, say the person forgets to remove it from the list, and the entire overtime calc' goes wrong.

Since the record haven't been created before any entry in hours worked is it likely to cause errors as well. Well I think I have an idea here, how to accomplish it though ... it needs some tinkering to get it working - for which I must ...as gentle as possible squeeze in between my wifes eager holiday making effords.

The question is if scheduled hours always are the same from week to week, so monday usually is say 7h and thuesday is 4h etc. decripe how often these gets totally arbitrary? Could previous months pattern be recycled?

--sd

Edited July 23, 2008 by Guest

[dodsonmd]

Thanks so much for all your efforts.

Unfortunately for our solution, the scheduled hours are quite variable. Usually ranging from 6 to 12 hours, and the days worked may change from month to month. This is why I have to have each user input their own scheduled hours.

The scheduled hours field can be a simple number, not a time.

John

Edited July 23, 2008 by Guest