Finding latest object

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Mark HHP
Posts: 387
Joined: Mon May 06, 2013 6:59 am
Location: Cape Town, South Africa

Finding latest object

Post by 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?
Mark
Running V5.7 (Build 1714) Linux Server. MySQL
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Finding latest object

Post by 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
Posts: 387
Joined: Mon May 06, 2013 6:59 am
Location: Cape Town, South Africa

Re: Finding latest object

Post by Mark HHP »

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

To help my understanding:

1. Is the 91 adding 3 months?
2. What is the Quotes_ob? What does it mean?
Mark
Running V5.7 (Build 1714) Linux Server. MySQL
Mark HHP
Posts: 387
Joined: Mon May 06, 2013 6:59 am
Location: Cape Town, South Africa

Re: Finding latest object

Post by 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.
Mark
Running V5.7 (Build 1714) Linux Server. MySQL
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Finding latest object

Post by 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
Posts: 387
Joined: Mon May 06, 2013 6:59 am
Location: Cape Town, South Africa

Re: Finding latest object

Post by 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?
Mark
Running V5.7 (Build 1714) Linux Server. MySQL
Post Reply