What’s the best way to compare two records against each other in a saved search? Basically a case when this record has this value and a different record has a different value then do logic. They are both in the same custom record

@Zack We have a search set up that sounds like is similar to what you're looking for. It's a purchase order to bill variance search. It is designed to pull any bill that is entered to a PO where the bill amount, rate or qty is any different than what's on the PO. It looks like this:

That’s not quite the way I need this. Basically I have a custom record that stores tasks. You could have a task called Design and one called Install. I need to be able to say if Design status is done, then check to see if Install status is In Progress.

are the two records linked together in any way?

@scottvonduhn They both have the same project record linked to them

Further question: is this to drive logic in a script, or does it need to be displayed in a saved search in the UI? I can envision doing this in a script without too much hassle.

@scottvonduhn I want it to be a script, but I am being challenged to use it for a scheduled workflow, so that the end user can modify it.

The issue i can see is that no single saved search is going to be able to combine the two unrelated records into the same line. The one-to-many (parent-child) relationship from Tasks to a Project is a problem for saved searches since you can't do arbitrary join conditions, you can only retrieve linked records. One way you might resolve that: you could create two new custom Project fields, called Design Task and Install Task, and in whatever code is creating the tasks, add code to update the relevant project field with the task internal id. Or, if they are created manually, create a User Event to populate those fields on the Project. Then, you will be able to combine values from both record into the same line in a saved search. Another possibility: the new Analytics Workbooks might actually work for this without additional custom fields, though I have only played with them briefly, and have no idea if you can use them for a scheduled workflow. If so, you might be able to define the joins to get fields from both tasks at the same time. I have done something like that with the

N/query

script module once before, but can't find the code I used.