Filter and Order a TPS table on AutoGuid by most Recent Date in code

1

Hi,
I have a TPS table which is keyed on AutoGuid. In the table is a ServiceDate ( long). On a browse, I filter it by AutoGuid and sort it descending on Service Date so that the most recent are at the top.
So, what I want to do is find the MOST RECENT service request for an AutoGuid in code.
The question then is how to code this - my first thought was to loop by AutoGuid and then do some manipulation to find the most recent one.

 JSRV:Auto_GUID = loc:autoGUID
    set(J_SRVREQ,JSRV:FK_AUTO)
    loop while Access:J_SRVREQ.next() = Level:Benign
        if JSRV:Auto_GUID = loc:autoGUID ! good match
            ! check dates here
        ELSE
            BREAK
        END
    END

I could make a new key with AutoGuid and Descending Service Date fields.
However, before I do this, I wondered if I could sort the result set by date descending with a Sort statement - that way the most recent would be the first record in the set? Not sure how this might be coded?
Can folks supply some code and insight?
Thanks,
Ron

2

I’d strongly recommend a VIEW.

SrvReqView VIEW(J_SrvReq)
  END
  CODE
  SrvReqView{Prop:Filter} = 'JSRV:Auto_GUID = <39>' & loc:AutoGUID & '<39>'
  SrvReqView{Prop:order} = '-JSRV:ServiceDate'
  SET(SrvReqView)
  LOOP 
     NEXT(SrvReqView)
     CASE ERRORCODE()
     Of NoError
     Of BadRecErr
        break
     ELSE
        ! Error - display or log message
        break
     END
     !First returned row is your most recent date
     !Do whatever you need
  END

Of course, J_SrvReq table needs to be open and the view needs to be opened, too.

3

THanks Rick,
I thought about using a VIEW but I haven’t coded views so the code looks like it will do what I need it to do.
I will give it a try.
Ron

4

You could just set the date 1 higher than you want and do a .previous() instead.

5

I think his post implies he does Not have a key with Date:

I could make a new key with AutoGuid and Descending Service Date fields.

I am wrong, at the top he says for the Browse he has the Key GUID + Date Ascending, so Sean is correct use Previous.

Rather than “set the date 1 higher than you want” you can set it to the highest possible value with CLEAR( ,1). This is a common pattern.

  CLEAR(JSRV:Record, 1)      ! +1=Clear all fields as High for Previous in case fields added to key
  CLEAR(JSRV:ServiceDate, 1) ! +1=Clear High just Date
  JSRV:Auto_GUID = loc:autoGUID
! set(J_SRVREQ , JSRV:FK_AUTO)  Never use set(File,Key) see help
  Set(JSRV:FK_AUTO , JSRV:FK_AUTO) !use set(key,key)
  loop while Access:J_SRVREQ.PREVOUS() = Level:Benign ! Note: Previous to read High to Low
      if JSRV:Auto_GUID = loc:autoGUID ! good match
          ! check dates here
      ELSE
          BREAK
      END
  END

The CLEAR(JSRV:Record, 1) is debatable. Since the Key has only Guid & Date it will have No effect.

If you were to add Fields to the Key then they would start with High values which is probably logical since this uses Previous. A likely Field to add would be Service Time so you would also want that high. Without any Clear(Record, ?) you are leaving them at their last value so likely buggy

You should search your code for all Set(File,Key) and fix them.

SET(file,key)

Specifies physical record order processing and positions to the first record which contains values matching the values in the component fields of the key.

NOTE: This form is rarely used and is only useful if the file has been physically sorted in the key order. A common mistake is to use this form when SET(key,key) is the actual form desired

SET(key,key)

Specifies keyed sequence processing and positions to the first or last record which contains values matching the values in the component fields of the key. Both key parameters must be the same.

6

Oh yeah :winking_face_with_tongue: Should probably read it properly hey?