8 Replies Latest reply on Feb 23, 2012 11:43 AM by philmodjunk

    Formatting different types of data in one field

    ultranix

      Title

      Formatting different types of data in one field

      Post

      I have a calculation field in which I mix Date and Amount of money.

      If I that same date and amount of money in a merge field, i.e. <<date>> | <<amount_of_money>>, then i'm able to give 2012-02-21 date format instead of 2/21/2012 and 1.350,00 Lt. instead of 1350 Lt. for money.

      However, if use calculation field with the formula date & " | " & amount_of_money, then, when i use that field in a type of merge field alone (i.e. <<calculation>>), i'm not able to format neither date, nor amount of money (or decimal).

      As for date, i could get the desired effect, if i put it like this year(date) & "-" & month(date) & "-" & day (date).

      But how do i get thousand separator and fixed 2 decimals?

      I'm sure there's a solution to this.

        • 1. Re: Formatting different types of data in one field
          philmodjunk

          I have a calculation field in which I mix Date and Amount of money.

          Why would you do that? the best solution to this issue is to not put two kinds of data into the same field. This causes more problems for you data than just the formatting issues.

          You are on the right track for how such a field would need to format the data with your date calculation. If you add in this expression for the number:

          Let ( m = amount_of_money ; int ( m ) & "," & Left ( ( m - int ( m ) ) * 100 & "00" ; 2 ) )

          But I don't recommend combining the data like this unless you have no alternative--such as in the body of an email.

          • 2. Re: Formatting different types of data in one field
            ultranix

            It's only for the display purposes, but as it goes throughout different layouts and it has button function, i don't want to update patterns and then re-link the script, so i just combine data into one field instead, so i could easily modify it in one place and it is showed in all other places with correct links to scripts everywhere else.

            • 3. Re: Formatting different types of data in one field
              ultranix

              as for "Let ( m = amount_of_money ; int ( m ) & "," & Left ( ( m - int ( m ) ) * 100 & "00" ; 2 ) )"

              It works for fixed 2 decimals, but it doesn't add . (thousand separator)

              I want to have it like that 1.353,00 (instead i get 1353,00).

              • 4. Re: Formatting different types of data in one field
                philmodjunk

                Is there an upper limit to the value that might appear in this field?

                We can add code for the Thousands separator, but then we will still have an issue if values can be a million or larger.

                Let ( [ m = amount_of_money ;
                           IntM = int ( m ) ;
                           MemDec = IntM & "," & Left ( ( m - IntM ) * 100 & "00" ; 2 ) ;
                           L = Length (MemDec) ]  ;
                           Case ( L > 6 ; Left ( MemDec ; L - 6 ) & "." & Right ( MemDec ; 6 ) ;
                                                   MemDec )
                      )

                • 5. Re: Formatting different types of data in one field
                  ultranix

                  as it's going to be used for personal finances, i'm highly doubt that i'm going to reach million-level income very soon, so i confirm it works and move on to the next issue.

                  Another issue: I have variable $Name, which is equal to one of the fields f_1; f_2; f_3; f_4; f_5; f_6; f_7; f_8. As that variable is formed according to the mouse positions in a calculated field, i doubt there's a way to extract field name that is included in the calculation. So is there an easier way than

                  If ($Name = f_1)

                  Perform script 1

                  Else If ($Name = f_2)

                  Perform script 2

                  Else If ($Name = f_3)

                  Perform script 3

                  and so on...

                   

                  Those f_x fields are global fields.

                  • 6. Re: Formatting different types of data in one field
                    philmodjunk

                    Explain this part in more detail:

                    As that variable is formed according to the mouse positions in a calculated field,

                    • 7. Re: Formatting different types of data in one field
                      ultranix

                       I used method u described in go-to-profile of one-row display of Birthdays: http://forums.filemaker.com/posts/28eb32d12f?post=28eb32d12f&start=11

                      So, I use f_total, which is calculation field (f_1 & " | " & f_2 & " | " etc.)

                      f_1; f_2; f_3 and etc. are again global text fields.

                      Then i use modified script, which can be found in the link above and it is run throughout script trigger (onobjectenter)

                      It is able to grasp text in the fields, so, if i could get field name of the pointed/clicked, then i could run corresponding script.

                      Basically, idea is like this:

                      Database: f_1 = About; f_2 = Invoices; f_3 = Main; f_total (f_1 & " | " & f_2 & " | " f_3)

                      Interface:

                      About | Invoices | Main

                      When i click pointing on About (which is f_1 field), first of all it performs script via script trigger (onobjectenter) and after it grasps $Name, I in the same script use if $Name = f_1, run script f_1, else if $name = f_2, run script f_2, else if $name = f_3, run script f_3.

                      So the question is how to get rid of that if $name = f_x, run script f_x and minimize the script.

                      • 8. Re: Formatting different types of data in one field
                        philmodjunk

                        I see no way to avoid stacking up If blocks like you have without changing the layout design. If you could display the data as individual fields or records--such as in a horizontal portal, then clicking one can be turned into a button click and you wouldn't have to parse the text like this.