2 Replies Latest reply on Mar 6, 2015 7:59 AM by raybaudi

    Calculate Variable

    Gilo

      Title

      Calculate Variable

      Post

      Hi

      I have a field which has the month and year (ex. 02/15)
      I need to search for all records from the month before, so I would like to set Field to 01/15. How can I script this? 

      thx in advance

        • 1. Re: Calculate Variable
          philmodjunk

          This is easier to do if you use a date field for your month and year. You can assign an arbitrary day such as 2/1/15 and use data formatting to hide the day value. But then it's simpler to specify the preceding month/year like this:

          SetVariable [$Criteria ; value: Month ( YourTable::DateField ) - 1 & "/" & Year ( YourTable::DateFIeld ) ]

          Assuming that you have a text field for this, the code to produce this value is more complex:

          SetVariable [$Criteria ; value: //put expression shown below here ]

          Let ( [ vList = Substitute ( YourTable::textField ; "/" ; ¶ ) ;
                 Mnth = GetValue ( vList ; 1 ) ;
                 PreMnth = If ( Mnth = 1 ; 12 ; Mnth - 1 ) ;
                 Yr = GetValue ( vList ; 2 ) ;
                  PreYr = If ( PreMnth = 1 ; Yr -1 ; Yr )
                 ] ;
                 PreMnth & "/" & PreYr
                ) // Let

          • 2. Re: Calculate Variable
            raybaudi

            Another calculation could be:

            Let([
            T = YourTable::YourTextField ;
            M = GetAsNumber ( Left ( T ; 2 ) ) ;
            Y = GetAsNumber ( Right ( T ; 2 ) )
            ];
            Case(
            M > 1 ; Right ( 0 & M - 1 ; 2 ) & "/" & Right ( 0 & Y ; 2 ) ;
            12 & "/" &  Right ( 0 & Y - 1 ; 2 )
            )
            )