Author Topic: Selecting based on last value in a multivalue  (Read 3581 times)

Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Selecting based on last value in a multivalue
« on: June 19, 2014, 03:17:27 PM »
Problem:
My application (Prelude) has a QUOTE file and QUOTE-LOG file.  The log tracks every instance the quote was entered into and/or changed.  QUOTE-LOG's key is the same as QUOTE.  The remaining fields in Q-L are multivalues.  I mention both files because I will need to "tie" fields together between the two (derive).

For each quote I'm interested in testing whether the last multivalue in field QUOTE-LOG > ACTION is SAV.  For a given quote, SAV could exist many times in ACTION, but I only care when it's the last value.  For those records only I'll be interested in displaying corresponding date/time fields, etc.

Can this be done with derived fields or am I better of writing code?  Ultimately I want to present a query or report that contains quotes in the SAV state.

Chris W.

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 224
  • Tom Pellitieri - Toledo, Ohio
Re: Selecting based on last value in a multivalue
« Reply #1 on: June 20, 2014, 06:34:30 AM »
You can use the DCOUNT function in your /FD Derived Value on the attribute in question.  Specifically:

/FD QUOTE-LOG MY.LAST.ACTION

Field Pos.Subpos: 0
Derived Field:  (<2,DCOUNT(<2>,@VM)>)

I use similar items to get the last A/R Transaction Date.

Chris Weitzel

  • Professional
  • ***
  • Posts: 30
Re: Selecting based on last value in a multivalue
« Reply #2 on: June 20, 2014, 12:24:58 PM »
Just what I was looking for.  I knew there was "a way" to do that, but couldn't remember.

Thanks, Santa.  ;)