7 Replies Latest reply on Sep 12, 2014 11:18 AM by Jason_Farnsworth

    Tagging records within given timeframes

    Jason_Farnsworth

      Hello all,

       

      I have an idea that I am trying to implement but can't seem to get a focused direction. I will explain the concept and then what I have established to help in getting the full picture.

       

      I would like to cull out records that have like information for (3) consecutive weeks in a row. There is one record per week generated, and there could be 20 weeks or more of records created. I would like to look at all the records and look for patterns of fields that have the same information for (3) weeks in a row. Once I have found the pattern I would like to tag them which should be easy enough.

       

      I am working with (1) table in which all records are stored in.

       

      This is and inspection table (OBI) where information is stored with respects to that weeks inspections for each JOBID.

      This inspection has (7) lineitems and each lineitem is rated from 1 - 5

       

      If any (1) of the (7) lineitems fails (or scores 3 - 5) for (3) weeks in a row I need to flag this record (JOBID) to be dealt with separately.

       

      I am looking for ideas on how to make this as dynamic a possible, please don't be shy.

       

      Jason Farnsworth

      Midland, TX

        • 1. Re: Tagging records within given timeframes
          erolst

          Jason_Farnsworth wrote:

          I am working with (1) table in which all records are stored in.

           

          Actually, you seem to have (2) tables: a Jobs table, and a line items table.

           

          Off the top of my head:

           

          Let ( [

            checkValues = List ( "0+0+0+" ; "3+3+3+" ; "4+4+4+" ; "5+5+5+" ) ;

            relatedValues = List ( 0 ; 2 ; 4 ; 3 ; 2 ; 3 ; 2 ) ; // that would be a list of related line item values, sorted by date

            listToTest = Substitute ( relatedValues ; ¶ ; "+" ) & "+"

            ] ;

            PatternCount ( listToTest ; GetValue ( checkValues ; 1 ) )

            or

            PatternCount ( listToTest ; GetValue ( checkValues ; 2 ) )

            or

            PatternCount ( listToTest ; GetValue ( checkValues ; 3 ) )

            or

            PatternCount ( listToTest ; GetValue ( checkValues ; 4 ) )

          )

           

          To make this truly dynamic (different arguments for check values, frequency etc), you need a script, or a recursive CF, or maybe pass this to an engine that can use regular expressions (Shell, SQL (?))

          • 2. Re: Tagging records within given timeframes
            Jason_Farnsworth

            How are you collecting the related values over several records?

             

            Is this a Function command? Are you scrolling the found set and recording via varibles?

             

            Jason Farnsworth,

            Midland, TX

            • 3. Re: Tagging records within given timeframes
              erolst

              Jason_Farnsworth wrote:

               

              How are you collecting the related values over several records?

               

              By using the List() function.

              • 4. Re: Tagging records within given timeframes
                Jason_Farnsworth

                Ok go out find the group of records

                Sort them by Date

                 

                Then

                 

                As such?

                 

                Let ( [

                  checkValues = List ( "0+0+0" ; "3+3+3" ; "4+4+4" ; "5+5+5" ) ;

                  relatedValues = List (CNST_OBI_FAILS::Erosion_Protection) ; // that would be a list of related line item values, sorted by date

                  listToTest = Substitute ( relatedValues ; ¶ ; "+" )

                  ] ;

                  PatternCount ( listToTest ; GetValue ( checkValues ; 1 ) )

                  or

                  PatternCount ( listToTest ; GetValue ( checkValues ; 2 ) )

                  or

                  PatternCount ( listToTest ; GetValue ( checkValues ; 3 ) )

                  or

                  PatternCount ( listToTest ; GetValue ( checkValues ; 4 ) )

                )

                • 5. Re: Tagging records within given timeframes
                  erolst

                  Yes; the only thing you need to do is insert your own list into the calculation.

                   

                  Please be aware that the idea is sound, but the execution sucked the first time around:

                   

                  1) no need for a fancy delimiter; ¶ is OK, since we can't protect it anyway (so then we cannot store the checklists in a list … oh well!), and

                  2. the lists need to be wrapped into the delimiter at both ends, because otherwise “2¶2¶25“ would return a false positive when checked for "2¶2¶2"

                   

                  So try

                   

                  Let ( [

                    listToTest = ¶ & List (CNST_OBI_FAILS::Erosion_Protection) & ¶ ;

                    ] ;

                    PatternCount ( listToTest ; "¶0¶0¶0¶" )

                    or

                    PatternCount ( listToTest ; "¶3¶3¶3¶" )

                    or

                    PatternCount ( listToTest ; "¶4¶4¶4¶" )

                    or

                    PatternCount ( listToTest ; "¶5¶5¶5¶" )

                  )

                  • 6. Re: Tagging records within given timeframes
                    erolst

                    Jason_Farnsworth wrote:

                    Then

                     

                    As such?

                     

                    … and here, just for fun, is the recursive version that allows for more flexibility. Not 100% tested, but looks nice …

                    • 7. Re: Tagging records within given timeframes
                      Jason_Farnsworth

                      Thanks man you rock!

                       

                      Got it

                       

                      Jason Farnsworth

                      Midland, TX