0 Replies Latest reply: Feb 4, 2013 11:51 AM by Marc Livingston RSS

    Pulling a line out of a log field.

      I am trying to pull a certian line out of a log field. Currently I have a very very long expression that seems to only work if I have 1 record selected. I am trying to do this in the script level so that this new line can be a selectable field.

       

      Example of Log (Red is the part I am trying to pull):

      Fernando 00:13:37

      Released: no -> yes

       

       

      Example 2:

      Narender 10:16:34

      Engineered: no -> yes

      Released: no -> yes

       

      Marek    13:19:39

      Engineered: yes -> no

      Released: yes -> no

       

      Marek    13:21:40

      Engineered: no -> yes

      Released: no -> yes

       

      Example 3:

      Andyk08:09:14

      Released: no -> yes

       

      Sergio   09:11:03

      Released: yes -> no

       

      Sergio   09:12:00

      Released: no -> yes

       

      The basic rule is that I want the name related to the last section that contains the text 'Released: no -> yes'

      As I said I have gotten this to work in a text object if the record is selected. This is not working on the script level, or if I copy the expression into a list box.

       

      Here is the expression that I use in the text object/list box:

      if(right(Replace(SubField (trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ') ,Chr(10),''),1)=':' , 
      Replace(SubField (if( right(Replace(SubField(
      (trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )))
      , ' ') ,Chr(10),''),1)=':' , trim(right(
      if(right(Replace(SubField(
       trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) ))
      , ' ') ,Chr(10),''),1)=':' , left(
      if(right(Replace(SubField (
      trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ',1 ) 
      ,Chr(10),''),1)=':' , left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)),
      index(trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)),Chr(10),-1) -3)),
      len(if(right(Replace(SubField (
      trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ') ,Chr(10),''),1)=':' , 
      left(if(right(Replace(SubField (
      trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ',1 ) 
      ,Chr(10),''),1)=':' , left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)) ,
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))
      ,Chr(10),-1)-3)))-
      Index(if(right(Replace(SubField (
      trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) ))
       , ' ') ,Chr(10),''),1)=':' , left(
      if(right(Replace(SubField (
      trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ',1 ) 
      ,Chr(10),''),1)=':' , left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)),
      index(trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))
      ,Chr(10),-1)-3)),chr(10),-1)))), ' ') ,Chr(10),'') ,
       Replace(SubField (trim(right(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2),
      len(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(log,Index (log, 'Released: no -> yes',-1)-1),len(left(log,Index (log, 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ') ,Chr(10),''))
      

       

       

      And here is the expression I am using in the script (Table is resident loaded and is using group by company and job number, I want only the most recent log returned):

      if(right(Replace(SubField (trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ') ,Chr(10),''),1)=':' , 
      Replace(SubField (if( right(Replace(SubField(
      (trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )))
      , ' ') ,Chr(10),''),1)=':' , trim(right(
      if(right(Replace(SubField(
       trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) ))
      , ' ') ,Chr(10),''),1)=':' , left(
      if(right(Replace(SubField (
      trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ',1 ) 
      ,Chr(10),''),1)=':' , left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)),
      index(trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)),Chr(10),-1) -3)),
      len(if(right(Replace(SubField (
      trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ') ,Chr(10),''),1)=':' , 
      left(if(right(Replace(SubField (
      trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ',1 ) 
      ,Chr(10),''),1)=':' , left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)) ,
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))
      ,Chr(10),-1)-3)))-
      Index(if(right(Replace(SubField (
      trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) ))
       , ' ') ,Chr(10),''),1)=':' , left(
      if(right(Replace(SubField (
      trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ',1 ) 
      ,Chr(10),''),1)=':' , left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)),
      index(trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))
      ,Chr(10),-1)-3)),chr(10),-1)))), ' ') ,Chr(10),'') ,
       Replace(SubField (trim(right(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2),
      len(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2))-
      index (trim(left(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1),len(left(firstsortedvalue(logtext,(date(datestamp))),Index (firstsortedvalue(logtext,(date(datestamp))), 'Released: no -> yes',-1)-1))-2)), Chr(10),-1) )), ' ') ,Chr(10),''))
      as 'Released By',
      

       

      Can anyone help me?