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];
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!

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
    Apr 13 '20 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. Apr 13 '20 at 17:23
  • Yes. Thank you!
    – TonySF
    Apr 13 '20 at 17:28
  • If this helped you, feel free to mark the answer as accepted to others can benefit from your experience. Apr 13 '20 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.