Portal Sort Automation?

[Jana]

Can anyone please help me sort this out. I've got two related databases (Time and Time Line)...Time creates a daily time sheet for each employee and the related Time Line database records are generated via a portal in the TIme database for each time increment related to a specific job number.

My challenge is that I need to have the portal rows sort by Begin time automatically when an employee enters a new portal line that could be for a time increment that's earlier than one that's already been entered. Ie. if the portal records are created in the following order, then I want it to automatically sort the 7:00a Begin time before the 9:00a Begin time.

9:00a - 10:30a

10:30a - 12:30p

7:00a - 8:15a

I've set the relationship for the portal to have a sort order using the Begin time. (Begin time is a field which calculates TextToTime from the Hour, Minutes and AM/PM)

But the only way to get the portal to resort is to open the Define Relationships, select Edit and then say Done.

That does get the portal line items to sort properly. How can I get it to happen automatically as the user enters thier line items?

Thanks

[Ender]

Why not use time fields for data entry?

[Jana]

I struggled a lot trying to figure out how to get my time calculations to work so I could make a calculation of End Time minus Begin Time for each line item, which wouldn't then total up and show the user a final total of Hours and Minutes for the timesheet. I used TextToTime and TimeToText conversions which finally ended up working. I'm sure there was a more efficient way, but everying I tried, except this, would work.The users have drop down menu's available for their hour and minutes and am/pm; which makes thier data entry accurate and easy. But the actual sort fields I based my relationship on are Time Conversion fields (for example 8:00 am was converted to a field which reflects 28800 as the time.

Is this affecting my ability to have my portal sort automatically; if so how? and what should I do to correct it?

[Ugo DI LUCA]

It's not clear what field you're currently using as your sort key.

Is it the TextToTime field or the TimeToText ?

Also, from your example, I'm unsure you'd sort based on the begin time or 10:30 am should be your first record rather than 9:00 am...

Just in case, as opposed to a List, portals can be sorted based upon fields that don't currently show in the portal.

Finally, are your calcs stored ?

[Ender]

If you are sorting by a text field, then 10:30 would show up before 9:00 because it's sorted by characters. Using time fields would be better.

In my timecard solution, users enter a Start Time and End Time. Because they are time fields, the database will make sure users enter valid times. No pull down menus--if someone wants to enter 9:05 - 9:42, this will be allowed. But this is no problem as Hours Worked can be calculated easily with a calc End Time - Start Time.

[Vaughan]

"Hours Worked can be calculated easily with a calc End Time - Start Time"

So what happens when somebody does overtime and works to 1:00 am?

You need to keep track of start and finish DATE as well as time.

[Ender]

Good point, though it's not important for my situation. No one works that late.

[Vaughan]

One day somebody will. Plan for it now.