2

On a visual-force page of a workorder record, I've to display list of workorders created for the same location where the createddate is within the last 30 days of the creationdate of the current record.

For ex: If the current record was created on Jan 31st for a location A, I want to display all the workorders that were created from Jan 1st to 31st for location A. This is the query I've but it obviously displays records created in last 30 days but not the records created in last 30 days of createddate of the current record. How can I modify my query?

CWOs=[SELECT Id, Status, location.Name, createddate FROM WorkOrder WHERE location.Name  = : getwo().location.Name and id != : getwo().id and recordtype.name =  'Child' and createddate = LAST_N_DAYS:30 ORDER BY CreatedDate asc];
Improve this question

1 Answer 1

Reset to default
4

In SOQL, you can't compare two fields together... so you can't say "Field A is within 30 days of Field B". However, there is a workaround.

  1. Create a formula field, DaysOld__c (or whatever) that calculates the number of days between the record's created date and the associated Location's created date (I am assuming there is a lookup you can leverage for this).
  2. Then change your SOQL WHERE clause to read WHERE DaysOld__c <= 30

Voila!

Improve this answer
4
  • Hello Sebastian, Thanks for the suggestion. I guess formula field is the way to go. I don't care about the creation date of the location. All I have to do is create a date/time formula: createddate - 30 and modify my where clause to WHERE createddate > getwo().formulafield__c and createddate < getwo().createdadate
    – TonySF
    Commented Apr 13, 2020 at 17:18
  • That is only going to work one record at a time, but it should do it. Your formula, however, needs to return the "Last Order", then, not the created date of the location.
    – Sebastian Kessel
    Commented Apr 13, 2020 at 17:23
  • Yes. Thank you!
    – TonySF
    Commented Apr 13, 2020 at 17:28
  • If this helped you, feel free to mark the answer as accepted to others can benefit from your experience.
    – Sebastian Kessel
    Commented Apr 13, 2020 at 17:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .