Simple date field question

[Søren Dyhr]

This is why I have to have each user input their own scheduled hours.

So hopefully does this mean that overtime is calculated for each day, and not what a month ideally should be of work-hours?

--sd

[dodsonmd]

So hopefully does this mean that overtime is calculated for each day, and not what a month ideally should be of work-hours?

I guess you could calculate it for each shift worked. But the only result I need is the number of hours worked compared to the number of hours scheduled, summarized on a monthly report.

Thanks, John

[Søren Dyhr]

Suits me fine, the trade off is that a default value will pop up, only when the hours worked have been entered, perhaps the tab-order should be opposite to provide an obvious flow?

I will tinker with this approach tomorrow!

--sd

[dodsonmd]

a default value will pop up

Maybe the default entry could be 0, and the user would have to change the value? I only need the total for that particular user, and to know how much overtime the user worked in that month. But best to have the scheduled hours remain in the record for future review.

I'm beginning to understand how this is working. I see no record is created until entries have been made into the time in/out fields. But when I create a new entry form, with a new month for which no records exist, the previously entered scheduled hours remain in the form, and are totaled in TotalHoursSched field. This is confusing.

Is it possible to create a script button that would clear those fields at the time the form is created? I left a blank at the top of the drop-down list, but that requires user to clear each line individually.

How can I change the entries on the drop-down to not show the seconds? Or is the drop-down not a good idea?

I re-arranged the columns, and the tab order to flow more easily.

I think we're getting close to something very useable here, I could never have down it without your help.

Thanks, John

timeSDSchedHrs.fp7.zip

[Søren Dyhr]

But when I create a new entry form, with a new month for which no records exist, the previously entered scheduled hours remain in the form, and are totaled in TotalHoursSched field. This is confusing.

It's because you have forgotten that I said the field needs to move back to the Values table.

Well I couldn't sleep so I got up and changed the latest file, to include the things we just have been debating!

--sd

timeSDSchedHrsMod.zip

Edited July 24, 2008 by Guest

[dodsonmd]

Søren,

I can't thank you enough for your help. It looks marvelous so far. I need to spend some time to understand what you've shown me.

This forum has been absolutely super for me. My meager understanding of FM has expanded far enough now for me to realize how much more I need to learn. Great experience.

Cheers, John

[Søren Dyhr]

Thanks for encouraging words, when you get thru on the other side of the digestion of the approach, would I guess your reporting should be a bit more sophisticated ... lets take it sometime, next week - becasue the wife and I are off on an extended weekend in the UK.

--sd

[dodsonmd]

Have a nice holiday.

John

[dodsonmd]

Soren,

Thanks to your help I’ve made tremendous progress. Getting real close to a usable solution. Sorry for the long post, but hopefully not much more to go.

First the easy stuff: Here’s my recent changes.

Finaized layout for the entry form.

Added dropdown menu to name field for ease of use.

Created some user accounts with exclusive privileges, which seem to work correctly. Allowing viewing and editing of only their own records. Created one record for each provider (user) to allow for privilege account construction.

Temporary password for all: 1234

Now for some more complex questions: (at least for me)

If I understand correctly, the ‘Record ID’, although not shown anywhere, is generated when a record is created, and the Case calculation in the ‘WithinSameMonth’ field ties the record to the month shown in the gBaseDate field. The one-to-one relationship between Placeholder and PlaceholderFiltered TO’s limits the display of records to only those associated with a certain month and employee.

Question How did you create the Placeholder TO? I see it is a variation of the Pling table, but without some of the fields? But it’s not in the Tables pane?

Now I need to sub-divide the employees (providers) into two groups. ‘Physicians’ and ‘midlevels’ to allow summary report creation for each type of provider. I placed this field in the Values table and named it ‘Designation’. Once an individual is labeled with a designation, that should appear on every record for that person, to allow for sorting and reporting.

Question: How do I make that association permanent? Should that be a global field? Maybe it should be in the Placeholder table or maybe an fk_ in the Values table?

LOG IN ISSUES

When a user first logs in, I would like the file to open to the last viewed record for that user.

Question: Should there be a script that runs when the file is opened? I see there is choice under FIle->File Options menu to designate a script, but I’m not sure how to construct it? I have a script ‘Open to Account Name’ that seems to work. Is this the best construction for this script? The other users are still visible, but their entry forms are blanked out. Any way to make the other users not visible at all?

Question: Can a script be attached to a value list so that when a selection is made, the field is automatically set, the value list closed, and does not require the user to click outside the field? Maybe the tab moves to the next open field when the page opens?

Question: Is it possible to make the opening login dialog remain blank, it always shows the computer name or admin in the account name box? NIce if there is a way, but not a big deal.

