Finding latest object

Mark HHP

This is related to my other post about timestamp/date help.

I need to pull a report of contract renewals, using QuoteLineItem BOs (which belong to a Quote BO)

So what I need to pull is:

All Clients with their last QuoteLineItem appearing within a 3 month period. So if I were doing the next renewal (I do it monthly) I would need to see any clients with their LAST QuoteLineItem appearing in June, July or September.

I thought of creating a separate BO called ContractRenewal and then making the QuoteLineItem = a line in the ContractRenewal or something but how do you tell it to find the QuoteLineItem with the latest date?

Or is there a better way?

BLOMASKY

I have NO idea if there is a better way, but I would try the following (I am not sure where the "date" field is, whether in the quote or the quoteLineItem)

FIND Clients WHERE EXISTS Quotes WHERE (DATE_ADD(QuoteLine.TheDate, 91) > CURRENT_TIMESTAMP AND QuoteLines.ob_Quotes.ID = Quotes.ID AND Quotes.ob_Clients.ID = Clients.ID)

Now, if TheDate is in the Quote and not the QuoteLines then you can remove the reference to QuoteLines.

Of course, there might be a better way

Bruce

Mark HHP

Thanks Bruce. I hate time/date things. I always struggle.

To help my understanding:

Is the 91 adding 3 months?
What is the Quotes_ob? What does it mean?

Mark HHP

Also there is a Quote.QuoteDate set to dd/MM/yyyy as well as a QuoteLineItem.Issue set as a date attribute to MMMM yyyy. The QuoteLineItem.Issue is what we refer to, as Clients book in Feb 2013, March 2015 etc and that's what we look at when we do Contract Renewals.

How would you find out which is the last QuoteLineItem in a Quote? I understand (kinda) that we're looking at the QuoteLineItem within 3 months of current date but that doesn't necessarily mean they are the last QuoteLineItem in the Quote. But how I determine that is the question...or does yours determine that and I'm missing it? I tried running a variation some spelling changes but it returned nothing.

BLOMASKY

I assume you have a relationship between clients and Quotes and the Quotes.ob_Clients is my naming style (thank you Mark for you help!). Since each quote is "O"wned "By" a Clients record so ob_Clients. and your QuoteLines are owned by Quotes.

Since we are finding records that are within the last 91 days (yes, adding 91 days is adding 3 months to the date) it does not matter if that was the last one or not.

Did I miss any questions?

Bruce

Mark HHP

Yeah, I'm specifically looking for any QuoteLineItems where that is the LAST QuoteLineItem within a Quote within the next 3 months. The end of the contract, so to speak. This will help find any within next three months but not necessarily the last month of the contract.

Am I making sense?