4 Replies Latest reply on Jun 7, 2011 9:47 AM by philmodjunk

    Committing Field formatting to field/record



      Committing Field formatting to field/record


      When I set a time field to be formatted like "10:30 AM", i can enter "10:30am" and it will display as "10:30 AM".  But when I click on that field/record or reference it in another calculation, it shows me what i initially typed("10:30am") and not the properly formatted version ("10:30 AM").  I want the record to commit with the formatting i assign it in the layout.

      The same thing happens with money.  I set the formatting to display "$1,000", I can type in "1000" and it will display properly there but i cannot get the record/field to commit that way for proper displaying in other places.  

      I get the feeling in need to make some sort of calculation attached to the table/field to make this work.

      Thank you in advance for your time and help.

        • 1. Re: Committing Field formatting to field/record
          Is there any way to do this?
          • 2. Re: Committing Field formatting to field/record

            Can you explain in more detail why you want this?

            The data formatting you specify in the inspector applies its settings only on the layout where you specify it. It does not apply any changes directly to the data as you have found. Normally, you want to store a dollar amount as a number, but can always format it on the layout where you display it. The format should not affect any calculaitons which would treat this value as a number whether it has the formatting characters or not.

            Likewise, time and timestamp fields store their values in terms of elapsed seconds. Formatting text such as AM, PM, will not affect how it evaluates in a calculation and you can select the time format you want when you display this value on a layout.

            That's not to say you can't do this, it just looks like something that requires a fairly complex solution without any real need to do so--but when you explain why you want this, I may change my mind.

            • 3. Re: Committing Field formatting to field/record

              I use scripts to generate emails for my employees that have schedules/tasks and other info in them.  When the times, dates and dollar amounts are pulled from the database for these emails, they are not formatted properly due to inconsistencies in the data entry.

              • 4. Re: Committing Field formatting to field/record

                For time, I'd use a text calculation such as this either in a calculated field, custom function or included in the calculation where you build your email text:

                Let ( [ H = Hour ( TimeField ) ;
                          Mr = If ( H > 12 ; "PM" ; "AM" ) ;
                          H2 = If ( H > 12 ; H - 12 ; H ) ;
                          M = Right ( "0" & Minute ( TimeField ) ; 2 )
                        ] ;
                        H2 & ":" & M  & " " & Mr )

                For formatted currency, I'd use something like:

                Let ( [ th = Div ( DollarField ; 1000 ) ;
                           th2 = If ( th > 0 ; th & "," ) ;
                           d = Right ( "0" & ( DollarField- Int ( DollarField) ) * 100  ; 2 ) ;
                           h = Right ( "00" & Mod ( Int ( DollarField) ; 1000 ) ; 3 )
                         "$" & th2 & h & "." & d )

                Note that this expression will not format correctly for values of less than $1.00.