When a user has finished entering his work times for the day they should close the file. I created a button to close the page.

Question: Is there a way to prevent any future editing of the timein/out fields once the entry form has been closed? I want any time changes/edits to require admin approval, to prevent a user from returning to the record and changing it.

REPORTS

I need two types of reports:

1. For each provider that includes all summary hours worked/scheduled for all months. For example, to make report of Hours Worked/Scheduled over past 12 months.

2. For each month that includes all providers, hours worked, hours scheduled, % over.

I need to read some more about summaries, and sub-summaries. And then I can start working on how to share file via the web.

Thanks for the help, John

TimeShts_Tan_Designation.fp7.zip

[dodsonmd]

Søren, or any interested observer,

Could you take a look at this file. I re-created it on my computer, seemed the only way to remove the Danish date/time formats so I could publish on the web.

Problem is the portal fields don't show up correctly. Don't show up at all? I've spent some hours trying figure this out, and I'm lost.

Thanks, John

TimeSheetsReDone.fp7.zip

[Søren Dyhr]

Søren, or any interested observer,

Could you take a look at this file. I re-created it on my computer, seemed the only way to remove the Danish date/time formats so I could publish on the web.

Problem is the portal fields don't show up correctly. Don't show up at all? I've spent some hours trying figure this out, and I'm lost.

Thanks, John

Hi John, you could use the above settings to prevent the user carries on using my mode ... the stuff to select between in the lower half of the dialog.

--sd

[dodsonmd]

Hi Søren,

I already had that setting checked. That way it read fine on my computer, but when I turned on Instant Web Publishing and opened the file in a browser, all the dates and days of the week were in Danish no matter what setting I used in FM itself. I'm not sure of the technicalities of the IWP process, but File Options settings did not transfer over.

Anyway, I remade the file, but the fields in the portal in the entry form layout won't appear, so I obviously missed something. I attached the redone file to my previous post, it looks like all the settings and functions are exactly like your file, just created on my computer with native English formats embedded. But something's messed up with the portal fields.

Thanks again, John

[Søren Dyhr]

Remember that the portal not is showing records from the first relational step but the second, hence the use of field names pre-fixed with details from that point of view.

I will make some inquireries to what might be happening here!

--sd

[dodsonmd]

Søren,

Maybe to make it a little easier for you to see what I've done, here is the file newly redone in a simple fashion. I'm surely doing something wrong with the portal. It looks fine in layout view, exactly like the file you made, but in form view nothing is visible. I'm completely baffled, and I've spent way too many hours trying to solve the mystery.

Thanks for your help.

John

TimShtsReDoAgain.fp7.zip

[Søren Dyhr]

It looks fine in layout view, exactly like the file you made, but in form view nothing is visible. I'm completely baffled, and I've spent way too many hours trying to solve the mystery.

Take a look at the first image, you need to prepopulate 31 records as placeholder/slots - before anything will work, the dates is done by calc'fields....

Take a look at the second image, you need to allow the creation of related records in the last branch of the relational graph.

Then is the next thing you need to do, to format each field to show the needed custom formatting ... this is in particular in the time and date fields ... keeping in mind that the hours scheduled is a number field and not a time, so the formatting needs to be done like currency

:00

This might even be the issue here, you should poke into each of the fields and choose the way it's presented ... if you somehow can't alter it, which I think you can ... could it always be done with brute force?

--sd

[Søren Dyhr]

Due to attachments restrictions is here the file with the changes made.

--sd

TimShtsReDoAgainMod.zip

[dodsonmd]

Søren,

Thanks again. Excellent help. I'll get to work on it now.

I had already done everything you mention, with exception of the placeholder records. I figured it had something to do with records, as the RecordID in your original file was at #34. But I couldn't find any specific records, and when I recreated the file, I did not make any new records. I understand now why 'comment' called your Pling table Slots.

Question: Once those records have been created and the file populated with my providers records, can I delete the placeholder files without messing things up again?

Question: Another thing I had noticed in the Instant Web Publishing, (before being distracted by the Danish format issue), was the ability to enter data into the time in/out fields with file opened through a browser. Those fields should translate correctly into the html code and remain editable, is that your understanding?

You need to teach me how to say (write) Thank You in Danish.

John

[Søren Dyhr]

Question: Once those records have been created and the file populated with my providers records, can I delete the placeholder files without messing things up again?

No they have to be there at all times!

Those fields should translate correctly into the html code and remain editable, is that your understanding?

You need to explain this, as it is am I not getting the meaning of it! Could I persuade make a quick snappie (screendump) to show where it is??

--sd

[dodsonmd]

Søren,

I'll work on the web view a little later.

