I have a BAQ that is displaying parts from new SO’s then have been manufactured in the last year, my Boss wants it to pull either the last time it finished manufacturing or if its an active Job now, Im pulling the data by the JobCompletionDate field in JobHead and using a calculated field max(JobCompletionDate), ut I need to get it do display blank if its an active Job or if not then display the Last Completion Date (max(JobCompletionDate)), not sure if I should use a case here and even then im not sure of how to get that to work, any help would be much appreciated
I think I would do this with a couple of subqueries. Since I am no pro here, I do this in steps. First I build a query that returns just the JobCompletionDate (along with the company, Job number, and asm seq for keys). Once that shows the right data, set it aside and build up a query that returns just the active jobs (along with the same keying fields). Once both queries separately return what they are supposed to, then make a third query to join them both. I would keep JobHead as the source and show only the matching values from each subquery. That should give you the blanks you need.
Good luck!
@StephenW As @NateS says two sub queries for JobHead grouped on company and part that are basically the same. Completed would be jobcompleted = true. Active for us would be released = true and completed = false.
Then in a calculated field in the main query either a case or a coalesce to get the first not null or blank if they are both null.