Time Format and Lookup

[mkohler28]

I have a two time fields which I'm trying to do two things: 1) lookup a value from a another record if available and 2) re-format the field based upon the data entered.

 

The fields are 'TimeFrom' and 'TimeTo'. I currently have the time fields set up as lookups with no validation. The users will enter the time in many different ways and my goal is to re-calc the format based upon what's entered as well as lookup a value if available. I am planning on having a second field that will also populate AM or PM based upon the results.

 

The different time formats entered will be look like the following:

 

1345 result 1:45 - result PM

13:45 desired result 1:45 - result PM

145 desired result 1:45 - result AM

1:45 desired result 1:45 - result AM

[Weetbicks]

if this is so they properly display on layouts, you can just set the formatting display of a field on a layout to the format you want, much simpler? select the field and go format -> time. from there you can setup how it looks..

 

I don't see any reason for actually making the contents of a time field to contain am/pm etc, 24hr time should be fine for storage.

[mkohler28]

I would prefer not to format the field because the users many times won't enter in the colons or AM/PM unless forced to, also most of the users will enter using the 24 hour time and do not enter in the colons, without them the 24 hour time does not compute correctly. So I'm looking for an auto-calc solution that will reformat the time regarless of how it's entered

[mkohler28]

Actually if you can tell me the best way to always add a ":" to the field if one does not exist (between hours and minutes), the time should be correct then no matter if they enter in exmaple: 1345 or 0145, this would make both examples end up 1:45 PM

[Weetbicks]

providing they always enter 4 digits into the field (ie 24hr time), you can set the time field to an auto-enter calculation:

 

if ( PatternCount ( TimeField ; ":" ) = 0 ;

 

Left ( TimeField ; 2 ) & ":" & Right ( TimeField ; 2 ) ; TimeField

 

)

 

- Basically this is triggered everytime the time field changes (ie entered), if there is no colon found, it inserts one between the 2nd and 3rd digit, otherwise it doesnt.

 

This is obviously only built for the ideal case where 4 digits is always entered, if someone enters "am" or "pm" its going to break. Hopefully this has at least given you an idea as to how you can reformat a field when it is modified, you can add to this calculation to ensure this only occurrs if the length of the field is 4, and handle other cases where it is not 4 correctly..

[mkohler28]

That worked, now I'm trying to lookup a value in the same calculation, how would you calc that in conjunction?

[Weetbicks]

Don't use a "Lookup" just grab the related field value in the calc.