Somehow I missed how we had the portal show the correct number of days for the month. For example, Sept with 30 day, Oct with 31, etc. I've looked at all the calcs but something is awry.

John

[dodsonmd]

Søren,

Never mind, I found the problem. I had the last relationship with the gBaseDate instead of the ShownDate. It looks fine now.

Now to try to get this into the browser. I'm very grateful for your help. I think I'm once again close to a useable solution.

John

[dodsonmd]

One more little thing. In my previous file, I had a job type designation, physician or midlevel, with a unique assignment to each provider. I would like that designation, once assigned, to always accompany that provider for each record without having to be re-entered. Would this be another global field?

John

It looks like I'm talking to myself online.

Edited July 31, 2008 by Guest

[Søren Dyhr]

It looks like I'm talking to myself online.

No - I read what you write!

Would this be another global field?

No - this means that the persons should be broken out in thier own table as well, with all their details!

--sd

Edited July 31, 2008 by Guest

[dodsonmd]

Søren, I'm kidding, I know you read what I write and I'm very grateful.

No - this means that the persons should be broken out in thier own table as well, with all their details!

That sounds way too complicated to create for just a single field. Perhaps I could have in the entry form a field for physician, another for midlevel. And if the two fields were mutually exclusive, then I should be able to sort based on that designation, and the provider would only have to populate that field when a new month is created.

What do think?

John

[Søren Dyhr]

I humbly dissagree, it could easily be a shortcut you later will regret you have done, think how handy such kind of structure would be in the selection of records you report upon, until now have only two selections been posible the unique person and all. But serious reporting might even have some faint hopes to group on jobs descriptions as well....

--sd

[dodsonmd]

Now to get back to a couple simple questions I had earlier.

Question: In the gEmployee field , if I enter the field with the cursor and then use the drop down list, I get two names in the list. How can I avoid that? It places the name I selected together with the name already in the field.

Question: Once I get all providers user accounts set up, I need to find a way to prevent changes to the file, even by the user, after they have entered times and logged out. In other words, they cannot edit times once they have been committed and saved.

Thanks so much, John

[Søren Dyhr]

The gEmployee is not something which should remain in the system, it needs to be a calc'field instead which gets it's data from the logging in....

--sd

[dodsonmd]

Søren,

I humbly dissagree, it could easily be a shortcut you later will regret you have done, think how handy such kind of structure would be in the selection of records you report upon, until now have only two selections been posible the unique person and all. But serious reporting might even have some faint hopes to group on jobs descriptions as well....

Point well taken. If I create a table for each provider, sort of a contact list, how do I structure the relationship table. I will send you a copy of the file by private email.

John

[dodsonmd]

The gEmployee is not something which should remain in the system, it needs to be a calc'field instead which gets it's data from the logging in....

You're right, the double names only happened when logged in as admin.

John

[Ballycroy]

In your example file how would I modify the Calc Field "WithinSameMonth"

so that it only shows the first 15 days or days after the 15. For example (1-15) or (16 to end of month) depending on date selected.

I tried this, but I must have done something wrong as it does not seem to work.

Case ( RecordID ≥ 15 and Month ( gBaseDate ) = Month ( ShownDate ); RecordID; RecordID ≤ 15 and Month ( gBaseDate ) = Month ( ShownDate ); RecordID; "" )

Edited January 20, 2009 by Guest

[aldipalo]

I didn't put this example together Soren did. I'm not sure if, in 8.5, you can name objects (See my signature) :

Take a look at the attached. If this could work for you try it. If 8.5 has an object naming capability then I would place one portal below the other in a tab, name the tab and use your formula to go to that tab.

I'm sure Soren, if he reads this post, will have a more elegant way to do it.

hth

TimShtsReDoAgainMod_1-20-09.zip

[Ballycroy]

Thanks and apologies to Soren.

Interesting idea using two portals.

Just out of curiosity can any let me know why the calculation in my previous post (#315136) does not work.

[aldipalo]

I'm not that great with complex calculations. Let's hope someone else can answer your question.

However, I don't see how that calculation would give you any result within a portal. I would think your approach should be through a find in a script.

[Ballycroy]

Even on the layout it does not work correctly.

TestTimeSheet.fp7.zip

[Ballycroy]

Ok, I believe I have solved it with the following Calc. in the WithinSameMonth field.:

Case ( Day ( gBaseDate ) ≤ 15 and Day ( ShowDate ) ≤ 15 and Month ( gBaseDate ) = Month ( ShowDate ) ; RecordID;

Day ( gBaseDate ) > 15 and Day ( ShowDate ) > 15 and Month ( gBaseDate ) = Month ( ShowDate ); RecordID; "" )