3 Replies Latest reply on Jun 3, 2014 9:08 AM by philmodjunk

    Filtering Data/Splitting Strings (Need Help)

    AnthonyPaliseno

      Title

      Filtering Data/Splitting Strings (Need Help)

      Post


           I have a list of items that look like this


           CLT/06/02-11
           MMH/03/07-14
           CLT/07/25-13
           CLT/09/40-14


           06 and 03 are months and 11 and 14 are years (2011, 2014)

           I need to make a field  or just list the objects where the month is janruary and year 2014
            

        • 1. Re: Filtering Data/Splitting Strings (Need Help)
          philmodjunk

               In this "list of items", Is what you have posted all text in a single field of a single record or does this list represent 4 different records where each item is the data in a different record?

          • 2. Re: Filtering Data/Splitting Strings (Need Help)
            AnthonyPaliseno

                 Each item in that list is a different record

                  

                 Ultimately im going to have a List Layout I believe

                 spanning across the months:

                 jan14 - feb14 - mar - apr- may - jun ....

                 And list the records (that I posted above) that are for each respective month (and year)

                 jan13 - feb13 ...

            • 3. Re: Filtering Data/Splitting Strings (Need Help)
              philmodjunk

                   The key detail that I needed to nail down was that there is only one such entry per record, not a list of multiple entries as that complicates the calculation.

                   But you haven't identified what the number just to the left of the hyphen represents. I will assume that it is a value to be ignored.

                   I'm posting two options. Either can be defined as a calculation field. The first would use Text as the result type. The second needs Date as the result type:

                   Let ( [ TheText = Substitute ( YourTable::YourField ; ["/" ; ¶] ; ["-" ; ¶ ] );
                             M = GetAsNumber ( GetValue ( TheText ; 2 ) ) ;
                             Y = 2000 + GetValue ( TheText ; 4 ) ;
                             Mname = MonthName ( Date ( M ; 1 ; Y ) )
                           ] ;
                           Mname & ", " & Y
                          )
                            

                   Let ( [ TheText = Substitute ( YourTable::YourField ; ["/" ; ¶] ; ["-" ; ¶ ] );
                             M = GetAsNumber ( GetValue ( TheText ; 2 ) ) ;
                             Y = 2000 + GetValue ( TheText ; 4 )
                           ] ;
                           Date ( M ; 1 ; Y )
                          )
                           

                   You could perform a find on either field to find the records for January, 2014, but the second option allows you to also sort records on this field and get an order matching the chronological order of these date/year combinations. (and you can use data formatting to show exactly the same month name and year returned by the first option.)